C# excel导入汇总
#region 导入1(Interop) /// <summary> /// 导入1(Interop) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataSet Interop(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; int colIndex = 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]; dr[l] = range.Text.ToString(); } dt.Rows.Add(dr); } ds.Tables.Add(dt); } } catch (Exception ex) { throw; } finally { workbook.Close(); excel.Quit(); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(excel); worksheet = null; workbook = null; excel = null; GC.Collect(); } return ds; } #endregion #region 导入2(Oledb) /// <summary> /// 导入2(Oledb) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataSet Oledb(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; } #endregion #region 导入3(NPOI) /// <summary> /// 导入3(NPOI) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataSet NPOI(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; } #endregion #region 导入4(Aspose) /// <summary> /// 导入4(Aspose) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataSet Aspose(string filePath) { DataSet ds = null; try { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath); ds = new DataSet(); DataTable dt = null; int rowIndex = 0; int colIndex = 0; for (int i = 0; i < workbook.Worksheets.Count; i++) { dt = new DataTable(); dt.TableName = "table" + i.ToString(); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i]; //获取每个sheet表的所有单元格 Aspose.Cells.Cells cells = worksheet.Cells; dt = cells.ExportDataTableAsString(rowIndex, colIndex, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true); ds.Tables.Add(dt); } } catch (Exception ex) { throw; } return ds; } #endregion #region 导入5(EPPlus) /// <summary> /// 导入5(EPPlus) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataSet EPPlus(string filePath) { DataSet ds = null; try { FileStream fileStream = new FileStream(filePath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite); ExcelPackage package = new ExcelPackage(fileStream); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; ExcelWorksheets worksheets = package.Workbook.Worksheets; ExcelWorksheet worksheet = null; ds = new DataSet(); DataTable dt = null; for (int i = 0; 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; int rowIndex = worksheet.Dimension.Start.Row; 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; } #endregion
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了