• .NET使用NPOI组件将数据导出Excel


    、NPOI官方网站http://npoi.codeplex.com/

       可以到此网站上去下载最新的NPOI组件版本

    2、NPOI在线学习教程(中文版):

        http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

       感谢Tony Qu分享出NPOI组件的使用方法

    3、.NET调用NPOI组件导入导出Excel的操作类
      此NPOI操作类的优点如下:
       (1)支持web及winform从DataTable导出到Excel; 
       (2)生成速度很快; 
       (3)准确判断数据类型,不会出现身份证转数值等问题; 
       (4)如果单页条数大于65535时会新建工作表; 
       (5)列宽自适应;

    NPOI操作类

    Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->  1 using System;
    using System.Data;
    using System.Configuration;
    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.IO;
    using System.Text;
    using NPOI;
    using NPOI.HPSF;
    using NPOI.HSSF;
    using NPOI.HSSF.UserModel;
    using NPOI.HSSF.Util;
    using NPOI.POIFS;
    using NPOI.Util;  
    namespace PMS.Common
    {
        public class NPOIHelper
        {
            /// <summary>
            /// DataTable导出到Excel文件
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            /// <param name="strFileName">保存位置</param>
            public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
            {
                using (MemoryStream ms = Export(dtSource, strHeaderText))
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }

            /// <summary>
            /// DataTable导出到Excel的MemoryStream
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            public static MemoryStream Export(DataTable dtSource, string strHeaderText)
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.CreateSheet();

                #region 右击文件 属性信息
                {
                    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                    dsi.Company = "NPOI";
                    workbook.DocumentSummaryInformation = dsi;

                    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                    si.Author = "文件作者信息"; //填加xls文件作者信息
                    si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
                    si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
                    si.Comments = "作者信息"; //填加xls文件作者信息
                    si.Title = "标题信息"; //填加xls文件标题信息
                    si.Subject = "主题信息";//填加文件主题信息
                    si.CreateDateTime = DateTime.Now;
                    workbook.SummaryInformation = si;
                }
                #endregion

                HSSFCellStyle dateStyle = workbook.CreateCellStyle();
                HSSFDataFormat format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                //取得列宽
                int[] arrColWidth = new int[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                int rowIndex = 0;
                foreach (DataRow row in dtSource.Rows)
                {
                    #region 新建表,填充表头,填充列头,样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }

                        #region 表头及样式
                        {
                            HSSFRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(strHeaderText);

                            HSSFCellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = CellHorizontalAlignment.CENTER;
                            HSSFFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                            headerRow.Dispose();
                        }
                        #endregion


                        #region 列头及样式
                        {
                            HSSFRow headerRow = sheet.CreateRow(1);
                            HSSFCellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = CellHorizontalAlignment.CENTER;
                            HSSFFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            foreach (DataColumn column in dtSource.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                //设置列宽
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                            }
                            headerRow.Dispose();
                        }
                        #endregion

                        rowIndex = 2;
                    }
                    #endregion


                    #region 填充内容
                    HSSFRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        HSSFCell newCell = dataRow.CreateCell(column.Ordinal);

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                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.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }

                    }
                    #endregion

                    rowIndex++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;

                    sheet.Dispose();
                    //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                    return ms;
                }
            }

            /// <summary>
            /// 用于Web导出
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            /// <param name="strFileName">文件名</param>
            public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
            {
                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition",
                    "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

                curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
                curContext.Response.End();
            }

            /// <summary>读取excel
            /// 默认第一行为标头
            /// </summary>
            /// <param name="strFileName">excel文档路径</param>
            /// <returns></returns>
            public static DataTable Import(string strFileName)
            {
                DataTable dt = new DataTable();

                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                HSSFRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                for (int j = 0; j < cellCount; j++)
                {
                    HSSFCell cell = headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }

                    dt.Rows.Add(dataRow);
                }
                return dt;
            }
        }
    }

    4、NPOI操作类的调用方法
       DataTable dt_Grade = Tools.Data.GetDataTable(strSQL);
       filename += "绩效考核结果分数统计表";
       PMS.Common.NPOIHelper.ExportByWeb(dt_Grade, filename, filename+".xls");

    5、效果展示
       

    6、NPOI类库下载:http://files.cnblogs.com/dreamof/NPOI类库.rar

    信息来源:http://www.cnblogs.com/yongfa365/archive/2010/05/10/NPOI-MyXls-DataTable-To-Excel-From-Excel.html

    查看原文:http://www.cnblogs.com/dreamof/archive/2010/06/02/1750151.html

  • 相关阅读:
    4 Apr 18 软件开发目录 logging模块的使用 序列化(Json, Pickle) os模块
    3 Apr 18 内置函数 列表生成式与生成器表达式 模块的使用之import 模块的使用之from…import…
    2 Apr 18 三元表达式 函数递归 匿名函数 内置函数
    30 Mar 18 迭代器 生成器 面向过程的编程
    29 Mar 18 函数 有参、无参装饰器
    28 Mar 18 函数
    27 Mar 18 函数的参数
    26 Mar 18 函数介绍
    23 Mar 18 文件处理
    22 Mar 18 补充数据类型+字符编码+文件处理
  • 原文地址:https://www.cnblogs.com/lizihong/p/4320002.html
Copyright © 2020-2023  润新知