关于npoi1.2. 4 导出excel样式设置

最新推荐文章于 2024-04-24 16:59:15 发布

duan135

最新推荐文章于 2024-04-24 16:59:15 发布

阅读量294

点赞数

文章标签： npoi excel 数据导出

本文链接：https://blog.csdn.net/u013893505/article/details/25691593

版权

首先.aspx页面引用

protected void Button1_Click1(object sender, EventArgs e)
{
var matchList = HappyPoolMatchHelper.GetHappyPoolWare(LotteryId, wareNo);
DataTable MatchTable = ListToDataTable(matchList);
ExcelGameResult.RenderToExcel(MatchTable, Context, "" + lotteryName + ".xls");
}

其次 npoi 代码

public class ExcelGameResult
{
/// <summary>
/// 保存Excel文档流到文件
/// </summary>
/// <param name="ms">Excel文档流</param>
/// <param name="fileName">文件名</param>
private static void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();

fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
}
/// <summary>
/// 输出文件到浏览器
/// </summary>
/// <param name="ms">Excel文档流</param>
/// <param name="context">HTTP上下文</param>
/// <param name="fileName">文件名</param>
private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
{
if (context.Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray());
}
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();

using (table)
{
using (HSSFWorkbook workbook = new HSSFWorkbook())
{

using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
// handling header.
headerRow.HeightInPoints = 20;
sheet.CreateFreezePane(0, 1, 0, 1);

foreach (DataColumn column in table.Columns)
{
sheet.SetColumnWidth(0, 6 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if(table.Columns.Contains("半场"))
{
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 10 * 256);
}
else if (table.Columns.Contains("下半场比分"))
{
sheet.SetColumnWidth(3, 13 * 256);
sheet.SetColumnWidth(4, 20 * 256);
}
else
{
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 10 * 256);
}
sheet.SetColumnWidth(5, 18 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 12 * 256);
sheet.SetColumnWidth(8, 13 * 256);
sheet.SetColumnWidth(9, 13 * 256);
sheet.SetColumnWidth(10, 13 * 256);
ICell cellSubTitle = headerRow.CreateCell(column.Ordinal);
cellSubTitle.SetCellValue(column.Caption);
ICellStyle styleSubTitle = workbook.CreateCellStyle();
styleSubTitle.Alignment = HorizontalAlignment.CENTER;
styleSubTitle.FillPattern = FillPatternType.SOLID_FOREGROUND;
styleSubTitle.FillForegroundColor = HSSFColor.BLACK.index;
IFont font = workbook.CreateFont();
font.Color = HSSFColor.WHITE.index;
font.Boldweight = (short)FontBoldWeight.BOLD;//加粗
font.FontName = "宋体";
font.FontHeightInPoints = 11;
styleSubTitle.SetFont(font);
cellSubTitle.CellStyle = styleSubTitle;
}
int rowIndex = 1;

foreach (DataRow row in table.Rows)
{
IFont font = workbook.CreateFont();
//设置数据的每行的行高
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 20;
font.FontName = "宋体";
font.FontHeightInPoints = 11;
foreach (DataColumn column in table.Columns)
{
ICell cellSubTitle = dataRow.CreateCell(column.Ordinal);
cellSubTitle.SetCellValue(row[column].ToString());
ICellStyle styleSubTitle = workbook.CreateCellStyle();
if (column.ToString() == "总比分" || column.ToString() == "彩果"||column.ToString()=="全场")
{
font.Color = HSSFColor.RED.index;
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.BOLD;//加粗
styleSubTitle.SetFont(font);

}
else if (column.ToString() == "半场")
{

IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 10;
font1.Boldweight = (short) FontBoldWeight.BOLD; //加粗
styleSubTitle.SetFont(font1);
}

styleSubTitle.Alignment = HorizontalAlignment.CENTER;
//边框
styleSubTitle.BorderBottom = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
styleSubTitle.BorderLeft = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
styleSubTitle.BorderRight = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
styleSubTitle.BorderTop = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
cellSubTitle.CellStyle = styleSubTitle;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return ms;
}
/// <summary>
/// DataTable转换成Excel文档流，并保存到文件
/// </summary>
/// <param name="table"></param>
/// <param name="fileName">保存的路径</param>
public static void RenderToExcel(DataTable table, string fileName)
{
using (MemoryStream ms = RenderToExcel(table))
{
SaveToFile(ms, fileName);
}
}
/// <summary>
/// DataTable转换成Excel文档流，并输出到客户端
/// </summary>
/// <param name="table"></param>
/// <param name="response"></param>
/// <param name="fileName">输出的文件名</param>
public static void RenderToExcel(DataTable table, HttpContext context, string fileName)
{
using (MemoryStream ms = RenderToExcel(table))
{
RenderToBrowser(ms, context, fileName);
}
}

}