public void should_convert_from_excel_to_pdf()
{
string from = Environment.CurrentDirectory + @"\TestFiles\报价明细表.xlsx";
string to = Environment.CurrentDirectory + @"\TestFiles\报价明细表excel.pdf";
Assert.AreEqual(ErrorMessages.Success, _excelToPdf.Convert(from, to));
Assert.IsTrue(File.Exists(to));
}
/// <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();
}
private static void ExportToPDF(SfSpreadsheet spreadsheetControl)
{
ExcelToPdfConverter converter = new ExcelToPdfConverter(spreadsheetControl.Workbook);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.NoScaling;
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save("Sample.pdf");
System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo("Sample.pdf");
info.UseShellExecute = true;
System.Diagnostics.Process.Start(info);
//System.Diagnostics.Process.Start("Sample.pdf");
}
public void should_convert_from_excel_to_pdf()
{
ExcelToPdfConverter _converter = new ExcelToPdfConverter();
Assert.AreEqual(ErrorMessages.Success, _converter.Convert(Environment.CurrentDirectory + @"\TestFiles\报价明细表.xlsx",
Environment.CurrentDirectory + @"\TestFiles\报价明细表excel.pdf"));
Assert.IsTrue(File.Exists(Environment.CurrentDirectory + @"\TestFiles\报价明细表excel.pdf"));
}
//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();
}
}
//
// GET: /ExcelToPDF/
public ActionResult ExcelToPDF(string button, string Group1)
{
if (button == null)
{
return(View());
}
ExcelToPdfConverter converter = new ExcelToPdfConverter(ResolveApplicationDataPath("ExcelTopdfwithChart.xlsx"));
converter.ChartToImageConverter = new ChartToImageConverter();
//Set the image quality
converter.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Intialize the PdfDocument Class
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (Group1 == "NoScaling")
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (Group1 == "FitAllRowsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (Group1 == "FitAllColumnsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the output PdfDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert the Excel document to PDf
pdfDoc = converter.Convert(settings);
try
{
pdfDoc.Save("ExcelToPDF.pdf", HttpContext.ApplicationInstance.Response, HttpReadType.Save);
// return new Syncfusion.Mvc.Pdf.PdfResult(pdfDoc, "ExcelToPDF.pdf", HttpContext.ApplicationInstance.Response, HttpReadType.Save);
}
catch (Exception)
{ }
finally
{
}
return(View());
}
/// <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);
}
}
}
public void PrintAll()
{
PdfDocument _dtrs = new PdfDocument();
PdfPage page = new PdfPage();
// foreach (EmployeeDTR dtr in DTRs.Values) {
//
// page = ConvertOne(dtr.Id);
//
// page = _dtrs.Pages.Add();
// }
foreach (ListViewItem li in Program.MainForm.lstresults.Items)
{
page = ConvertOne(int.Parse(li.SubItems[1].Text));
page = _dtrs.Pages.Add();
}
// pdf converter object
ExcelToPdfConverter _converter = new ExcelToPdfConverter();
// pdf settings
ExcelToPdfConverterSettings _settings = new ExcelToPdfConverterSettings();
_settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
_settings.TemplateDocument = _dtrs;
_settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
_dtrs.PageSettings.Orientation = PdfPageOrientation.Landscape;
_dtrs.PageSettings.Size = new PdfPageSettings().Size = PdfPageSize.Letter;
// convert now
_dtrs = _converter.Convert(_settings);
// save now
_dtrs.Save("dtrs.pdf");
// print dtr
Process pr = new Process();
pr.StartInfo.Verb = "Print";
pr.StartInfo.FileName = "dtrs.pdf";
pr.Start();
}
public void ExcelToPDFConversion()
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Open($@"{workPath}{dataFileName}", ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets["事业在职沿革表"];
//convert the sheet to PDF
ExcelToPdfConverter converter = new ExcelToPdfConverter(sheet);
PdfDocument pdfDocument = new PdfDocument();
pdfDocument = converter.Convert();
pdfDocument.Save($@"{workPath}ExcelToPDF.pdf");
}
}
bool convertExcelToPDF(string source, string destination)
{
ExcelToPdfConverter converter = new ExcelToPdfConverter(source);
converter.ChartToImageConverter = new Syncfusion.ExcelChartToImageConverter.ChartToImageConverter();
//Set the image quality
converter.ChartToImageConverter.ScalingMode = Syncfusion.XlsIO.ScalingMode.Best;
//Intialize the PdfDocument Class
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
//if (Group1 == "NoScaling")
// settings.LayoutOptions = LayoutOptions.NoScaling;
//else if (Group1 == "FitAllRowsOnOnePage")
// settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
//else if (Group1 == "FitAllColumnsOnOnePage")
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//else
// settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
//Assign the output PdfDocument to the TemplateDocument property of ExcelToPdfConverterSettings
pdfDoc.PageSettings.Orientation = PdfPageOrientation.Landscape;
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert the Excel document to PDf
pdfDoc = converter.Convert(settings);
try
{
pdfDoc.Save(destination);
}
catch (Exception)
{
return(false);
}
finally
{
}
return(true);
}
private static void PrintFromPdfViewer(Spreadsheet spreadsheetControl)
{
//Create the previewdialog for print the document.
PrintPreviewDialog printdialog = new PrintPreviewDialog();
//Create the pdfviewer for load the document.
PdfViewerControl pdfviewer = new PdfViewerControl();
// PdfDocumentViewer
MemoryStream pdfstream = new MemoryStream();
IWorksheet worksheet = spreadsheetControl.Workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
worksheet.CalcEngine.UseFormulaValues = true;
ExcelToPdfConverter converter = new ExcelToPdfConverter(spreadsheetControl.Workbook);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.NoScaling;
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save(pdfstream);
//Load the document to pdfviewer
pdfviewer.ReferencePath = @"..\..\..\..\..\..\Common\Data\PDF";
pdfviewer.Load(pdfstream);
printdialog.Document = pdfviewer.PrintDocument;
printdialog.ShowDialog();
}
private static void OnExecuteExportToPDF(object sender, ExecutedRoutedEventArgs args)
{
SpreadsheetControl spreadsheetControl = args.Source as SpreadsheetControl;
ExcelToPdfConverter converter = new ExcelToPdfConverter(spreadsheetControl.ExcelProperties.WorkBook);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
if (args.Parameter != null)
{
string layoutOption = args.Parameter.ToString();
if (layoutOption == "NoScaling")
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (layoutOption == "FitAllRowsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (layoutOption == "FitAllColumnsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
}
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save("Sample.pdf");
System.Diagnostics.Process.Start("Sample.pdf");
}
public void ConvertToPdf(string source, string destination)
{
try
{
using (var excelEngine = new ExcelEngine())
{
var application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
var workbook = application.Workbooks.Open(source, ExcelOpenType.Automatic);
var converter = new ExcelToPdfConverter(workbook);
var pdfDocument = converter.Convert();
pdfDocument.Save(destination);
}
}
catch (Exception ex)
{
FileLogger.SetLog(string.Format(ExceptionConstants.ConvertToPdf, source, ex.Message));
}
}
public static bool ConvertExcelToPDF(string ExcelFileName, string PDFFileName = null)
{
if (PDFFileName == null)
{
PDFFileName = ExcelFileName + ".pdf";
}
if (!System.IO.File.Exists(ExcelFileName))
{
return(false);
}
ExcelToPdfConverter converter = new ExcelToPdfConverter(ExcelFileName);
//Initialize the PdfDocument Class
PdfDocument pdfDoc = new PdfDocument();
//Initialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings()
{
LayoutOptions = LayoutOptions.FitSheetOnOnePage,
TemplateDocument = pdfDoc,
DisplayGridLines = GridLinesDisplayStyle.Invisible,
EmbedFonts = true,
ExportQualityImage = true
};
//Convert the Excel document to PDf
pdfDoc = converter.Convert(settings);
try
{
pdfDoc.Save(PDFFileName);
return(true);
}
catch (Exception)
{
return(false);
}
}
private void LoadDocumentToPdfViewer()
{
//Create Memory Stream to save pdfdocument.
MemoryStream pdfstream = new MemoryStream();
IWorksheet worksheet = this.sfspreadsheet.Workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
worksheet.CalcEngine.UseFormulaValues = true;
ExcelToPdfConverter converter = new ExcelToPdfConverter(this.sfspreadsheet.Workbook);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.NoScaling;
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save(pdfstream);
//Load the document to pdfviewer
pdfDocumentView1.ReferencePath = @"..\..\..\..\..\..\Common\Data\PDF";
//Load the pdfstream to pdfDocumentView
pdfDocumentView1.Load(pdfstream);
lblTotalPageCount.Text = pdfDocumentView1.PageCount.ToString();
EnablePagination();
pageTimer.Tick += pageTimer_Tick;
pageTimer.Start();
}
private static void PrintFromPdfViewer()
{
workbook.Worksheets[0].EnableSheetCalculations();
workbook.Worksheets[0].CalcEngine.AllowShortCircuitIFs = true;
workbook.Worksheets[0].CalcEngine.MaximumRecursiveCalls = 10000;
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.NoScaling;
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save(pdfstream);
}
private void excelToPdfConvertBtnClick(object sender, EventArgs e)
{
if (this.textBox1.Text != String.Empty)
{
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter((string)textBox1.Tag);
//Intialize the PDFDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfconverterSettings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (noScaleRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (allRowsRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (allColumnRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PDFDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the pdf file
pdfDoc.Save("ExceltoPDF.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the PDF file?", "File has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
#if NETCORE
Process process = Process.Start(@"cmd.exe", @"/c " + "ExceltoPDF.pdf");
process.CloseMainWindow();
#else
Process.Start("ExceltoPDF.pdf");
#endif
//Exit
this.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
else
{
MessageBox.Show("Browse a word document and click the button to convert as a PDF.");
}
}
public ActionResult ExcelToPDF(string button, string checkboxStream, string checkboxName, string Group1)
{
if (button == null)
{
return(View());
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (button == "Input Template")
{
if (checkboxStream != null || checkboxName != null)
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"invoiceTemplate.xlsx"), ExcelOpenType.Automatic);
return(excelEngine.SaveAsActionResult(workbook, "invoiceTemplate.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"ExcelTopdfwithChart.xlsx"), ExcelOpenType.Automatic);
return(excelEngine.SaveAsActionResult(workbook, "ExcelTopdfwithChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
checkfontName = checkboxName;
checkfontStream = checkboxStream;
if (checkboxStream != null || checkboxName != null)
{
application.SubstituteFont += new Syncfusion.XlsIO.Implementation.SubstituteFontEventHandler(SubstituteFont);
workbook = application.Workbooks.Open(ResolveApplicationDataPath("invoiceTemplate.xlsx"));
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath("ExcelTopdfwithChart.xlsx"));
}
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
converter.ChartToImageConverter = new ChartToImageConverter();
//Set the image quality
converter.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Intialize the PdfDocument Class
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (Group1 == "NoScaling")
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (Group1 == "FitAllRowsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (Group1 == "FitAllColumnsOnOnePage")
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the output PdfDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert the Excel document to PDf
pdfDoc = converter.Convert(settings);
try
{
pdfDoc.Save("ExcelToPDF.pdf", HttpContext.ApplicationInstance.Response, HttpReadType.Save);
}
catch (Exception)
{ }
finally
{
}
return(View());
}
public static bool WriteCalculationsToTemplate(ObservableCollection <Calculation3d> calcs,
string template, string path, ExcelWriteTemplateSettings Settings, bool asPdf = false
)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook
IWorkbook workbook = application.Workbooks.Open(template, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Accessing first table in the sheet
IListObject table = worksheet.ListObjects[0];
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
int insertRow = Convert.ToInt32(Settings.StartRow);
IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle;
worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter);
//Go to list
for (int i = 0; i < calcs.Count; i++)
{
int Row = Convert.ToInt32(Settings.StartRow) + i;
//worksheet.Rows.ElementAt(Row).CellStyle = rowStyle;
string Col = Settings.StartColumn;
// Pos
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1;
// Description
StringBuilder sb = new StringBuilder();
sb.Append(calcs[i].Name);
/*
* sb.AppendLine(string.Format("Volume: {0} | Time: {1}",
* Convert.ToDouble(calcs[i].Volume),
* Convert.ToDouble(calcs[i].CalculatedPrintTime))
* );
*/
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString();
// Quantity
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0";
// Single
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity)));
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
// Total
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
if (i < calcs.Count - 1)
{
var loc = table.Location;
//worksheet.InsertRow(Row +1, 1, ExcelInsertOptions.FormatAsBefore);
}
}
// remove the empty rows
worksheet.DeleteRow(insertRow + calcs.Count - 1, calcs.Count - 1);
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert();
//Save the PDF file
pdfDocument.Save(path);
}
else
{
workbook.SaveAs(path);
}
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
private void excelToPdfBtnClick(object sender, RoutedEventArgs e)
{
if (this.textBox1.Text != String.Empty && (string)this.textBox1.Tag != string.Empty)
{
if (File.Exists((string)this.textBox1.Tag))
{
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter((string)this.textBox1.Tag);
//Intialize the PdfDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
if ((bool)noScaleRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if ((bool)allRowsRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if ((bool)allColumnRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save("ExceltoPDF.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Conversion Successful. Do you want to view the PDF File?", "Status", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("ExceltoPDF.pdf")
{
UseShellExecute = true
};
process.Start();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Stop);
}
}
}
else
{
MessageBox.Show("File doesn’t exist");
}
}
else
{
MessageBox.Show("Browse an Excel document to convert to Pdf", "Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
}
public PdfPage ConvertOne(int Id)
{
EmployeeDTR emp = _ds.M.DTRs[Id];
using (ExcelEngine E = new ExcelEngine()) {
string filename = "template.xlsx";
IWorkbook wb = E.Excel.Workbooks.Open(filename);
IWorksheet ws;
wb.Version = ExcelVersion.Excel2013;
ws = wb.Worksheets["template"];
ws.Unprotect("0xc00000190132424343");
ws.Range["A14:Q44"].Clear();
// set name
ws.Range["A6"].Text = emp.Name;
ws.Range["J6"].Text = emp.Name;
int rw = 14;
string rw2 = rw.ToString();
foreach (WorkDay wd in _ds.M.DTRs[Id].Workdays.Values)
{
//names
ws.Range['A' + rw2].Text = wd.Id;
ws.Range['J' + rw2].Text = wd.Id;
// am time in
ws.Range['B' + rw2].Text = wd.TimeIn_AM.ToString() != "00:00:00" ? wd.TimeIn_AM.ToString(@"hh\:mm") : "";
ws.Range['K' + rw2].Text = wd.TimeIn_AM.ToString() != "00:00:00" ? wd.TimeIn_AM.ToString(@"hh\:mm") : "";
// am time out
ws.Range['C' + rw2].Text = wd.TimeOut_AM.ToString() != "00:00:00" ? wd.TimeOut_AM.ToString(@"hh\:mm") : "";
ws.Range['L' + rw2].Text = wd.TimeOut_AM.ToString() != "00:00:00" ? wd.TimeOut_AM.ToString(@"hh\:mm") : "";
// pm time in
ws.Range['E' + rw2].Text = wd.TimeIn_PM.ToString() != "00:00:00" ? wd.TimeIn_PM.ToString(@"hh\:mm") : "";
ws.Range['N' + rw2].Text = wd.TimeIn_PM.ToString() != "00:00:00" ? wd.TimeIn_PM.ToString(@"hh\:mm") : "";
// pm time out
ws.Range['F' + rw2].Text = wd.TimeOut_PM.ToString() != "00:00:00" ? wd.TimeOut_PM.ToString(@"hh\:mm") : "";
ws.Range['O' + rw2].Text = wd.TimeOut_PM.ToString() != "00:00:00" ? wd.TimeOut_PM.ToString(@"hh\:mm") : "";
}
ws.Protect("0xc00000190132424343");
wb.Save();
//save as pdf
string _reportfilename = "dtr.pdf";
//remove existing one to avoid errors
if (File.Exists(_reportfilename))
{
File.Delete(_reportfilename);
}
// initialize pdf
PdfDocument _pdfdoc = new PdfDocument();
PdfPage _pdfpage = new PdfPage();
// pdf converter object
ExcelToPdfConverter _converter = new ExcelToPdfConverter(wb);
// pdf settings
ExcelToPdfConverterSettings _settings = new ExcelToPdfConverterSettings();
_settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
_settings.TemplateDocument = _pdfdoc;
_settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
_pdfdoc.PageSettings.Orientation = PdfPageOrientation.Landscape;
_pdfdoc.PageSettings.Size = new PdfPageSettings().Size = PdfPageSize.Letter;
// convert now
_pdfdoc = _converter.Convert(_settings);
return(_pdfdoc.Pages[0]);
}
}
private void excelToPdfBtnClick(object sender, RoutedEventArgs e)
{
if (this.textBox1.Text != String.Empty && (string)this.textBox1.Tag != string.Empty)
{
if (File.Exists((string)this.textBox1.Tag))
{
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook book = application.Workbooks.Open((string)textBox1.Tag);
if (checkfontName.IsChecked.Value || checkfontStream.IsChecked.Value)
{
application.SubstituteFont += new SubstituteFontEventHandler(SubstituteFont);
}
application.ChartToImageConverter = new ChartToImageConverter();
//Set the Quality of chart Image
application.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(book);
//Intialize the PDFDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfConverter Settings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
if ((bool)noScaleRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if ((bool)allRowsRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if ((bool)allColumnRadioBtn.IsChecked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PdfDocument to the templateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the PDF file
pdfDoc.Save("ExceltoPdf.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Conversion Successful. Do you want to view the PDF File?", "Status", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
try
{
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(@"ExceltoPdf.pdf")
{
UseShellExecute = true
};
process.Start();
#else
System.Diagnostics.Process.Start(@"ExceltoPdf.pdf");
#endif
//Exit
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Stop);
}
}
}
else
{
MessageBox.Show("File doesn’t exist");
}
}
else
{
MessageBox.Show("Browse an Excel document to convert to Pdf", "Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
}
public string GeneratePdf(string value, string type, string authKey)
{
if (!_authKey.Equals(authKey))
{
throw new AuthenticationException("AuthKey not valid");
}
PdfDocument pdf;
MemoryStream stream;
switch (type)
{
// Word
case ".doc":
case ".docx":
case ".docm":
case ".dotm":
case ".rtf":
case ".dot":
case ".dotx":
// Retrieve the Blob Uri as a String
using (WebClient webClient = new WebClient())
{
try
{
// Retrive the Blob URI (Document) and convert it into a bytearray.
// From there it is Base-64 encoded for sending via Service call.
byte[] bytes = webClient.DownloadData(value);
logger.Info($"111 Loading Word Document with value: {value}");
//Loading word document
WordDocument document = new WordDocument(new MemoryStream(bytes));
logger.Info("Created Word Document");
DocToPDFConverter conv = new DocToPDFConverter();
//Converts the word document to pdf
pdf = conv.ConvertToPDF(document);
logger.Info("Converted to PDF");
stream = new MemoryStream();
//Saves the Pdf document to stream
pdf.Save(stream);
logger.Info("Saved PDF to Stream");
//Sets the stream position
stream.Position = 0;
pdf.Close();
document.Close();
//Returns the stream data as Base 64 string
string returnData = Convert.ToBase64String(stream.ToArray());
logger.Info($"Returing converted stream as Base 64 data: {returnData}");
return(returnData);
}
catch (Exception ex)
{
logger.Error(ex);
throw;
}
}
// Clean up old files
// Excel
case ".xlsx":
case ".xlsm":
case ".xlsb":
case ".xls":
case ".xltx":
case ".csv":
// Retrieve the Blob Uri as a String
using (WebClient webClient = new WebClient())
{
try
{
// Retrive the Blob URI (Document) and convert it into a bytearray.
// From there it is Base-64 encoded for sending via Service call.
byte[] bytes = webClient.DownloadData(value);
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.ChartToImageConverter = new ChartToImageConverter();
IWorkbook workbook =
application.Workbooks.Open(new MemoryStream(bytes), ExcelOpenType.Automatic);
ExcelToPdfConverter convert = new ExcelToPdfConverter(workbook);
pdf = convert.Convert();
stream = new MemoryStream();
//Saves the Pdf document to stream
pdf.Save(stream);
//Sets the stream position
stream.Position = 0;
pdf.Close();
workbook.Close();
//Returns the stream data as Base 64 string
return(Convert.ToBase64String(stream.ToArray()));
}
catch (Exception ex)
{
logger.Error(ex);
throw;
}
}
// PowerPoint
case ".pptx":
case ".pptm":
case ".ppt":
case ".potx":
case ".ppsx":
case ".potm":
case ".pot":
case ".ppsm":
case ".pps":
// Retrieve the Blob Uri as a String
using (WebClient webClient = new WebClient())
{
try
{
// Retrive the Blob URI (Document) and convert it into a bytearray.
// From there it is Base-64 encoded for sending via Service call.
byte[] bytes = webClient.DownloadData(value);
//Opens a PowerPoint Presentation
IPresentation presentation = Presentation.Open(new MemoryStream(bytes));
//Creates an instance of ChartToImageConverter and assigns it to ChartToImageConverter property of Presentation
presentation.ChartToImageConverter = new Syncfusion.OfficeChartToImageConverter.ChartToImageConverter();
//Converts the PowerPoint Presentation into PDF document
pdf = PresentationToPdfConverter.Convert(presentation);
stream = new MemoryStream();
//Saves the PDF document
pdf.Save(stream);
//Sets the stream position
stream.Position = 0;
pdf.Close();
presentation.Close();
//Returns the stream data as Base 64 string
return(Convert.ToBase64String(stream.ToArray()));
}
catch (Exception ex)
{
logger.Error(ex);
throw;
}
}
}
return(null);
}
//
// GET: /PivotTable/
public ActionResult PivotLayout(string button, string LayoutOption)
{
if (button == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = null;
application.DefaultVersion = ExcelVersion.Excel2016;
if (button == "Convert Pivot Table")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath("PivotLayout.xlsx"));
CreatePivotTable(workbook, LayoutOption);
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the PdfDocument Class
PdfDocument pdfDoc = new PdfDocument();
//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
pdfDoc = converter.Convert(settings);
try
{
return(new Syncfusion.Mvc.Pdf.PdfResult(pdfDoc, "PivotLayout.pdf", HttpContext.ApplicationInstance.Response, HttpReadType.Save));
}
catch (Exception)
{
}
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath("PivotLayout.xlsx"));
CreatePivotTable(workbook, LayoutOption);
IPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0];
pivotTable.Layout();
//To view the pivot table inline formatting in MS Excel, we have to set the IsRefreshOnLoad property as true.
(workbook.PivotCaches[pivotTable.CacheIndex] as PivotCacheImpl).IsRefreshOnLoad = true;
try
{
return(excelEngine.SaveAsActionResult(workbook, "PivotLayout.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
private void excelToPdfConvertBtnClick(object sender, EventArgs e)
{
if (this.textBox1.Text != String.Empty)
{
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook book = application.Workbooks.Open((string)textBox1.Tag);
application.ChartToImageConverter = new ChartToImageConverter();
//Set the Quality of chart Image
application.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(book);
//Intialize the PDFDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfconverterSettings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (noScaleRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (allRowsRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (allColumnRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PDFDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the pdf file
pdfDoc.Save("ExceltoPDF.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the PDF file?", "File has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process.Start(@"ExceltoPDF.pdf");
//Exit
this.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
else
{
MessageBox.Show("Browse a word document and click the button to convert as a PDF.");
}
}
public static bool WriteCalculationsToExporterTemplate(ObservableCollection <Calculation3d> calcs,
string path, ExporterTemplate template, bool asPdf = false
)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
bool targetIsFolder = false;
application.DefaultVersion = ExcelVersion.Excel2013;
string ext = Path.GetExtension(path);
targetIsFolder = string.IsNullOrEmpty(ext);
string root = Path.GetDirectoryName(path);
string filename = string.Format(@"{0}_{1}{2}",
Regex.Replace(Strings.MyCalculation, "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
Regex.Replace("1", "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
string.IsNullOrEmpty(ext) ? asPdf ? ".pdf" : ".xlsx" : ext
);
filename = getDuplicatedFileName(targetIsFolder ? path : root, filename);
//Create a workbook
IWorkbook workbook = application.Workbooks.Open(template.TemplatePath, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
foreach (ExporterSettings setting in template.Settings)
{
worksheet = workbook.Worksheets[setting.WorkSheetName];
switch (setting.Attribute.Property)
{
#region List
case ExporterProperty.CalculationList:
int insertRow = Convert.ToInt32(setting.Coordinates.Row);
IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle;
if (calcs.Count != 1)
{
worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter);
}
//Go to list
for (int i = 0; i < calcs.Count; i++)
{
foreach (Printer3d printer in calcs[i].Printers)
{
foreach (Material3d material in calcs[i].Materials)
{
int Row = Convert.ToInt32(insertRow) + i;
string Col = setting.Coordinates.Column;
// Pos
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1;
calcs[i].Material = material;
calcs[i].Printer = printer;
// Description
StringBuilder sb = new StringBuilder();
sb.Append(calcs[i].Name);
/*
* sb.AppendLine(string.Format("Volume: {0} | Time: {1}",
* Convert.ToDouble(calcs[i].Volume),
* Convert.ToDouble(calcs[i].CalculatedPrintTime))
* );
*/
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString();
// Quantity
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0";
// Single
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity)));
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
// Total
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
if (i < calcs.Count - 1)
{
}
}
}
}
// remove the empty rows
if (calcs.Count != 1)
{
worksheet.DeleteRow(insertRow + calcs.Count, calcs.Count - 1);
}
break;
#endregion
}
}
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert();
//Save the PDF file
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
pdfDocument.Save(savePath);
}
else
{
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
workbook.SaveAs(savePath);
}
workbook.Close();
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
public static bool ExportCaclulations(ObservableCollection <Calculation3d> calcs, string path, bool asPdf = false)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
worksheet.UsedRange.AutofitColumns();
// Create Header
worksheet.Range["A1"].Text = Strings.Printer;
//worksheet.Range["A1"].ColumnWidth = 80;
worksheet.Range["B1"].Text = Strings.Material;
//worksheet.Range["B1"].ColumnWidth = 80;
worksheet.Range["C1"].Text = Strings.Name;
//worksheet.Range["C1"].ColumnWidth = 80;
worksheet.Range["D1"].Text = "Volume";
worksheet.Range["E1"].Text = "Quantity";
worksheet.Range["F1"].Text = "Print Time";
worksheet.Range["G1"].Text = "Print Costs";
worksheet.Range["H1"].Text = "Material Costs";
worksheet.Range["I1"].Text = "Enregy Costs";
worksheet.Range["J1"].Text = "Worksteps";
worksheet.Range["K1"].Text = "Handling";
worksheet.Range["L1"].Text = "Margin";
worksheet.Range["M1"].Text = "Tax";
worksheet.Range["N1"].Text = "Total";
worksheet.Range["A1:N1"].CellStyle.Color = Color.LightGray;
worksheet.Range["A1:N1"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["A1:N1"].RowHeight = 25;
worksheet.Range["A1:N1"].CellStyle.Font.Bold = true;
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
//Go to list
int currentRow = 2;
for (int i = 0; i < calcs.Count; i++)
{
foreach (Printer3d printer in calcs[i].Printers)
{
foreach (Material3d material in calcs[i].Materials)
{
//Printer Name
worksheet.Range[string.Format("A{0}", currentRow)].Text = printer.Name;
//Material name
worksheet.Range[string.Format("B{0}", currentRow)].Text = material.Name;
//Name
worksheet.Range[string.Format("C{0}", currentRow)].Text = calcs[i].Name;
//Volume
worksheet.Range[string.Format("D{0}", currentRow)].Number = Convert.ToDouble(calcs[i].TotalVolume);
worksheet.Range[string.Format("D{0}", currentRow)].NumberFormat = "0.00";
//Quantity
worksheet.Range[string.Format("E{0}", currentRow)].Number = Convert.ToDouble(calcs[i].Quantity);
worksheet.Range[string.Format("E{0}", currentRow)].NumberFormat = "0";
//Print time
worksheet.Range[string.Format("F{0}", currentRow)].Number = Convert.ToDouble(calcs[i].TotalPrintTime);
worksheet.Range[string.Format("F{0}", currentRow)].NumberFormat = "0.000";
//Print Costs
calcs[i].Material = material;
calcs[i].Printer = printer;
var printCosts = calcs[i].MachineCosts;
worksheet.Range[string.Format("G{0}", currentRow)].Number = Convert.ToDouble(printCosts);
worksheet.Range[string.Format("G{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
//Material costs
var materialCosts = calcs[i].MaterialCosts;
worksheet.Range[string.Format("H{0}", currentRow)].Number = Convert.ToDouble(materialCosts);
worksheet.Range[string.Format("H{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
//Energy costs
var energyCosts = calcs[i].EnergyCosts;
worksheet.Range[string.Format("I{0}", currentRow)].Number = Convert.ToDouble(energyCosts);
worksheet.Range[string.Format("I{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
// Worksteps
var workstepCosts = calcs[i].WorkstepCosts;
worksheet.Range[string.Format("J{0}", currentRow)].Number = Convert.ToDouble(workstepCosts);
worksheet.Range[string.Format("J{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
// Handling fee
worksheet.Range[string.Format("K{0}", currentRow)].Number = Convert.ToDouble(calcs[i].getTotalCosts(CalculationAttributeType.FixCost));
worksheet.Range[string.Format("K{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
// Margin
worksheet.Range[string.Format("L{0}", currentRow)].Number = Convert.ToDouble(calcs[i].CalculatedMargin);
worksheet.Range[string.Format("L{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
//Tax
worksheet.Range[string.Format("M{0}", currentRow)].Number = Convert.ToDouble(calcs[i].CalculatedTax);
worksheet.Range[string.Format("M{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
//Total
worksheet.Range[string.Format("N{0}", currentRow)].Number = Convert.ToDouble(calcs[i].TotalCosts);
worksheet.Range[string.Format("N{0}", currentRow)].NumberFormat = currencySymbol + "#,##0.00";
currentRow++;
}
}
}
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
settings.LayoutOptions = LayoutOptions.Automatic;
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert(settings);
//Save the PDF file
pdfDocument.Save(path);
}
else
{
workbook.SaveAs(path);
}
workbook.Close();
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
public static bool WriteCalculationToExporterTemplate(Calculation3d calculation,
string path, ExporterTemplate template, bool asPdf = false
)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
bool targetIsFolder = false;
application.DefaultVersion = ExcelVersion.Excel2013;
string ext = Path.GetExtension(path);
targetIsFolder = string.IsNullOrEmpty(ext);
string root = Path.GetDirectoryName(path);
// Foreach printer...
foreach (Printer3d printer in calculation.Printers)
{
//... and material
foreach (Material3d material in calculation.Materials)
{
string filename = string.Format(@"{0}_{1}{2}",
Regex.Replace(printer.ToString(), "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
Regex.Replace(material.ToString(), "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
string.IsNullOrEmpty(ext) ? asPdf ? ".pdf" : ".xlsx" : ext
);
//Create a workbook
IWorkbook workbook = application.Workbooks.Open(template.TemplatePath, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
foreach (ExporterSettings setting in template.Settings)
{
worksheet = workbook.Worksheets[setting.WorkSheetName];
switch (setting.Attribute.Property)
{
#region Prices
case ExporterProperty.CalculationMargin:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.CalculatedMargin);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceMaterial:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.MaterialCosts);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceEnergy:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.EnergyCosts);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceHandling:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.getTotalCosts(CalculationAttributeType.FixCost));
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPricePrinter:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.MachineCosts);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceTax:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.CalculatedTax);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceWorksteps:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.WorkstepCosts);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
case ExporterProperty.CalculationPriceTotal:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.TotalCosts);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = currencySymbol + "#,##0.00";
break;
#endregion
#region Material
case ExporterProperty.CalculationMaterial:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Text
= material.ToString();
break;
#endregion
#region Printer
case ExporterProperty.CalculationPrinter:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Text
= printer.ToString();
break;
#endregion
#region Misc
case ExporterProperty.CalculationFailrate:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.FailRate);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = "#0 %";
break;
case ExporterProperty.CalculationPrintTime:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.TotalPrintTime);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = "#0 %";
break;
case ExporterProperty.CalculationQuantity:
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].Number
= Convert.ToDouble(calculation.Quantity);
worksheet.Range[string.Format("{0}{1}", setting.Coordinates.Column, setting.Coordinates.Row)].NumberFormat = "0";
break;
#endregion
}
}
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert();
//Save the PDF file
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
pdfDocument.Save(savePath);
}
else
{
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
workbook.SaveAs(savePath);
}
workbook.Close();
}
}
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
/// <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 void excelToPdfConvertBtnClick(object sender, EventArgs e)
{
if (this.textBox1.Text != String.Empty)
{
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
if (checkfontName.Checked || checkfontStream.Checked)
{
application.SubstituteFont += new SubstituteFontEventHandler(SubstituteFont);
}
IWorkbook book = application.Workbooks.Open((string)textBox1.Tag);
application.ChartToImageConverter = new ChartToImageConverter();
//Set the Quality of chart Image
application.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(book);
//Intialize the PDFDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfconverterSettings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (noScaleRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (allRowsRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (allColumnRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PDFDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
settings.EnableFormFields = true;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the pdf file
pdfDoc.Save("ExceltoPDF.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the PDF file?", "File has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("ExceltoPDF.pdf")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("ExceltoPDF.pdf");
#endif
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
else
{
MessageBox.Show("Browse an Excel document and click the button to convert as a PDF.");
}
}