欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.NET6导入和导出EXCEL

程序员文章站 2022-03-06 13:57:54
使用npoi导入.xlsx遇到“eof in header”报错,网上找好很多方法,没解决,最后换成epplus.core导入。导出默认是.xls。npoi操作类:using npoi.hpsf;us...

使用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();

.NET6导入和导出EXCEL

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

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