Shulou(Shulou.com)06/02 Report--

Net 6 how to import and export EXCEL, for this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Use NPOI import .xlsx encountered "EOF in header" error, online to find a lot of methods, did not solve, and finally changed to EPPlus.Core import.

Export defaults to .xls.

NPOI operation class: using NPOI.HPSF;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using System.Collections;using System.Data Namespace CommonUtils {/ Excel operation related / public class ExcelHelper {# region read Excel to DataTable / read the contents of the Excel file / worksheet name / 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) }} / read from Excel file stream / public static DataTable GetDataTable (Stream file, string contentType, string sheetName = null) {/ / load workbook 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 ("File format is not supported!") } / / get the worksheet (the first worksheet is obtained by default if sheetName is empty) var sheet = string.IsNullOrEmpty (sheetName)? WorkBook.GetSheetAt (0): workBook.GetSheet (sheetName); / / generate DataTable if (sheet! = null) return GetDataTable (sheet); else throw new Exception (string.Format ("worksheet {0} does not exist!", sheetName? ")) } / read worksheet data / private static DataTable GetDataTable (ISheet sheet) {IEnumerator rows = sheet.GetRowEnumerator (); DataTable dt = new DataTable (sheet.SheetName); / / default first non-empty behavior column header bool isTitle = true / / the header row index int titleRowIndex = 0; / / the first data row after the default column header, as the basis for the DataTable column type 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 } / / whether the blank line if (IsEmptyRow (row)) {if (isTitle) {titleRowIndex++;} continue } else {if (isTitle) {firstDataRow = sheet.GetRow (titleRowIndex + 1) / / the first data row after the default column header, as the basis for DataTable column type}} 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 } / get the cell value / 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 } / check whether the data row is empty / 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; } /// /// 检查是否空的Excel行 /// /// /// 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 /// /// 生成Excel数据到路径 /// /// /// public static void GenerateExcel(DataTable data, string path) { var workBook = GenerateExcelData(data); //保存至路径 using (FileStream fs = File.OpenWrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！ { workBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 } } /// /// 生成Excel数据到字节流 /// /// /// public static byte[] GenerateExcel(DataTable data) { var workBook = GenerateExcelData(data); using (MemoryStream ms = new MemoryStream()) { workBook.Write(ms); return ms.GetBuffer(); } } /// /// 生成DataTable到Excel /// /// /// private static IWorkbook GenerateExcelData(DataTable data) { //创建工作簿 var workBook = new HSSFWorkbook(); //生成文件基本信息 GenerateSummaryInformation(workBook); //创建工作表 var sheet = workBook.CreateSheet("Sheet1"); //创建标题行 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); } } //创建数据行 if (data != null && data.Rows.Count >

0) {for (int rowIndex = 1; rowIndex)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.