首先得找到几个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);