如何用C#将Excel表中数据导入数据库

可用第三方来导数据，参考spire.xls for .net的数据导入方法：

//创建Workbook对象并加载Excel文档

Workbook workbook = new Workbook();

workbook.LoadFromFile(@"F:\ExportData.xlsx" , ExcelVersion.Version2013);

//获取第一张sheet

Worksheet sheet = workbook.Worksheets[0];

//设置range范围

CellRange range = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn];

//输出数据, 同时输出列名以及公式值

DataTable dt = sheet.ExportDataTable(range, true, true);

 代码参考自原文。

//引入oledb
using system.data.oledb;
//读取入口
public static datatable query(string excelpath, string sheetname)
{
oledbconnection conn = createconnection(excelpath);
conn.open();
datatable dt = new datatable();
dt = querybysheetname(conn, sheetname + "$");
conn.close();
return dt;
}
//定义连接串
internal static oledbconnection createconnection(string excelpath)
{
return new oledbconnection("provider=microsoft.ace.oledb.12.0;data source= " + excelpath + ";extended properties='excel 12.0;hdr=yes;imex=1;'");
}
//读取excel指定sheet的数据
private static datatable querybysheetname(oledbconnection conn, string sheetname)
{
string cmd = "select * from [" + sheetname + "]";
oledbdataadapter adapter = new oledbdataadapter(cmd, conn);
datatable dt = new datatable();
adapter.fill(dt);
return dt;
}
//从datatable插入数据库,具体取值插入代码略
for(int i=0;i

评论
0

9

加载更多

protected

DataSet
ImportExcel(string
filepath)
{
DataSet
ds
=
new
DataSet();
string
strCon
=
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
+
filepath
+
";Extended
Properties='Excel
8.0;HDR=Yes;IMEX=1;'";
using
(OleDbConnection
con
=
new
OleDbConnection(strCon))
{
con.Open();
OleDbDataAdapter
adapter
=
new
OleDbDataAdapter("select
*
from
[Sheet1$]",
con);
adapter.Fill(ds,
"dtSheet1");
OleDbDataAdapter
adapter2
=
new
OleDbDataAdapter("select
*
from
[Sheet2$]
",
con);
adapter2.Fill(ds,
"dtSheet2");
con.Close();
con.Dispose();
}
return
ds;
}
返回ds，你遍历生成
sql语句
就可以了。