4

C#NPOI操作Excel，实现Excel数据导入导出（支持多个sheet） - ￡冷☆月№

 1 year ago
source link: https://www.cnblogs.com/wsk198726/p/17120104.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

C#NPOI操作Excel，实现Excel数据导入导出（支持多个sheet） - ￡冷☆月№ - 博客园

随笔 - 66,  文章 - 5,  评论 - 18,  阅读 -

73182

首先在项目中引用NPOI,通过管理NuGet程序包，搜索NPOI，选择版本2.3.0（支持.NET Framework 4.0）根据自己项目选择适当版本。

1.NpoiExcelHelper.cs  Npoi操作Excel类

ContractedBlock.gifExpandedBlockStart.gif

View Code

2.WEB项目的调用方法：

（1）数据导出到Excel中（支持根据DataTable数据及Excel自动分成多个Sheet）

 调用方法：

                int record = 500;
                DataTable data = CreateDataTable(record);

                string fileName = "客户明细_" + DateTime.Now.ToString("MMddhhmmss") + ".xls";
                string sheetName = "客户明细";

                string[,] columnFieldText = new[,]{
                    { "ID", "编号" },
                    { "Name", "姓名" },
                    { "CreateTime", "创建时间" }
                };

                //string[,] columnFieldText = null;

                var buf = NpoiExcelHelper.DataTableToExcel(data, fileName, columnFieldText, sheetName);

                Response.Buffer = true;
                Response.Clear();
                Response.ClearHeaders();
                Response.ClearContent();
                Response.Charset = "UTF8";
                Response.ContentEncoding = Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                string browser = Request.Browser.Browser;
                if (browser.Contains("InternetExplorer"))
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
                else
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

                Response.AddHeader("Content-Length", buf.Length.ToString());

                Response.Flush();
                Response.BinaryWrite(buf);                    
        /// <summary>
        /// 创建DataTable对象
        /// </summary>
        public DataTable CreateDataTable(int record)
        {
            //创建DataTable
            DataTable dt = new DataTable("NewDt");

            //创建自增长的ID列
            DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32"));
            dc.AutoIncrement = true;  //自动增加
            dc.AutoIncrementSeed = 1; //起始为1
            dc.AutoIncrementStep = 1; //步长为1
            dc.AllowDBNull = false;  //非空

            //创建其它列表
            dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime")));

            DataRow dr;
            for (int i = 0; i < record; i++)
            {
                dr = dt.NewRow();
                dr["Name"] = "名字" + i.ToString();
                dr["CreateTime"] = DateTime.Now;
                dt.Rows.Add(dr);
            }
            return dt;
        }

（2）Excel中数据导入DataTable中（支持指定Sheet名称 / 多个数据格式一致的Shee）

                string fileName = "客户明细_0213023109.xls";
                string sheetName = "客户明细1";

                string[,] columnFieldText = new[,]{
                    { "ID", "编号" },
                    { "Name", "姓名" },
                    { "CreateTime", "创建时间" }
                };

                //string[,] columnFieldText = null;

                var dt = NpoiExcelHelper.ExcelToDataTable(Server.MapPath(fileName), columnFieldText, sheetName);

以上基本实现WEB通过NPOI操作Excel数据导入导出的功能。其他可自行研究。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK