/// <summary>
/// Convert Pivot Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Convert_to_PDF_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
// Accessing workbook
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("PivotLayout.xlsx", Request));
CreatePivotTable(workbook);
//Intialize the ExcelToPdfConverter class
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
//Convert the Excel document to PDf
PdfDocument pdfDoc = converter.Convert(settings);
//Save the document as PDf
pdfDoc.Save("PivotLayout.pdf", Response, HttpReadType.Save);
pdfDoc.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
}
//bouton "Generer PDF"
private void button2_Click(object sender, EventArgs e)
{
genererXLS();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("../../Result/Defauts_test.xlsx", ExcelOpenType.Automatic);
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize PDF Document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel Document into PDF document
pdfDocument = converter.Convert();
//Save the pdf file
pdfDocument.Save("ExceltoPDF.pdf");
//Dispose the objects
pdfDocument.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
//Message box confirmation to view the created PDF document.
if (MessageBox.Show("Do you want to view the PDF file?", "PDF File Created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the PDF file using the default Application.[Acrobat Reader]
System.Diagnostics.Process.Start("ExceltoPDF.pdf"); //TODO revoir pourquoi ça marche pas ../../PDFResult
this.Close();
}
else
{
// Exit
this.Close();
}
}
/// <summary>
/// Converts the Excel document to PDF document.
/// </summary>
public HttpResponseMessage ConvertToPDF()
{
using (Stream stream = Request.Content.ReadAsStreamAsync().Result)
{
// Creates new MemoryStream instance for output PDF.
MemoryStream pdfStream = new MemoryStream();
//Initializes the Excel Engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// Instantiates the ChartToImageConverter and assigns the ChartToImageConverter instance of XlsIO application
application.ChartToImageConverter = new ChartToImageConverter();
// Tuning Chart Image Quality.
application.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Opens the Excel document from stream
IWorkbook workbook = application.Workbooks.Open(stream, ExcelOpenType.Automatic);
//Creates an instance of the ExcelToPdfConverter
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Converts Excel document into PDF document
PdfDocument pdfDocument = converter.Convert();
//Adds watermark at top left corner of first page in the generated PDF document, to denote it is generated using demo web service
//If you want to remove this watermark, please comment or delete the codes within below "if" statement
if (pdfDocument.Pages.Count > 0)
{
PdfPage pdfPage = pdfDocument.Pages[0];
//Create PDF font and PDF font style using Font.
Font font = new Font("Times New Roman", 12f, FontStyle.Regular);
PdfFont pdfFont = new PdfTrueTypeFont(font, false);
//Create a new pdf brush to draw the rectangle.
PdfBrush pdfBrush = new PdfSolidBrush(Color.White);
//Draw rectangle in the current page.
pdfPage.Graphics.DrawRectangle(pdfBrush, 0f, 0f, 200f, 20.65f);
//Create a new brush to draw the text.
pdfBrush = new PdfSolidBrush(Color.Red);
//Draw text in the current page.
pdfPage.Graphics.DrawString("Created by Syncfusion – XlsIO library", pdfFont, pdfBrush, 6f, 4f);
}
// Saves the PDF document to stream.
pdfDocument.Save(pdfStream);
pdfStream.Position = 0;
converter.Dispose();
workbook.Close();
pdfDocument.Close(true);
// Creates HttpResponseMessage to return result with output PDF stream.
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StreamContent(pdfStream);
result.Content.Headers.ContentLength = pdfStream.Length;
return(result);
}
}
}
/// <summary>
/// Convert To PDF
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnConvert_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
// Accessing workbook
string inputPath = GetFullTemplatePath("PivotLayout.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
CreatePivotTable(workbook);
//Intialize the ExcelToPdfConverter class
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
//Convert the Excel document to PDF
PdfDocument pdfDoc = converter.Convert(settings);
//Save the document as PDF
string outputFileName = "PivotLayoutCompact.pdf";
if (rdBtnOutline.Checked)
{
outputFileName = "PivotLayoutOutline.pdf";
}
else if (rdBtnTabular.Checked)
{
outputFileName = "PivotLayoutTabular.pdf";
}
try
{
pdfDoc.Save(outputFileName);
pdfDoc.Close();
converter.Dispose();
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the PDF?", "PDF has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
//Launching the PDF file using the default Application.
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(outputFileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(outputFileName);
#endif
}
catch (Win32Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
else
{
// Exit
this.Close();
}
}
catch
{
MessageBox.Show("Sorry, PDF can't opened", "File is already open", MessageBoxButtons.OK);
}
workbook.Close();
excelEngine.Dispose();
}
private string Export(string saveOption, List <UserEventLogSearchResult> eventLogList, UserEventLogSearchCondition model)
{
string pathExport = string.Empty;
// Khỏi tạo bảng excel
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(HttpContext.Current.Server.MapPath("/Template/LichSuTruyCapSuDung.xlsx"));
IWorksheet sheet = workbook.Worksheets[0];
//Thay đổi title trong báo cáo
// Utiliti.FillTitleExport(db, sheet);
//DateTime dateNow = DateTime.Now;
//IRange rangeDay = sheet.FindFirst("<Day>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeDay.Text = rangeDay.Text.Replace("<Day>", dateNow.Day.ToString());
//IRange rangeMonth = sheet.FindFirst("<Month>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeMonth.Text = rangeMonth.Text.Replace("<Month>", dateNow.Month.ToString());
//IRange rangeYear = sheet.FindFirst("<Year>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeYear.Text = rangeYear.Text.Replace("<Year>", dateNow.Year.ToString());
IRange rangeDateFrom = sheet.FindFirst("<DateFrom>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
rangeDateFrom.Text = rangeDateFrom.Text.Replace("<DateFrom>", (model.LogDateFrom.HasValue ? model.LogDateFrom.Value.ToString("dd/MM/yyy") : "--/--/----"));
IRange rangeDateTo = sheet.FindFirst("<DateTo>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
rangeDateTo.Text = rangeDateTo.Text.Replace("<DateTo>", (model.LogDateTo.HasValue ? model.LogDateTo.Value.ToString("dd/MM/yyy") : "--/--/----"));
int total = eventLogList.Count;
int index = 1;
var listExport = (from a in eventLogList
select new
{
Index = index++,
a.LogTypeName,
// a.UserType,
a.Description,
CreateDate = a.CreateDate.HasValue ? a.CreateDate.Value.ToString("dd-MM-yyyy HH:mm:ss") : string.Empty,
a.UserName
}).ToList();
IRange iRangeData = sheet.FindFirst("<Data>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
sheet.ImportData(listExport, iRangeData.Row, iRangeData.Column, false);
if (saveOption.Equals("PDF"))
{
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders.Color = ExcelKnownColors.Black;
pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.pdf";
//convert the sheet to pdf
ExcelToPdfConverter converter = new ExcelToPdfConverter(sheet);
PdfDocument pdfDocument = new PdfDocument();
pdfDocument = converter.Convert();
pdfDocument.Save(HttpContext.Current.Server.MapPath(pathExport));
pdfDocument.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
}
else
{
pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.xlsx";
workbook.SaveAs(HttpContext.Current.Server.MapPath(pathExport));
}
return(pathExport);
}
