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