NOPI导出excel

来源：互联网发布：移动数据开关已关闭编辑：程序博客网时间：2024/05/22 06:14

在近在网上找了许多给予web的office空间，比如pageoffice，说是免费版的，赶脚很不错，但是用的时候却发现很多只能付费后才能使用。擦，这么忽悠人的，于是乎有学习了nopi的方式，但是网上大多用的都是HSSForwork 也就是说是office2003版本的写法，下面整理的代码是office2007版本以后的及XSSForwork，调用只需传入参数即可,希望对大家有用！

注意：调用的时候请在官方下载最新版本NOPI的包，并添加引用（下载地址：http://npoi.codeplex.com/releases）

1.C#代码：NOPIHelper.cs

/// <summary>
/// 完整的导出成Excel方法，带表样式
/// </summary>
/// <param name="dtSource">数据源 DataTable格式</param>
/// <param name="dtHeaderText">文件标题</param>
/// <param name="sheetName">文件工作区名称</param>
/// <param name="maxRows">每个工作区sheet的最大行数，-1 表示不限制</param>
/// <param name="backColor">表头字体权重</param>
/// <param name="tilteFontBlod">表头字体权重</param>
/// <param name="tilteFamily">表头字体字体大小风格</param>
/// <param name="tilteBorder">表头边框</param>
/// <param name="tilteHeight">表头行高</param>
/// <param name="tilteFontSize">表头字体大小</param>
/// <param name="tilteHorV">表头对其方式可选参数（1:左对齐 2:居中 3:右对齐）</param>
/// <param name="headerFontSize">列头字体大小</param>
/// <param name="headerFamily">列头字体样式</param>
/// <param name="headerFontBold">列头字体权重</param>
/// <param name="headerHorV">列头对其方式可选参数（1:左对齐 2:居中 3:右对齐）</param>
/// <param name="contentFontSize">单元格字体大小</param>
/// <param name="contentFamily">单元格字体风格</param>
/// <param name="contentFontBold">单元格字体权重</param>
/// <param name="contentHorV">单元格对其方式可选参数（1:左对齐 2:居中 3:右对齐）</param>
/// <param name="headerBorder">列头边框</param>
/// <param name="unitBorder">单元格边框</param>
/// <returns>MemoryStream</returns>
public MemoryStream OutToExcel(DataTable dtSource, String dtHeaderText, string sheetName, int maxRows, short tilteFontBlod, float tilteHeight,
FontFamily tilteFamily, short tilteFontSize, int tilteHorV, short headerFontBold, FontFamily headerFamily,
short headerFontSize, bool headerBorder, short contentFontSize, FontFamily contentFamily, short contentFontBold, int headerHorV, bool unitBorder, int contentHorV)
{
//创建一个Excel文件对象
XSSFWorkbook workbook = new XSSFWorkbook();
//创建第一个工作区
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(sheetName + "_1");
//创建列的样式
XSSFCellStyle cell = (XSSFCellStyle)workbook.CreateCellStyle();
//cell.SetFillBackgroundColor(new XSSFColor().GetARGBHex("").);
//创建数据格式
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
cell.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
//取得列宽保存在数组
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.UTF8.GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
//行数
int rowIndex = 1;
//工作区标识
int flagPage = 1;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (maxRows != -1)
{
if (rowIndex == maxRows || rowIndex == 1)
{
if (rowIndex != 1)
{
flagPage++;
sheet = (XSSFSheet)workbook.CreateSheet(sheetName + "_" + flagPage);
}
CreateTile(dtSource, arrColWidth, workbook, sheet, dtHeaderText, tilteFontBlod, tilteHeight, tilteFamily, tilteFontSize,
tilteHorV, headerFontBold, headerFontSize, headerFamily, headerHorV, headerBorder);
rowIndex = 2;
}
}
else if (rowIndex == 1 && maxRows == -1)
{
CreateTile(dtSource, arrColWidth, workbook, sheet, dtHeaderText, tilteFontBlod, tilteHeight, tilteFamily, tilteFontSize,
tilteHorV, headerFontBold, headerFontSize, headerFamily, headerHorV, headerBorder);

rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
XSSFCellStyle cellstyle = (XSSFCellStyle)workbook.CreateCellStyle();//样式

switch (contentHorV)
{
case 1: cellstyle.Alignment = HorizontalAlignment.Left;
break;
case 2: cellstyle.Alignment = HorizontalAlignment.Center;
break;
case 3: cellstyle.Alignment = HorizontalAlignment.Right;
break;
default:
cellstyle.Alignment = HorizontalAlignment.Center;
break;
}
if (unitBorder)
{
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
}

newCell.CellStyle = cellstyle;
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 = cell;//格式化显示
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.Dispose();
workbook.Clear();
workbook.Close();
return ms;
}

}
/// <summary>
/// 创建表头和列头
/// </summary>
/// <param name="dtSource"></param>
/// <param name="arrColWidth"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="dtHeaderText"></param>
/// <param name="tilteFontBlod"></param>
/// <param name="tilteHeight"></param>
/// <param name="tilteFamily"></param>
/// <param name="tilteFontSize"></param>
/// <param name="tilteHorV"></param>
/// <param name="headerFontBold"></param>
/// <param name="headerFontSize"></param>
/// <param name="headerFamily"></param>
/// <param name="headerHorV"></param>
/// <param name="headerBorder"></param>
private void CreateTile(DataTable dtSource, int[] arrColWidth, XSSFWorkbook workbook, XSSFSheet sheet, String dtHeaderText, short tilteFontBlod, float tilteHeight,
FontFamily tilteFamily, short tilteFontSize, int tilteHorV, short headerFontBold, short headerFontSize, FontFamily headerFamily, int headerHorV, bool headerBorder)
{
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = tilteHeight;
headerRow.CreateCell(0).SetCellValue(dtHeaderText);

XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();

headStyle.VerticalAlignment = VerticalAlignment.Center;

// headStyle.SetFillForegroundColor((XSSFColor)IndexedColors.Green);
//byte[] b = new byte[] { 248, 101, 101 };
//headStyle.
//headStyle.setFillPattern(XSSFCellStyle.);
//headStyle.setFillBackgroundXSSFColor(IndexedColors.RED.getIndex());
switch (tilteHorV)
{
case 1: headStyle.Alignment = HorizontalAlignment.Left;
break;
case 2: headStyle.Alignment = HorizontalAlignment.Center;
break;
case 3: headStyle.Alignment = HorizontalAlignment.Right;
break;
default:
headStyle.Alignment = HorizontalAlignment.Center;
break;
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = tilteFontSize;
font.Boldweight = tilteFontBlod;
font.SetFamily(tilteFamily);
headStyle.SetFont(font);
NPOI.SS.Util.CellRangeAddress reg = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1);

sheet.AddMergedRegion(reg);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headStyle.VerticalAlignment = VerticalAlignment.Center;
if (headerBorder)
{
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
}
switch (headerHorV)
{
case 1: headStyle.Alignment = HorizontalAlignment.Left;
break;
case 2: headStyle.Alignment = HorizontalAlignment.Center;
break;
case 3: headStyle.Alignment = HorizontalAlignment.Right;
break;
default:
headStyle.Alignment = HorizontalAlignment.Center;
break;
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = headerFontSize;
font.Boldweight = headerFontBold;
font.SetFamily(headerFamily);
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]) * 256);
}
}
#endregion

}

1 0