您好,登錄后才能下訂單哦!
.NET6如何導(dǎo)入和導(dǎo)出EXCEL,針對(duì)這個(gè)問(wèn)題,這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答,希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。
使用NPOI導(dǎo)入.xlsx遇到“EOF in header”報(bào)錯(cuò),網(wǎng)上找好很多方法,沒(méi)解決,最后換成EPPlus.Core導(dǎo)入。
導(dǎo)出默認(rèn)是.xls。
using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.Collections; using System.Data; namespace CommonUtils { /// <summary> /// Excel操作相關(guān) /// </summary> public class ExcelHelper { #region 讀取Excel到DataTable /// <summary> /// 讀取Excel文件的內(nèi)容 /// </summary> /// <param name="path"></param> /// <param name="sheetName">工作表名稱(chēng)</param> /// <returns></returns> public static DataTable GetDataTable(string path, string sheetName = null) { if (path.ToLower().EndsWith(".xlsx")) return EPPlusHelper.WorksheetToTable(path, sheetName); using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { return GetDataTable(file, sheetName); } } /// <summary> /// 從Excel文件流讀取內(nèi)容 /// </summary> /// <param name="file"></param> /// <param name="sheetName"></param> /// <returns></returns> public static DataTable GetDataTable(Stream file, string contentType, string sheetName = null) { //載入工作簿 IWorkbook workBook = null; if (contentType == "application/vnd.ms-excel") { workBook = new HSSFWorkbook(file); } else if (contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { workBook = new XSSFWorkbook(file); } else { try { workBook = new HSSFWorkbook(file); } catch { try { workBook = new XSSFWorkbook(file); } catch { throw new Exception("文件格式不被支持!"); } } } //獲取工作表(sheetName為空則默認(rèn)獲取第一個(gè)工作表) var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName); //生成DataTable if (sheet != null) return GetDataTable(sheet); else throw new Exception(string.Format("工作表{0}不存在!", sheetName ?? "")); } /// <summary> /// 讀取工作表數(shù)據(jù) /// </summary> /// <param name="sheet"></param> /// <returns></returns> private static DataTable GetDataTable(ISheet sheet) { IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(sheet.SheetName); //默認(rèn)第一個(gè)非空行為列頭 bool isTitle = true; //標(biāo)題行索引 int titleRowIndex = 0; //默認(rèn)列頭后的第一個(gè)數(shù)據(jù)行,作為DataTable列類(lèi)型的依據(jù) IRow firstDataRow = null; while (rows.MoveNext()) { IRow row = null; if (rows.Current is XSSFRow)//*.xlsx { row = (XSSFRow)rows.Current; } else//*.xls { row = (HSSFRow)rows.Current; } //是否空行 if (IsEmptyRow(row)) { if (isTitle) { titleRowIndex++; } continue; } else { if (isTitle) { firstDataRow = sheet.GetRow(titleRowIndex + 1);//默認(rèn)列頭后的第一個(gè)數(shù)據(jù)行,作為DataTable列類(lèi)型的依據(jù) } } DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { var cell = row.GetCell(i); if (isTitle) { var firstDataRowCell = firstDataRow.GetCell(i); if (firstDataRowCell != null || cell != null) { dt.Columns.Add(cell.StringCellValue.Trim()); } else { dt.Columns.Add(string.Format("未知列{0}", i + 1)); } } else { if (i > dt.Columns.Count - 1) break; dr[i] = GetCellValue(cell, dt.Columns[i].DataType); } } if (!isTitle && !IsEmptyRow(dr, dt.Columns.Count)) { dt.Rows.Add(dr); } isTitle = false; } return dt; } /// <summary> /// 獲取單元格值 /// </summary> /// <param name="cell"></param> /// <param name="colType"></param> /// <returns></returns> private static object GetCellValue(ICell cell, Type colType) { if (cell == null || cell.ToString().ToUpper().Equals("NULL") || cell.CellType == NPOI.SS.UserModel.CellType.Blank) return DBNull.Value; object val = null; switch (cell.CellType) { case NPOI.SS.UserModel.CellType.Boolean: val = cell.BooleanCellValue; break; case NPOI.SS.UserModel.CellType.Numeric: var cellValueStr = cell.ToString().Trim(); if (cellValueStr.IndexOf('-') >= 0 || cellValueStr.IndexOf('/') >= 0) { DateTime d = DateTime.MinValue; DateTime.TryParse(cellValueStr, out d); if (!d.Equals(DateTime.MinValue)) val = cellValueStr; } if (val == null) { decimal vNum = 0; decimal.TryParse(cellValueStr, out vNum); val = vNum; } break; case NPOI.SS.UserModel.CellType.String: val = cell.StringCellValue; break; case NPOI.SS.UserModel.CellType.Error: val = cell.ErrorCellValue; break; case NPOI.SS.UserModel.CellType.Formula: default: val = "=" + cell.CellFormula; break; } return val; } /// <summary> /// 檢查是否空數(shù)據(jù)行 /// </summary> /// <param name="dr"></param> /// <returns></returns> private static bool IsEmptyRow(DataRow dr, int colCount) { bool isEmptyRow = true; for (int i = 0; i < colCount; i++) { if (dr[i] != null && !dr[i].Equals(DBNull.Value)) { isEmptyRow = false; break; } } return isEmptyRow; } /// <summary> /// 檢查是否空的Excel行 /// </summary> /// <param name="row"></param> /// <returns></returns> private static bool IsEmptyRow(IRow row) { bool isEmptyRow = true; for (int i = 0; i < row.LastCellNum; i++) { if (row.GetCell(i) != null) { isEmptyRow = false; break; } } return isEmptyRow; } #endregion #region 生成DataTable到Excel /// <summary> /// 生成Excel數(shù)據(jù)到路徑 /// </summary> /// <param name="data"></param> /// <param name="path"></param> public static void GenerateExcel(DataTable data, string path) { var workBook = GenerateExcelData(data); //保存至路徑 using (FileStream fs = File.OpenWrite(path)) //打開(kāi)一個(gè)xls文件,如果沒(méi)有則自行創(chuàng)建,如果存在則在創(chuàng)建時(shí)不要打開(kāi)該文件! { workBook.Write(fs); //向打開(kāi)的這個(gè)xls文件中寫(xiě)入mySheet表并保存。 } } /// <summary> /// 生成Excel數(shù)據(jù)到字節(jié)流 /// </summary> /// <param name="data"></param> /// <param name="path"></param> public static byte[] GenerateExcel(DataTable data) { var workBook = GenerateExcelData(data); using (MemoryStream ms = new MemoryStream()) { workBook.Write(ms); return ms.GetBuffer(); } } /// <summary> /// 生成DataTable到Excel /// </summary> /// <param name="data"></param> /// <param name="path"></param> private static IWorkbook GenerateExcelData(DataTable data) { //創(chuàng)建工作簿 var workBook = new HSSFWorkbook(); //生成文件基本信息 GenerateSummaryInformation(workBook); //創(chuàng)建工作表 var sheet = workBook.CreateSheet("Sheet1"); //創(chuàng)建標(biāo)題行 if (data != null && data.Columns.Count > 0) { IRow row = sheet.CreateRow(0); for (int i = 0; i < data.Columns.Count; i++) { var cell = row.CreateCell(i); cell.SetCellValue(data.Columns[i].ColumnName); } } //創(chuàng)建數(shù)據(jù)行 if (data != null && data.Rows.Count > 0) { for (int rowIndex = 1; rowIndex <= data.Rows.Count; rowIndex++) { IRow row = sheet.CreateRow(rowIndex); for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++) { var cell = row.CreateCell(colIndex); var cellValue = data.Rows[rowIndex - 1][colIndex]; switch (data.Columns[colIndex].DataType.Name) { case "Byte": case "Int16": case "Int32": case "Int64": case "Decimal": case "Single": case "Double": double doubleVal = 0; if (cellValue != null && !cellValue.Equals(System.DBNull.Value)) { double.TryParse(cellValue.ToString(), out doubleVal); cell.SetCellValue(doubleVal); } break; case "DateTime": DateTime dtVal = DateTime.MinValue; if (cellValue != null && !cellValue.Equals(System.DBNull.Value)) { DateTime.TryParse(cellValue.ToString(), out dtVal); if (dtVal != DateTime.MinValue) { cell.SetCellValue(dtVal); } } break; default: if (cellValue != null && !cellValue.Equals(System.DBNull.Value)) { cell.SetCellValue(cellValue.ToString()); } break; } } } } return workBook; } /// <summary> /// 創(chuàng)建文檔的基本信息(右擊文件屬性可看到的) /// </summary> /// <param name="workBook"></param> private static void GenerateSummaryInformation(HSSFWorkbook workBook) { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Company"; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "Subject";//主題 si.Author = "Author";//作者 workBook.DocumentSummaryInformation = dsi; workBook.SummaryInformation = si; } #endregion } }
//using EPPlus.Extensions; using OfficeOpenXml; using System.Data; namespace CommonUtils { /// <summary> /// 使用 EPPlus 第三方的組件讀取Excel /// </summary> public class EPPlusHelper { private static string GetString(object obj) { if (obj == null) return ""; return obj.ToString(); } /// <summary> ///將指定的Excel的文件轉(zhuǎn)換成DataTable (Excel的第一個(gè)sheet) /// </summary> /// <param name="fullFielPath">文件的絕對(duì)路徑</param> /// <returns></returns> public static DataTable WorksheetToTable(string fullFielPath, string sheetName = null) { //如果是“EPPlus”,需要指定LicenseContext。 //EPPlus.Core 不需要指定。 //ExcelPackage.LicenseContext = LicenseContext.NonCommercial; FileInfo existingFile = new FileInfo(fullFielPath); ExcelPackage package = new ExcelPackage(existingFile); ExcelWorksheet worksheet = null; if (string.IsNullOrEmpty(sheetName)) { //不傳入 sheetName 默認(rèn)取第1個(gè)sheet。 //EPPlus 索引是0 //EPPlus.Core 索引是1 worksheet = package.Workbook.Worksheets[1]; } else { worksheet = package.Workbook.Worksheets[sheetName]; } if (worksheet == null) throw new Exception("指定的sheetName不存在"); return WorksheetToTable(worksheet); } /// <summary> /// 將worksheet轉(zhuǎn)成datatable /// </summary> /// <param name="worksheet">待處理的worksheet</param> /// <returns>返回處理后的datatable</returns> public static DataTable WorksheetToTable(ExcelWorksheet worksheet) { //獲取worksheet的行數(shù) int rows = worksheet.Dimension.End.Row; //獲取worksheet的列數(shù) int cols = worksheet.Dimension.End.Column; DataTable dt = new DataTable(worksheet.Name); DataRow dr = null; for (int i = 1; i <= rows; i++) { if (i > 1) dr = dt.Rows.Add(); for (int j = 1; j <= cols; j++) { //默認(rèn)將第一行設(shè)置為datatable的標(biāo)題 if (i == 1) dt.Columns.Add(GetString(worksheet.Cells[i, j].Value)); //剩下的寫(xiě)入datatable else dr[j - 1] = GetString(worksheet.Cells[i, j].Value); } } return dt; } } }
// See https://aka.ms/new-console-template for more information using CommonUtils; using System.Data; Console.WriteLine("Hello, World!"); try { string dir = AppContext.BaseDirectory; //2003 string fullName = Path.Combine(dir, "測(cè)試excel.xls"); DataTable dt = ExcelHelper.GetDataTable(fullName); Console.WriteLine("Hello, World!" + dir); //2007 string fullName2 = Path.Combine(dir, "測(cè)試excel.xlsx"); //dt = ExcelHelper.GetDataTable(fullName); //DataTable dt2 = ExcelHelper.GetDataTable(fullName2, "sheetf"); DataTable dt2 = ExcelHelper.GetDataTable(fullName2); string saveFullName = Path.Combine(dir, "save_excel.xls"); //ExcelHelper2.ExportExcelByMemoryStream(saveFullName, dt2); string saveFullName2 = Path.Combine(dir, "save_excel2.xls"); ExcelHelper.GenerateExcel(dt2, saveFullName2); Console.WriteLine("Hello, World!" + dir); } catch (Exception ex) { Console.WriteLine("ex:" + ex.Message); } Console.ReadKey();
關(guān)于.NET6如何導(dǎo)入和導(dǎo)出EXCEL問(wèn)題的解答就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,如果你還有很多疑惑沒(méi)有解開(kāi),可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。