在项目中使用到遇到了需要将excel文件导入到数据库中，在此做个总结记录，防止后面人踩坑。
开发环境：VS2008+Win10
第一种方式：Office.Interop.Excel方式 数据量大时有点慢
public static DataTable getExcelDataKHFY(string fileName, DataTable dt)
{
string[] names = GetExcelTableName(fileName);
int ysCount = 0;//获取页数
string exname = "";
if (names.Length > 1)
{
Yx_Module.winChangeExcel win = new THKClient.Yx_Module.winChangeExcel(names);
win.ShowDialog();
if (win.IsReturn)
{
ysCount = int.Parse(win.Excelid) + 1;//页码
exname = win.ExName;//选择的页名
}
}
DataTable result = dt;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null) return null;
xlApp.Visible = false;
xlWorkbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Dictionary<int, Microsoft.Office.Interop.Excel.Range> rowrange = new Dictionary<int, Microsoft.Office.Interop.Excel.Range>();
if (ysCount != 0)
{
try
{
//动态页插入数据
int i = ysCount;
// Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];//根据页码
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[exname];//根据页名获取
string str = sheet.Name;
for (int rowIndex = 2; rowIndex < sheet.Cells.Rows.Count - 1; rowIndex++)
{
rowrange.Clear();
object[] parm = new object[result.Columns.Count];
for (int j = 0; j < result.Columns.Count; j++)
{
rowrange[j] = (Microsoft.Office.Interop.Excel.Range)(sheet.Cells[rowIndex, j + 1]);
parm[j] = rowrange[j].Value2 == null ? "" : rowrange[j].Value2.ToString();
}
rowrange.Clear();
if (parm[0].ToString().Trim().Length == 0)
{
break;
}
result.Rows.Add(parm);
}
}
catch (Exception ee)
{
throw;
}
}
else
{
for (int i = 1; i < xlWorkbook.Sheets.Count + 1; i++)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
string str = sheet.Name;
for (int rowIndex = 2; rowIndex < sheet.Cells.Rows.Count - 1; rowIndex++)
{
rowrange.Clear();
object[] parm = new object[result.Columns.Count];
for (int j = 0; j < result.Columns.Count; j++)
{
rowrange[j] = (Microsoft.Office.Interop.Excel.Range)(sheet.Cells[rowIndex, j + 1]);
parm[j] = rowrange[j].Value2 == null ? "" : rowrange[j].Value2.ToString();
}
rowrange.Clear();
if (parm[0].ToString().Trim().Length == 0)
{
break;
}
result.Rows.Add(parm);
}
}
}
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
}
finally
{
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
List<Process> excelProcesses = GetExcelProcesses();
if (excelProcesses.Count > 0)
{
KillTheExcel();//杀死进程
}
}
return result;
}
第二种方式：NPOI方式 此种方式最优
/// <summary>读取excel
/// 默认第一行为表头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
第三种方式：OLEDB方式 需要将程序设置为X86运行
image.png
public static DataTable ExcelToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
DataSet ds = new DataSet();
//using (OleDbConnection myConn = new OleDbConnection(connStr))
//{
// using (OleDbDataAdapter myCommand = new OleDbDataAdapter(sql_F, myConn))
// {
// myConn.Open();
// myCommand.Fill(ds);
// }
//}
//if (ds == null || ds.Tables.Count <= 0) return null;
//return ds.Tables[0];
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds.Tables[0];
}
