.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
{
    /// <summary>
    /// excel操作相关
    /// </summary>
    public class excelhelper
    {
        #region 读取excel到datatable

        /// <summary>
        /// 读取excel文件的内容
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetname">工作表名称</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文件流读取内容
        /// </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为空则默认获取第一个工作表)
            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>
        /// 读取工作表数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        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;
        }

        /// <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>
        /// 检查是否空数据行
        /// </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数据到路径
        /// </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)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！
            {
                workbook.write(fs);   //向打开的这个xls文件中写入mysheet表并保存。
            }
        }

        /// <summary>
        /// 生成excel数据到字节流
        /// </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)
        {
            //创建工作簿
            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;
        }

        /// <summary>
        /// 创建文档的基本信息(右击文件属性可看到的)
        /// </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
    }
}

epplus.core工具类：

//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的文件转换成datatable （excel的第一个sheet）
        /// </summary>
        /// <param name="fullfielpath">文件的绝对路径</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 默认取第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);
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        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 information
using 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();

源码：http://xiazai.jb51.net/202112/yuanma/consoleoperexcel_jb51.rar，使用vs2022 。

到此这篇关于.net6导入和导出excel的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。