public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string billNo = Request.QueryString["billNo"];
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = ProfitLossBillDetailService.GetProfitLoassBillDetail(page, rows, billNo);
ep.HeadTitle1 = "损益单明细";
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return(new FileStreamResult(ms, "application/ms-excel"));
}
//
//PathNode/CreateExcelToClient/
public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string id = Request.QueryString["id"];
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = PathNodeService.GetPathNode(page, rows, id);
ep.HeadTitle1 = "路径节点信息";
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return new FileStreamResult(ms, "application/ms-excel");
}
public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string billNo = Request.QueryString["billNo"];
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = ProfitLossBillDetailService.GetProfitLoassBillDetail(page, rows, billNo);
ep.HeadTitle1 = "损益单明细";
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return new FileStreamResult(ms, "application/ms-excel");
}
//
//PathNode/CreateExcelToClient/
public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string id = Request.QueryString["id"];
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = PathNodeService.GetPathNode(page, rows, id);
ep.HeadTitle1 = "路径节点信息";
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return(new FileStreamResult(ms, "application/ms-excel"));
}
// GET: /DailyBalance/CreateExcelToClient/
public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string warehouseCode = Request.QueryString["warehouseCode"];
string settleDate = Request.QueryString["settleDate"];
string unitType = Request.QueryString["unitType"];
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = DailyBalanceService.GetInfoDetail(page, rows, warehouseCode, settleDate, unitType);
ep.DT2 = DailyBalanceService.GetInfoChecking(page, rows, warehouseCode, settleDate, unitType);;
ep.HeadTitle1 = "仓库库存日结明细";
ep.HeadTitle2 = "仓库库存日结核对";
ep.ContentModule = "DailyBalance";
ep.ContentModuleColor = NPOI.HSSF.Util.HSSFColor.RED.index;
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return(new FileStreamResult(ms, "application/ms-excel"));
}
// GET: /DailyBalance/CreateExcelToClient/
public FileStreamResult CreateExcelToClient()
{
int page = 0, rows = 0;
string warehouseCode = Request.QueryString["warehouseCode"];
string settleDate = Request.QueryString["settleDate"];
string unitType = Request.QueryString["unitType"];
string areas = Request.QueryString["areas"] ?? null;
THOK.Common.NPOI.Models.ExportParam ep = new THOK.Common.NPOI.Models.ExportParam();
ep.DT1 = DailyBalanceService.GetInfoDetail(page, rows, warehouseCode, settleDate, unitType,areas);
ep.DT2 = DailyBalanceService.GetInfoChecking(page, rows, warehouseCode, settleDate, unitType);;
ep.HeadTitle1 = "仓库库存日结明细";
ep.HeadTitle2 = "仓库库存日结核对";
ep.ContentModule = "DailyBalance";
ep.ContentModuleColor = NPOI.HSSF.Util.HSSFColor.RED.index;
System.IO.MemoryStream ms = THOK.Common.NPOI.Service.ExportExcel.ExportDT(ep);
return new FileStreamResult(ms, "application/ms-excel");
}
/// <summary>填充内容</summary>
static void FillContent(HSSFRow hssfRow
, DataColumn column, DataRow row
, HSSFCellStyle contentStyle, HSSFFont contentFont
, HSSFCellStyle contentDateStyle
, HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance
, THOK.Common.NPOI.Models.ExportParam ep
, HSSFSheet sheet, string headTextStrA)
{
HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell;
#region 当数据访问转换DataTime时生效
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
string columnF = row[column].ToString();
decimal i;
bool b = decimal.TryParse(columnF, out i);
#region 判断如果是仓库库存日结核对
if (ep.ContentModule == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA) &&
((column.Ordinal == 5 && b == false) ||
(column.Ordinal == 6 && b == false) ||
(column.Ordinal == 7 && b == false) ||
(column.Ordinal == 8 && b == false) ||
(column.Ordinal == 9 && b == false) ||
(column.Ordinal == 10 && b == false)))
{
fontDailyBalance.FontName = ep.ColHeadFont;
fontDailyBalance.FontHeightInPoints = ep.ColHeadSize;
fontDailyBalance.Color = ep.ContentModuleColor;
contentStyleDailyBalance.SetFont(fontDailyBalance);
if (ep.ColHeadBorder == true)
{
contentStyleDailyBalance.BorderBottom = BorderStyle.THIN;
contentStyleDailyBalance.BorderLeft = BorderStyle.THIN;
contentStyleDailyBalance.BorderRight = BorderStyle.THIN;
contentStyleDailyBalance.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance;
}
#endregion
else
{
contentFont.FontName = ep.ColHeadFont;
contentFont.FontHeightInPoints = ep.ColHeadSize;
contentFont.Color = ep.ContentColor;
contentStyle.SetFont(contentFont);
//画边框
if (ep.ColHeadBorder == true)
{
contentStyle.BorderBottom = BorderStyle.THIN;
contentStyle.BorderLeft = BorderStyle.THIN;
contentStyle.BorderRight = BorderStyle.THIN;
contentStyle.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle;
}
string drValue = row[column].ToString();
THOK.Common.NPOI.Common.ExportExcelHelper.ChangeFormat(column, drValue, newCell, contentDateStyle);
}
/// <summary>Export excle Single table Double table</summary>
public static MemoryStream ExportDT(THOK.Common.NPOI.Models.ExportParam ep)
{
MemoryStream ms = new MemoryStream();
#region Variable
string exportDate = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
double columnWidth = ep.ColHeadSize - 9;
short printHeight = 0;
short printWidth = 10;
int sheetCount = 65535; //Max 65536 rows data in one Sheet
int page = 0;
byte[] error1 = Encoding.ASCII.GetBytes("Error : Data Table or Array is null!");
byte[] error2 = Encoding.ASCII.GetBytes("Error : Return MemoryStream Failed!");
#endregion
try
{
#region HttpResponse
THOK.Common.NPOI.Common.ExportExcelHelper.BrowserLoad(ep.HeadTitle1);
#endregion
#region Init
workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
#endregion
#region Create cell style
HSSFCellStyle contentDateStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容时间 单元格
HSSFCellStyle styleHead = workbook.CreateCellStyle() as HSSFCellStyle; //大标题 单元格
HSSFFont fontHead = workbook.CreateFont() as HSSFFont; //大标题 字体
HSSFCellStyle styleDate = workbook.CreateCellStyle() as HSSFCellStyle; //导出时间 单元格
HSSFCellStyle styleColHead = workbook.CreateCellStyle() as HSSFCellStyle; //列头 单元格
HSSFFont fontColHead = workbook.CreateFont() as HSSFFont; //列头 字体
HSSFCellStyle contentStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容 单元格
HSSFFont fontContent = workbook.CreateFont() as HSSFFont; //内容 字体
contentStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //内容 设置所有列整型格式，也可以通过数据访问层来改变内容格式
HSSFCellStyle contentStyleDailyBalance = workbook.CreateCellStyle() as HSSFCellStyle; //特殊模块 单元格
HSSFFont fontDailyBalance = workbook.CreateFont() as HSSFFont; //特殊模块 字体
#endregion
#region Global style
HSSFCellStyle headStyle = GetTitleStyle(ep.BigHeadFont, ep.BigHeadSize, ep.BigHeadColor, styleHead, fontHead);
HSSFCellStyle dateStyle = GetExportDate(styleDate);
HSSFCellStyle colHeadStyle = GetColumnStyle(ep.ColHeadFont, ep.ColHeadSize, ep.ColHeadColor, ep.ColHeadBorder, styleColHead, fontColHead);
#endregion
#region Get column width
int[] arrColWidth1 = new int[0];
int[] arrColWidth2 = new int[0];
if (ep.DT1 != null && ep.HeadTitle1 != null)
{
arrColWidth1 = new int[ep.DT1.Columns.Count];
GetColumnWidth(ep.DT1, arrColWidth1);
}
if (ep.DT2 != null && ep.HeadTitle2 != null)
{
arrColWidth2 = new int[ep.DT2.Columns.Count];
GetColumnWidth(ep.DT2, arrColWidth2);
}
#endregion
#region Create excel table one
if (ep.DT1 != null && ep.HeadTitle1 != null)
{
int dt1count = ep.DT1.Rows.Count;
#region 判断多少页
if (dt1count % sheetCount == 0)
{
page = dt1count / sheetCount;
}
else
{
page = dt1count / sheetCount + 1;
}
#endregion
for (int a = 0; a < page; a++)
{
#region SHEET分页标题
string sheetNum = a.ToString();
if (a == 0)
{
sheetNum = sheetNum.Substring(0, a.ToString().Length - 1);
}
string headTitle1_sheetNum = ep.HeadTitle1 + sheetNum;
sheet = workbook.CreateSheet(headTitle1_sheetNum) as HSSFSheet;
#endregion
#region SHEET打印设置
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
#endregion
int rowIndex1 = 0;
#region 内容分页
DataTable newdt1 = THOK.Common.NPOI.Common.ExportExcelHelper.SetPage(ep.DT1, a + 1, sheetCount);
#endregion
#region 填充数据
foreach (DataRow row in newdt1.Rows)
{
if (rowIndex1 == 0)
{
if (rowIndex1 != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
}
#region 填充“表头”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4);
headerRow.CreateCell(0).SetCellValue(ep.HeadTitle1);
headerRow.GetCell(0).CellStyle = headStyle;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt1.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.BigHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);//给合并的画线
}
}
#endregion
#region 填充“导出时间”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
headerRow.CreateCell(0).SetCellValue(exportDate);
headerRow.GetCell(0).CellStyle = dateStyle;
CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt1.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.ColHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 填充“列头”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(2) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.ColHeadSize * 1.4);
foreach (DataColumn column in newdt1.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle;
sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth1[column.Ordinal] + columnWidth) * 256));//设置列宽
}
}
#endregion
rowIndex1 = 3;
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex1) as HSSFRow;
foreach (DataColumn column in newdt1.Columns)
{
FillContent(dataRow, column, row, contentStyle, fontContent
, contentDateStyle
, contentStyleDailyBalance, fontDailyBalance
, ep
, sheet, headTitle1_sheetNum);
}
rowIndex1++;
#endregion
}
#endregion
}
}
#endregion
#region Create excel table two
if (ep.DT2 != null && ep.HeadTitle2 != null)
{
int dt2count = ep.DT2.Rows.Count;
if (dt2count % sheetCount == 0)
{
page = dt2count / sheetCount;
}
else
{
page = dt2count / sheetCount + 1;
}
for (int a = 0; a < page; a++)
{
int rowIndex2 = 0;
DataTable newdt2 = THOK.Common.NPOI.Common.ExportExcelHelper.SetPage(ep.DT2, a + 1, sheetCount);
string strA = a.ToString();
if (a == 0)
{
strA = strA.Substring(0, a.ToString().Length - 1);
}
string headText2strA = ep.HeadTitle2 + strA;
#region 填充数据
foreach (DataRow row in newdt2.Rows)
{
if (rowIndex2 == 0)
{
HSSFRow headerRow;
if (rowIndex2 != 1)
{
sheet = workbook.CreateSheet(headText2strA) as HSSFSheet;
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
}
#region 填充表头、样式
{
headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4);
headerRow.CreateCell(0).SetCellValue(ep.HeadTitle2);
headerRow.GetCell(0).CellStyle = headStyle;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt2.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.BigHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 导出时间、样式
{
headerRow = sheet.CreateRow(1) as HSSFRow;
headerRow.CreateCell(0).SetCellValue(exportDate);
headerRow.GetCell(0).CellStyle = dateStyle;
CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt2.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.ColHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 填充列头、样式
{
headerRow = sheet.CreateRow(2) as HSSFRow;
foreach (DataColumn column in newdt2.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle;
sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth2[column.Ordinal] + columnWidth) * 256));
}
}
rowIndex2 = 3;
#endregion
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex2) as HSSFRow;
foreach (DataColumn column in newdt2.Columns)
{
FillContent(dataRow, column, row, contentStyle, fontContent
, contentDateStyle
, contentStyleDailyBalance, fontDailyBalance
, ep
, sheet, headText2strA);
}
rowIndex2++;
#endregion
}
#endregion
}
}
#endregion
#region Set the header footer
try
{
sheet.Header.Left = ep.HeaderFooter[0].ToString();
sheet.Header.Center = ep.HeaderFooter[1].ToString();
sheet.Header.Right = ep.HeaderFooter[2].ToString();
sheet.Footer.Left = ep.HeaderFooter[3].ToString();
sheet.Footer.Center = ep.HeaderFooter[4].ToString();
sheet.Footer.Right = ep.HeaderFooter[5].ToString();
}
catch (Exception)
{
return(new MemoryStream(error1));
}
#endregion
#region Return MemoryStream
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
#endregion
}
catch (Exception)
{
return(new MemoryStream(error2));
}
}
