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