C#Excel导入多条数据到数据库,如何快速导入？
引用Spire.Xls.dll就可以了
//创建Workbook对象并加载Excel文档
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"F:\ExportData.xlsx" , ExcelVersion.Version2013);
//获取第一张
sheetWorksheet sheet = workbook.Worksheets[0];
//设置range范围
CellRange range = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn];
//输出数据, 同时输出列名以及公式值
DataTable dt = sheet.ExportDataTable(range, true, true);
可参考原文C# 导入/导出Excel数据到数据库
public static DataSet GetDataSet(string FilePath)
{
string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbConnection conn = new OleDbConnection(OledbConnectionString);
ArrayList SheetNameList = new ArrayList();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = "";
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
SheetNameList.Add(SheetName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
DataSet dsExcel = new DataSet(); try
{
string strSql = ""; for (int i = 0; i < SheetNameList.Count; i++)
{
strSql = "select * from [" + (string)SheetNameList[i] + "]";
OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn);
DataTable dtExcel = new DataTable((string)SheetNameList[i]);
oleExcelDataAdapter.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch (Exception ex)
{
throw ex;
}
}这个方法就是从EXCEL文件读取数据转换为DataSet 下面一段时调用的时候要注意的，写要在服务器端保存一下上传的EXCEL，然后再调用 string filePath = "";
DataSet ds = new DataSet();
if (System.IO.Path.GetExtension(FileUpload1.FileName) != ".xls")
{
ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alter('hao')</script>");
return;
}
else
{
filePath = "D:\\" + FileUpload1.FileName;
FileUpload1.SaveAs(filePath);
string fileName = FileUpload1.FileName;
int start = fileName.IndexOf('.');
fileName = fileName.Substring(0, start);
ds = GetDataSet(filePath);
}
