asp.net core导出导入excel

使用npoi导入导出excel,已经封装好action可以直接调用

导出

效果图

使用方法

定义导出实体

class student
{
    public int id { get; set; }

    public string name { get; set; }

    public bool isbanzhang { get; set; }

    public static ienumerable<student> getstudents()
    {
        return new list<student>
        {
                new student{name="小强",id=1,isbanzhang=false},
        new student{name="小文",id=2,isbanzhang=true},
        new student{name="小黄",id=3,isbanzhang=false},
        new student{name="小刚",id=3,isbanzhang=false},
        };
    }
}

导出action

public iactionresult downexcel()
{
    var list = student.getstudents();
    var excelheper = new excelhelper();
    var config = new list<excelgridmodel> {
            new excelgridmodel{name="id",label="学号", align="left",},
            new excelgridmodel{name="name",label="姓名", align="left",},
            new excelgridmodel{name="isbanzhang",label="是否班长", align="left",},
    };

    var filename = "a.excel";

    return excelheper.exceldownload(list, config, filename);
}

主要代码

导入导出代码

/// <summary>
/// 描 述：npoi excel datatable操作类
/// </summary>
public class excelhelper
{
    #region excel导出方法 exceldownload

    /// <summary>
    /// excel导出下载
    /// </summary>
    /// <typeparam name="t">数据类型</typeparam>
    /// <param name="list">数据源</param>
    /// <param name="columnjson">导出列</param>
    /// <param name="filename">下载文件名称</param>
    public actionresult exceldownload<t>(ienumerable<t> list, ienumerable<excelgridmodel> columnlist, string filename)
    {
        var excelconfig = convertexcelgridmodeltoconfig(columnlist, filename);

        var rowdata = list.todatatable(columnlist.select(i => i.name));

        var stream = exportmemorystream(rowdata, excelconfig);
        return new filestreamresult(stream, mimetype.xls) { filedownloadname = jointxls(filename) };

    }

    /// <summary>
    /// excel导出下载
    /// </summary>
    /// <param name="dtsource">datatable数据源</param>
    /// <param name="excelconfig">导出设置包含文件名、标题、列设置</param>
    public actionresult exceldownload(datatable dtsource, ienumerable<excelgridmodel> columnlist, string filename)
    {
        var excelconfig = convertexcelgridmodeltoconfig(columnlist, filename);
        var stream = exportmemorystream(dtsource, excelconfig);
        return new filestreamresult(stream, mimetype.xls) { filedownloadname = jointxls(filename) };
    }

    /// <summary>
    /// excel导出下载
    /// </summary>
    /// <param name="dtsource">datatable数据源</param>
    /// <param name="excelconfig">导出设置包含文件名、标题、列设置</param>
    public actionresult exceldownload(datatable dtsource, excelconfig excelconfig, string filename)
    {
        var stream = exportmemorystream(dtsource, excelconfig);
        return new filestreamresult(stream, mimetype.xls) { filedownloadname = jointxls(filename) };
    }

    #endregion excel导出方法 exceldownload

    #region datatable导出到excel文件excelconfig中filename设置为全路径

    /// <summary>
    /// datatable导出到excel文件 export()
    /// </summary>
    /// <param name="dtsource">datatable数据源</param>
    /// <param name="excelconfig">导出设置包含文件名、标题、列设置</param>
    public string excelexporttofile(datatable dtsource, excelconfig excelconfig, string filename)
    {
        filename = jointxls(filename);
        using (memorystream ms = exportmemorystream(dtsource, excelconfig))
        {
            using (filestream fs = new filestream(filename, filemode.create, fileaccess.write))
            {
                byte[] data = ms.toarray();
                fs.write(data, 0, data.length);
                fs.flush();
            }
        }

        return filename;
    }


    #endregion datatable导出到excel文件excelconfig中filename设置为全路径

    #region datatable导出到excel的memorystream

    /// <summary>
    /// datatable导出到excel的memorystream export()
    /// </summary>
    /// <param name="dtsource">datatable数据源</param>
    /// <param name="excelconfig">导出设置包含文件名、标题、列设置</param>
    public memorystream exportmemorystream(datatable dtsource, excelconfig excelconfig)
    {
        var columnentity = excelconfig.columnentity;
        if (columnentity == null || columnentity.count == 0)
        {
            if (columnentity == null)
            {
                columnentity = new list<columnmodel>();
            }

            foreach (datacolumn dc in dtsource.columns)
            {
                columnentity.add(new columnmodel
                {
                    alignment = "center",
                    column = dc.columnname,
                    excelcolumn = dc.columnname
                });
            }
        }
        else
        {
            int colint = 0;
            for (int i = 0; i < dtsource.columns.count;)
            {
                datacolumn column = dtsource.columns[i];
                if (excelconfig.columnentity[colint].column != column.columnname)
                {
                    dtsource.columns.remove(column.columnname);
                }
                else
                {
                    i++;
                    if (colint < excelconfig.columnentity.count - 1)
                    {
                        colint++;
                    }
                }
            }
        }

        hssfworkbook workbook = new hssfworkbook();
        isheet sheet = workbook.createsheet();

        #region 右击文件 属性信息

        {
            documentsummaryinformation dsi = propertysetfactory.createdocumentsummaryinformation();
            dsi.company = "npoi";
            workbook.documentsummaryinformation = dsi;

            summaryinformation si = propertysetfactory.createsummaryinformation();
            si.author = ""; //填加xls文件作者信息
            si.applicationname = ""; //填加xls文件创建程序信息
            si.lastauthor = ""; //填加xls文件最后保存者信息
            si.comments = ""; //填加xls文件作者信息
            si.title = ""; //填加xls文件标题信息
            si.subject = "";//填加文件主题信息
            si.createdatetime = system.datetime.now;
            workbook.summaryinformation = si;
        }

        #endregion 右击文件 属性信息

        #region 设置标题样式

        icellstyle headstyle = workbook.createcellstyle();
        int[] arrcolwidth = new int[dtsource.columns.count];
        string[] arrcolname = new string[dtsource.columns.count];//列名
        icellstyle[] arrycolumstyle = new icellstyle[dtsource.columns.count];//样式表
        headstyle.alignment = horizontalalignment.center; // ------------------
        if (excelconfig.background != new color())
        {
            if (excelconfig.background != new color())
            {
                headstyle.fillpattern = fillpattern.solidforeground;
                headstyle.fillforegroundcolor = getxlcolour(workbook, excelconfig.background);
            }
        }
        ifont font = workbook.createfont();
        font.fontheightinpoints = excelconfig.titlepoint;
        if (excelconfig.forecolor != new color())
        {
            font.color = getxlcolour(workbook, excelconfig.forecolor);
        }
        font.boldweight = 700;
        headstyle.setfont(font);

        #endregion 设置标题样式

        #region 列头及样式

        icellstyle cheadstyle = workbook.createcellstyle();
        cheadstyle.alignment = horizontalalignment.center; // ------------------
        ifont cfont = workbook.createfont();
        cfont.fontheightinpoints = excelconfig.headpoint;
        cheadstyle.setfont(cfont);

        #endregion 列头及样式

        #region 设置内容单元格样式

        foreach (datacolumn item in dtsource.columns)
        {
            icellstyle columnstyle = workbook.createcellstyle();
            columnstyle.alignment = horizontalalignment.center;
            arrcolwidth[item.ordinal] = encoding.utf8.getbytes(item.columnname.tostring()).length;
            arrcolname[item.ordinal] = item.columnname.tostring();
            if (excelconfig.columnentity != null)
            {
                columnmodel columnentity = excelconfig.columnentity.find(t => t.column == item.columnname);
                if (columnentity != null)
                {
                    arrcolname[item.ordinal] = columnentity.excelcolumn;
                    if (columnentity.width != 0)
                    {
                        arrcolwidth[item.ordinal] = columnentity.width;
                    }
                    if (columnentity.background != new color())
                    {
                        if (columnentity.background != new color())
                        {
                            columnstyle.fillpattern = fillpattern.solidforeground;
                            columnstyle.fillforegroundcolor = getxlcolour(workbook, columnentity.background);
                        }
                    }
                    if (columnentity.font != null || columnentity.point != 0 || columnentity.forecolor != new color())
                    {
                        ifont columnfont = workbook.createfont();
                        columnfont.fontheightinpoints = 10;
                        if (columnentity.font != null)
                        {
                            columnfont.fontname = columnentity.font;
                        }
                        if (columnentity.point != 0)
                        {
                            columnfont.fontheightinpoints = columnentity.point;
                        }
                        if (columnentity.forecolor != new color())
                        {
                            columnfont.color = getxlcolour(workbook, columnentity.forecolor);
                        }
                        columnstyle.setfont(font);
                    }
                    columnstyle.alignment = getalignment(columnentity.alignment);
                }
            }
            arrycolumstyle[item.ordinal] = columnstyle;
        }
        if (excelconfig.isallsizecolumn)
        {
            #region 根据列中最长列的长度取得列宽

            for (int i = 0; i < dtsource.rows.count; i++)
            {
                for (int j = 0; j < dtsource.columns.count; j++)
                {
                    if (arrcolwidth[j] != 0)
                    {
                        int inttemp = encoding.utf8.getbytes(dtsource.rows[i][j].tostring()).length;
                        if (inttemp > arrcolwidth[j])
                        {
                            arrcolwidth[j] = inttemp;
                        }
                    }
                }
            }

            #endregion 根据列中最长列的长度取得列宽
        }

        #endregion 设置内容单元格样式

        int rowindex = 0;

        #region 表头及样式

        if (excelconfig.title != null)
        {
            irow headerrow = sheet.createrow(rowindex);
            rowindex++;
            if (excelconfig.titleheight != 0)
            {
                headerrow.height = (short)(excelconfig.titleheight * 20);
            }
            headerrow.heightinpoints = 25;
            headerrow.createcell(0).setcellvalue(excelconfig.title);
            headerrow.getcell(0).cellstyle = headstyle;
            sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, 0, dtsource.columns.count - 1)); // ------------------
        }

        #endregion 表头及样式

        #region 列头及样式

        {
            irow headerrow = sheet.createrow(rowindex);
            rowindex++;

            #region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出

            foreach (datacolumn column in dtsource.columns)
            {
                headerrow.createcell(column.ordinal).setcellvalue(arrcolname[column.ordinal]);
                headerrow.getcell(column.ordinal).cellstyle = cheadstyle;
                //设置列宽
                sheet.setcolumnwidth(column.ordinal, (arrcolwidth[column.ordinal] + 1) * 256);
            }

            #endregion 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
        }

        #endregion 列头及样式

        icellstyle datestyle = workbook.createcellstyle();
        idataformat format = workbook.createdataformat();
        datestyle.dataformat = format.getformat("yyyy-mm-dd");

        foreach (datarow row in dtsource.rows)
        {
            #region 新建表，填充表头，填充列头，样式

            if (rowindex == 65535)
            {
                sheet = workbook.createsheet();
                rowindex = 0;

                #region 表头及样式

                {
                    if (excelconfig.title != null)
                    {
                        irow headerrow = sheet.createrow(rowindex);
                        rowindex++;
                        if (excelconfig.titleheight != 0)
                        {
                            headerrow.height = (short)(excelconfig.titleheight * 20);
                        }
                        headerrow.heightinpoints = 25;
                        headerrow.createcell(0).setcellvalue(excelconfig.title);
                        headerrow.getcell(0).cellstyle = headstyle;
                        sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, 0, dtsource.columns.count - 1)); // ------------------
                    }
                }

                #endregion 表头及样式

                #region 列头及样式

                {
                    irow headerrow = sheet.createrow(rowindex);
                    rowindex++;

                    #region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出

                    foreach (datacolumn column in dtsource.columns)
                    {
                        headerrow.createcell(column.ordinal).setcellvalue(arrcolname[column.ordinal]);
                        headerrow.getcell(column.ordinal).cellstyle = cheadstyle;
                        //设置列宽
                        sheet.setcolumnwidth(column.ordinal, (arrcolwidth[column.ordinal] + 1) * 256);
                    }

                    #endregion 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
                }

                #endregion 列头及样式
            }

            #endregion 新建表，填充表头，填充列头，样式

            #region 填充内容

            irow datarow = sheet.createrow(rowindex);
            foreach (datacolumn column in dtsource.columns)
            {
                icell newcell = datarow.createcell(column.ordinal);
                newcell.cellstyle = arrycolumstyle[column.ordinal];
                string drvalue = row[column].tostring();
                setcell(newcell, datestyle, column.datatype, drvalue);
            }

            #endregion 填充内容

            rowindex++;
        }
        //using (memorystream ms = new memorystream())
        {
            memorystream ms = new memorystream();
            workbook.write(ms);
            ms.flush();
            ms.position = 0;
            return ms;
        }
    }

    #endregion datatable导出到excel的memorystream


    #region 设置表格内容

    private void setcell(icell newcell, icellstyle datestyle, type datatype, string drvalue)
    {
        switch (datatype.tostring())
        {
            case "system.string"://字符串类型
                newcell.setcellvalue(drvalue);
                break;

            case "system.datetime"://日期类型
                system.datetime datev;
                if (system.datetime.tryparse(drvalue, out datev))
                {
                    newcell.setcellvalue(datev);
                }
                else
                {
                    newcell.setcellvalue("");
                }
                newcell.cellstyle = datestyle;//格式化显示
                break;

            case "system.boolean"://布尔型
                bool boolv = false;
                bool.tryparse(drvalue, out boolv);
                newcell.setcellvalue(boolv);
                break;

            case "system.int16"://整型
            case "system.int32":
            case "system.int64":
            case "system.byte":
                int intv = 0;
                int.tryparse(drvalue, out intv);
                newcell.setcellvalue(intv);
                break;

            case "system.decimal"://浮点型
            case "system.double":
                double doubv = 0;
                double.tryparse(drvalue, out doubv);
                newcell.setcellvalue(doubv);
                break;

            case "system.dbnull"://空值处理
                newcell.setcellvalue("");
                break;

            default:
                newcell.setcellvalue("");
                break;
        }
    }

    #endregion 设置表格内容

    #region 从excel导入
    /// <summary>
    /// 读取excel ,默认第一行为标头
    /// </summary>
    /// <param name="strfilename">excel文档路径</param>
    /// <returns></returns>
    public datatable excelimport(string strfilename)
    {
        return excelimport(strfilename, 0);
    }

    /// <summary>
    /// 读取excel ,默认第一行为标头
    /// </summary>
    /// <param name="strfilename">excel文档路径</param>
    /// <param name="headerrowno">标题行号，以0开始</param>
    /// <returns></returns>
    public datatable excelimport(string strfilename, int headerrowno)
    {
        isheet sheet;
        using (filestream file = new filestream(strfilename, filemode.open, fileaccess.read))
        {
            if (strfilename.indexof(".xlsx", stringcomparison.ordinal) == -1)//2003
            {
                hssfworkbook hssfworkbook = new hssfworkbook(file);
                sheet = hssfworkbook.getsheetat(0);
            }
            else//2007
            {
                xssfworkbook xssfworkbook = new xssfworkbook(file);
                sheet = xssfworkbook.getsheetat(0);
            }
        }

        return readsheettodatatable(headerrowno, sheet);
    }

    /// <summary>
    /// 读取excel ,默认第一行为标头
    /// </summary>
    /// <param name="filestream">文件数据流</param>
    /// <returns></returns>
    public datatable excelimport(stream filestream, string flietype)
    {
        return excelimport(filestream, flietype, 0);
    }

    /// <summary>
    /// 读取excel ,默认第一行为标头
    /// </summary>
    /// <param name="filestream">文件数据流</param>
    /// <param name="headerrowno">标题行号从0开始</param>
    /// <returns></returns>
    public datatable excelimport(stream filestream, string flietype, int headerrowno)
    {
        datatable dt = new datatable();
        isheet sheet = null;
        if (flietype == ".xls")
        {
            hssfworkbook hssfworkbook = new hssfworkbook(filestream);
            sheet = hssfworkbook.getsheetat(0);
        }
        else
        {
            xssfworkbook xssfworkbook = new xssfworkbook(filestream);
            sheet = xssfworkbook.getsheetat(0);
        }
        return readsheettodatatable(headerrowno, sheet);
    }

    /// <summary>
    /// 从sheet中读取数据到datatable
    /// </summary>
    /// <param name="headerrowno">标题行号（数据行号=标题行号+1）</param>
    /// <param name="sheet"></param>
    /// <returns></returns>
    private datatable readsheettodatatable(int headerrowno, isheet sheet)
    {
        var dt = new datatable();
        irow headerrow = sheet.getrow(headerrowno);
        int cellcount = headerrow.lastcellnum;

        for (int j = 0; j < cellcount; j++)
        {
            icell cell = headerrow.getcell(j);
            dt.columns.add(cell.tostring());
        }

        for (int i = (headerrowno + 1); i <= sheet.lastrownum; i++)
        {
            irow row = sheet.getrow(i);
            datarow datarow = dt.newrow();

            for (int j = 0; j < cellcount; j++)
            {
                if (row.getcell(j) == null)
                {
                    continue;
                }

                icell cell = row.getcell(j);
                if (cell.celltype == celltype.error)
                {
                    throw new exception($"第{i + 1}行，列【{dt.columns[j].columnname}】,单元格格式错误");
                }
                else if (cell.celltype == celltype.numeric && dateutil.iscelldateformatted(cell))
                {
                    datarow[j] = cell.datecellvalue;
                }
                else if (cell.celltype == celltype.numeric)
                {
                    datarow[j] = cell.numericcellvalue;
                }
                else if (cell.celltype == celltype.blank)
                {
                    datarow[j] = "";
                }
                else
                {
                    datarow[j] = cell.stringcellvalue;
                }

                //datarow[j] = row.getcell(j).tostring();
            }

            bool existsvalue = false;
            foreach (datacolumn column in dt.columns)
            {
                if (datarow[column.columnname] == null || string.isnullorempty(datarow[column.columnname].tostring()))
                {
                    continue;
                }

                existsvalue = true;
                break;
            }
            if (existsvalue)
            {
                dt.rows.add(datarow);
            }
        }
        return dt;
    }

    #endregion 从excel导入

    #region rgb颜色转npoi颜色

    private short getxlcolour(hssfworkbook workbook, color systemcolour)
    {
        short s = 0;
        hssfpalette xlpalette = workbook.getcustompalette();
        npoi.hssf.util.hssfcolor xlcolour = xlpalette.findcolor(systemcolour.r, systemcolour.g, systemcolour.b);
        if (xlcolour == null)
        {
            if (npoi.hssf.record.paletterecord.standard_palette_size < 255)
            {
                xlcolour = xlpalette.findsimilarcolor(systemcolour.r, systemcolour.g, systemcolour.b);
                s = xlcolour.indexed;
            }
        }
        else
        {
            s = xlcolour.indexed;
        }

        return s;
    }

    #endregion rgb颜色转npoi颜色

    #region 设置列的对齐方式

    /// <summary>
    /// 设置对齐方式
    /// </summary>
    /// <param name="style"></param>
    /// <returns></returns>
    private horizontalalignment getalignment(string style)
    {
        switch (style)
        {
            case "center":
                return horizontalalignment.center;

            case "left":
                return horizontalalignment.left;

            case "right":
                return horizontalalignment.right;

            case "fill":
                return horizontalalignment.fill;

            case "justify":
                return horizontalalignment.justify;

            case "centerselection":
                return horizontalalignment.centerselection;

            case "distributed":
                return horizontalalignment.distributed;
        }
        return npoi.ss.usermodel.horizontalalignment.general;
    }

    #endregion 设置列的对齐方式


    #region 辅助方法
    /// <summary>
    /// 如果文件名中没有后缀名，增加文件后缀名
    /// </summary>
    /// <param name="filename"></param>
    /// <returns></returns>
    private string jointxls(string filename)
    {
        if (!filename.endswith(".xls"))
        {
            filename += ".xls";
        }

        return filename;
    }

    private excelconfig convertexcelgridmodeltoconfig(ienumerable<excelgridmodel> columnlist, string filename)
    {
        excelconfig excelconfig = new excelconfig();
        excelconfig.title = filename;
        excelconfig.titlefont = "微软雅黑";
        excelconfig.titlepoint = 15;
        excelconfig.isallsizecolumn = true;
        excelconfig.columnentity = new list<columnmodel>();
        foreach (excelgridmodel columnmodel in columnlist)
        {
            excelconfig.columnentity.add(new columnmodel()
            {
                column = columnmodel.name,
                excelcolumn = columnmodel.label,
                alignment = columnmodel.align,
            });
        }

        return excelconfig;
    }

    /// <summary>
    /// mime文件类型
    /// </summary>
    class mimetype
    {
        public const string xls = "application/ms-excel";
    }
    #endregion
}

配置类型

excelconfig

/// <summary>
/// 描 述：excel导入导出设置
/// </summary>
public class excelconfig
{
    
    /// <summary>
    /// 标题
    /// </summary>
    public string title { get; set; }
    /// <summary>
    /// 前景色
    /// </summary>
    public color forecolor { get; set; }
    /// <summary>
    /// 背景色
    /// </summary>
    public color background { get; set; }
    private short _titlepoint;
    /// <summary>
    /// 标题字号
    /// </summary>
    public short titlepoint
    {
        get
        {
            if (_titlepoint == 0)
            {
                return 20;
            }
            else
            {
                return _titlepoint;
            }
        }
        set { _titlepoint = value; }
    }
    private short _headpoint;
    /// <summary>
    /// 列头字号
    /// </summary>
    public short headpoint
    {
        get
        {
            if (_headpoint == 0)
            {
                return 10;
            }
            else
            {
                return _headpoint;
            }
        }
        set { _headpoint = value; }
    }
    /// <summary>
    /// 标题高度
    /// </summary>
    public short titleheight { get; set; }
    /// <summary>
    /// 列标题高度
    /// </summary>
    public short headheight { get; set; }
    private string _titlefont;
    /// <summary>
    /// 标题字体
    /// </summary>
    public string titlefont
    {
        get
        {
            if (_titlefont == null)
            {
                return "微软雅黑";
            }
            else
            {
                return _titlefont;
            }
        }
        set { _titlefont = value; }
    }
    private string _headfont;
    /// <summary>
    /// 列头字体
    /// </summary>
    public string headfont
    {
        get
        {
            if (_headfont == null)
            {
                return "微软雅黑";
            }
            else
            {
                return _headfont;
            }
        }
        set { _headfont = value; }
    }
    /// <summary>
    /// 是否按内容长度来适应表格宽度
    /// </summary>
    public bool isallsizecolumn { get; set; }
    /// <summary>
    /// 列设置
    /// </summary>
    public list<columnmodel> columnentity { get; set; }

}

columnmodel

/// <summary>
/// 描 述：excel导入导出列设置模型
/// </summary>
public class columnmodel
{
    /// <summary>
    /// 列名
    /// </summary>
    public string column { get; set; }
    /// <summary>
    /// excel列名
    /// </summary>
    public string excelcolumn { get; set; }
    /// <summary>
    /// 宽度
    /// </summary>
    public int width { get; set; }
    /// <summary>
    /// 前景色
    /// </summary>
    public color forecolor { get; set; }
    /// <summary>
    /// 背景色
    /// </summary>
    public color background { get; set; }
    /// <summary>
    /// 字体
    /// </summary>
    public string font { get; set; }
    /// <summary>
    /// 字号
    /// </summary>
    public short point { get; set; }
    /// <summary>
    ///对齐方式
    ///left 左
    ///center 中间
    ///right 右
    ///fill 填充
    ///justify 两端对齐
    ///centerselection 跨行居中
    ///distributed
    /// </summary>
    public string alignment { get; set; }
}

excelgridmodel

public class excelgridmodel
{
    /// <summary>
    /// 属性名称
    /// </summary>
    public string name { get; set; }
    /// <summary>
    /// excel列名
    /// </summary>
    public string label { get; set; }
    /// <summary>
    /// 宽度
    /// </summary>
    public string width { get; set; }
    /// <summary>
    /// 对其方式
    /// </summary>
    public string align { get; set; }
    /// <summary>
    /// 高度
    /// </summary>
    public string height { get; set; }
    /// <summary>
    /// 是否隐藏
    /// </summary>
    public string hidden { get; set; }
}

示例下载地址：