在导入之前都需要将上传的文件保存到服务器，所以避免重复的写这些代码，先贴出上传文件并保存到服务器指定路径的代码。
protected void btnImport_Click(object sender, EventArgs e){Random random = new Random();ImportClass Import = new ImportClass();//保存文件的虚拟路径string path = "Import/";//获取选择的文件名string fileName = FileUpload1.FileName;//获取文件扩展名称string fileExt = Path.GetExtension(fileName);//生成新文件名string newName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + random.Next(0, 9999).ToString();//获取指定虚机路径的物理路径string fullPath = HttpContext.Current.Server.MapPath(path);//上传文件保存路径string savePath = fullPath + newName + fileExt;//保存文件到服务器FileUpload1.SaveAs(savePath);try{//获取导入的数据DataSet ds = Import.ImportExcel(savePath);if (ds != null && ds.Tables.Count > 0){//这里可以写插入数据库的方法}}catch (Exception ex){throw;}}
第一种：OleDB
public DataSet ImportExcel(string filePath){DataSet ds = null;OleDbConnection conn;string strConn = string.Empty;string sheetName = string.Empty;try{// Excel 2003 版本连接字符串strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1;'";conn = new OleDbConnection(strConn);conn.Open();}catch{// Excel 2007 以上版本连接字符串strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";conn = new OleDbConnection(strConn);conn.Open();}//获取所有的 sheet 表DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });ds = new DataSet();for (int i = 0; i < dtSheetName.Rows.Count; i++){DataTable dt = new DataTable();dt.TableName = "table" + i.ToString();//获取表名sheetName = dtSheetName.Rows[i]["TABLE_NAME"].ToString();OleDbDataAdapter oleda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);oleda.Fill(dt);ds.Tables.Add(dt);}//关闭连接，释放资源conn.Close();conn.Dispose();return ds;}
除了读取过程不太灵活之外，这种读取方式还有个弊端就是，当 Excel 数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常，不过一般都能适用了。
第二种：Microsoft.Office.Interop.Excel.dll
public DataSet ImportExcel(string filePath){DataSet ds = null;DataTable dt = null;Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbook workbook = null;Microsoft.Office.Interop.Excel.Worksheet worksheet = null;Microsoft.Office.Interop.Excel.Sheets sheets = null;Microsoft.Office.Interop.Excel.Range range = null;object missing = System.Reflection.Missing.Value;try{if (excel == null){return null;}//打开 Excel 文件workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//获取所有的 sheet 表sheets = workbook.Worksheets;ds = new DataSet();for (int i = 1; i <= sheets.Count; i++){//获取第一个表worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);int rowCount = worksheet.UsedRange.Rows.Count;int colCount = worksheet.UsedRange.Columns.Count;int rowIndex = 1; //起始行为 1int colIndex = 1; //起始列为 1DataColumn dc;dt = new DataTable();dt.TableName = "table" + i.ToString();//读取列名for (int j = 0; j < colCount; j++){range = worksheet.Cells[rowIndex, colIndex + j];dc = new DataColumn();dc.DataType = Type.GetType("System.String");dc.ColumnName = range.Text.ToString().Trim();//添加列dt.Columns.Add(dc);}//读取行数据for (int k = 1; k < rowCount; k++){DataRow dr = dt.NewRow();for (int l = 0; l < colCount; l++){range = worksheet.Cells[rowIndex + k, colIndex + l];//使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值dr[l] = range.Text.ToString();}dt.Rows.Add(dr.ItemArray);}ds.Tables.Add(dt);}}catch (Exception ex){throw;}finally{workbook.Close();//关闭退出excel.Quit();//释放 COM 对象Marshal.ReleaseComObject(worksheet);Marshal.ReleaseComObject(workbook);Marshal.ReleaseComObject(excel);worksheet = null;workbook = null;excel = null;GC.Collect();}return ds;}
这种方法首先需要安装有 office Excel，并且是一个单元格一个单元格的读取，所以性能会比较差。
第三种：NPOI
public DataSet ImportExcel(string filePath){DataSet ds = null;try{//打开文件FileStream fileStream = new FileStream(filePath, FileMode.Open);XSSFWorkbook workbook = new XSSFWorkbook(fileStream);ISheet sheet = null;IRow row = null;ds = new DataSet();DataTable dt = null;for (int i = 0; i < workbook.Count; i++){dt = new DataTable();dt.TableName = "table" + i.ToString();//获取 sheet 表sheet = workbook.GetSheetAt(i);//起始行索引int rowIndex = sheet.FirstRowNum;//获取行数int rowCount = sheet.LastRowNum;//获取第一行IRow firstRow = sheet.GetRow(rowIndex);//起始列索引int colIndex = firstRow.FirstCellNum;//获取列数int colCount = firstRow.LastCellNum;DataColumn dc = null;//获取列名for (int j = colIndex; j < colCount; j++){dc = new DataColumn(firstRow.GetCell(j).StringCellValue);dt.Columns.Add(dc);}//跳过第一行列名rowIndex++;for (int k = rowIndex; k <= rowCount; k++){DataRow dr = dt.NewRow();row = sheet.GetRow(k);for (int l = colIndex; l < colCount; l++){if (row.GetCell(l) == null){continue;}dr[l] = row.GetCell(l).StringCellValue;}dt.Rows.Add(dr);}ds.Tables.Add(dt);}sheet = null;workbook = null;fileStream.Close();fileStream.Dispose();}catch (Exception ex){throw;}return ds;}
我这里用的是 .xlsx 格式的，也就是 Excel 2007 及以上版本，2003 版本的话方法类似，只是打开文件的操作类不一样。
// 2007 及以上版本
XSSFWorkbook xWorkbook = new XSSFWorkbook(fileStream);
// 2003 版本
HSSFWorkbook hWorkbook = new HSSFWorkbook(fileStream);
第四种：Aspose.Cells.dll
public DataSet ImportExcel(string filePath){DataSet ds = null;try{Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();//打开文件，参数可以是文件的路径，也可以直接传入一个文件流workbook.Open(filePath);//获取 sheet 表Aspose.Cells.Worksheets worksheets = workbook.Worksheets;Aspose.Cells.Worksheet worksheet = null;Aspose.Cells.Cells cells = null;ds = new DataSet();DataTable dt = null;int rowIndex = 0; //起始行int colIndex = 0; //起始列for (int i = 0; i < worksheets.Count; i++){dt = new DataTable();dt.TableName = "table" + i.ToString();worksheet = worksheets[i];//获取每个 sheet 表的所有单元格cells = worksheet.Cells;dt = cells.ExportDataTableAsString(rowIndex, colIndex, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);ds.Tables.Add(dt);}worksheets.Clear();worksheet = null;worksheets = null;workbook = null;}catch (Exception ex){throw;}return ds;}
这种方法操作比较方便，跟 OleDB 一样直接获取一个 sheet 表的数据，不用一个单元格一个单元格的获取。而且操作也比较灵活，你可以一行一行的获取数据，甚至一个单元格一个单元格的获取数据。
第五种：EPPlus
public DataSet ImportExcel(string filePath){DataSet ds = null;try{//打开文件FileStream fileStream = new FileStream(filePath, FileMode.Open);//读取文件流ExcelPackage package = new ExcelPackage(fileStream);//获取 sheet 表ExcelWorksheets worksheets = package.Workbook.Worksheets;ExcelWorksheet worksheet = null;ds = new DataSet();DataTable dt = null;for (int i = 1; i <= worksheets.Count; i++){dt = new DataTable();dt.TableName = "table" + i.ToString();worksheet = worksheets[i];//获取行数int rowCount = worksheet.Dimension.End.Row;//获取列数int colCount = worksheet.Dimension.End.Column;//起始行为 1int rowIndex = worksheet.Dimension.Start.Row;//起始列为 1int colIndex = worksheet.Dimension.Start.Column;DataColumn dc = null;for (int j = colIndex; j <= colCount; j++){dc = new DataColumn(worksheet.Cells[rowIndex, j].Value.ToString());dt.Columns.Add(dc);}rowIndex++;for (int k = rowIndex; k <= rowCount; k++){DataRow dr = dt.NewRow();for (int l = colIndex; l <= colCount; l++){if (worksheet.GetValue(k, l) == null){continue;}dr[l - 1] = worksheet.GetValue(k, l).ToString();}dt.Rows.Add(dr);}ds.Tables.Add(dt);}package.Dispose();worksheet = null;worksheets = null;package = null;fileStream.Close();fileStream.Dispose();}catch (Exception ex){throw;}return ds;}
这种方法读取 Excel 数据性能还不错，但是要注意的是，sheet、row、col 的起始值都是从 1 开始的。
若有收获，就点个赞吧
0 人点赞
