在xls工作表中更新到数据库 [英] update in xls sheet to database

问题描述

你好，
我想执行三个步骤

step1)
将datatble导出到excel表.
此步骤完成.
Excel工作表中填充了数据表条目.

step2)
在此工作表中有列名状态.
我想检查一下状态
从后面的代码写在excel工作表中.
保存后可以由用户更改，也可以在导出时打开.

步骤3)
然后我想更新数据库中的状态.
上载该Excel工作表后.在浏览器中
我应该为第2步和第3步做什么?
请告诉我！
谢谢你！

hello,
i want to perform three steps

step1)
export datatble to excel sheet .
this step is completed.
excel sheet is fill with the datatable entry.

step2)
in this sheet there is column name status.
i want to check that status
from code behind which is written in excel sheet.
it can be change by user after save or open at time of export .

step 3)
and then after i want to update the that status in database.
after uploading that excel sheet. in browser
what should i do for the step 2 and step 3
plz tell me!
thank u!

推荐答案

要在步骤2中读取文件，可以使用以下代码:

to read the file in step 2 you can use the below piece of code:

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<<absolute path of the excel file including the file name>>;Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from <<sheet name in the file>>", connection );
DataTable dt = new DataTable();
da.Fill(dt);

现在您将所有数据存储在一个数据表中.
您可以验证数据并根据需要进行更改.

对于步骤3，您可以制定一个更新where子句中的status列的过程.
您可以从代码中为数据表的每一行调用该过程

Now you have all the data in a datatable.
You can validate the data and make changes as you want.

For step 3 you can make a procedure for updating the status column on a where clause.
You can call the procedure for every row of the datatable from the code

解决问题

字符串oledbConnectionString = string.Empty;
OleDbConnection conn = null;
oledbConnectionString ="Provider = Microsoft.Jet.OLEDB.4.0;数据源=" + filepath +;扩展属性= Excel 8.0;";
conn = new OleDbConnection(oledbConnectionString);
如果(conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand命令=新的OleDbCommand(从[sheet1

solve the problem

string oledbConnectionString = string.Empty;
OleDbConnection conn = null;
oledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filepath + "; Extended Properties=Excel 8.0;";
conn = new OleDbConnection(oledbConnectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand command = new OleDbCommand("Select * from [sheet1

"中选择*，conn);
OleDbDataAdapter objAdapter =新的OleDbDataAdapter();
objAdapter.SelectCommand =命令;
DataSet objDataset =新的DataSet();
objAdapter.Fill(objDataset，"Report");
conn.Close();
返回objDataset.Tables [0];

", conn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = command;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset, "Report");
conn.Close();
return objDataset.Tables[0];

这篇关于在xls工作表中更新到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！