.net中关于 DataTable利用NPOI导出到Excel

 首先得找到几个dll文件

NPOI.Util.dll

NPOI.POIFS.dll

NPOI.HSSF.dll

NPOI.HPSF.dll

NPOI.dll

NPOI.DDF.dll

用NPOI导数据的好处在于，在本机没有安装office97-03时依旧支持office格式导出！ 而其数据格式是根据数据库中列的前八条数据进行判断，在数据放入excel时对数据的格式进行判断，所以这里千万要注意，在页面遍历数据时千万不要在数据后+”Tostring（）“，此代码支持‘00001’格式 不会将数据显示时变成 ‘1’，身份证，手机等都会转换为文本格式。并在没到处60000行数据后自动添加sheet

下面是一段示例代码：

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

using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.DDF;

namespace NPOIUse
{
    public class NPOIUse
    {

          ///  NPOI导出数据，每6000行新建一个工作簿
       /// </summary>
       /// <param name="dt"> datatable</param>
       /// <returns>stream</returns>
        public static Stream StreamData(DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = null;
           HSSFCellStyle dateStyle = workbook.CreateCellStyle();
            int sheetcount = 0;
            sheet = workbook.CreateSheet ("sheet" + sheetcount);
            HSSFRow headerrow = sheet.CreateRow(0);
            foreach(DataColumn column in dt.Columns)
            {
                headerrow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);  //遍历datatable，将dataset的列名赋予sheet
            }
            int index = 1; //headerRow已经占用一行，所以从1开始
            foreach (DataRow row in dt.Rows)
            {
                HSSFRow datarow = sheet.CreateRow(index);
                if (index == 60000)// 数据超过6w新建一个工作簿
                {
                    index = 0;
                    sheetcount++;
                    sheet = workbook.CreateSheet("sheet" + sheetcount);

                     headerrow = sheet.CreateRow(0);
                }
                foreach (DataColumn column in dt.Columns)
                {
                    // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet
                  
                    HSSFCell newCell = datarow.CreateCell(column.Ordinal); //　　实例化cell
                   
                  string drValue = row[column].ToString();
                
                    switch (column.DataType.ToString())
                    {        
                         case "System.String"://字符串类型          
                         newCell.SetCellValue(drValue);
                         break;
                     case "System.DateTime"://日期类型

                        newCell.SetCellValue(drValue);
                        newCell.SetCellValue(dateV.ToShortDateString()); // 转换成段日期格式
                        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;
                      }
                 }
                index++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            headerrow = null;
            workbook = null;
            return ms;
        }
       /// <summary>
       /// Datatable数据填充如excel
       /// </summary>
       /// <param name="filename">excel文件名</param>
       /// <param name="dt"> 数据源</param>
        public static void DataTableToExcel(string filename, DataTable  dt)
        {
            MemoryStream ms = StreamData(dt) as MemoryStream; //as MemoryStream  as用作转换,此处可以省略
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");
            System.Web.HttpContext.Current.Response.AddHeader("content-length",ms.Length.ToString());
            Byte[] data = ms.ToArray(); //文件写入采用二进制流的方式。所以此处要转换为字节数组
            System.Web.HttpContext.Current.Response.BinaryWrite(data);
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.DisableKernelCache();
            ms = null;
        }

          }
}

 

 

在页面调用时只需要遍历datatable的列名和filename（导出的文件名）就可以了

 如：

    DataTable dt = ds.Tables[0];
            dt.Columns[0].ColumnName = "运单号";
            dt.Columns[1].ColumnName = "订单号";
            dt.Columns[2].ColumnName = "下单时间";
            dt.Columns[3].ColumnName = "合并时间";
            dt.Columns[4].ColumnName = "稽核时间";
            dt.Columns[5].ColumnName = "生成面单时间";
            dt.Columns[6].ColumnName = "配货时间";
            dt.Columns[7].ColumnName = "出库状态";

            string filename = "invoice";

            NPOIUse.NPOIUse.DataTableToExcel(filename ,dt);