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

ASP.NET操作Excel

程序员文章站 2022-05-29 09:21:09
使用NPOI操作Excel,无需Office COM组件 部分代码来自于:https://docs.microsoft.com/zh-tw/previous-versions/ee818993(v=msdn.10)?redirectedfrom=MSDN using System.Data; usi ......

使用npoi操作excel,无需office com组件

部分代码来自于:https://docs.microsoft.com/zh-tw/previous-versions/ee818993(v=msdn.10)?redirectedfrom=msdn

using system.data;
using system.io;
using system.text;
using system.web;
using npoi.hssf.usermodel;
using npoi.ss.usermodel;

/// <summary>
/// 使用npoi操作excel,无需office com组件
/// 部分代码取自http://msdn.microsoft.com/zh-tw/ee818993.asp
/// </summary>
public class excelrender
{
    /// <summary>
    /// 根据excel列类型获取列的值
    /// </summary>
    /// <param name="cell">excel列</param>
    /// <returns></returns>
    private static string getcellvalue(icell cell)
    {
        if (cell == null)
            return string.empty;
        switch (cell.celltype)
        {
            case celltype.blank:
                return string.empty;
            case celltype.boolean:
                return cell.booleancellvalue.tostring();
            case celltype.error:
                return cell.errorcellvalue.tostring();
            case celltype.numeric:
            case celltype.unknown:
            default:
                return cell.tostring();//this is a trick to get the correct value of the cell. numericcellvalue will return a numeric value no matter the cell value is a date or a number
            case celltype.string:
                return cell.stringcellvalue;
            case celltype.formula:
                try
                {
                    hssfformulaevaluator e = new hssfformulaevaluator(cell.sheet.workbook);
                    e.evaluateincell(cell);
                    return cell.tostring();
                }
                catch
                {
                    return cell.numericcellvalue.tostring();
                } 
        }
    }

    /// <summary>
    /// 自动设置excel列宽
    /// </summary>
    /// <param name="sheet">excel表</param>
    private static void autosizecolumns(isheet sheet)
    {
        if (sheet.physicalnumberofrows > 0)
        {
            irow headerrow = sheet.getrow(0);

            for (int i = 0, l = headerrow.lastcellnum; i < l; i++)
            {
                sheet.autosizecolumn(i);
            }
        }
    }

    /// <summary>
    /// 保存excel文档流到文件
    /// </summary>
    /// <param name="ms">excel文档流</param>
    /// <param name="filename">文件名</param>
    private static void savetofile(memorystream ms, string filename)
    {
        using (filestream fs = new filestream(filename, filemode.create, fileaccess.write))
        {
            byte[] data = ms.toarray();

            fs.write(data, 0, data.length);
            fs.flush();

            data = null;
        }
    }

    /// <summary>
    /// 输出文件到浏览器
    /// </summary>
    /// <param name="ms">excel文档流</param>
    /// <param name="context">http上下文</param>
    /// <param name="filename">文件名</param>
    private static void rendertobrowser(memorystream ms, httpcontext context, string filename)
    {
        if (context.request.browser.browser == "ie")
            filename = httputility.urlencode(filename);
        context.response.addheader("content-disposition", "attachment;filename=" + filename);
        context.response.binarywrite(ms.toarray());
    }

    /// <summary>
    /// datareader转换成excel文档流
    /// </summary>
    /// <param name="reader"></param>
    /// <returns></returns>
    public static memorystream rendertoexcel(idatareader reader)
    {
        memorystream ms = new memorystream();

        using (reader)
        {
            using (iworkbook workbook = new hssfworkbook())
            {
                using (isheet sheet = workbook.createsheet())
                {
                    irow headerrow = sheet.createrow(0);
                    int cellcount = reader.fieldcount;

                    // handling header.
                    for (int i = 0; i < cellcount; i++)
                    {
                        headerrow.createcell(i).setcellvalue(reader.getname(i));
                    }

                    // handling value.
                    int rowindex = 1;
                    while (reader.read())
                    {
                        irow datarow = sheet.createrow(rowindex);

                        for (int i = 0; i < cellcount; i++)
                        {
                            datarow.createcell(i).setcellvalue(reader[i].tostring());
                        }

                        rowindex++;
                    }

                    autosizecolumns(sheet);

                    workbook.write(ms);
                    ms.flush();
                    ms.position = 0;
                }
            }
        }
        return ms;
    }

    /// <summary>
    /// datareader转换成excel文档流,并保存到文件
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="filename">保存的路径</param>
    public static void rendertoexcel(idatareader reader, string filename)
    {
        using (memorystream ms = rendertoexcel(reader))
        {
            savetofile(ms, filename);
        }
    }

    /// <summary>
    /// datareader转换成excel文档流,并输出到客户端
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="context">http上下文</param>
    /// <param name="filename">输出的文件名</param>
    public static void rendertoexcel(idatareader reader, httpcontext context, string filename)
    {
        using (memorystream ms = rendertoexcel(reader))
        {
            rendertobrowser(ms, context, filename);
        }
    }

    /// <summary>
    /// datatable转换成excel文档流
    /// </summary>
    /// <param name="table"></param>
    /// <returns></returns>
    public static memorystream rendertoexcel(datatable table)
    {
        memorystream ms = new memorystream();

        using (table)
        {
            using (iworkbook workbook = new hssfworkbook())
            {
                using (isheet sheet = workbook.createsheet())
                {
                    irow headerrow = sheet.createrow(0);

                    // handling header.
                    foreach (datacolumn column in table.columns)
                        headerrow.createcell(column.ordinal).setcellvalue(column.caption);//if caption not set, returns the columnname value

                    // handling value.
                    int rowindex = 1;

                    foreach (datarow row in table.rows)
                    {
                        irow datarow = sheet.createrow(rowindex);

                        foreach (datacolumn column in table.columns)
                        {
                            datarow.createcell(column.ordinal).setcellvalue(row[column].tostring());
                        }

                        rowindex++;
                    }
                    autosizecolumns(sheet);

                    workbook.write(ms);
                    ms.flush();
                    ms.position = 0;
                }
            }
        }
        return ms;
    }

    /// <summary>
    /// datatable转换成excel文档流,并保存到文件
    /// </summary>
    /// <param name="table"></param>
    /// <param name="filename">保存的路径</param>
    public static void rendertoexcel(datatable table, string filename)
    {
        using (memorystream ms = rendertoexcel(table))
        {
            savetofile(ms, filename);
        }
    }

    /// <summary>
    /// datatable转换成excel文档流,并输出到客户端
    /// </summary>
    /// <param name="table"></param>
    /// <param name="response"></param>
    /// <param name="filename">输出的文件名</param>
    public static void rendertoexcel(datatable table, httpcontext context, string filename)
    {
        using (memorystream ms = rendertoexcel(table))
        {
            rendertobrowser(ms, context, filename);
        }
    }

    /// <summary>
    /// excel文档流是否有数据
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <returns></returns>
    public static bool hasdata(stream excelfilestream)
    {
        return hasdata(excelfilestream, 0);
    }

    /// <summary>
    /// excel文档流是否有数据
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="sheetindex">表索引号,如第一个表为0</param>
    /// <returns></returns>
    public static bool hasdata(stream excelfilestream, int sheetindex)
    {
        using (excelfilestream)
        {
            using (iworkbook workbook = new hssfworkbook(excelfilestream))
            {
                if (workbook.numberofsheets > 0)
                {
                    if (sheetindex < workbook.numberofsheets)
                    {
                        using (isheet sheet = workbook.getsheetat(sheetindex))
                        {
                            return sheet.physicalnumberofrows > 0;
                        }
                    }
                }
            }
        }
        return false;
    }

    /// <summary>
    /// excel文档流转换成datatable
    /// 第一行必须为标题行
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="sheetname">表名称</param>
    /// <returns></returns>
    public static datatable renderfromexcel(stream excelfilestream, string sheetname)
    {
        return renderfromexcel(excelfilestream, sheetname, 0);
    }

    /// <summary>
    /// excel文档流转换成datatable
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="sheetname">表名称</param>
    /// <param name="headerrowindex">标题行索引号,如第一行为0</param>
    /// <returns></returns>
    public static datatable renderfromexcel(stream excelfilestream, string sheetname, int headerrowindex)
    {
        datatable table = null;

        using (excelfilestream)
        {
            using (iworkbook workbook = new hssfworkbook(excelfilestream))
            {
                using (isheet sheet = workbook.getsheet(sheetname))
                {
                    table = renderfromexcel(sheet, headerrowindex);
                }
            }
        }
        return table;
    }

    /// <summary>
    /// excel文档流转换成datatable
    /// 默认转换excel的第一个表
    /// 第一行必须为标题行
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <returns></returns>
    public static datatable renderfromexcel(stream excelfilestream)
    {
        return renderfromexcel(excelfilestream, 0, 0);
    }

    /// <summary>
    /// excel文档流转换成datatable
    /// 第一行必须为标题行
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="sheetindex">表索引号,如第一个表为0</param>
    /// <returns></returns>
    public static datatable renderfromexcel(stream excelfilestream, int sheetindex)
    {
        return renderfromexcel(excelfilestream, sheetindex, 0);
    }

    /// <summary>
    /// excel文档流转换成datatable
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="sheetindex">表索引号,如第一个表为0</param>
    /// <param name="headerrowindex">标题行索引号,如第一行为0</param>
    /// <returns></returns>
    public static datatable renderfromexcel(stream excelfilestream, int sheetindex, int headerrowindex)
    {
        datatable table = null;

        using (excelfilestream)
        {
            using (iworkbook workbook = new hssfworkbook(excelfilestream))
            {
                using (isheet sheet = workbook.getsheetat(sheetindex))
                {
                    table = renderfromexcel(sheet, headerrowindex);
                }
            }
        }
        return table;
    }

    /// <summary>
    /// excel表格转换成datatable
    /// </summary>
    /// <param name="sheet">表格</param>
    /// <param name="headerrowindex">标题行索引号,如第一行为0</param>
    /// <returns></returns>
    private static datatable renderfromexcel(isheet sheet, int headerrowindex)
    {
        datatable table = new datatable();

        irow headerrow = sheet.getrow(headerrowindex);
        int cellcount = headerrow.lastcellnum;//lastcellnum = physicalnumberofcells
        int rowcount = sheet.lastrownum;//lastrownum = physicalnumberofrows - 1

        //handling header.
        for (int i = headerrow.firstcellnum; i < cellcount; i++)
        {
            datacolumn column = new datacolumn(headerrow.getcell(i).stringcellvalue);
            table.columns.add(column);
        }

        for (int i = (sheet.firstrownum + 1); i <= rowcount; i++)
        {
            irow row = sheet.getrow(i);
            datarow datarow = table.newrow();

            if (row != null)
            {
                for (int j = row.firstcellnum; j < cellcount; j++)
                {
                    if (row.getcell(j) != null)
                        datarow[j] = getcellvalue(row.getcell(j));
                }
            }

            table.rows.add(datarow);
        }

        return table;
    }

    /// <summary>
    /// excel文档导入到数据库
    /// 默认取excel的第一个表
    /// 第一行必须为标题行
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="insertsql">插入语句</param>
    /// <param name="dbaction">更新到数据库的方法</param>
    /// <returns></returns>
    public static int rendertodb(stream excelfilestream, string insertsql, dbaction dbaction)
    {
        return rendertodb(excelfilestream, insertsql, dbaction, 0, 0);
    }

    public delegate int dbaction(string sql, params idataparameter[] parameters);

    /// <summary>
    /// excel文档导入到数据库
    /// </summary>
    /// <param name="excelfilestream">excel文档流</param>
    /// <param name="insertsql">插入语句</param>
    /// <param name="dbaction">更新到数据库的方法</param>
    /// <param name="sheetindex">表索引号,如第一个表为0</param>
    /// <param name="headerrowindex">标题行索引号,如第一行为0</param>
    /// <returns></returns>
    public static int rendertodb(stream excelfilestream, string insertsql, dbaction dbaction, int sheetindex, int headerrowindex)
    {
        int rowaffected = 0;
        using (excelfilestream)
        {
            using (iworkbook workbook = new hssfworkbook(excelfilestream))
            {
                using (isheet sheet = workbook.getsheetat(sheetindex))
                {
                    stringbuilder builder = new stringbuilder();

                    irow headerrow = sheet.getrow(headerrowindex);
                    int cellcount = headerrow.lastcellnum;//lastcellnum = physicalnumberofcells
                    int rowcount = sheet.lastrownum;//lastrownum = physicalnumberofrows - 1

                    for (int i = (sheet.firstrownum + 1); i <= rowcount; i++)
                    {
                        irow row = sheet.getrow(i);
                        if (row != null)
                        {
                            builder.append(insertsql);
                            builder.append(" values (");
                            for (int j = row.firstcellnum; j < cellcount; j++)
                            {
                                builder.appendformat("'{0}',", getcellvalue(row.getcell(j)).replace("'", "''"));
                            }
                            builder.length = builder.length - 1;
                            builder.append(");");
                        }

                        if ((i % 50 == 0 || i == rowcount) && builder.length > 0)
                        {
                            //每50条记录一次批量插入到数据库
                            rowaffected += dbaction(builder.tostring());
                            builder.length = 0;
                        }
                    }
                }
            }
        }
        return rowaffected;
    }
}

弄一个dbheple 就可以完成该操作excel