Net之HSSFWorkbook 下载excel 核心功能

try
{
DateTime allowTime = DateTime.Now.ToARCUniversalTime().ToARCLocalTime().AddMonths(-3);
DateTime ivTime = DateTime.Parse(RadAllInvoice.SelectedDate.ToString());
#region 下载功能
string[] excelHeader = { "序号", " 其他", "姓名", "项目开始时间", "项目截止时间", "编号", "项目名称", "金额", "公司名称", "部门", "客户", "未开票金额", "备注说明" };
var excelName = "AutoInvoiceList-" + DateTime.Now.ToARCUniversalTime().ToARCLocalTime().ToString("yyyyMMdd") + ".xls";
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("逾期未开票表"); //创建sheet
sheet.DefaultColumnWidth = 15; //sheet.DefaultRowHeightInPoints = 15;
IRow row = sheet.CreateRow(0);
//在行中：建立单元格，参数为列号，从0计
ICell cell = row.CreateCell(0);

#region 单元格样式
//header
IRow row0 = sheet.CreateRow(0);
//标题和数据源
ICellStyle ss = workbook.CreateCellStyle();
ss.FillForegroundColor = HSSFColor.RoyalBlue.Index;
IFont font = workbook.CreateFont();
font.Color = HSSFColor.White.Index;
ss.SetFont(font);//HEAD 样式

ss.FillPattern = FillPattern.SolidForeground;
ss.VerticalAlignment = VerticalAlignment.Justify;
ss.Alignment = HorizontalAlignment.Center;
ss.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ss.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ss.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ss.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
for (int i = 0; i < excelHeader.Length; i++)
{
ICell header = row0.CreateCell(i);
header.SetCellValue(excelHeader[i].Trim());
header.CellStyle = ss;
}
#endregion

#region 数据处理添加到excel表
int allCount = 0;
var ProjectList = pm.GetAutoProjectInvoiceByPdTime(ivTime, out allCount);
if (allCount > 0)
{
#region 数据处理
var count = 0; var invoMoney = 0.0; var num = 0;
var ProCount = ProjectList.Count;

ICellStyle ceStyle = workbook.CreateCellStyle();
ceStyle.FillForegroundColor = HSSFColor.Yellow.Index;
ceStyle.FillPattern = FillPattern.SolidForeground;
//ceStyle.VerticalAlignment = VerticalAlignment.Justify;
//ceStyle.Alignment = HorizontalAlignment.Center;
ceStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ceStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ceStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ceStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

foreach (var Pro in ProjectList)
{
var userInfo = um.GetUserInfoByUserID(Pro.OwnerID);
var uName = um.GetTrueNameByUserID(Pro.OwnerID);

//获取所有发票
var iMoney = im.GetAutoInvoiceListByProjectID(Pro.ProjectID);
if (iMoney < Pro.ContractMoney && Pro.ContractMoney != 0)
{
count++; num++;
var tmCount = 0;//所占列表数目

#region 数据处理生成新列
IRow row2 = sheet.CreateRow(count);
row2.CreateCell(0).SetCellValue(num);
row2.CreateCell(1).SetCellValue("");
row2.CreateCell(2).SetCellValue(userInfo.TrueName);

row2.CreateCell(3).SetCellValue(Pro.StartTime.ToString("yyyy-MM-dd"));
row2.CreateCell(4).SetCellValue(Pro.PredictEndTime.ToString("yyyy-MM-dd"));
row2.CreateCell(5).SetCellValue(Pro.CID);
row2.CreateCell(6).SetCellValue(GetProductAbstract(Pro.ProjectName));
row2.CreateCell(7).SetCellValue(Pro.ContractMoney.ToString("C2"));
if (Pro.AccountID == null)
row2.CreateCell(8).SetCellValue("");
else
{
var accInfo = AccManager.GetAccountByAccountID(Pro.AccountID, 0);
if (accInfo != null)
row2.CreateCell(8).SetCellValue(accInfo.AccountName);
else
row2.CreateCell(8).SetCellValue("");
}
if (Pro.DepartmentID == null)
row2.CreateCell(9).SetCellValue("");
else
{
var depInfo = dpm.GetDepartByDepartIDNoDel(Pro.DepartmentID, 0);
if (depInfo != null)
row2.CreateCell(9).SetCellValue(depInfo.DepartmentName);
else
row2.CreateCell(9).SetCellValue("");
}
if (Pro.ContactID == null)
row2.CreateCell(10).SetCellValue("");
else
{
var conInfo = conm.GetContactByContactID(Pro.ContactID);
if (conInfo != null)
row2.CreateCell(10).SetCellValue(conInfo.ContactName);
else
row2.CreateCell(10).SetCellValue("");
}
invoMoney += 0.0;

var leftMoney = Pro.ContractMoney - iMoney;
invoMoney += leftMoney;
row2.CreateCell(11).SetCellValue(leftMoney.ToString("C2"));
row2.CreateCell(12).SetCellValue("部分开具");

if (allowTime > Pro.PredictEndTime)
{
row2.GetCell(0).CellStyle = ceStyle;
row2.GetCell(1).CellStyle = ceStyle;
row2.GetCell(2).CellStyle = ceStyle;
row2.GetCell(3).CellStyle = ceStyle;
row2.GetCell(4).CellStyle = ceStyle;
row2.GetCell(5).CellStyle = ceStyle;
row2.GetCell(6).CellStyle = ceStyle;
row2.GetCell(7).CellStyle = ceStyle;
row2.GetCell(8).CellStyle = ceStyle;
row2.GetCell(9).CellStyle = ceStyle;
row2.GetCell(10).CellStyle = ceStyle;
row2.GetCell(11).CellStyle = ceStyle;
row2.GetCell(12).CellStyle = ceStyle;
}
//最后一列生成合并单元格和最后一列
if (ProCount == (tmCount + 1))
{
#region 合并单元格
count = count + 1;
//横向单元格
IRow row3 = sheet.CreateRow(count);
ICellStyle s3 = workbook.CreateCellStyle();
s3.FillForegroundColor = HSSFColor.LightOrange.Index;
IFont font3 = workbook.CreateFont();
s3.SetFont(font3);//HEAD 样式
row3.CreateCell(0).SetCellValue("合计");
row3.CreateCell(12).SetCellValue(invoMoney.ToString("C2"));
row3.RowStyle = s3;
CellRangeAddress range = new CellRangeAddress(count, count, 0, 11);
sheet.AddMergedRegion(range);
#endregion
}
tmCount++;
#endregion
}
}
#endregion
}
#endregion
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + excelName);
HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
HttpContext.Current.Response.End();
memoryStream.Dispose();
#endregion
}
catch (Exception ex)
{
    return;
}