思享工具箱

NPOI导出EXCEL数据量大，分多个sheet显示数据

摘要：
//NPOIHelper类密钥代码usingSystem；使用System.Collections。通用的使用系统。Linq；使用系统。文本使用系统。数据使用系统。IO；使用NPOI.HSSF。用户模型；使用系统。收藏；使用系统。网状物名称pac

//NPOIHelper  类关键代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Collections;
using System.Web;

namespace Yikeba_htmlConverter
{
    public class NPOIHelper
    {
        /// <summary>
        /// 列名集合
        /// </summary>
        public static System.Collections.SortedList ListColumnsName;
        /// <summary>
        /// 导出Excel通过文件路径
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="filePath">文件路径</param>
        public static void ExportExcel(DataTable dtSource, string filePath)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列名！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, filePath);
        }

        /// <summary>
        /// 导出Excel通过流
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="excelStream">流操作</param>
        public static void ExportExcel(DataTable dtSource, Stream excelStream)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile(); //新建一个工作区域
            InsertRow(dtSource, excelWorkbook);//导入数据的方法
            SaveExcelFile(excelWorkbook, excelStream);//保存excel
        }

        /// <summary>
        /// 保存Excel文件通过路径
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(filePath, FileMode.Create);
                excelWorkBook.Write(file);
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {

            }
        }

        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        protected static HSSFWorkbook CreateExcelFile()
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            return hssfworkbook;
        }

        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        protected static void CreateHeader(HSSFSheet excelSheet)
        {
            int cellIndex = 0;
            //循环导出列
            foreach (System.Collections.DictionaryEntry de in ListColumnsName)
            {
                HSSFRow newRow = excelSheet.CreateRow(0);
                HSSFCell newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                cellIndex++;
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount); //创建第一个sheet
            CreateHeader(newsheet);//加载sheet的头信息
            foreach (DataRow dr in dtSource.Rows)  //循环DataTable里面的数据行
            {
                rowCount++;
                //超出10000条数据 创建新的工作簿
                if (rowCount == 10000)   //超过10000行数据就新建一个sheet
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount); //新建sheet
                    CreateHeader(newsheet);//新建sheet的头信息
                }

                HSSFRow newRow = newsheet.CreateRow(rowCount);//创建sheet的当前行
                InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);  //往当前行里面的每一列循环添加数据
            }
        }

        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                HSSFCell newCell = null;
                System.Type rowType = drSource[cellIndex].GetType();
                string drValue = drSource[cellIndex].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }
    }

    //排序实现接口 不进行排序 根据添加顺序导出
   
    public class NoSort : System.Collections.IComparer
    {
        public int Compare(object x, object y)
        {
            return -1;
        }
    }
}

//新建一个WebForm1页面测试

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Yikeba_htmlConverter;
using System.Xml;
using System.IO;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //导出数据列 实现根据添加顺序导出列
            NPOIHelper.ListColumnsName = new SortedList(new NoSort());
            NPOIHelper.ListColumnsName.Add("MemberName", "姓名");
            NPOIHelper.ListColumnsName.Add("username", "账号");
            NPOIHelper.ListColumnsName.Add("starttime", "登陆时间");
            NPOIHelper.ListColumnsName.Add("lasttime", "在线到期时间");
            NPOIHelper.ListColumnsName.Add("state", "状态");
            Response.Clear();
            Response.BufferOutput = false;
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("在线用户yyyyMMdd"));
            Response.AddHeader("Content-Disposition", "attachment;filename=DownExcel" + ".xls");
            Response.ContentType = "application/ms-excel";

            string str = "<table><tr><td>CSBT-120906-TG6</td><td>廖建军</td><td>LIAO</td><td>JIANJUN</td><td>男</td></tr><tr><td>CSBT-120906-TG7</td><td>王汉刚</td><td>WANG</td><td>HANGANG</td><td>男</td></tr></table>";
            str = "<?xml version=\"1.0\" standalone=\"yes\"?>" + str;

            //清洗
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(str);

            XmlNodeList nodes = doc.SelectNodes("table/tr");
            foreach (XmlElement node in nodes)
            {
                node.Attributes.RemoveAll(); //这里把Tr所有的属性都去掉

                for (int i = 0; i < node.ChildNodes.Count; i++) //列的循环,为每个列指定名称
                {
                    XmlNode n = doc.CreateNode(XmlNodeType.Element, "col" + i.ToString(), "");
                    n.InnerXml = node.ChildNodes[i].InnerXml;
                    node.ReplaceChild(n, node.ChildNodes[i]);
                }
            }
            //导入Dataset
            StringReader tr = new StringReader(doc.InnerXml);
            DataSet ds = new DataSet();
            ds.ReadXml(tr);
            DataTable dtSource = ds.Tables[0];
           // DataTable dtSource = new DataTable();
            NPOIHelper.ExportExcel(dtSource, Response.OutputStream);
            Response.Close();
        }
    }
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//辅助

http://www.cnblogs.com/atao/archive/2009/09/24/1572980.html  生成下拉列表
NPOI导出EXCEL数据量大，分多个sheet显示数据第1张
http://www.cnblogs.com/tonyqus/category/182110.html    更多更详细的execl用法 
http://www.cnblogs.com/atao/archive/2009/09/13/1565644.html  处理日期函数
http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html  NPOI使用Excel公式、 单元格操作、创建图形等高级功能

标签：#大数据#npoi#datatable#td处理 | 浏览：193 | 发布日期：

免责声明：文章转载自《NPOI导出EXCEL数据量大，分多个sheet显示数据》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Android：android studio快捷键大全(转）使用C#控制远程计算机的服务下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Spring Boot页面中select选项绑定数据库数据

　　在一个select框中，option往往是写好的，这样难以适应数据库中项目的动态变化，因此需要将option中的项目与数据库数据进行绑定，本项目使用Spring Boot进行开发，下面演示绑定方法。 　　首先在前端定义一个基本select框，在这里把第一项写好了，并显示为select框的默认项。 <select id="selectshijuan...

笔记--随时更新

一、电脑先安装SqlServer2008，后安装SqlServer2014 在通过公司封装的Lnd.BaseOper（其内部是通过OLEDBConnection实现的）去访问SqlServer2014的数据库会报错，因为如果先安装2008，再安装2014，导致sqlexpress实例占用了1433端口，2014的实例MSSQLSERVER只能使用动态端口。...

数据挖掘的几种经典方法论

 目录 CRISP-DM方法 SEMMA方法 DMAIC方法 AOSP-SM模型 5A模型 数据挖掘与分析的“七步法” 一、CRISP-DM方法（最流行） CRISP-DM - Data Science Project Managementhttps://www.datascience-pm.com/crisp-dm-2/ 在老外的这个网站中，我们可以了可...

CUSPARSE 第三章 CUSPARAE索引和数据格式

(纯属自学笔记，部分翻译，不会翻译的不翻译) 3.1 索引基本格式       该函数库支持 zero- and one-based 索引. The index base 是通过 cusparseIndexBase_t 选择, 且是一个独立参数，或者是矩阵形容器 cusparseMatDescr_t type的一部分 3.2 向量格式      略 3.3...

sqlserver超时时间已到

public DataTable GetProDataTable(string produceName, SqlParameter[] para) { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(pro...

[MyBatis]五分钟向MySql数据库插入一千万条数据 批量插入 用时5分左右

本例代码下载：https://files.cnblogs.com/files/xiandedanteng/InsertMillionComparison20191012.rar 我的数据库环境是mysql Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper 这个数据库是安装...

最新文章

随机推荐

思享工具箱导航