protected void btnFooter_Click(object sender, EventArgs e)
{
var excelArg = new ExportDataTable2ExcelArg
{
dataSource = GetDataSource(),
HeaderCenter = "&24 This is Report Header ...",
HeaderRight = $"&12 使用者:Rainmaker\r日期:{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")}",
FooterRight = "&10 &P/&N",
ColumnInfos = new Dictionary <string, Tuple <string, double, Aspose.Cells.Style> >
{
{ "ProductID", new Tuple <string, double, Aspose.Cells.Style>($"產品代號", -1, null) },
{ "ProductName", new Tuple <string, double, Aspose.Cells.Style>("產品名稱", -1, null) },
{ "ProductDesc", new Tuple <string, double, Aspose.Cells.Style>("產品描述", -1, null) },
{ "Units", new Tuple <string, double, Aspose.Cells.Style>("產品 庫存", -1, null) },
{ "CreDte", new Tuple <string, double, Aspose.Cells.Style>("日期", 10, new Aspose.Cells.Style {
Number = 14
}) }
},
PageOrientation = PageOrientationType.Landscape,
IsTextWrapped = false,
PageScale = 80,
FontName = "標楷體",
HeaderHorizontalAlignment = TextAlignmentType.Center
};
var pdfStream = GenPDFFromDataTable(excelArg);
var fileNameWithoutExt = $"{Guid.NewGuid().ToString("N")}";
//string pdfFileName = Path.Combine(Server.MapPath("./data"), $"{fileNameWithoutExt}_temp.pdf");
//using (FileStream file = new FileStream(pdfFileName, FileMode.Create, System.IO.FileAccess.Write))
// pdfStream.CopyTo(file);
var watermarkArg = new WatermarkArg
{
Watermark = $"* 使用者:亂馬客 *{Environment.NewLine}{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")}",
WMStyle = WatermarkStyle.RepeatHorizontal,
WatermarkHeight = 100,
WatermarkWidth = 130,
WatermarkHorizontalSpace = 50,
WatermarkVerticalSpace = 30,
RotateAngle = 30,
Opacity = .1
};
var waterStream = AddFooterAndWatermark(pdfStream, watermarkArg);
//string watermarkFileName = Path.Combine(Server.MapPath("./data"), $"{fileNameWithoutExt}.pdf");
//using (FileStream file = new FileStream(watermarkFileName, FileMode.Create, System.IO.FileAccess.Write))
// waterStream.CopyTo(file);
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment; filename=" + $"{fileNameWithoutExt}.pdf");
var fileSize = waterStream.Length;
byte[] pdfBuffer = new byte[(int)fileSize];
waterStream.Read(pdfBuffer, 0, (int)fileSize);
waterStream.Close();
Response.BinaryWrite(pdfBuffer);
Response.End();
}
/// <summary>
/// 將 DataTable 的資料轉到Excel處理並存成 PDF Stream
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public static MemoryStream GenPDFFromDataTable(ExportDataTable2ExcelArg arg)
{
//Change DataTable's ColumnName
ChangedDataTableColumnName(arg.dataSource, arg.ColumnInfos);
//proc excel
// Instantiating a Workbook object
var workbook = new Workbook();
if (!string.IsNullOrWhiteSpace(arg.FontName))
{
var wbStyle = workbook.DefaultStyle;
wbStyle.Font.Name = arg.FontName;
workbook.DefaultStyle = wbStyle;
}
var worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportDataTable(arg.dataSource, true, "A1");
//https://docs.aspose.com/display/cellsnet/Setting+Page+Options
var pageSetup = workbook.Worksheets[0].PageSetup;
pageSetup.PrintTitleRows = "$1:$1";
pageSetup.IsPercentScale = true;
pageSetup.Orientation = arg.PageOrientation;
pageSetup.Zoom = arg.PageScale < 10 ? 100 : arg.PageScale;
//https://docs.aspose.com/display/cellsnet/Setting+Headers+and+Footers
if (!string.IsNullOrEmpty(arg.HeaderLeft))
{
pageSetup.SetHeader(0, arg.HeaderLeft);
}
if (!string.IsNullOrEmpty(arg.HeaderCenter))
{
pageSetup.SetHeader(1, arg.HeaderCenter);
}
if (!string.IsNullOrEmpty(arg.HeaderRight))
{
pageSetup.SetHeader(2, arg.HeaderRight);
}
if (!string.IsNullOrEmpty(arg.FooterLeft))
{
pageSetup.SetFooter(0, arg.FooterLeft);
}
if (!string.IsNullOrEmpty(arg.FooterCenter))
{
pageSetup.SetFooter(1, arg.FooterCenter);
}
if (!string.IsNullOrEmpty(arg.FooterRight))
{
pageSetup.SetFooter(2, arg.FooterRight);
}
var range = worksheet.Cells.MaxDisplayRange;
//border
//Setting border for each cell in the range
var style = workbook.CreateStyle();
var colorBlack = System.Drawing.Color.Black;
style.SetBorder(BorderType.BottomBorder, CellBorderType.Medium, colorBlack);
style.SetBorder(BorderType.LeftBorder, CellBorderType.Medium, colorBlack);
style.SetBorder(BorderType.RightBorder, CellBorderType.Medium, colorBlack);
style.SetBorder(BorderType.TopBorder, CellBorderType.Medium, colorBlack);
style.IsTextWrapped = arg.IsTextWrapped;
range.SetStyle(style);
worksheet.AutoFitColumns();
//adjust columns
ChangedSheetColumnStyle(worksheet, arg.ColumnInfos, arg.HeaderHorizontalAlignment);
worksheet.AutoFitRows();
//string xlsFile = Path.Combine(HttpContext.Current.Server.MapPath("./data"), $"test.xlsx");
//workbook.Save(xlsFile, Aspose.Cells.SaveFormat.Xlsx);
//save to stream
var pdfStream = new MemoryStream();
workbook.Save(pdfStream, Aspose.Cells.SaveFormat.Pdf);
return(pdfStream);
}
