如何从ASP.NET(C#)中的Gridview行更新Excel文件? [英] How To Update an Excel File From a Gridview Row in ASP.NET (C#)?
本文介绍了如何从ASP.NET(C#)中的Gridview行更新Excel文件?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试一个简单的程序.当用户单击按钮时，将对存储的proc运行TextBox1的值，并返回客户ID.在Excel工作表中查找该客户ID.如果找到，则对应行的信息将绑定到Gridview.但是，一旦完成，我想将该行写到从中提取信息的excel文件的第二张纸中.代码有点混乱，因为我在此过程中尝试了一些不同的事情.
I am attempting a simple program. When the user clicks the button, the value of TextBox1 is run against a stored proc, returning a Customer ID. That Customer ID is looked up in an Excel sheet. If found, the information for the corresponding row is bound to a Gridview. However, once this is done, I would like to write THAT row to the second sheet of the excel file the info was pulled from. The code is a bit messy, as I'm trying a few different things in the process.
当前已保存Excel文件，但是当然，当前存在的行将被覆盖，因此总是只有一行.
Currently the Excel file is saved, but of course the row that is currently there, is overwritten, so there will always be just one row.
将Gridview(仅保留一行)中的数据更新(或插入)到Excel文件中的工作表的最干净，最简单的方法是什么?基本上，这将一遍又一遍地完成(当用户输入数字并单击事件按钮时)，因此第二张工作表(Sheet2)中的行将不断从Gridview中更新.任何帮助表示赞赏.如果这听起来/看起来很业余，我深表歉意.
What is the cleanest, easiest way to update (or insert) the data from a Gridview (only holding one row) to a sheet on an Excel file? Basically, this will be done over and over (when a user inputs a number and clicks the event button) so rows in the second sheet (Sheet2) will continually be updated from the Gridview. Any help is appreciated. I apologize if this sounds/looks amateurish.
protected void Button1_Click(object sender, EventArgs e)
{
if (TextBox1.Text != "")
{
DateTime saveNow = DateTime.Now;
long numCardNumber;
string strCardNumber; // Card number (stored as string)
char[] MyChar = { ';', '?' }; // array with 2 char
string customerID; //holds the customer ID returned by the stored proc CNBID
int CustID = 0; //Customer_id returned from stored proc CNBID
int incrementByOne; //Used to increment number of cards scanned Application level variable
//Create local label and assign text -> Site.Master lblTimeStamp
Label lblTimeStampLocal = (Label)Master.FindControl("lblTimeStamp");
Label lblScannedLocal = (Label)Master.FindControl("lblScanned");
//Cleanup input
strCardNumber = TextBox1.Text.TrimEnd(MyChar); // Trims end
strCardNumber = strCardNumber.TrimStart(MyChar); //Trims beginning
lbliMAG.Text = strCardNumber;
lblYourNumber.Visible = false; //if previously displayed, turns the label off
try //try and convert the string to a number (if valid numerical characters
{
numCardNumber = Convert.ToInt64(strCardNumber);
}
catch (FormatException) // thrown if input characters are not valid numeric
{
lblYourNumber.Visible = true;
GridView1.Visible = false;
lblQualify.Visible = false;
lblYourNumber.Text = "NOT A VALID CARD NUMBER!";
TextBox1.Focus();
return;
}
try //try and convert the string to a number (if valid numerical characters
{
string connectionInfo = Convert.ToString(ConfigurationManager.ConnectionStrings["SQLConn"]);
SqlConnection connection = new SqlConnection(connectionInfo);
connection.Open();
SqlCommand cmd = new SqlCommand("CNBID", connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@iMAG", SqlDbType.Char, 18); //@iMAG parameter
param.Direction = ParameterDirection.Input;
param.Value = strCardNumber; //Sets the parameter to the value of the scanned card (after trimmed characters)
try
{
CustID = (int)cmd.ExecuteScalar(); //returns int to customerID if card # found
}
catch
{
lblYourNumber.Visible = true;
GridView1.Visible = false;
lblQualify.Visible = false;
lblYourNumber.Text = "NOT A VALID CARD NUMBER!";
TextBox1.Focus();
return;
}
TextBox1.Text = ""; //resets TextBox1;
connection.Close();
}
catch (FormatException) // thrown if input characters are not valid numeric
{
lblYourNumber.Visible = true;
GridView1.Visible = false;
lblQualify.Visible = false;
lblYourNumber.Text = "NOT A VALID CARD NUMBER!";
TextBox1.Focus();
return;
}
//if (customerID != null)
if (CustID != 0)
{
lblCustID.Text = Convert.ToString(CustID); //assigns customerID to stat label
}
else
{
lblYourNumber.Visible = true;
GridView1.Visible = false;
lblQualify.Visible = false;
lblYourNumber.Text = "Customer Not Found!";
}
//string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
string Excel = Server.MapPath("App_Data\\CNB.xls");
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
//txtReturn.Text = connString; (//shows the connection string
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(connString);
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
string ExcelConn = "SELECT custid,first,last,addr1,addr2,city,state,zip FROM [Sheet1$] WHERE custid=" + CustID;
OleDbCommand cmd2 = new OleDbCommand(ExcelConn, oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter(); //OleDbDataAdapter.SelectCommand
oleda.SelectCommand = cmd2;
// Create a DataSet which will hold the data extracted from the worksheet.
DataSet ds = new DataSet();
//DataTable dt = new DataTable();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "Processed Customer");
if (ds.Tables[0].Rows.Count != 0)
{
// Bind the data to the GridView
lblCol1.Text = ds.Tables[0].Columns[0].ColumnName;
GridView1.DataSource = ds.Tables[0].Columns[0].ColumnName = " Customer ID ";
GridView1.DataSource = ds.Tables[0].Columns[1].ColumnName = " First Name ";
GridView1.DataSource = ds.Tables[0].Columns[2].ColumnName = " Last Name ";
GridView1.DataSource = ds.Tables[0].Columns[3].ColumnName = " Address 1 ";
GridView1.DataSource = ds.Tables[0].Columns[4].ColumnName = " Address 2 ";
GridView1.DataSource = ds.Tables[0].Columns[5].ColumnName = " City ";
GridView1.DataSource = ds.Tables[0].Columns[6].ColumnName = " Province / State ";
GridView1.DataSource = ds.Tables[0].Columns[7].ColumnName = " Postal Code / Zip ";
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
GridView1.Visible = true; // SHows the GridView after it populates
lblQualify.Visible = true;
lblQualify.Text = "Customer Qualifies!";
TextBox1.Focus();
incrementByOne = (int)Application["numberofTimesScanned"] + 1;
Application["numberofTimesScanned"] = incrementByOne;
lblTimeStampLocal.Text = "Last Scan: " + Convert.ToString(saveNow);
lblScannedLocal.Text = "Number Of Scans Completed: " + Convert.ToString(Application["numberofTimesScanned"]);
// Saves Excel document
var wb = new XLWorkbook();
wb.Worksheets.Add(ds);
wb.SaveAs(Server.MapPath("App_Data\\CustomersProcessed.xlsx"));
TextBox1.Focus();
try
{
cmd2 = new OleDbCommand("INSERT INTO [Sheet2$] (CustID, FirstName) VALUES ('1123', 'Homer')", oledbConn);
oleda.InsertCommand = cmd2;
}
catch (Exception error)
{
lblYourNumber.Text = error.Message;
}
}
else
{
lblQualify.Text = "Customer Does Not Qualify!";
GridView1.Visible = false; // Hides the Gridview
TextBox1.Focus();
}
}
catch (Exception error)
{
lblYourNumber.Visible = true;
GridView1.Visible = false;
lblQualify.Visible = false;
lblYourNumber.Text = error.Message;
}
finally
{
// Close connection
oledbConn.Close();
}
} //END IF
else
{
lblYourNumber.Visible = true;
lblYourNumber.Text = "NO CARD NUMBER SUBMITTED!";
}
}
推荐答案
我最终仅使用以下内容:
I ended up just using the following:
using (DbCommand command = oledbConn.CreateCommand())
{
command.CommandText = "INSERT INTO [Sheet2$] (custid, Fullname, Salutation) VALUES (" + CustID + ",\"John Smith\",\"John\")";
//connection.Open();
command.ExecuteNonQuery();
}
就成功了.:-)
这篇关于如何从ASP.NET(C#)中的Gridview行更新Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
