有关从EXCEL中导入数据到MS SQL数据表问题,常有D字段(数据库中为文本型)导入数据时,有部分行数据导入为空,主要原因是这些行数据值为数值数据,若在前面加入小瞥点“’”就可以导入,有时间是数值型导入了,而文字或字符没有导入,请问这种问题怎样解决?代码如下://向导入表中插入数据
string ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + FileName;
string strSQL = "SELECT A,B,C,D FROM [Sheet1$]";DataSet DsExcel = new DataSet();
OleDbConnection ConnExcel = new OleDbConnection(ConnectString);
OleDbDataAdapter DaExcel = new OleDbDataAdapter(strSQL, ConnExcel);
DaExcel.Fill(DsExcel, "S");SqlDataAdapter obImportDA = new SqlDataAdapter("Select A,B,C,D From TA_PurchaseOrderImport ", obConn);
SqlCommandBuilder obImportCB = new SqlCommandBuilder(obImportDA);
obImportDA.Fill(DsExcel, "D");
string ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + FileName;
string strSQL = "SELECT A,B,C,D FROM [Sheet1$]";DataSet DsExcel = new DataSet();
OleDbConnection ConnExcel = new OleDbConnection(ConnectString);
OleDbDataAdapter DaExcel = new OleDbDataAdapter(strSQL, ConnExcel);
DaExcel.Fill(DsExcel, "S");SqlDataAdapter obImportDA = new SqlDataAdapter("Select A,B,C,D From TA_PurchaseOrderImport ", obConn);
SqlCommandBuilder obImportCB = new SqlCommandBuilder(obImportDA);
obImportDA.Fill(DsExcel, "D");
{
DsExcel.Tables["D"].Rows.Add(aRow.ItemArray);
}
obImportDA.Update(DsExcel, "D");
先将excel转成xml文档,
再用linq to xml和linq to sql导入到mssql
网上有一个ExcelHelper类,可以把Excel表另存XML文件。
然后用LinqToXml分析这个XML文件,先得到每个WorkSheet,
再分析这个WorkSheet,拆分已合并的单元格,找出有效的单元格值,
逐个值填入DataTable,或EF实例,更新数据库。
insert into TA_PurchaseOrderImport(a,b,c,d) select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=d:\pp.xls',sheet1$)
http://topic.csdn.net/t/20050916/01/4273875.html