#region NPOI导出Excel2007到xlsx
/// <summary>
/// 导出excel到xlsx
/// </summary>
/// <param name="dt">datatable数据源</param>
/// <param name="strHeaderText">表头</param>
/// <param name="columnNames">列头</param>
/// <returns></returns>
private static MemoryStream ExportXlsx(DataTable dt,
string strHeaderText,
string[] columnNames)
{
if (columnNames !=
null && columnNames.Length !=
dt.Columns.Count)
{
throw new ArgumentException(
"参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!");
}
XSSFWorkbook workbook =
new XSSFWorkbook();
ISheet sheet =
null;
string sheetName =
"Sheet1";
if (!
string.IsNullOrEmpty(dt.TableName))
{
sheetName =
dt.TableName;
}
sheet =
workbook.CreateSheet(sheetName);
int rowIndex =
0;
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(
0);
headerRow.HeightInPoints =
25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle =
workbook.CreateCellStyle();
headStyle.Alignment =
HorizontalAlignment.Center;
IFont font =
workbook.CreateFont();
font.FontHeightInPoints =
20;
font.Boldweight =
700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle =
headStyle;
sheet.AddMergedRegion(new CellRangeAddress(
0,
0,
0, dt.Columns.Count -
1));
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(
1);
ICellStyle headStyle =
workbook.CreateCellStyle();
headStyle.Alignment =
HorizontalAlignment.Center;
IFont font =
workbook.CreateFont();
font.FontHeightInPoints =
10;
font.Boldweight =
700;
headStyle.SetFont(font);
foreach (DataColumn column
in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[rowIndex]);
headerRow.GetCell(column.Ordinal).CellStyle =
headStyle;
rowIndex++
;
}
}
#endregion
#region 填充内容
rowIndex =
1;
foreach (DataRow row
in dt.Rows)
{
rowIndex++
;
XSSFRow dataRow =
(XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column
in dt.Columns)
{
string drValue =
row[column].ToString();
dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
}
}
#endregion
MemoryStream ms =
new MemoryStream();
workbook.Write(ms);
ms.Flush();
return ms;
}
/// <summary>
/// web导出
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="strFileName">表头</param>
/// <param name="columnNames">列头</param>
public static void ExportXlsxByWeb(DataTable dt,
string strFileName,
string[] columnNames)
{
HttpContext curContext =
HttpContext.Current;
MemoryStream ms =
ExportXlsx(dt, strFileName, columnNames);
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName + DateTime.Now.ToString(
"yyyyMMddhhmmssfff"), Encoding.UTF8) +
".xlsx");
curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
curContext.Response.ContentEncoding =
Encoding.UTF8;
curContext.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
curContext.Response.End();
}
#endregion
View Code
以上就是导出数据到excel2007的npoi代码
下面是导出操作
protected void lbtnExportExcel_Click(
object sender, EventArgs e)
{
try
{
DataSet dsData =
GetData();
if (dsData !=
null && dsData.Tables[
0].Rows.Count >
0)
{
string headerText =
"excel文件名";
DataTable tab = dsData.Tables[
0];
bool b = tab.Columns.Contains(
"新增列名");
if (!
b)
{
tab.Columns.Add("新增列名", Type.GetType(
"System.String"));
}
foreach (DataRow dr
in dsData.Tables[
0].Rows)
{
dr["ServiceAreaName"] =
"";
//调用方法进行新列的赋值
}
//表头和列头
string[] Colums = {
"ProductName",
"PhysicalCode",
"FLgsBar",
"PromoterMobile",
"Name",
"ServiceAreaName",
"IntegralNum",
"IntegralTime" };
string[] ColumNames =
{ Resources.Admin.Lable_GoodName, Resources.Admin.Lable_PromoterIntegralCode, Resources.Admin.Label_LogisticsCode, Resources.Admin.Lable_MobilePhone, Resources.Admin.Lable_FullName, Resources.Admin.Lable_Area, Resources.Admin.Lable_IntegralValue, Resources.Admin.Lable_IntegralTime };
NPOIHelper.ExportXlsxByWeb(GetTableData(Colums, tab), headerText, ColumNames);
}
else
{
Common.MessageBox.ShowFailTip(this, Resources.Admin.Tip_Fail_Empty_Search);
}
}
catch (Exception ex)
{
LogHelp.AddErrorLog("ERROR: ", ex.StackTrace,
this.Request);
}
}
View Code
将列头插入到数据源datatable表中
protected DataTable GetTableData(
string[] Columns, DataTable Data)
{
DataTable tab =
new DataTable();
foreach (
string col
in Columns)
{
tab.Columns.Add(col);
}
foreach (DataRow dr
in Data.Rows)
{
DataRow drt =
tab.NewRow();
foreach (
string col
in Columns)
{
drt[col] =
dr[col];
}
tab.Rows.Add(drt);
}
return tab;
}
View Code
附：
以上代码针对的是npoi2.2.1.0版本
据说Npoi导出大数据到excel的时候，性能不是特别好，推荐适用EPPLus进行大数据导入。
转载于:https://www.cnblogs.com/SmilePastaLi/p/6844808.html
相关资源：NPOI导出Excel（复杂表头）
