最近发现有很多人对excel的导入导出需求很大。根据一些网上的资料加上个人的整理把项目中的DATABLE导出到excel实现具体代码如下:
第一步首先说下功能中的优点和实现的部分:
功能说明:
(1)
支持web及winform从DataTable导出到Excel。
(2)
生成速度很快。
(3)
准确判断数据类型,不会出现身份证转数值等上面提到的一系列问题。
(4)
如果单页条数大于65535时会新建工作表。
(5)
列宽自适应
第二步怎么在程序中调用:
//
列的名称
String[] headerTitle = { "列名1", "列名", "列名3", "列名4", "列名5", "列名6", "列名7", "列名8",
"列名9"
};
//
标题
String titleName =
"测试名称";
String fileName =
"文件名称";//文件名称
DataTable _Datable =
null;//数据集
NPOIHelper.ExportExcelByWeb(_Datable, headerTitle , titleName, fileName +
".xls");
第三步:具体实现代码:
using
System;
using System.Collections.Generic;
using
System.Linq;
using System.Text;
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 NPOI;
using
NPOI.HPSF;
using NPOI.HSSF;
using
NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using
NPOI.POIFS;
using NPOI.Util;
namespace
XuanRui.Common
{
///
<summary>
///
功能说明:
/// (1)
支持web及winform从DataTable导出到Excel。
///
(2) 生成速度很快。
/// (3)
准确判断数据类型,不会出现身份证转数值等上面提到的一系列问题。
/// (4)
如果单页条数大于65535时会新建工作表。
/// (5)
列宽自适应
///
</summary>
public class
NPOIHelper
{
#region
DataTable导出到Excel文件
///
<summary>
///
DataTable导出到Excel文件
///
</summary>
/// <param
name="dtSource">源DataTable</param>
/// <param
name="strHeaderText">表头文本</param>
/// <param
name="strFileName">保存位置</param>
public static void Export(DataTable dtSource, String[] headersTitle, string
strHeaderText, string
strFileName)
{
using (MemoryStream ms = Export(dtSource,headersTitle,
strHeaderText))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create,
FileAccess.Write))
{
byte[] data =
ms.ToArray();
fs.Write(data, 0,
data.Length);
fs.Flush();
}
}
}
#endregion
#region
DataTable导出到Excel的MemoryStream
///
<summary>
///
DataTable导出到Excel的MemoryStream
///
</summary>
/// <param
name="dtSource">源DataTable</param>
/// <param
name="headersTitle">列头名称</param>
/// <param
name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, String[] headersTitle,
string
strHeaderText)
{
HSSFWorkbook workbook = new
HSSFWorkbook();
HSSFSheet sheet =
workbook.CreateSheet();
#region
右击文件属性相关信息
{
DocumentSummaryInformation dsi =
PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company =
"公司名称“
workbook.DocumentSummaryInformation =
dsi;
SummaryInformation si =
PropertySetFactory.CreateSummaryInformation();
si.Author =
"zhaolf";
si.ApplicationName =
"创建程序信息";
si.LastAuthor =
"zhaolf";
si.Comments =
"软件部";
si.Title =
"报表导出";
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");
dateStyle.Alignment =
CellHorizontalAlignment.CENTER;
dateStyle.BorderBottom =
CellBorderType.THIN;
dateStyle.BorderTop =
CellBorderType.THIN;
dateStyle.BorderLeft =
CellBorderType.THIN;
dateStyle.BorderRight =
CellBorderType.THIN;
//取得列宽
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 index = 0; index < dtSource.Rows.Count;
index++)
{
for (int icount = 0; icount < dtSource.Columns.Count;
icount++)
{
int intTemp =
Encoding.GetEncoding(936).GetBytes(dtSource.Rows[index][icount].ToString()).Length;
if (intTemp >
arrColWidth[icount])
{
arrColWidth[icount] =
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;
//
填充列的背景色
headStyle.FillForegroundColor =
HSSFColor.GREY_50_PERCENT.index;
headStyle.FillPattern =
CellFillPattern.SOLID_FOREGROUND;
headStyle.BorderBottom =
CellBorderType.THIN;
headStyle.BorderTop =
CellBorderType.THIN;
headStyle.BorderLeft =
CellBorderType.THIN;
headStyle.BorderRight=
CellBorderType.THIN;
HSSFFont font =
workbook.CreateFont();
font.FontHeightInPoints =
10;
font.Boldweight =
700;
headStyle.SetFont(font);
for (int index = 0; index < headersTitle.Count();
index++)
{
headerRow.CreateCell(index).SetCellValue(headersTitle[index]);
headerRow.GetCell(index).CellStyle =
headStyle;
sheet.SetColumnWidth(index, (arrColWidth[index] + 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);
HSSFCellStyle headStyle =
workbook.CreateCellStyle();
headStyle.Alignment =
CellHorizontalAlignment.CENTER;
headStyle.BorderBottom =
CellBorderType.THIN;
headStyle.BorderTop =
CellBorderType.THIN;
headStyle.BorderLeft =
CellBorderType.THIN;
headStyle.BorderRight =
CellBorderType.THIN;
//
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
dataRow.GetCell(column.Ordinal).CellStyle =
headStyle;
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();
return
ms;
}
}
#region
用于Web导出到EXCEL
///
<summary>
///
用于Web导出
///
</summary>
/// <param
name="dtSource">源DataTable</param>
/// <param
name="headersTitle">列标题信息集合</param>
/// <param
name="strHeaderText">表头文本</param>
/// <param
name="strFileName">文件名</param>
public static void ExportExcelByWeb(DataTable dtSource, String[] headersTitle,
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, headersTitle,
strHeaderText).GetBuffer());
curContext.Response.End();
}
#endregion
#region
读取excel默认第一行为标头
///
<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;
}
#endregion
}
}
以上经过本人测试无误希望对有所要求的人给与帮助,谢谢光临及转载。
原文:http://www.cnblogs.com/tonglei/p/3571883.html