NPOI导出Excel-白红宇

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

#region NPOI

using NPOI.HSSF.UserModel;

using NPOI.HSSF.Util;

using NPOI.SS.UserModel;

using System.Web;

using System.IO;

using System.Data;

using System.Collections;

using System.Web.UI.WebControls;

#endregion

/*========================================================================================

* 说明：导入、导出Excel

* 作者：李朝强

* 日期：2015/03/28

* =====================================================================================*/

namespace TiKu.Common.Office

{

public class Excel

{

#region ===============================================导出==================================

/// <summary>

/// 导出

/// </summary>

/// <param name="context">请求上下文</param>

/// <param name="fileName">导出文件的名</param>

/// <param name="stream">数据流</param>

public void Export(HttpContext context,

string fileName,

MemoryStream stream)

{

if (context.Request.Browser.Browser == "IE")

fileName = HttpUtility.UrlEncode(fileName);

context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);

context.Response.BinaryWrite(stream.ToArray());

}

/// <summary>

/// 导出

/// </summary>

/// <param name="context">上下文</param>

/// <param name="filename">导出的文件名</param>

/// <param name="data">数据</param>

public void Export(HttpContext context,

string filename,

string sheetName,

Hashtable hashColumnInfos,

DataTable data)

{

MemoryStream ms = null;

try

{

ms = ReadToExcel(data,

sheetName,

hashColumnInfos);

//执行导出

Export(context, filename, ms);

}

catch (Exception ex) { throw ex; }

finally

{

if (null != ms)

{

ms.Close();

ms.Dispose();

}

/// <summary>

/// 从Repeat控件中，导出Excel

/// </summary>

/// <param name="RepList">控件</param>

/// <param name="filename">文件名</param>

/// <param name="sheetName">工作表</param>

public void Export(Repeater RepList,

string filename,

string sheetName,

HttpContext context)

{

object data = RepList.DataSource;

if (data is DataTable)

{

Export(context,

filename,

sheetName,

null,

(DataTable)data);

return;

}

if (data is DataView)

{

return;

}

if (data is IList)

{

return;

}

#endregion

#region ================================================数据流========================================

/// <summary>

/// 把DataTable对象，读取到内存流中

/// </summary>

/// <param name="data">DataTable对象</param>

/// <param name="hashColumnInfos">（可选）列</param>

/// <returns></returns>

public MemoryStream ReadToExcel(DataTable data,

string sheetName,

Hashtable hashColumnInfos)

{

sheetName = string.IsNullOrEmpty(sheetName) ? string.Format("Sheet-{0:yyy/MM/dd}", DateTime.Now) : sheetName;

//0>内存流

MemoryStream ms = new MemoryStream();

//1>创建工作薄

IWorkbook __workbook = new HSSFWorkbook();

//2>创建工作表

ISheet __sheet = __workbook.CreateSheet(sheetName);

#region 单元格样式

//单元格样式

ICellStyle cellStyle = __workbook.CreateCellStyle();

cellStyle.FillPattern = FillPattern.SolidForeground;

cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;

//设置字体

IFont font = __workbook.CreateFont();

font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;

cellStyle.SetFont(font);

cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;

#endregion

#region 3>创建表头

//创建一行

IRow __header = __sheet.CreateRow(0);

if (null == hashColumnInfos)

{

for (int i = 0; i < data.Columns.Count; i++)

{

//创建单元格

ICell __cell = __header.CreateCell(i);

__cell.SetCellValue(data.Columns[i].ColumnName);

__sheet.SetColumnWidth(i, 20 * 256);

__cell.CellStyle = cellStyle;

}

else

{

int j = 0;

foreach (DictionaryEntry item in hashColumnInfos)

{

//创建单元格

ICell __cell = __header.CreateCell(j);

__cell.SetCellValue(item.Value.ToString());

__sheet.SetColumnWidth(j, 20 * 256);

__cell.CellStyle = cellStyle;

++j;

}

#endregion

#region 4>创建数据行

int c = 0;

for (int k = 0; k < data.Rows.Count; k++)

{

IRow __dataRow = __sheet.CreateRow(k + 1);

for (c = 0; c < data.Columns.Count; c++)

{

ICell iCell = __dataRow.CreateCell(c);

iCell.SetCellValue(data.Rows[k][data.Columns[c].ColumnName].ToString());

c++;

}

c = 0;

}

#endregion

//5>最后，写入内存流

__workbook.Write(ms);

return ms;

}

/// <summary>

/// 把IDataReader对象，读取到内存流中

/// </summary>

/// <param name="dataReader">IDataReader对象</param>

/// <param name="sheetName">工作表名称</param>

/// <param name="hashColumnInfos">列</param>

/// <returns></returns>

public MemoryStream ReadToExcel(IDataReader dataReader,

string sheetName,

Hashtable hashColumnInfos)

{

//0>内存流

MemoryStream ms = new MemoryStream();

//1>创建工作薄

IWorkbook __workbook = new HSSFWorkbook();

//2>创建工作表

ISheet __sheet = __workbook.CreateSheet(sheetName);

#region 3>表头

IRow __header = __sheet.CreateRow(0);

if (null == hashColumnInfos)

{

for (int i = 0; i < dataReader.FieldCount; i++)

{

ICell iHeaderCell = __header.CreateCell(i);

iHeaderCell.SetCellValue(dataReader.GetName(i));

}

else

{

int j = 0;

foreach (DictionaryEntry item in hashColumnInfos)

{

//创建单元格

ICell __cell = __header.CreateCell(j);

__cell.SetCellValue(item.Value.ToString());

++j;

}

#endregion

#region 4>数据行

int n = 1;

while (dataReader.Read())

{

IRow __dataRow = __sheet.CreateRow(n);

for (int c = 0; c < dataReader.FieldCount; c++)

{

ICell iCell = __dataRow.CreateCell(c);

iCell.SetCellValue((dataReader.GetValue(c) ?? "").ToString());

}

#endregion

//5>写入内存流

__workbook.Write(ms);

return ms;

}

#endregion

}