导航：首页 > 开发技术 >

.NET6如何导入和导出EXCEL

发表于：2024-10-18 作者：千家信息网编辑

千家信息网最后更新 2024年10月18日，.NET6如何导入和导出EXCEL，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。使用NPOI导入.xlsx遇到"EOF in hea

千家信息网最后更新 2024年10月18日.NET6如何导入和导出EXCEL

.NET6如何导入和导出EXCEL，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

使用NPOI导入.xlsx遇到"EOF in header"报错，网上找好很多方法，没解决，最后换成EPPlus.Core导入。

导出默认是.xls。

NPOI操作类：

using NPOI.HPSF;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using System.Collections;using System.Data;namespace CommonUtils{    ///     /// Excel操作相关    /// 
    public class ExcelHelper    {        #region 读取Excel到DataTable        ///         /// 读取Excel文件的内容        /// 
        ///         /// 工作表名称        ///         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);            }        }        ///         /// 从Excel文件流读取内容        /// 
        ///         ///         ///         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为空则默认获取第一个工作表)            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 ?? ""));        }        ///         /// 读取工作表数据        /// 
        ///         ///         private static DataTable GetDataTable(ISheet sheet)        {            IEnumerator rows = sheet.GetRowEnumerator();            DataTable dt = new DataTable(sheet.SheetName);            //默认第一个非空行为列头            bool isTitle = true;            //标题行索引            int titleRowIndex = 0;            //默认列头后的第一个数据行，作为DataTable列类型的依据            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);//默认列头后的第一个数据行，作为DataTable列类型的依据                    }                }                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;        }        ///         /// 获取单元格值        /// 
        ///         ///         ///         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;        }        ///         /// 检查是否空数据行        /// 
        ///         ///         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 <= 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;        }        ///         /// 创建文档的基本信息(右击文件属性可看到的)        /// 
        ///         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    }}

EPPlus.Core工具类：

//using EPPlus.Extensions;using OfficeOpenXml;using System.Data;namespace CommonUtils{    ///     /// 使用  EPPlus 第三方的组件读取Excel    /// 
    public class EPPlusHelper    {        private static string GetString(object obj)        {            if (obj == null)                return "";            return obj.ToString();        }        ///         ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）        /// 
        /// 文件的绝对路径        ///         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 默认取第1个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);        }        ///         /// 将worksheet转成datatable        /// 
        /// 待处理的worksheet        /// 返回处理后的datatable        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)        {            //获取worksheet的行数            int rows = worksheet.Dimension.End.Row;            //获取worksheet的列数            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++)                {                    //默认将第一行设置为datatable的标题                    if (i == 1)                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));                    //剩下的写入datatable                    else                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);                }            }            return dt;        }    }}

使用：

// See https://aka.ms/new-console-template for more informationusing CommonUtils;using System.Data;Console.WriteLine("Hello, World!");try{    string dir = AppContext.BaseDirectory;    //2003    string fullName = Path.Combine(dir, "测试excel.xls");    DataTable dt = ExcelHelper.GetDataTable(fullName);    Console.WriteLine("Hello, World!" + dir);    //2007    string fullName2 = Path.Combine(dir, "测试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();