当前位置： 移动技术网 > IT编程>开发语言>.net > asp.net(C#)之NPOI"操作Excel

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

2018年12月26日  | 移动技术网IT编程  | 我要评论

火影忍者全集国语高清,51网贷网,占蕈釜钰

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 < 33333;="" i++)="" {="" var="" id="guid.newguid().tostring();" dt.rows.add(id,="" id,="" id,="" id,="" id,="" id,="" id,="" id,="" id,="" id);="" }="" excelhelper.x2007.tabletoexcelforxlsx(dt,="" path);="" downloadfile(path);="" }="" void="" downloadfile(string="" s_path)="" {="" system.io.fileinfo="" file="new" system.io.fileinfo(s_path);="" httpcontext.current.response.contenttype="application/ms-download" ;="" httpcontext.current.response.clear();="" httpcontext.current.response.addheader("content-type",="" "application/octet-stream");="" httpcontext.current.response.charset="utf-8" ;="" httpcontext.current.response.addheader("content-disposition",="" "attachment;filename=" + system.web.httputility.urlencode(file.name, system.text.encoding.utf8));
        httpcontext.current.response.addheader(" content-length",="" file.length.tostring());="" httpcontext.current.response.writefile(file.fullname);="" httpcontext.current.response.flush();="" httpcontext.current.response.clear();="" httpcontext.current.response.end();="" }="">


您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网