在导入之前都需要将上传的文件保存到服务器，所以避免重复的写这些代码，先贴出上传文件并保存到服务器指定路径的代码。
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; //起始行为 1 int colIndex = 1; //起始列为 1 DataColumn 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; //起始行为 1 int rowIndex = worksheet.Dimension.Start.Row; //起始列为 1 int 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 开始的。
内容来源于网络如有侵权请私信删除
- 还没有人评论，欢迎说说您的想法！
客服
