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); }