asp.net(C#)之NPOI"操作Excel

1.首先到网上下载"npoi.dll"，引用。

2.新建一个操作类“excelhelper.cs”：

using system.collections.generic;
using system.data;
using system.io;
using system.linq;
using npoi.hssf.usermodel;
using npoi.ss.usermodel;
using npoi.xssf.usermodel;

public class excelhelper
{
    public class x2003
    {
        #region excel2003
        /// 
        /// 将excel文件中的数据读出到datatable中(xls)
        /// 
        /// 
        /// 
        public static datatable exceltotableforxls(string file)
        {
            datatable dt = new datatable();
            using (filestream fs = new filestream(file, filemode.open, fileaccess.read))
            {
                hssfworkbook hssfworkbook = new hssfworkbook(fs);
                isheet sheet = hssfworkbook.getsheetat(0);

                //表头
                irow header = sheet.getrow(sheet.firstrownum);
                list columns = new list();
                for (int i = 0; i 
        /// 将datatable数据导出到excel文件中(xls)
        /// 
        /// 
        /// 
        public static void tabletoexcelforxls(datatable dt, string file)
        {
            hssfworkbook hssfworkbook = new hssfworkbook();
            isheet sheet = hssfworkbook.createsheet("test");

            //表头
            irow row = sheet.createrow(0);
            for (int i = 0; i 
        /// 获取单元格类型(xls)
        /// 
        /// 
        /// 
        private static object getvaluetypeforxls(hssfcell cell)
        {
            if (cell == null)
                return null;
            switch (cell.celltype)
            {
                case celltype.blank: //blank:
                    return null;
                case celltype.boolean: //boolean:
                    return cell.booleancellvalue;
                case celltype.numeric: //numeric:
                    return cell.numericcellvalue;
                case celltype.string: //string:
                    return cell.stringcellvalue;
                case celltype.error: //error:
                    return cell.errorcellvalue;
                case celltype.formula: //formula:
                default:
                    return "=" + cell.cellformula;
            }
        }
        #endregion
    }

    public class x2007
    {
        #region excel2007
        /// 
        /// 将excel文件中的数据读出到datatable中(xlsx)
        /// 
        /// 
        /// 
        public static datatable exceltotableforxlsx(string file)
        {
            datatable dt = new datatable();
            using (filestream fs = new filestream(file, filemode.open, fileaccess.read))
            {
                xssfworkbook xssfworkbook = new xssfworkbook(fs);
                isheet sheet = xssfworkbook.getsheetat(0);

                //表头
                irow header = sheet.getrow(sheet.firstrownum);
                list columns = new list();
                for (int i = 0; i 
        /// 将datatable数据导出到excel文件中(xlsx)
        /// 
        /// 
        /// 
        public static void tabletoexcelforxlsx(datatable dt, string file)
        {
            xssfworkbook xssfworkbook = new xssfworkbook();
            isheet sheet = xssfworkbook.createsheet("test");

            //表头
            irow row = sheet.createrow(0);
            for (int i = 0; i 
        /// 获取单元格类型(xlsx)
        /// 
        /// 
        /// 
        private static object getvaluetypeforxlsx(xssfcell cell)
        {
            if (cell == null)
                return null;
            switch (cell.celltype)
            {
                case celltype.blank: //blank:
                    return null;
                case celltype.boolean: //boolean:
                    return cell.booleancellvalue;
                case celltype.numeric: //numeric:
                    return cell.numericcellvalue;
                case celltype.string: //string:
                    return cell.stringcellvalue;
                case celltype.error: //error:
                    return cell.errorcellvalue;
                case celltype.formula: //formula:
                default:
                    return "=" + cell.cellformula;
            }
        }
        #endregion
    }

    public static datatable getdatatable(string filepath)
    {
        var dt = new datatable("xls");
        if (filepath.last()=='s')
        {
            dt = x2003.exceltotableforxls(filepath);
        }
        else
        {
            dt = x2007.exceltotableforxlsx(filepath);
        }
        return dt;
    }
}

3.程序后台主要代码：

using system;
using system.collections.generic;
using system.web;
using system.web.ui;
using system.web.ui.webcontrols;
using system.data;
using system.linq;
public partial class _default : system.web.ui.page 
{
    protected void page_load(object sender, eventargs e)
    {

    }
    protected void btn_read_03_click(object o, eventargs e)
    {
        var dt = excelhelper.getdatatable(server.mappath("~/xls_tmp/2003.xls"));
        g1.datasource = dt;
        g1.databind();
    }
    protected void btn_read_07_click(object o, eventargs e)
    {
        var dt = excelhelper.getdatatable(server.mappath("~/xls_tmp/2007.xlsx"));
        g1.datasource = dt;
        g1.databind();
    }
    protected void btn_import_03_click(object o, eventargs e)
    {
        var name = datetime.now.tostring("yyyymmddhhmmss") + new random(datetime.now.second).next(10000);
        var path = server.mappath("~/xls_down/" + name + ".xls");
        var dt = new system.data.datatable();
        var columns=enumerable.range(1, 10).select(d => new datacolumn("a"+d.tostring(), typeof(string))).toarray();
        dt.columns.addrange(columns);
        for (int i = 0; i  new datacolumn("a" + d.tostring(), typeof(string))).toarray();
        dt.columns.addrange(columns);
        for (int i = 0; i