ASP.NET操作Excel

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;
    }
}