private void btnReadFormula_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//Open the workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
string fullPath = Path.GetFullPath(DEFAULTPATH);
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.EnableSheetCalculations();
#region Formula and Computed value
//Read computed Formula Value.
this.txtFomulaNumber.Text = worksheet.Range["A1"].CalculatedValue;
//Read Formula
this.txtFormula.Text = worksheet.Range["A1"].Formula;
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
}
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();
}
public void Open(string filename)
{
m_excelEngine = new ExcelEngine();
m_application = m_excelEngine.Excel;
m_application.DefaultVersion = ExcelVersion.Excel2013;
Stream fileStream = Utils.OpenFile(filename);
fileStream.Position = 0;
m_workbook = m_application.Workbooks.Open(fileStream);
m_workbook.Version = ExcelVersion.Excel2013;
m_sheet = m_workbook.Worksheets[0];
m_numberSheets = m_workbook.Worksheets.Count;
m_sheet.EnableSheetCalculations();
fileStream.Close();
}
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();
}
//
// GET: /ComputeAllformulas/
public ActionResult ComputeAllformulas(string Saveoption)
{
if (Saveoption == 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;
//Get the path of the input file.
IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("TimelogTemplate.xls"));
IWorksheet worksheet = myWorkbook.Worksheets[0];
string FileName = "";
// set the workbook version
if (Saveoption == "Xls")
{
myWorkbook.Version = ExcelVersion.Excel97to2003;
FileName = "ComputeFormulas.xls";
}
else
{
myWorkbook.Version = ExcelVersion.Excel2016;
FileName = "ComputeFormulas.xlsx";
}
//Enable to calculate the formulas in the sheet.s
worksheet.EnableSheetCalculations();
//hourly rate
worksheet["G7"].Number = 11;
//overtime rate.
worksheet["J7"].Formula = "=SUM(G7*1.5)";
//Regular hours
worksheet["F10"].Formula = "=IF((((C10-B10)+(E10-D10))*24)>8,8,((C10-B10)+(E10-D10))*24)";
worksheet["F11"].Formula = "=IF((((C11-B11)+(E11-D11))*24)>8,8,((C11-B11)+(E11-D11))*24)";
worksheet["F12"].Formula = "=IF((((C12-B12)+(E12-D12))*24)>8,8,((C12-B12)+(E12-D12))*24)";
worksheet["F13"].Formula = "=IF((((C13-B13)+(E13-D13))*24)>8,8,((C13-B13)+(E13-D13))*24)";
worksheet["F14"].Formula = "=IF((((C14-B14)+(E14-D14))*24)>8,8,((C14-B14)+(E14-D14))*24)";
worksheet["F15"].Formula = "=IF((((C15-B15)+(E15-D15))*24)>8,8,((C15-B15)+(E15-D15))*24)";
//overtime hours
worksheet["G10"].Formula = "=IF(((C10-B10)+(E10-D10))*24>8, ((C10-B10)+(E10-D10))*24-8,0)";
worksheet["G11"].Formula = "=IF(((C11-B11)+(E11-D11))*24>8, ((C11-B11)+(E11-D11))*24-8,0)";
worksheet["G12"].Formula = "=IF(((C12-B12)+(E12-D12))*24>8, ((C12-B12)+(E12-D12))*24-8,0)";
worksheet["G13"].Formula = "=IF(((C13-B13)+(E13-D13))*24>8, ((C13-B13)+(E13-D13))*24-8,0)";
worksheet["G14"].Formula = "=IF(((C14-B14)+(E14-D14))*24>8, ((C14-B14)+(E14-D14))*24-8,0)";
worksheet["G15"].Formula = "=IF(((C15-B15)+(E15-D15))*24>8, ((C15-B15)+(E15-D15))*24-8,0)";
//regular pay
worksheet["H10"].Formula = "=SUM(F10*G7)";
worksheet["H11"].Formula = "=SUM(F11*G7)";
worksheet["H12"].Formula = "=SUM(F12*G7)";
worksheet["H13"].Formula = "=SUM(F13*G7)";
worksheet["H14"].Formula = "=SUM(F14*G7)";
worksheet["H15"].Formula = "=SUM(F15*G7)";
//overtime pay
worksheet["I10"].Formula = "=SUM(G10*J7)";
worksheet["I11"].Formula = "=SUM(G11*J7)";
worksheet["I12"].Formula = "=SUM(G12*J7)";
worksheet["I13"].Formula = "=SUM(G13*J7)";
worksheet["I14"].Formula = "=SUM(G14*J7)";
worksheet["I15"].Formula = "=SUM(G15*J7)";
//total pay
worksheet["J10"].Formula = "=SUM(H10+I10)";
worksheet["J11"].Formula = "=SUM(H11+I11)";
worksheet["J12"].Formula = "=SUM(H12+I12)";
worksheet["J13"].Formula = "=SUM(H13+I13)";
worksheet["J14"].Formula = "=SUM(H14+I14)";
worksheet["J15"].Formula = "=SUM(H15+I15)";
//total regular hours
worksheet["F17"].Formula = "=SUM(F10:F15)";
//total overtime hours
worksheet["G17"].Formula = "=SUM(G10:G15)";
//total regular pay
worksheet["H17"].Formula = "=SUM(H10:H15)";
//total overtime pay
worksheet["I17"].Formula = "=SUM(I10:I15)";
//total pay
worksheet["J17"].Formula = "=SUM(J10:J15)";
//consolidated pay
worksheet["C20"].Formula = "=J17";
//allowance
worksheet["C21"].Number = 20;
//PF
worksheet["C22"].Number = 38;
//Net pay
worksheet["C24"].Formula = "=SUM(C20:C21)-C22";
try
{
//Saving the workbook to disk.
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(myWorkbook, FileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(myWorkbook, FileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
myWorkbook.Close();
excelEngine.Dispose();
return(View());
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet1 = workbook.Worksheets[0];
sheet1.Name = "Budget";
sheet1.IsGridLinesVisible = false;
sheet1.EnableSheetCalculations();
sheet1.Range[1, 1].ColumnWidth = 19.86;
sheet1.Range[1, 2].ColumnWidth = 14.38;
sheet1.Range[1, 3].ColumnWidth = 12.98;
sheet1.Range[1, 4].ColumnWidth = 12.08;
sheet1.Range[1, 5].ColumnWidth = 8.82;
sheet1.Range["A1:A18"].RowHeight = 20.2;
//Adding cell style.
IStyle style1 = workbook.Styles.Add("style1");
style1.Color = Syncfusion.Drawing.Color.FromArgb(217, 225, 242);
style1.HorizontalAlignment = ExcelHAlign.HAlignLeft;
style1.VerticalAlignment = ExcelVAlign.VAlignCenter;
style1.Font.Bold = true;
IStyle style2 = workbook.Styles.Add("style2");
style2.Color = Syncfusion.Drawing.Color.FromArgb(142, 169, 219);
style2.VerticalAlignment = ExcelVAlign.VAlignCenter;
style2.NumberFormat = "[Red]($#,###)";
style2.Font.Bold = true;
sheet1.Range["A10"].CellStyle = style1;
sheet1.Range["B10:D10"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(217, 225, 242);
sheet1.Range["B10:D10"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet1.Range["B10:D10"].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["B10:D10"].CellStyle.Font.Bold = true;
sheet1.Range["A11:A17"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_25_percent;
sheet1.Range["D18"].CellStyle = style2;
sheet1.Range["D18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(142, 169, 219);
sheet1.Range["A18:C18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Font.Bold = true;
sheet1.Range["A18:C18"].NumberFormat = "$#,###";
sheet1.Range[10, 1].Text = "Category";
sheet1.Range[10, 2].Text = "Expected cost";
sheet1.Range[10, 3].Text = "Actual Cost";
sheet1.Range[10, 4].Text = "Difference";
sheet1.Range[11, 1].Text = "Venue";
sheet1.Range[12, 1].Text = "Seating & Decor";
sheet1.Range[13, 1].Text = "Technical team";
sheet1.Range[14, 1].Text = "Performers";
sheet1.Range[15, 1].Text = "Performer\'s transport";
sheet1.Range[16, 1].Text = "Performer\'s stay";
sheet1.Range[17, 1].Text = "Marketing";
sheet1.Range[18, 1].Text = "Total";
sheet1.Range["B11:D17"].NumberFormat = "$#,###";
sheet1.Range["D11"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D12"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D14"].NumberFormat = "[Red]($#,###)";
sheet1.Range["B11"].Number = 16250;
sheet1.Range["B12"].Number = 1600;
sheet1.Range["B13"].Number = 1000;
sheet1.Range["B14"].Number = 12400;
sheet1.Range["B15"].Number = 3000;
sheet1.Range["B16"].Number = 4500;
sheet1.Range["B17"].Number = 3000;
sheet1.Range["B18"].Formula = "=SUM(B11:B17)";
sheet1.Range["C11"].Number = 17500;
sheet1.Range["C12"].Number = 1828;
sheet1.Range["C13"].Number = 800;
sheet1.Range["C14"].Number = 14000;
sheet1.Range["C15"].Number = 2600;
sheet1.Range["C16"].Number = 4464;
sheet1.Range["C17"].Number = 2700;
sheet1.Range["C18"].Formula = "=SUM(C11:C17)";
sheet1.Range["D11"].Formula = "=IF(C11>B11,C11-B11,B11-C11)";
sheet1.Range["D12"].Formula = "=IF(C12>B12,C12-B12,B12-C12)";
sheet1.Range["D13"].Formula = "=IF(C13>B13,C13-B13,B13-C13)";
sheet1.Range["D14"].Formula = "=IF(C14>B14,C14-B14,B14-C14)";
sheet1.Range["D15"].Formula = "=IF(C15>B15,C15-B15,B15-C15)";
sheet1.Range["D16"].Formula = "=IF(C16>B16,C16-B16,B16-C16)";
sheet1.Range["D17"].Formula = "=IF(C17>B17,C17-B17,B17-C17)";
sheet1.Range["D18"].Formula = "=IF(C18>B18,C18-B18,B18-C18)";
IChartShape chart = sheet1.Charts.Add();
chart.ChartType = ExcelChartType.Pie;
chart.DataRange = sheet1.Range["A11:B17"];
chart.IsSeriesInRows = false;
chart.ChartTitle = "Event Expenses";
chart.ChartTitleArea.Bold = true;
chart.ChartTitleArea.Size = 16;
chart.TopRow = 1;
chart.BottomRow = 10;
chart.LeftColumn = 1;
chart.RightColumn = 5;
chart.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
string OutputFilename = "ExpensesReport.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
internal void OnButtonClicked(object sender, EventArgs e)
{
//Instantiate excel engine
ExcelEngine excelEngine = new ExcelEngine();
//Excel application
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
// Generate Excel
sheet.EnableSheetCalculations();
sheet.Range["A2"].ColumnWidth = 20;
sheet.Range["B2"].ColumnWidth = 13;
sheet.Range["C2"].ColumnWidth = 13;
sheet.Range["D2"].ColumnWidth = 13;
sheet.Range["A2:D2"].Merge(true);
//Inserting sample text into the first cell of the first worksheet.
sheet.Range["A2"].Text = "EXPENSE REPORT";
sheet.Range["A2"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 28;
sheet.Range["A2"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A2"].RowHeight = 34;
sheet.Range["A4"].Text = "Employee";
sheet.Range["B4"].Text = "Roger Federer";
sheet.Range["A4:B7"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A4:B7"].CellStyle.Font.Bold = true;
sheet.Range["A4:B7"].CellStyle.Font.Size = 11;
sheet.Range["A4:A7"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 128, 128, 128);
sheet.Range["A4:A7"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["B4:B7"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 174, 170, 170);
sheet.Range["B4:B7"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B4:D4"].Merge(true);
sheet.Range["A9:D20"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A9:D20"].CellStyle.Font.Size = 11;
sheet.Range["A5"].Text = "Department";
sheet.Range["B5"].Text = "Administration";
sheet.Range["B5:D5"].Merge(true);
sheet.Range["A6"].Text = "Week Ending";
sheet.Range["B6"].NumberFormat = "m/d/yyyy";
sheet.Range["B6"].DateTime = DateTime.Parse("10/10/2012");
sheet.Range["B6:D6"].Merge(true);
sheet.Range["A7"].Text = "Mileage Rate";
sheet.Range["B7"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Number = 0.70;
sheet.Range["B7:D7"].Merge(true);
sheet.Range["A10"].Text = "Miles Driven";
sheet.Range["A11"].Text = "Reimbursement";
sheet.Range["A12"].Text = "Parking/Tolls";
sheet.Range["A13"].Text = "Auto Rental";
sheet.Range["A14"].Text = "Lodging";
sheet.Range["A15"].Text = "Breakfast";
sheet.Range["A16"].Text = "Lunch";
sheet.Range["A17"].Text = "Dinner";
sheet.Range["A18"].Text = "Snacks";
sheet.Range["A19"].Text = "Others";
sheet.Range["A20"].Text = "Total";
sheet.Range["A20:D20"].CellStyle.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A20:D20"].CellStyle.Font.Color = ExcelKnownColors.Black;
sheet.Range["A20:D20"].CellStyle.Font.Bold = true;
IStyle style = sheet["B9:D9"].CellStyle;
style.VerticalAlignment = ExcelVAlign.VAlignCenter;
style.HorizontalAlignment = ExcelHAlign.HAlignRight;
style.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
style.Font.Bold = true;
style.Font.Color = ExcelKnownColors.Black;
sheet.Range["A9"].Text = "Expenses";
sheet.Range["A9"].CellStyle.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A9"].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range["A9"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Day 1";
sheet.Range["B10"].Number = 100;
sheet.Range["B11"].NumberFormat = "$#,##0.00";
sheet.Range["B11"].Formula = "=(B7*B10)";
sheet.Range["B12"].NumberFormat = "$#,##0.00";
sheet.Range["B12"].Number = 0;
sheet.Range["B13"].NumberFormat = "$#,##0.00";
sheet.Range["B13"].Number = 0;
sheet.Range["B14"].NumberFormat = "$#,##0.00";
sheet.Range["B14"].Number = 0;
sheet.Range["B15"].NumberFormat = "$#,##0.00";
sheet.Range["B15"].Number = 9;
sheet.Range["B16"].NumberFormat = "$#,##0.00";
sheet.Range["B16"].Number = 12;
sheet.Range["B17"].NumberFormat = "$#,##0.00";
sheet.Range["B17"].Number = 13;
sheet.Range["B18"].NumberFormat = "$#,##0.00";
sheet.Range["B18"].Number = 9.5;
sheet.Range["B19"].NumberFormat = "$#,##0.00";
sheet.Range["B19"].Number = 0;
sheet.Range["B20"].NumberFormat = "$#,##0.00";
sheet.Range["B20"].Formula = "=SUM(B11:B19)";
sheet.Range["C9"].Text = "Day 2";
sheet.Range["C10"].Number = 145;
sheet.Range["C11"].NumberFormat = "$#,##0.00";
sheet.Range["C11"].Formula = "=(B7*C10)";
sheet.Range["C12"].NumberFormat = "$#,##0.00";
sheet.Range["C12"].Number = 15;
sheet.Range["C13"].NumberFormat = "$#,##0.00";
sheet.Range["C13"].Number = 0;
sheet.Range["C14"].NumberFormat = "$#,##0.00";
sheet.Range["C14"].Number = 45;
sheet.Range["C15"].NumberFormat = "$#,##0.00";
sheet.Range["C15"].Number = 9;
sheet.Range["C16"].NumberFormat = "$#,##0.00";
sheet.Range["C16"].Number = 12;
sheet.Range["C17"].NumberFormat = "$#,##0.00";
sheet.Range["C17"].Number = 15;
sheet.Range["C18"].NumberFormat = "$#,##0.00";
sheet.Range["C18"].Number = 7;
sheet.Range["C19"].NumberFormat = "$#,##0.00";
sheet.Range["C19"].Number = 0;
sheet.Range["C20"].NumberFormat = "$#,##0.00";
sheet.Range["C20"].Formula = "=SUM(C11:C19)";
sheet.Range["D9"].Text = "Day 3";
sheet.Range["D10"].Number = 113;
sheet.Range["D11"].NumberFormat = "$#,##0.00";
sheet.Range["D11"].Formula = "=(B7*D10)";
sheet.Range["D12"].NumberFormat = "$#,##0.00";
sheet.Range["D12"].Number = 17;
sheet.Range["D13"].NumberFormat = "$#,##0.00";
sheet.Range["D13"].Number = 8;
sheet.Range["D14"].NumberFormat = "$#,##0.00";
sheet.Range["D14"].Number = 45;
sheet.Range["D15"].NumberFormat = "$#,##0.00";
sheet.Range["D15"].Number = 7;
sheet.Range["D16"].NumberFormat = "$#,##0.00";
sheet.Range["D16"].Number = 11;
sheet.Range["D17"].NumberFormat = "$#,##0.00";
sheet.Range["D17"].Number = 16;
sheet.Range["D18"].NumberFormat = "$#,##0.00";
sheet.Range["D18"].Number = 7;
sheet.Range["D19"].NumberFormat = "$#,##0.00";
sheet.Range["D19"].Number = 5;
sheet.Range["D20"].NumberFormat = "$#,##0.00";
sheet.Range["D20"].Formula = "=SUM(D11:D19)";
sheet.Range["A10:D10"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 174, 170, 170);
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("CreateSheet.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("CreateSheet.xlsx", "application/msexcel", stream);
}
}
private void button1_Click(object sender, EventArgs e)
{
eng = new ExcelEngine();
#if NETCORE
workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\..\Common\Data\XlsIO\Input.xls");
#else
workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\Input.xls");
#endif
IWorksheet sheet = workBook.Worksheets[0];
IWorksheet sheet2 = workBook.Worksheets[1];
//Enable to calculate formulas in the sheet.
sheet.EnableSheetCalculations();
//Assign the sheet calcEngine.
calcEngine = sheet.CalcEngine;
//Load the datagrids with the data from Xls file.
DataTable dt = new DataTable("Input Data");
dt = sheet.ExportDataTable(1, 1, 15, 5, ExcelExportDataTableOptions.None);
this.dataGrid1.DataSource = dt;
DataTable dt2 = new DataTable("Sheet2 Data");
dt2 = sheet2.ExportDataTable(1, 1, 15, 8, ExcelExportDataTableOptions.None);
this.dataGrid2.DataSource = dt2;
this.button1.Enabled = false;
DataGridTableStyle tabStyle = new DataGridTableStyle();
tabStyle.MappingName = dt.TableName;
this.dataGrid1.TableStyles.Add(tabStyle);
DataGridTableStyle tabStyle2 = new DataGridTableStyle();
tabStyle2.MappingName = dt2.TableName;
this.dataGrid2.TableStyles.Add(tabStyle2);
for (int i = 0; i < 5; i++)
{
switch (i)
{
case 3:
tabStyle.GridColumnStyles[i].Width = 180; break;
case 4:
tabStyle.GridColumnStyles[i].Width = 210; break;
default:
tabStyle.GridColumnStyles[i].Width = 60; break;
}
tabStyle.GridColumnStyles[i].HeaderText = string.Format("{0}", (char)((int)'A' + i));
tabStyle.GridColumnStyles[i].Alignment = HorizontalAlignment.Left;
}
for (int j = 0; j < 8; j++)
{
tabStyle2.GridColumnStyles[j].Width = 70;
tabStyle2.GridColumnStyles[j].HeaderText = string.Format("{0}", (char)((int)'A' + j));
tabStyle2.GridColumnStyles[j].Alignment = HorizontalAlignment.Left;
}
tabStyle.HeaderBackColor = Color.LightSteelBlue;
tabStyle2.HeaderBackColor = Color.LightSteelBlue;
}
//
// GET: /ExternalFormula/
public ActionResult ExternalFormula(string button)
{
ViewData["computedValue"] = "";
//Read Formula
ViewData["formulaString"] = "";
if (button == null)
{
return(View());
}
else if (button == "Write Formula")
{
//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;
application.DefaultVersion = ExcelVersion.Excel2016;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
string fullPath = new System.IO.DirectoryInfo(Request.PhysicalPath + DEFAULTPATH).FullName;
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.Range["A2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$2";
worksheet.Range["A3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$3";
worksheet.Range["A4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$4";
worksheet.Range["A5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$5";
worksheet.Range["A6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$6";
worksheet.Range["A7"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$7";
worksheet.Range["B1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$1";
worksheet.Range["B2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$2";
worksheet.Range["B3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$3";
worksheet.Range["B4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$4";
worksheet.Range["B5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$5";
worksheet.Range["B6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$6";
worksheet.Range["C1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$1";
worksheet.Range["C2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$2";
worksheet.Range["C3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$3";
worksheet.Range["C4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$4";
worksheet.Range["C5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$5";
worksheet.Range["C6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$6";
worksheet.Range["D1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$1";
worksheet.Range["D2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$2";
worksheet.Range["D3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$3";
worksheet.Range["D4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$4";
worksheet.Range["D5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$5";
worksheet.Range["D6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$6";
worksheet.Range["E1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$1";
worksheet.Range["E2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$2";
worksheet.Range["E3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$3";
worksheet.Range["E4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$4";
worksheet.Range["E5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$5";
worksheet.Range["E6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$6";
worksheet.Range["F1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$F$1";
worksheet.Range["B7"].Formula = "=SUM(B2:B6)";
worksheet.Range["C7"].Formula = "=SUM(C2:C6)";
worksheet.Range["D7"].Formula = "=SUM(D2:D6)";
worksheet.Range["E7"].Formula = "=SUM(E2:E6)";
worksheet.Range["F7"].Formula = "=SUM(F2:F6)";
worksheet.Range["F2"].Formula = "=B2*C2+D2-E2";
worksheet.Range["F3"].Formula = "=B3*C3+D3-E3";
worksheet.Range["F4"].Formula = "=B4*C4+D4-E4";
worksheet.Range["F5"].Formula = "=B5*C5+D5-E5";
worksheet.Range["F6"].Formula = "=B6*C6+D6-E6";
worksheet.Range["A1:F7"].CellStyle.Font.FontName = "Verdana";
worksheet.Range["C2:F7"].NumberFormat = "_($* #,##0.00_)";
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A7:F7"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Font.Size = 11;
worksheet.Columns[0].ColumnWidth = 17;
worksheet.Columns[1].ColumnWidth = 13;
worksheet.Columns[2].ColumnWidth = 11;
worksheet.Columns[3].ColumnWidth = 11;
worksheet.Columns[4].ColumnWidth = 13;
worksheet.Columns[5].ColumnWidth = 13;
worksheet.Calculate();
string fileName = "ExternalFormula.xlsx";
try
{
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
else
{
//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 = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
string fullPath = new System.IO.DirectoryInfo(Request.PhysicalPath + DEFAULTPATH).FullName;
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.EnableSheetCalculations();
//Read computed Formula Value.
ViewData["computedValue"] = worksheet.Range["A1"].CalculatedValue;
//Read Formula
ViewData["formulaString"] = worksheet.Range["A1"].Formula;
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//Get the path of the input file.
string inputPath = GetFullTemplatePath("TimelogTemplate.xls");
IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet worksheet = myWorkbook.Worksheets[0];
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
myWorkbook.Version = ExcelVersion.Excel97to2003;
fileName = "ComputeFormulas.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcek2007.Checked)
{
myWorkbook.Version = ExcelVersion.Excel2007;
fileName = "ComputeFormulas.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
myWorkbook.Version = ExcelVersion.Excel2010;
fileName = "ComputeFormulas.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
myWorkbook.Version = ExcelVersion.Excel2013;
fileName = "ComputeFormulas.xlsx";
}
#endregion
#region Compute Formulas
//Enable to calculate formulas in the worksheet.
worksheet.EnableSheetCalculations();
//hourly rate
worksheet["G7"].Number = 11;
//overtime rate.
worksheet["J7"].Formula = "=SUM(G7*1.5)";
//Regular hours
worksheet["F10"].Formula = "=IF((((C10-B10)+(E10-D10))*24)>8,8,((C10-B10)+(E10-D10))*24)";
worksheet["F11"].Formula = "=IF((((C11-B11)+(E11-D11))*24)>8,8,((C11-B11)+(E11-D11))*24)";
worksheet["F12"].Formula = "=IF((((C12-B12)+(E12-D12))*24)>8,8,((C12-B12)+(E12-D12))*24)";
worksheet["F13"].Formula = "=IF((((C13-B13)+(E13-D13))*24)>8,8,((C13-B13)+(E13-D13))*24)";
worksheet["F14"].Formula = "=IF((((C14-B14)+(E14-D14))*24)>8,8,((C14-B14)+(E14-D14))*24)";
worksheet["F15"].Formula = "=IF((((C15-B15)+(E15-D15))*24)>8,8,((C15-B15)+(E15-D15))*24)";
//overtime hours
worksheet["G10"].Formula = "=IF(((C10-B10)+(E10-D10))*24>8, ((C10-B10)+(E10-D10))*24-8,0)";
worksheet["G11"].Formula = "=IF(((C11-B11)+(E11-D11))*24>8, ((C11-B11)+(E11-D11))*24-8,0)";
worksheet["G12"].Formula = "=IF(((C12-B12)+(E12-D12))*24>8, ((C12-B12)+(E12-D12))*24-8,0)";
worksheet["G13"].Formula = "=IF(((C13-B13)+(E13-D13))*24>8, ((C13-B13)+(E13-D13))*24-8,0)";
worksheet["G14"].Formula = "=IF(((C14-B14)+(E14-D14))*24>8, ((C14-B14)+(E14-D14))*24-8,0)";
worksheet["G15"].Formula = "=IF(((C15-B15)+(E15-D15))*24>8, ((C15-B15)+(E15-D15))*24-8,0)";
//regular pay
worksheet["H10"].Formula = "=SUM(F10*G7)";
worksheet["H11"].Formula = "=SUM(F11*G7)";
worksheet["H12"].Formula = "=SUM(F12*G7)";
worksheet["H13"].Formula = "=SUM(F13*G7)";
worksheet["H14"].Formula = "=SUM(F14*G7)";
worksheet["H15"].Formula = "=SUM(F15*G7)";
//overtime pay
worksheet["I10"].Formula = "=SUM(G10*J7)";
worksheet["I11"].Formula = "=SUM(G11*J7)";
worksheet["I12"].Formula = "=SUM(G12*J7)";
worksheet["I13"].Formula = "=SUM(G13*J7)";
worksheet["I14"].Formula = "=SUM(G14*J7)";
worksheet["I15"].Formula = "=SUM(G15*J7)";
//total pay
worksheet["J10"].Formula = "=SUM(H10+I10)";
worksheet["J11"].Formula = "=SUM(H11+I11)";
worksheet["J12"].Formula = "=SUM(H12+I12)";
worksheet["J13"].Formula = "=SUM(H13+I13)";
worksheet["J14"].Formula = "=SUM(H14+I14)";
worksheet["J15"].Formula = "=SUM(H15+I15)";
//total regular hours
worksheet["F17"].Formula = "=SUM(F10:F15)";
//total overtime hours
worksheet["G17"].Formula = "=SUM(G10:G15)";
//total regular pay
worksheet["H17"].Formula = "=SUM(H10:H15)";
//total overtime pay
worksheet["I17"].Formula = "=SUM(I10:I15)";
//total pay
worksheet["J17"].Formula = "=SUM(J10:J15)";
//consolidated pay
worksheet["C20"].Formula = "=J17";
//allowance
worksheet["C21"].Number = 20;
//PF
worksheet["C22"].Number = 38;
//Net pay
worksheet["C24"].Formula = "=SUM(C20:C21)-C22";
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
myWorkbook.SaveAs(fileName);
//Close the workbook.
myWorkbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(fileName);
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private void generarreporte()
{
DataTable tabla = dtblcatalogo;
//Refresh(CashierBusyIndicator);
//generar();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ").ToUpper();
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
int osos = tabla.Rows.Count;
worksheet.Name = "Servicios";
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:K2"];
worksheet.Range["A1"].Text = "CATÁLOGO DE SERVICIOS AL " + dateday;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 30);
worksheet.SetColumnWidth(3, 15);
worksheet.SetColumnWidth(4, 20);
worksheet.SetColumnWidth(5, 7);
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 15);
worksheet.SetColumnWidth(11, 15);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\CATÁLOGO DE SERVICIOS AL " + dateday + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, EventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet1 = workbook.Worksheets[0];
sheet1.Name = "Budget";
sheet1.IsGridLinesVisible = false;
sheet1.EnableSheetCalculations();
sheet1.Range[1, 1].ColumnWidth = 19.86;
sheet1.Range[1, 2].ColumnWidth = 14.38;
sheet1.Range[1, 3].ColumnWidth = 12.98;
sheet1.Range[1, 4].ColumnWidth = 12.08;
sheet1.Range[1, 5].ColumnWidth = 8.82;
sheet1.Range["A1:A18"].RowHeight = 20.2;
//Adding cell style.
IStyle style1 = workbook.Styles.Add("style1");
style1.Color = System.Drawing.Color.FromArgb(217, 225, 242);
style1.HorizontalAlignment = ExcelHAlign.HAlignLeft;
style1.VerticalAlignment = ExcelVAlign.VAlignCenter;
style1.Font.Bold = true;
IStyle style2 = workbook.Styles.Add("style2");
style2.Color = System.Drawing.Color.FromArgb(142, 169, 219);
style2.VerticalAlignment = ExcelVAlign.VAlignCenter;
style2.NumberFormat = "[Red]($#,###)";
style2.Font.Bold = true;
sheet1.Range["A10"].CellStyle = style1;
sheet1.Range["B10:D10"].CellStyle.Color = System.Drawing.Color.FromArgb(217, 225, 242);
sheet1.Range["B10:D10"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet1.Range["B10:D10"].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["B10:D10"].CellStyle.Font.Bold = true;
sheet1.Range["A11:A17"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_25_percent;
sheet1.Range["D18"].CellStyle = style2;
sheet1.Range["D18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Color = System.Drawing.Color.FromArgb(142, 169, 219);
sheet1.Range["A18:C18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Font.Bold = true;
sheet1.Range["A18:C18"].NumberFormat = "$#,###";
sheet1.Range[10, 1].Text = "Category";
sheet1.Range[10, 2].Text = "Expected cost";
sheet1.Range[10, 3].Text = "Actual Cost";
sheet1.Range[10, 4].Text = "Difference";
sheet1.Range[11, 1].Text = "Venue";
sheet1.Range[12, 1].Text = "Seating & Decor";
sheet1.Range[13, 1].Text = "Technical team";
sheet1.Range[14, 1].Text = "Performers";
sheet1.Range[15, 1].Text = "Performer\'s transport";
sheet1.Range[16, 1].Text = "Performer\'s stay";
sheet1.Range[17, 1].Text = "Marketing";
sheet1.Range[18, 1].Text = "Total";
sheet1.Range["B11:D17"].NumberFormat = "$#,###";
sheet1.Range["D11"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D12"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D14"].NumberFormat = "[Red]($#,###)";
sheet1.Range["B11"].Number = 16250;
sheet1.Range["B12"].Number = 1600;
sheet1.Range["B13"].Number = 1000;
sheet1.Range["B14"].Number = 12400;
sheet1.Range["B15"].Number = 3000;
sheet1.Range["B16"].Number = 4500;
sheet1.Range["B17"].Number = 3000;
sheet1.Range["B18"].Formula = "=SUM(B11:B17)";
sheet1.Range["C11"].Number = 17500;
sheet1.Range["C12"].Number = 1828;
sheet1.Range["C13"].Number = 800;
sheet1.Range["C14"].Number = 14000;
sheet1.Range["C15"].Number = 2600;
sheet1.Range["C16"].Number = 4464;
sheet1.Range["C17"].Number = 2700;
sheet1.Range["C18"].Formula = "=SUM(C11:C17)";
sheet1.Range["D11"].Formula = "=IF(C11>B11,C11-B11,B11-C11)";
sheet1.Range["D12"].Formula = "=IF(C12>B12,C12-B12,B12-C12)";
sheet1.Range["D13"].Formula = "=IF(C13>B13,C13-B13,B13-C13)";
sheet1.Range["D14"].Formula = "=IF(C14>B14,C14-B14,B14-C14)";
sheet1.Range["D15"].Formula = "=IF(C15>B15,C15-B15,B15-C15)";
sheet1.Range["D16"].Formula = "=IF(C16>B16,C16-B16,B16-C16)";
sheet1.Range["D17"].Formula = "=IF(C17>B17,C17-B17,B17-C17)";
sheet1.Range["D18"].Formula = "=IF(C18>B18,C18-B18,B18-C18)";
IChartShape chart = sheet1.Charts.Add();
chart.ChartType = ExcelChartType.Pie;
chart.DataRange = sheet1.Range["A11:B17"];
chart.IsSeriesInRows = false;
chart.ChartTitle = "Event Expenses";
chart.ChartTitleArea.Bold = true;
chart.ChartTitleArea.Size = 16;
chart.TopRow = 1;
chart.BottomRow = 10;
chart.LeftColumn = 1;
chart.RightColumn = 5;
chart.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
string OutputFileName = "ExpensesReport.xlsx";
workbook.SaveAs(OutputFileName);
excelEngine.Dispose();
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook 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(OutputFileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(OutputFileName);
#endif
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
}
public async Task <IActionResult> OnPostRangeAsync()
{
// month dictionary to convert the string input into the corresponding int for the month.
Dictionary <String, Int32> months = new Dictionary <String, Int32>
{
{ "January", 1 },
{ "February", 2 },
{ "March", 3 },
{ "April", 4 },
{ "May", 5 },
{ "June", 6 },
{ "July", 7 },
{ "August", 8 },
{ "September", 9 },
{ "October", 10 },
{ "November", 11 },
{ "December", 12 }
};
// Assigning input dates to their corresponding varialbles
int startDay = Int32.Parse(Request.Form["start-day"]);
int startMonth = months.GetValueOrDefault(Request.Form["start-month"]);
int startYear = Int32.Parse(Request.Form["start-year"]);
int endDay = Int32.Parse(Request.Form["end-day"]);
int endMonth = months.GetValueOrDefault(Request.Form["end-month"]);
int endYear = Int32.Parse(Request.Form["end-year"]);
// Converting info to datetime objects.
DateTime start = new DateTime(startYear, startMonth, startDay);
DateTime end = new DateTime(endYear, endMonth, endDay);
// Checking if the datetime is valid.
if (start > end)
{
//Send a toast to the user saying that the selection is invalid.
TempData["message"] = "NO";
System.Threading.Thread.Sleep(500);
return(RedirectToPage("./adminindex"));
}
//Initialize database variables.
var volunteers = _context.Volunteer;
var volunteerActivities = _context.VolunteerActivity;
var valuesOfHours = _context.ValueOfHour;
var initiatives = _context.Initiative;
var volunteerTypes = _context.VolunteerType;
using ExcelEngine excelEngine = new ExcelEngine();
//Initialize Application.
IApplication application = excelEngine.Excel;
//Set default version for application.
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a new workbook.
IWorkbook workbook = application.Workbooks.Create(1);
//Accessing first worksheet in the workbook.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
worksheet.SetColumnWidth(1, 20);
// Here we separately check through each of the three volunteer types.
int i = 1;
foreach (var volunteerType in volunteerTypes)
{
worksheet.Range[i, 1].Text = volunteerType.Description;
var volunteersOfType = volunteers.Where(m => m.VolunteerType.Description == volunteerType.Description);
i++;
foreach (var volunteer in volunteersOfType)
{
double hours = 0.0;
worksheet.Range[i, 1].Text = volunteer.FullName;
var activitiesRange = volunteerActivities.Where(m => m.Volunteer == volunteer && m.StartTime >= start && m.StartTime <= end);
foreach (var activity in activitiesRange)
{
hours += activity.ElapsedTime.Hours;
hours += (activity.ElapsedTime.Minutes / 60.0);
}
worksheet.Range[i, 2].Number = Math.Round(hours, 2);
i++;
}
// Setting totals of current volunteer type
worksheet.Range[i, 1].Text = volunteerType.Description + " Hours Total";
worksheet.Range[i, 2].Formula = "=SUM(B" + (i - volunteersOfType.Count() + ":B" + (i - 1) + ")");
i += 2;
}
//Saving the Excel to the MemoryStream
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
//Set the position as '0'.
stream.Position = 0;
//Download the Excel file in the browser
FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel")
{
FileDownloadName = "Hours.xlsx"
};
RedirectToPage("./adminindex");
return(fileStreamResult);
}
public FileStreamResult OnPostYearReport()
{
//Initialize database variables.
var volunteers = _context.Volunteer;
var volunteerActivities = _context.VolunteerActivity;
var valuesOfHours = _context.ValueOfHour;
var initiatives = _context.Initiative;
var volunteerTypes = _context.VolunteerType;
int numTypes = volunteerTypes.Count();
int year = Int32.Parse(Request.Form["report-year"]);
//Number of initiatives currently.
int numInitiatives = initiatives.Count();
using ExcelEngine excelEngine = new ExcelEngine();
//Initialize Application.
IApplication application = excelEngine.Excel;
//Set default version for application.
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a new workbook.
IWorkbook workbook = application.Workbooks.Create(numTypes + 1);
//Accessing first worksheet in the workbook.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Totals";
// Enable use of Excel Formulas.
worksheet.EnableSheetCalculations();
//Setting first column width to 35.
worksheet.SetColumnWidth(1, 35);
//Setting all other column widths to 15.
for (int j = 2; j < 20; j++)
{
worksheet.SetColumnWidth(j, 15);
}
int rows = 0;
//looping through initiatives
for (int l = 0; l < numInitiatives + 1; l++)
{
rows = 2 * (l + 1) + 1;
if (l > 0)
{
// non-staff values and hours.
GenerateStaffNonStaffHours(worksheet, l, initiatives);
}
else
{
// staff values and hours.
worksheet.Range[2, 1].Text = "Staff Hours";
worksheet.Range[3, 1].Text = "Staff Value";
}
//looping through the months.
for (int j = 1; j < 13; j++)
{
// Initializing the hours and values to keep track of current month's (j) total values and hours.
double hours = 0;
double value = 0;
//querying the activities of the current month (j) and initiative id (i).
System.Linq.IQueryable <CoHO.Models.VolunteerActivity> activities;
// If l == 0 then we are in the first two rows that correspond to the staff hours/values.
if (l == 0)
{
activities = volunteerActivities.Where(m => m.StartTime.Month == j);
}
// If we are looking at hours for initiatives and non-staff.
else
{
activities = volunteerActivities.Where(m => m.StartTime.Month == j && m.InitiativeId == l);
}
foreach (var activity in activities.Where(m => m.StartTime.Year == year))
{
// time for this one activity
double time = activity.ElapsedTime.Hours + (activity.ElapsedTime.Minutes / 60.0);
//adding the current activities hours and values to their respective variables
// First check if they're staff.
if (volunteers.Single(m => m.VolunteerID == activity.VolunteerId).VolunteerTypeID != 2 && l != 0)
{
hours += time;
value += time * valuesOfHours.OrderBy(m => m.EffectiveDate).Last(m => m.EffectiveDate <= activity.StartTime).Value;
}
else if (volunteers.Single(m => m.VolunteerID == activity.VolunteerId).VolunteerTypeID == 2 && l == 0)
{
hours += time;
value += time * valuesOfHours.OrderBy(m => m.EffectiveDate).Last(m => m.EffectiveDate <= activity.StartTime).Value;
}
}
//adding the hours and values to the table.
worksheet.Range[2 * (l + 1), j + 1].Number = Math.Round(hours, 2);
worksheet.Range[2 * (l + 1) + 1, j + 1].Number = Math.Round(value, 2);
}
// Formulas for summing the data from the rows.
int row = 2 * (l + 1);
worksheet.Range[row, 14].Formula = "=SUM(A" + row + ":M" + row + ")";
worksheet.Range[row + 1, 14].Formula = "=SUM(A" + (row + 1) + ":M" + (row + 1) + ")";
}
// Formulas for summing the totals of all hours/values.
worksheet.Range[rows + 1, 1].Text = "Total Hours";
worksheet.Range[rows + 2, 1].Text = "Total Value";
String hoursFormula = "=SUM(N2";
String valueFormula = "=SUM(N3";
for (int m = 4; m <= rows; m++)
{
if (m % 2 == 0)
{
hoursFormula += ",N" + m;
}
else
{
valueFormula += ",N" + m;
}
}
worksheet.Range[rows + 1, 14].Formula = hoursFormula + ")";
worksheet.Range[rows + 2, 14].Formula = valueFormula + ")";
String[] months = { "January", "February", "March", "April", "May",
"June", "July", "August", "September", "October","November",
"December" };
for (int i = 1; i < 13; i++)
{
worksheet.Range[1, i + 1].Text = months[i - 1];
}
worksheet.Range[1, 14].Text = year + " Totals";
// Call function to generate second table on first worksheet.
SecondMainGraph(worksheet, rows + 5, months);
// Creating distinct worksheets for each volunteer type.
int o = 1;
foreach (var type in volunteerTypes)
{
VolunteerTypeWorksheet(workbook.Worksheets[o], type.Description, year, months);
workbook.Worksheets[o].Name = type.Description;
o++;
}
//Saving the Excel to the MemoryStream
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
//Set the position as '0'.
stream.Position = 0;
//Download the Excel file in the browser
FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel")
{
FileDownloadName = "Hours/Values.xlsx"
};
return(fileStreamResult);
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Invoice";
sheet.IsGridLinesVisible = false;
sheet.EnableSheetCalculations();
sheet.Range["A1"].ColumnWidth = 4.82;
sheet.Range["B1:C1"].ColumnWidth = 13.82;
sheet.Range["D1"].ColumnWidth = 12.20;
sheet.Range["E1"].ColumnWidth = 8.50;
sheet.Range["F1"].ColumnWidth = 9.73;
sheet.Range["G1"].ColumnWidth = 8.82;
sheet.Range["H1"].ColumnWidth = 4.46;
sheet.Range["A1:H1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(51, 63, 79);
sheet.Range["A1:H1"].Merge();
sheet.Range["B4:D6"].Merge();
sheet.Range["B4"].Text = "INVOICE";
sheet.Range["B4"].CellStyle.Font.Bold = true;
sheet.Range["B4"].CellStyle.Font.Size = 32;
IStyle style1 = workbook.Styles.Add("style1");
style1.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
style1.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_40_percent;
sheet.Range["B7:G7"].Merge();
sheet.Range["B7:G7"].CellStyle = style1;
sheet.Range["B13:G13"].Merge();
sheet.Range["B13:G13"].CellStyle = style1;
sheet.Range["B8"].Text = "BILL TO:";
sheet.Range["B8"].CellStyle.Font.Size = 9;
sheet.Range["B8"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Abraham Swearegin";
sheet.Range["B9"].CellStyle.Font.Size = 12;
sheet.Range["B9"].CellStyle.Font.Bold = true;
sheet.Range["B10"].Text = "United States, California, San Mateo,";
sheet.Range["B10"].CellStyle.Font.Size = 9;
sheet.Range["B11"].Text = "9920 BridgePointe Parkway,";
sheet.Range["B11"].CellStyle.Font.Size = 9;
sheet.Range["B12"].Number = 9365550136;
sheet.Range["B12"].CellStyle.Font.Size = 9;
sheet.Range["B12"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
IRange range1 = sheet.Range["F8:G8"];
IRange range2 = sheet.Range["F9:G9"];
IRange range3 = sheet.Range["F10:G10"];
IRange range4 = sheet.Range["E11:G11"];
IRange range5 = sheet.Range["F12:G12"];
range1.Merge();
range2.Merge();
range3.Merge();
range4.Merge();
range5.Merge();
sheet.Range["F8"].Text = "INVOICE#";
range1.CellStyle.Font.Size = 8;
range1.CellStyle.Font.Bold = true;
range1.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F9"].Number = 2058557939;
range2.CellStyle.Font.Size = 9;
range2.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F10"].Text = "DATE";
range3.CellStyle.Font.Size = 8;
range3.CellStyle.Font.Bold = true;
range3.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E11"].DateTime = new DateTime(2020, 08, 31);
sheet.Range["E11"].NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy";
range4.CellStyle.Font.Size = 9;
range4.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range5.CellStyle.Font.Size = 8;
range5.CellStyle.Font.Bold = true;
range5.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
IRange range6 = sheet.Range["B15:G15"];
range6.CellStyle.Font.Size = 10;
range6.CellStyle.Font.Bold = true;
sheet.Range[15, 2].Text = "Code";
sheet.Range[16, 2].Text = "CA-1098";
sheet.Range[17, 2].Text = "LJ-0192";
sheet.Range[18, 2].Text = "So-B909-M";
sheet.Range[19, 2].Text = "FK-5136";
sheet.Range[20, 2].Text = "HL-U509";
sheet.Range[15, 3].Text = "Description";
sheet.Range[16, 3].Text = "AWC Logo Cap";
sheet.Range[17, 3].Text = "Long-Sleeve Logo Jersey, M";
sheet.Range[18, 3].Text = "Mountain Bike Socks, M";
sheet.Range[19, 3].Text = "ML Fork";
sheet.Range[20, 3].Text = "Sports-100 Helmet, Black";
sheet.Range[15, 3, 15, 4].Merge();
sheet.Range[16, 3, 16, 4].Merge();
sheet.Range[17, 3, 17, 4].Merge();
sheet.Range[18, 3, 18, 4].Merge();
sheet.Range[19, 3, 19, 4].Merge();
sheet.Range[20, 3, 20, 4].Merge();
sheet.Range[15, 5].Text = "Quantity";
sheet.Range[16, 5].Number = 2;
sheet.Range[17, 5].Number = 3;
sheet.Range[18, 5].Number = 2;
sheet.Range[19, 5].Number = 6;
sheet.Range[20, 5].Number = 1;
sheet.Range[15, 6].Text = "Price";
sheet.Range[16, 6].Number = 8.99;
sheet.Range[17, 6].Number = 49.99;
sheet.Range[18, 6].Number = 9.50;
sheet.Range[19, 6].Number = 175.49;
sheet.Range[20, 6].Number = 34.99;
sheet.Range[15, 7].Text = "Total";
sheet.Range[16, 7].Formula = "=E16*F16+(E16*F16)";
sheet.Range[17, 7].Formula = "=E17*F17+(E17*F17)";
sheet.Range[18, 7].Formula = "=E18*F18+(E18*F18)";
sheet.Range[19, 7].Formula = "=E19*F19+(E19*F19)";
sheet.Range[20, 7].Formula = "=E20*F20+(E20*F20)";
sheet.Range[15, 6, 20, 7].NumberFormat = "$#,##0.00";
sheet.Range["E15:G15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B15:G15"].CellStyle.Font.Size = 10;
sheet.Range["B15:G15"].CellStyle.Font.Bold = true;
sheet.Range["B16:G20"].CellStyle.Font.Size = 9;
sheet.Range["E22:G22"].Merge();
sheet.Range["E22:G22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E23:G24"].Merge();
IRange range7 = sheet.Range["E22"];
IRange range8 = sheet.Range["E23"];
range7.Text = "TOTAL";
range7.CellStyle.Font.Size = 8;
range7.CellStyle.Font.Color = ExcelKnownColors.Blue_grey;
range8.Formula = "=SUM(G16:G20)";
range8.NumberFormat = "$#,##0.00";
range8.CellStyle.Font.Size = 24;
range8.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range8.CellStyle.Font.Bold = true;
sheet.Range[26, 1].Text = "800 Interchange Blvd, Suite 2501, Austin, TX 78721 | [email protected]";
sheet.Range[26, 1].CellStyle.Font.Size = 8;
IRange range9 = sheet.Range["A26:H27"];
range9.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(172, 185, 202);
range9.Merge();
range9.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range9.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.invoice.jpeg";
Assembly assembly = typeof(FormatCells).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
sheet.Pictures.AddPicture(3, 4, 7, 8, input);
string OutputFilename = "Invoice.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
public async void ExportGunList(object obj, EventArgs args)
{
exportGunBtn.IsEnabled = false;
//Create an instance of ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Set the default application version as Excel 2013.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook with a worksheet
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Access first worksheet from the workbook instance.
IWorksheet worksheet = workbook.Worksheets[0];
//Enabling formula calculation.
worksheet.EnableSheetCalculations();
worksheet["A1"].Text = "ID";
worksheet["B1"].Text = "Name";
worksheet["C1"].Text = "Type";
worksheet["D1"].Text = "Serial Number";
worksheet["E1"].Text = "Description";
worksheet["F1"].Text = "Value";
worksheet["G1"].Text = "Caliber";
worksheet["H1"].Text = "Manufacturer";
//Set the column width to fit to text.
worksheet.Range["A1:H1"].AutofitColumns();
//Set the row to have wrapped text.
//worksheet.Range["A1:H1"].WrapText = true;
//Set the style for header range.
IRange headingRange = worksheet["A1:H1"];
headingRange.CellStyle.Font.Bold = true;
headingRange.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headingRange.CellStyle.ColorIndex = ExcelKnownColors.Light_green;
try
{
_gunDatabase = new GunDatabase();
var guns = _gunDatabase.GetGuns();
var i = 2;
foreach (var item in guns)
{
worksheet["A" + i].Text = item.ID.ToString();
worksheet["B" + i].Text = item.GunName;
worksheet["C" + i].Text = item.GunType;
worksheet["D" + i].Text = item.GunSerialNumber;
worksheet["E" + i].Text = item.GunDescription;
worksheet["F" + i].Text = item.GunValue;
worksheet["G" + i].Text = item.GunCaliber;
worksheet["H" + i].Text = item.GunManufacturer;
i++;
}
worksheet.SetColumnWidth(5, 40);
worksheet.UsedRange.WrapText = true;
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
await DependencyService.Get <ISave>().SaveAndView("GunStockList.xlsx", stream);
}
catch (Exception ex)
{
Debug.WriteLine(@" GunStock.GunPage.ExportGunList ERROR {0}", ex.Message);
}
}
}
public IActionResult CreateDocument([Bind()] InputModel inputModel)
{
string lookupDirectory = @"E:\temp\tvlk\files\";
string suffix = " - Processed";
inputModel.ProcessResult = new List <ProcessResult>();
if (inputModel.Directory != null)
{
if (inputModel.Directory.Length > 0)
{
lookupDirectory = inputModel.Directory;
}
}
if (inputModel.OutputSuffix != null)
{
if (inputModel.OutputSuffix.Length > 0)
{
suffix = inputModel.OutputSuffix;
}
}
string[] files = Directory.GetFiles(lookupDirectory, "*" + inputModel.SearchString + "*");
foreach (string file in files)
{
if (!file.Contains(suffix))
{
ProcessResult processResult = new ProcessResult();
try
{
//FileStream fileStream = new FileStream(file, FileMode.Open);
using (FileStream fileStream = new FileStream(file, FileMode.Open))
{
//setting up application
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
application.EnableIncrementalFormula = true;
//setting up workbook & shits
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
worksheet.UsedRangeIncludesFormatting = false;
//define initial values
int colCount = worksheet.UsedRange.LastColumn;
int rowCount = worksheet.UsedRange.LastRow;
//int colCount = 39;
//int rowCount = 10;
AuditColumns colName = new AuditColumns();
AuditColumns cleanColName = new AuditColumns();
AuditColumns colBase = new AuditColumns();
ExcelColumnEnum en = new ExcelColumnEnum();
//get column alphabet by header text
for (int i = 1; i <= colCount; i++)
{
string[] date = colBase.date.Split(',');
foreach (string split in date)
{
if (worksheet[1, i].Value == split)
{
colName.date = en.TranslateIndex(i);
break;
}
}
string[] bid = colBase.bid.Split(',');
foreach (string split in bid)
{
if (worksheet[1, i].Value == split)
{
colName.bid = en.TranslateIndex(i);
break;
}
}
string[] locale = colBase.locale.Split(',');
foreach (string split in locale)
{
if (worksheet[1, i].Value == split)
{
colName.locale = en.TranslateIndex(i);
break;
}
}
string[] contractEntity = colBase.contractEntity.Split(',');
foreach (string split in contractEntity)
{
if (worksheet[1, i].Value == split)
{
colName.contractEntity = en.TranslateIndex(i);
break;
}
}
string[] contractCurrency = colBase.contractCurrency.Split(',');
foreach (string split in contractCurrency)
{
if (worksheet[1, i].Value == split)
{
colName.contractCurrency = en.TranslateIndex(i);
break;
}
}
string[] collectEntity = colBase.collectEntity.Split(',');
foreach (string split in collectEntity)
{
if (worksheet[1, i].Value == split)
{
colName.collectEntity = en.TranslateIndex(i);
break;
}
}
string[] collectCurrency = colBase.collectCurrency.Split(',');
foreach (string split in collectCurrency)
{
if (worksheet[1, i].Value == split)
{
colName.collectCurrency = en.TranslateIndex(i);
break;
}
}
string[] commissionRevenue = colBase.commissionRevenue.Split(',');
foreach (string split in commissionRevenue)
{
if (worksheet[1, i].Value == split)
{
colName.commissionRevenue = en.TranslateIndex(i);
break;
}
}
string[] transactionFee = colBase.transactionFee.Split(',');
foreach (string split in transactionFee)
{
if (worksheet[1, i].Value == split)
{
colName.transactionFee = en.TranslateIndex(i);
break;
}
}
string[] premium = colBase.premium.Split(',');
foreach (string split in premium)
{
if (worksheet[1, i].Value == split)
{
colName.premium = en.TranslateIndex(i);
break;
}
}
string[] discount = colBase.discount.Split(',');
foreach (string split in discount)
{
if (worksheet[1, i].Value == split)
{
colName.discount = en.TranslateIndex(i);
break;
}
}
string[] coupon = colBase.coupon.Split(',');
foreach (string split in coupon)
{
if (worksheet[1, i].Value == split)
{
colName.coupon = en.TranslateIndex(i);
break;
}
}
string[] redeemedPoints = colBase.redeemedPoints.Split(',');
foreach (string split in redeemedPoints)
{
if (worksheet[1, i].Value == split)
{
colName.redeemedPoints = en.TranslateIndex(i);
break;
}
}
string[] uniqueCode = colBase.uniqueCode.Split(',');
foreach (string split in uniqueCode)
{
if (worksheet[1, i].Value == split)
{
colName.uniqueCode = en.TranslateIndex(i);
break;
}
}
string[] installmentFee = colBase.installmentFee.Split(',');
foreach (string split in installmentFee)
{
if (worksheet[1, i].Value == split)
{
colName.installmentFee = en.TranslateIndex(i);
break;
}
}
string[] deliveryFee = colBase.deliveryFee.Split(',');
foreach (string split in deliveryFee)
{
if (worksheet[1, i].Value == split)
{
colName.deliveryFee = en.TranslateIndex(i);
break;
}
}
string[] invoiceAmount = colBase.invoiceAmount.Split(',');
foreach (string split in invoiceAmount)
{
if (worksheet[1, i].Value == split)
{
colName.invoiceAmount = en.TranslateIndex(i);
break;
}
}
string[] refundFee = colBase.refundFee.Split(',');
foreach (string split in refundFee)
{
if (worksheet[1, i].Value == split)
{
colName.refundFee = en.TranslateIndex(i);
break;
}
}
string[] rescheduleFee = colBase.rescheduleFee.Split(',');
foreach (string split in rescheduleFee)
{
if (worksheet[1, i].Value == split)
{
colName.rescheduleFee = en.TranslateIndex(i);
break;
}
}
string[] rebookCost = colBase.rebookCost.Split(',');
foreach (string split in rebookCost)
{
if (worksheet[1, i].Value == split)
{
colName.rebookCost = en.TranslateIndex(i);
break;
}
}
}
cleanColName = cleanUpNonExistingColumn(colName);
string colNotFound = getColumnNotFoundWarning(cleanColName);
//define new columns header
worksheet[1, colCount + 1].Text = "Margin Amount";
worksheet[1, colCount + 2].Text = "Margin";
worksheet[1, colCount + 3].Text = "Status";
//calculate first non-header row & copy to the rest of sheet
string formula = getAddingFormula(cleanColName, 2, false) + getSubtractFormula(cleanColName, 2, true);
worksheet[2, colCount + 1, rowCount, colCount + 1].Formula = "=" + formula.Replace("#+", "").Replace("#", "");
string formula2 = getTaggingResult(2, en.TranslateIndex(colCount + 1));
worksheet[2, colCount + 2, rowCount, colCount + 2].Formula = "=" + formula2;
worksheet[2, colCount + 3, rowCount, colCount + 3].Text = "ISSUED";
//conditional formatting
/*
* //prepare writing to compiled shit
* List<string> listContractEntity = new List<string>();
* List<string> listCollectingCurrency = new List<string>();
*
* //group contract entity
* if (cleanColName.contractEntity != "")
* {
* var distinct = worksheet[cleanColName.contractEntity + "2:" + cleanColName.contractEntity + rowCount.ToString()].Columns.Distinct();
* int countContractEntity = distinct.Count();
* if (countContractEntity > 0)
* {
* foreach (var item in distinct)
* {
* listContractEntity.Add(item.Value);
* }
* }
* }
*
* //group transaction currency
* if (cleanColName.collectCurrency != "")
* {
* var distinct = worksheet[cleanColName.collectCurrency + "2:" + cleanColName.collectCurrency + rowCount.ToString()].Columns.Distinct();
* int countCollectCurrency = distinct.Count();
* if (countCollectCurrency > 0)
* {
* foreach (var item in distinct)
* {
* listCollectingCurrency.Add(item.Value);
* }
* }
* }
*/
using (FileStream stream = new FileStream(@"" + file.Split('.')[0] + suffix + "." + file.Split('.')[1], FileMode.Create))
{
workbook.SaveAs(stream);
}
workbook.Close();
excelEngine.Dispose();
processResult.Success = true;
processResult.Message = file + " success";
if (colNotFound != "")
{
processResult.Message += " with " + colNotFound + " not found.";
}
}
}
catch (Exception ex)
{
processResult.Success = false;
processResult.Message = file + " failed";
}
inputModel.ProcessResult.Add(processResult);
//write to compiled shit
}
}
ViewData["ProcessResult"] = inputModel.ProcessResult;
return(View(inputModel));
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Create a workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";
sheet.IsGridLinesVisible = false;
IWorksheet sheet2 = workbook.Worksheets.Create("Data");
sheet.EnableSheetCalculations();
sheet.Range[1, 1, 1, 7].Merge();
IRange range = sheet.Range["A1"];
range.RowHeight = 22.5;
range.Text = "Yearly Sales";
range.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
range.CellStyle.Font.Size = 14;
range.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(155, 194, 230);
sheet.Range["A1"].ColumnWidth = 3.44;
sheet.Range["B1"].ColumnWidth = 11.0;
sheet.Range["C1"].ColumnWidth = 11.0;
sheet.Range["D1"].ColumnWidth = 0.92;
sheet.Range["E1"].ColumnWidth = 11.0;
sheet.Range["F1"].ColumnWidth = 11.0;
sheet.Range["G1"].ColumnWidth = 3.44;
sheet.Range[1, 1, 1, 7].Merge();
sheet.Range["A13"].RowHeight = 12;
sheet.Range["A14"].RowHeight = 21;
sheet.Range["A15"].RowHeight = 15;
sheet.Range["A16"].RowHeight = 3;
sheet.Range["A17"].RowHeight = 21;
sheet.Range["A18"].RowHeight = 15;
sheet.Range["A19"].RowHeight = 12;
IRange range5 = sheet.Range["B14:C14"];
IRange range6 = sheet.Range["B15:C15"];
IRange range7 = sheet.Range["B17:C17"];
IRange range8 = sheet.Range["B18:C18"];
IRange range9 = sheet.Range["E14:F14"];
IRange range10 = sheet.Range["E15:F15"];
IRange range11 = sheet.Range["E17:F17"];
IRange range12 = sheet.Range["E18:F18"];
range5.Text = "$ 4.51 M";
range9.Formula = "=Data!D14";
range7.Formula = "=Data!C19";
range11.Formula = "=Data!E14";
range5.Merge();
range6.Merge();
range7.Merge();
range8.Merge();
range9.Merge();
range10.Merge();
range11.Merge();
range12.Merge();
List <IStyle> styles = CreateStyles(workbook);
range5.CellStyle = styles[0];
range9.CellStyle = styles[1];
range7.CellStyle = styles[2];
range11.CellStyle = styles[3];
range6.CellStyle = styles[4];
range6.Text = "Sales Amount";
range10.CellStyle = styles[5];
range10.Text = "Average Unit Price";
range8.CellStyle = styles[6];
range8.Text = "Gross Profit Margin";
range12.CellStyle = styles[7];
range12.Text = "Customer Count";
sheet2.Range["B1"].ColumnWidth = 23;
sheet2.Range["C1"].ColumnWidth = 23;
sheet2.Range["D1"].ColumnWidth = 10;
sheet2.Range["E1"].ColumnWidth = 10;
sheet2.Range["A1"].Text = "Months";
sheet2.Range["B1"].Text = "Internet Sales Amount";
sheet2.Range["C1"].Text = "Reseller Sales Amount";
sheet2.Range["D1"].Text = "Unit Price";
sheet2.Range["E1"].Text = "Customers";
sheet2.Range["A2"].Text = "Jan";
sheet2.Range["A3"].Text = "Feb";
sheet2.Range["A4"].Text = "Mar";
sheet2.Range["A5"].Text = "Apr";
sheet2.Range["A6"].Text = "May";
sheet2.Range["A7"].Text = "June";
sheet2.Range["A8"].Text = "Jul";
sheet2.Range["A9"].Text = "Aug";
sheet2.Range["A10"].Text = "Sep";
sheet2.Range["A11"].Text = "Oct";
sheet2.Range["A12"].Text = "Nov";
sheet2.Range["A13"].Text = "Dec";
sheet2.Range["A14"].Text = "Total";
sheet2.Range["B2"].Number = 226170;
sheet2.Range["B3"].Number = 212259;
sheet2.Range["B4"].Number = 181079;
sheet2.Range["B5"].Number = 188809;
sheet2.Range["B6"].Number = 198195;
sheet2.Range["B7"].Number = 235524;
sheet2.Range["B8"].Number = 185786;
sheet2.Range["B9"].Number = 196745;
sheet2.Range["B10"].Number = 164897;
sheet2.Range["B11"].Number = 175673;
sheet2.Range["B12"].Number = 212896;
sheet2.Range["B13"].Number = 325634;
sheet2.Range["B14"].Formula = "=SUM(B2:B13)";
sheet2.Range["C2"].Number = 170234;
sheet2.Range["C3"].Number = 189456;
sheet2.Range["C4"].Number = 168795;
sheet2.Range["C5"].Number = 143567;
sheet2.Range["C6"].Number = 163567;
sheet2.Range["C7"].Number = 163546;
sheet2.Range["C8"].Number = 143787;
sheet2.Range["C9"].Number = 149898;
sheet2.Range["C10"].Number = 153784;
sheet2.Range["C11"].Number = 164289;
sheet2.Range["C12"].Number = 172453;
sheet2.Range["C13"].Number = 223430;
sheet2.Range["C14"].Formula = "=SUM(C2:C13)";
sheet2.Range["D2"].Number = 202;
sheet2.Range["D3"].Number = 204;
sheet2.Range["D4"].Number = 191;
sheet2.Range["D5"].Number = 223;
sheet2.Range["D6"].Number = 203;
sheet2.Range["D7"].Number = 185;
sheet2.Range["D8"].Number = 198;
sheet2.Range["D9"].Number = 196;
sheet2.Range["D10"].Number = 220;
sheet2.Range["D11"].Number = 218;
sheet2.Range["D12"].Number = 299;
sheet2.Range["D13"].Number = 185;
sheet2.Range["D14"].Formula = "=AVERAGE(D2:D13)";
sheet2.Range["E2"].Number = 1861;
sheet2.Range["E3"].Number = 1522;
sheet2.Range["E4"].Number = 1410;
sheet2.Range["E5"].Number = 1488;
sheet2.Range["E6"].Number = 1781;
sheet2.Range["E7"].Number = 2155;
sheet2.Range["E8"].Number = 1657;
sheet2.Range["E9"].Number = 1767;
sheet2.Range["E10"].Number = 1448;
sheet2.Range["E11"].Number = 1556;
sheet2.Range["E12"].Number = 1928;
sheet2.Range["E13"].Number = 2956;
sheet2.Range["E14"].Formula = "=SUM(E2:E13)";
sheet2.Range["B17"].Text = "2018 Sales";
sheet2.Range["B18"].Text = "2018 Sales";
sheet2.Range["B19"].Text = "Gain %";
sheet2.Range["C17"].Number = 3845634;
sheet2.Range["C18"].Formula = "=B14+C14";
sheet2.Range["C19"].Formula = "=(C18-C17)/10000000";
sheet2.Range["C19"].NumberFormat = "0.00%";
sheet2.Range["C17:C18"].NumberFormat = "_($* #,##0.00";
sheet2.Range["B2:D13"].NumberFormat = "_($* #,##0.00";
sheet2.Range["A1:E1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A1:E1"].CellStyle.Font.Bold = true;
sheet2.Range["A14:E14"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A14:E14"].CellStyle.Font.Bold = true;
sheet.Range["G30"].Text = ".";
#region Chart Creation
IChartShape chart1 = sheet.Charts.Add();
chart1.ChartType = ExcelChartType.Column_Stacked;
chart1.DataRange = sheet2.Range["A1:B13"];
chart1.IsSeriesInRows = false;
chart1.Legend !.Position = ExcelLegendPosition.Bottom;
chart1.PrimaryValueAxis.NumberFormat = "$#,###";
chart1.PrimaryValueAxis.HasMajorGridLines = false;
chart1.TopRow = 2;
chart1.BottomRow = 13;
chart1.LeftColumn = 1;
chart1.RightColumn = 8;
chart1.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
chart1.PrimaryCategoryAxis.TextRotationAngle = -45;
IChartShape chart2 = sheet.Charts.Add();
chart2.ChartType = ExcelChartType.Line;
chart2.DataRange = sheet2.Range["A1:C13"];
chart2.IsSeriesInRows = false;
chart2.ChartTitleArea.Bold = true;
chart2.ChartTitleArea.Size = 11;
chart2.ChartTitleArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.ChartTitleArea.Text = "Internet Sales vs Reseller Sales";
chart2.Legend !.Position = ExcelLegendPosition.Bottom;
chart2.Legend !.TextArea.Size = 9;
chart2.Legend.TextArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.TopRow = 20;
chart2.BottomRow = 32;
chart2.LeftColumn = 1;
chart2.RightColumn = 8;
chart2.PrimaryValueAxis.NumberFormat = "$#,###";
chart2.PrimaryValueAxis.HasMajorGridLines = false;
chart2.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
#endregion
string OutputFilename = "YearlySale.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
//
// GET: /CallCenterDashboard/
public ActionResult CallCenterDashboard(string button)
{
if (button == null)
{
return(View());
}
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.EnablePartialTrustCode = true;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx"));
return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
#region Workbook Initialize
//Initialize the spreadsheet creation engine
ExcelEngine excelEngine = new ExcelEngine();
//Initialize the Excel application object
IApplication application = excelEngine.Excel;
//Set the default application version
application.DefaultVersion = ExcelVersion.Excel2016;
//Enable the incremental formula
application.EnableIncrementalFormula = true;
//Load the existing Excel document into IWorkbook
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx"));
#endregion
#region Calculation Sheet
//Access the Calculation sheet
IWorksheet calculation = workbook.Worksheets["Calculation"];
//Formula calculation is enabled for the sheet
calculation.EnableSheetCalculations();
calculation.Range["A1"].Text = "Week";
calculation.Range["B1"].Text = "Sorting Control";
calculation.Range["D2"].NumberFormat = "dd-mmm-yyyy";
calculation.Range["D2"].Formula = "=DATE(2016,1,4)+7*(A2-1)";
calculation.Range["E2"].Formula = "=\"Week # \"&A2";
#region Call data in the week
calculation.Range["A4"].Text = "Total Calls";
calculation.Range["B4"].Formula = "=COUNTIF(Data[Column1],TRUE)";
calculation.Range["A5"].Text = "Calls Answered";
calculation.Range["B5"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"Y\")*(Data[Column1]=TRUE))";
calculation.Range["A6"].Text = "Avg Speed of Answer";
calculation.Range["B6"].Formula = "=SUMPRODUCT((Data[Speed of Answer]),--(Data[Column1]=TRUE))/B4";
calculation.Range["A7"].Text = "Abandon Rate";
calculation.Range["B7"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"N\")*(Data[Column1]=TRUE))/B4";
calculation.Range["A8"].Text = "Avg Call/Min";
calculation.Range["B8"].Formula = "=B4/(7*9*60)";
calculation.Range["A9"].Text = "Satisfaction Overall";
calculation.Range["B9"].Formula = "=SUMPRODUCT((Data[Satisfaction rating]),--(Data[Column1]=TRUE))/B5";
calculation.Range["A10"].Text = "Calls of Less than 180 Seconds";
calculation.Range["B10"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Answered (Y/N)]=\"Y\")*(Data[AvgTalkDuration]<TIME(0,3,0)))";
calculation.Range["A11"].Text = "% Calls of Less than 180 Seconds";
calculation.Range["B11"].Formula = "=B10/B5";
calculation.Range["A12"].Text = "Satisfaction less than equal to 3";
calculation.Range["B12"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Date]<D2+6)*(Data[Satisfaction rating]<=3))";
#endregion
#region Call data of each agent
calculation.Range["A15"].Text = "Agent Name";
calculation.Range["B15"].Text = "Total Calls";
calculation.Range["C15"].Text = "Calls Answered";
calculation.Range["D15"].Text = "Avg Speed of Answer";
calculation.Range["E15"].Text = "Call Resolution %";
calculation.Range["F15"].Text = "Call Resolved";
calculation.Range["H15"].Text = "For Sorting";
calculation.Range["M15"].Text = "Total Calls";
calculation.Range["N15"].Text = "Calls Answered";
calculation.Range["O15"].Text = "Avg Speed of Answer";
calculation.Range["P15"].Text = "Call Resolution (%)";
calculation.Range["A16"].Text = "Diane";
calculation.Range["A17"].Text = "Becky";
calculation.Range["A18"].Text = "Stewart";
calculation.Range["A19"].Text = "Greg";
calculation.Range["A20"].Text = "Jim";
calculation.Range["A21"].Text = "Joe";
calculation.Range["A22"].Text = "Martha";
calculation.Range["A23"].Text = "Dan";
calculation.Range["B16:B23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16))";
calculation.Range["C16:C23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Answered (Y/N)]=\"Y\"))";
calculation.Range["D16:D23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16),(Data[Speed of Answer]))/C16";
calculation.Range["E16:E23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))/B16";
calculation.Range["F16:F23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))";
calculation.Range["H16:H23"].Formula = "=INDEX($B$16:$E$23,ROWS($G$16:G16),$B$2)";
calculation.Range["I16:I23"].Formula = "=H16+ROWS($H$16:H16)/1000000";
calculation.Range["J16:J23"].Formula = "=IF($B$2=3,SMALL($I$16:$I$23,ROWS($I$16:I16)),LARGE($I$16:$I$23,ROWS($I$16:I16)))";
calculation.Range["K16:K23"].Formula = "=MATCH(J16,$I$16:$I$23,0)";
calculation.Range["L16:L23"].Formula = "=INDEX($A$16:$A$23,K16)";
calculation.Range["M16:M23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:L16))";
calculation.Range["N16:N23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:M16))";
calculation.Range["O16:O23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:N16))";
calculation.Range["P16:P23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:O16))";
#endregion
#region Overall Satisfaction chart
calculation.Range["A25"].Text = "Satisfaction Chart";
calculation.Range["A26"].Number = 50;
calculation.Range["A27"].Number = 20;
calculation.Range["A28"].Number = 30;
calculation.Range["A29"].Number = 100;
calculation.Range["B26"].Formula = "=B9*20-2";
calculation.Range["B27"].Number = 2;
calculation.Range["B28"].Formula = "=200-B26";
#endregion
#region call data of each agent per day
calculation.Range["A32"].Text = "Agent Name";
calculation.Range["B32"].Text = "Mon";
calculation.Range["C32"].Text = "Tue";
calculation.Range["D32"].Text = "Wed";
calculation.Range["E32"].Text = "Thu";
calculation.Range["F32"].Text = "Fri";
calculation.Range["G32"].Text = "Sat";
calculation.Range["H32"].Text = "Sun";
calculation.Range["A33:A40"].Formula = "=L16";
calculation.Range["B33:B40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:A33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["C33:C40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:B33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["D33:D40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:C33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["E33:E40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:D33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["F33:F40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:E33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["G33:G40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:F33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
calculation.Range["H33:H40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:G33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))";
#endregion
#region Satisfaction score for each agent
calculation.Range["A43"].Text = "Agent Name";
calculation.Range["B43"].Text = "Satisfaction Score";
calculation.Range["C43"].Text = "Target";
calculation.Range["A44:A51"].Formula = "=A33";
calculation.Range["B44:B51"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=$A44),(Data[Satisfaction rating]))/N16";
calculation.Range["C44:C51"].Number = 3.5;
calculation.Range["D44:D51"].Formula = "=IF(B44>C44,$A$52&\" \"&A44,A44)";
calculation.Range["E44:E51"].Formula = "=B44";
#endregion
#region call data for each product
calculation.Range["B54"].Text = "Total Cell";
calculation.Range["C54"].Text = "Call Answered";
calculation.Range["D54"].Text = "Abandoned Calls %";
calculation.Range["E54"].Text = "SLA Limit";
calculation.Range["F54"].Text = "SLA Breached";
calculation.Range["A55"].Text = "Washing Machine";
calculation.Range["A56"].Text = "Toaster";
calculation.Range["A57"].Text = "Fridge";
calculation.Range["A58"].Text = "Air Conditioner";
calculation.Range["A59"].Text = "Television";
calculation.Range["B55:B59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55))";
calculation.Range["C55:C59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55)*(Data[Answered (Y/N)]=\"Y\"))";
calculation.Range["D55:D59"].Formula = "=(B55-C55)/B55";
calculation.Range["E55:E59"].NumberFormat = "0%";
calculation.Range["E55:E59"].Value = "20%";
calculation.Range["F55:F59"].Formula = "=IF(D55>E55,D55,NA())";
#endregion
//Formula calculation is disabled for the sheet
calculation.DisableSheetCalculations();
#endregion
#region Dashboard Sheet
//Create Dashboard sheet
IWorksheet dashboard = workbook.Worksheets.Create("Dashboard");
dashboard.Range["A1"].ColumnWidth = 0.5;
dashboard.Range["A1"].RowHeight = 30;
dashboard.Range["I1"].ColumnWidth = 0.5;
#region Marcos for selecting the week and sort option
//Cell Style for B1 to R1
dashboard.Range["B1:R1"].CellStyle.Color = Color.FromArgb(48, 13, 225);
//Cell Style and text in B6 - Click to Sort
dashboard.Range["B6:B7"].Merge();
dashboard.Range["B6"].Text = "Click to Sort";
dashboard.Range["B6"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225);
dashboard.Range["B6"].CellStyle.Font.Italic = true;
dashboard.Range["B6"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["B6"].VerticalAlignment = ExcelVAlign.VAlignCenter;
//Macros for Scroll Bar and Option Buttons
IVbaProject project = workbook.VbaProject;
IVbaModules vbaModules = project.Modules;
IVbaModule scrollBar = vbaModules.Add("scrollBar", VbaModuleType.StdModule);
scrollBar.Code = "Sub Auto_Open()" +
"\n Dim Worksheet_Name As String" +
"\n Worksheet_Name = \"Dashboard\"" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).Select" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).ScrollBars.Add(12, 3.5, 96.5, 20).Select" +
"\n With Selection" +
"\n .Value = 0" + "\n .Min = 1" + "\n .Max = 4" + "\n .SmallChange = 1" +
"\n .LargeChange = 10" + "\n .LinkedCell = \"=Calculation!A2\"" + "\n .Display3DShading = True" +
"\n End With" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(98.5, 97, 63, 17.5).Select" +
"\n Selection.Characters.Text = \"\"" + "\n With Selection" +
"\n .Value = xlOn" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" +
"\n End With" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(166, 97, 63, 17.5).Select" +
"\n Selection.Characters.Text = \"\"" + "\n With Selection" +
"\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" +
"\n End With" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(270, 97, 63, 17.5).Select" +
"\n Selection.Characters.Text = \"\"" + "\n With Selection" +
"\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" +
"\n End With" +
"\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(380, 97, 63, 17.5).Select" +
"\n Selection.Characters.Text = \"\"" + "\n With Selection" +
"\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" +
"\n End With" +
"\n End Sub";
#endregion
#region Week display
//Week Selected
dashboard.Range["D1"].Formula = "=Calculation!E2";
dashboard.Range["D1"].CellStyle.Font.Bold = true;
dashboard.Range["D1"].CellStyle.Font.Italic = true;
dashboard.Range["D1"].CellStyle.Font.Size = 16;
dashboard.Range["D1"].CellStyle.Font.RGBColor = Color.FromArgb(225, 225, 225);
dashboard.Range["D1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["D1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
#endregion
#region Call data overview for the week
dashboard.Range["B2:D3"].Merge();
dashboard.Range["B4:D5"].Merge();
dashboard.Range["E2:G3"].Merge();
dashboard.Range["E4:G5"].Merge();
dashboard.Range["H2:M3"].Merge();
dashboard.Range["H4:M5"].Merge();
dashboard.Range["N2:R3"].Merge();
dashboard.Range["N4:R5"].Merge();
dashboard.Range["B2:N4"].CellStyle.Font.FontName = "Verdana";
dashboard.Range["B2:N4"].CellStyle.Font.Bold = true;
dashboard.Range["B2:N2"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 0);
dashboard.Range["B4:N4"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225);
dashboard.Range["B2:N2"].CellStyle.Font.Size = 12;
dashboard.Range["B4:N4"].CellStyle.Font.Size = 18;
dashboard.Range["B2:N4"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["B2:N4"].VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["B2:N4"].CellStyle.Color = Color.FromArgb(217, 217, 217);
dashboard.Range["B2"].Text = "Total Calls";
dashboard.Range["E2"].Text = "Avg. Answer Speed (in sec)";
//Create a rich text string
IRichTextString richText = dashboard.Range["E2"].RichText;
//Create fonts for rich text formatting
IFont font1 = workbook.CreateFont();
font1.FontName = "Verdana";
font1.RGBColor = Color.FromArgb(0, 0, 0);
font1.Bold = true;
font1.Size = 12;
richText.SetFont(0, 17, font1);
IFont font2 = workbook.CreateFont();
font2.FontName = "Verdana";
font2.RGBColor = Color.FromArgb(0, 0, 0);
font2.Bold = false;
font2.Size = 10;
richText.SetFont(18, 25, font2);
dashboard.Range["H2"].Text = "Abandon rate";
dashboard.Range["N2"].Text = "Avg Calls/Minute";
dashboard.Range["B4"].Formula = "=Calculation!B4";
dashboard.Range["E4"].NumberFormat = "0.0";
dashboard.Range["E4"].Formula = "=Calculation!B6";
dashboard.Range["H4"].NumberFormat = "0.0%";
dashboard.Range["H4"].Formula = "=Calculation!B7";
dashboard.Range["N4"].NumberFormat = "0.000";
dashboard.Range["N4"].Formula = "=Calculation!B8";
dashboard.Range["B2:D5"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["E2:G5"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["H2:M5"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["N2:R5"].BorderAround(ExcelLineStyle.Thin, Color.Black);
#endregion
#region Table to display call data of each agent
dashboard.Range["B8"].Text = "Agent Name";
dashboard.Range["C8"].Text = "Total Calls";
dashboard.Range["D8"].Text = "Calls Answered";
dashboard.Range["E8"].Text = "Avg. Speed of Answer";
dashboard.Range["F8"].Text = "Call Resolution (%)";
dashboard.Range["H8"].Text = "CR Trend";
dashboard.Range["B9:B16"].Formula = "=Calculation!L16";
dashboard.Range["C9:C16"].Formula = "=Calculation!M16";
dashboard.Range["D9:D16"].Formula = "=Calculation!N16";
dashboard.Range["E9:E16"].NumberFormat = "0.0";
dashboard.Range["E9:E16"].Formula = "=Calculation!O16";
dashboard.Range["F9:F16"].NumberFormat = "0.0%";
dashboard.Range["F9:F16"].Formula = "=Calculation!P16";
dashboard.Range["G9:G16"].Formula = "=F9";
#region Conditional formats
//Create icon sets for the data in the specified range
IConditionalFormats conditionalFormats = dashboard.Range["G9:G16"].ConditionalFormats;
IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = conditionalFormat.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSigns;
//Apply three signs icon and hide the data in the specified range
IIconConditionValue iconValue2 = iconSet.IconCriteria[1] as IIconConditionValue;
iconValue2.IconSet = ExcelIconSetType.ThreeSigns;
iconValue2.Index = 1;
iconValue2.Type = ConditionValueType.Number;
iconValue2.Value = "0.7";
iconValue2.Operator = ConditionalFormatOperator.GreaterThan;
IIconConditionValue iconValue3 = iconSet.IconCriteria[2] as IIconConditionValue;
iconValue3.IconSet = ExcelIconSetType.ThreeSigns;
iconValue3.Index = 2;
iconValue3.Type = ConditionValueType.Number;
iconValue3.Value = "0.8";
iconValue3.Operator = ConditionalFormatOperator.GreaterThanorEqualTo;
iconSet.ShowIconOnly = true;
#endregion
//Auto-fit columns
dashboard.Range["B8:G16"].AutofitColumns();
dashboard.Range["H8"].ColumnWidth = 11;
#region Sparklines
//Add sparkline groups
ISparklineGroup sparklineGroup = dashboard.SparklineGroups.Add();
sparklineGroup.SparklineType = SparklineType.Line;
sparklineGroup.MarkersColor = Color.FromArgb(51, 102, 153);
sparklineGroup.LowPointColor = Color.FromArgb(192, 0, 0);
sparklineGroup.ShowHighPoint = false;
sparklineGroup.ShowFirstPoint = false;
sparklineGroup.ShowLastPoint = false;
sparklineGroup.ShowMarkers = false;
//Add sparklines
ISparklines sparklines = sparklineGroup.Add();
IRange dataRange = calculation.Range["B33:H40"];
IRange referenceRange = dashboard.Range["H9:H16"];
sparklines.Add(dataRange, referenceRange);
#endregion
//Apply range formatting using cellstyle properties
dashboard.Range["B8:E16"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["B8:E16"].BorderInside(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F8:G8"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F9:G9"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F10:G10"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F11:G11"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F12:G12"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F13:G13"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F14:G14"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F15:G15"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["F16:G16"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["H8:H16"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["B8:H8"].CellStyle.Font.Bold = true;
dashboard.Range["B8:H8"].CellStyle.Font.Italic = true;
dashboard.Range["B8:H8"].CellStyle.Color = Color.FromArgb(217, 217, 217);
#endregion
#region Column Chart - Call Abandon Rate By Department
//Column Chart - Chart Title
dashboard.Range["J8:R8"].Merge();
dashboard.Range["J8"].CellStyle.Font.Italic = true;
dashboard.Range["J8"].CellStyle.Font.Size = 12;
dashboard.Range["J8"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127);
dashboard.Range["J8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["J8"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["J8"].Text = "Call Abandon Rate - By Department";
//Column Chart - Call Abandon Rate - By Department
IChartShape columnChart = dashboard.Charts.Add();
IChartSerie columnserieOne = columnChart.Series.Add();
columnserieOne.Values = calculation.Range["D55:D59"];
columnserieOne.CategoryLabels = calculation.Range["A55:A59"];
columnserieOne.SerieFormat.CommonSerieOptions.GapWidth = 150;
columnserieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
columnChart.PrimaryValueAxis.MajorUnit = 0.1;
columnChart.PrimaryValueAxis.NumberFormat = "0.0%";
columnChart.PrimaryValueAxis.HasMajorGridLines = false;
columnChart.HasLegend = false;
columnChart.ChartArea.Border.LineColor = Color.White;
columnChart.Left = 1;
columnChart.TopRow = 9;
columnChart.LeftColumn = 10;
columnChart.BottomRow = 17;
columnChart.RightColumn = 19;
(columnChart as IChart).Height = (columnChart as IChart).Height - 10;
(columnChart as IChart).Width = (columnChart as IChart).Width - 10;
dashboard.Range["J8:R16"].BorderAround(ExcelLineStyle.Thin, Color.Black);
#endregion
#region SLA Limits
dashboard.Range["J18:R18"].Merge();
dashboard.Range["J21:O23"].Merge();
dashboard.Range["J26:O28"].Merge();
dashboard.Range["P21:R23"].Merge();
dashboard.Range["P26:R28"].Merge();
dashboard.Range["J18"].Text = "SLA LIMITS";
dashboard.Range["J18"].CellStyle.Font.Bold = true;
dashboard.Range["J18"].CellStyle.Font.Size = 16;
dashboard.Range["J18"].CellStyle.Font.FontName = "Calibri (body)";
dashboard.Range["J18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["J18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["J18"].CellStyle.Font.Color = ExcelKnownColors.Black;
dashboard.Range["J18"].CellStyle.Color = Color.FromArgb(217, 217, 217);
dashboard.Range["J18:R18"].BorderAround(ExcelLineStyle.Thin, Color.Black);
dashboard.Range["J21"].Text = "Calls answered in less than 180 Seconds:";
dashboard.Range["J21"].CellStyle.Font.Bold = true;
dashboard.Range["J21"].CellStyle.Font.Size = 14;
dashboard.Range["J21"].CellStyle.Font.FontName = "Verdana";
dashboard.Range["J21"].CellStyle.WrapText = true;
dashboard.Range["J21"].CellStyle.Font.Color = ExcelKnownColors.Black;
dashboard.Range["J21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["P21"].Formula = "=Calculation!B11";
dashboard.Range["P21"].CellStyle.Font.Bold = true;
dashboard.Range["P21"].CellStyle.Font.Size = 28;
dashboard.Range["P21"].CellStyle.Font.FontName = "Verdana";
dashboard.Range["P21"].NumberFormat = "0.0%";
dashboard.Range["P21"].CellStyle.Font.RGBColor = Color.FromArgb(84, 130, 53);
dashboard.Range["P21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["P21"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
dashboard.Range["J26"].Text = "Calls with satisfaction score less than 3:";
dashboard.Range["J26"].CellStyle.Font.Bold = true;
dashboard.Range["J26"].CellStyle.Font.Size = 14;
dashboard.Range["J26"].CellStyle.Font.FontName = "Verdana";
dashboard.Range["J26"].CellStyle.WrapText = true;
dashboard.Range["J26"].CellStyle.Font.Color = ExcelKnownColors.Black;
dashboard.Range["J26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["P26"].Formula = "=Calculation!B12";
dashboard.Range["P26"].CellStyle.Font.Bold = true;
dashboard.Range["P26"].CellStyle.Font.Size = 28;
dashboard.Range["P26"].CellStyle.Font.FontName = "Verdana";
dashboard.Range["P26"].CellStyle.Font.RGBColor = Color.FromArgb(192, 0, 0);
dashboard.Range["P26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["P26"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
dashboard.Range["J19:R30"].BorderAround(ExcelLineStyle.Thin, Color.Black);
#endregion
#region Bar Chart - Satisfaction Score By Agent
//Bar Chart - Chart Title
dashboard.Range["E18:H18"].Merge();
dashboard.Range["E18"].CellStyle.Font.Italic = true;
dashboard.Range["E18"].CellStyle.Font.Underline = ExcelUnderline.Single;
dashboard.Range["E18"].CellStyle.Font.Size = 12;
dashboard.Range["E18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127);
dashboard.Range["E18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["E18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["E18"].Text = "Satisfaction Score - By Agent";
//Bar Chart - Satisfaction Score - By Agent
IChartShape barChart = dashboard.Charts.Add();
IChartSerie barSerieOne = barChart.Series.Add();
barSerieOne.SerieType = ExcelChartType.Bar_Clustered;
barSerieOne.CategoryLabels = calculation.Range["D44:D51"];
barSerieOne.Values = calculation.Range["E44:E51"];
barChart.PrimaryValueAxis.MinimumValue = 0;
barChart.PrimaryValueAxis.MaximumValue = 5;
barChart.PrimaryValueAxis.HasMajorGridLines = false;
barChart.HasLegend = false;
barChart.ChartArea.Border.LineColor = Color.White;
barChart.Top = 1;
barChart.TopRow = 20;
barChart.LeftColumn = 5;
barChart.BottomRow = 31;
barChart.RightColumn = 9;
(barChart as IChart).Height = (barChart as IChart).Height - 10;
(barChart as IChart).Width = (barChart as IChart).Width - 10;
#endregion
#region Doughnut and Pie Chart - Overall Satisfaction Score
//Doughnut Chart - Chart Title
dashboard.Range["B18:D18"].Merge();
dashboard.Range["B18"].CellStyle.Font.Italic = true;
dashboard.Range["B18"].CellStyle.Font.Underline = ExcelUnderline.Single;
dashboard.Range["B18"].CellStyle.Font.Size = 12;
dashboard.Range["B18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127);
dashboard.Range["B18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["B18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["B18"].Text = "Overall Satisfaction Score";
//Doughnut Chart - Overall Satisfaction Score
IChartShape doughnutChart = dashboard.Charts.Add();
IChartSerie doughnutSerie = doughnutChart.Series.Add();
doughnutChart.ChartType = ExcelChartType.Doughnut;
doughnutSerie.Values = calculation.Range["A26:A29"];
doughnutSerie.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270;
doughnutSerie.DataPoints[0].DataFormat.Fill.ForeColor = Color.FromArgb(255, 0, 0);
doughnutSerie.DataPoints[1].DataFormat.Fill.ForeColor = Color.FromArgb(255, 192, 0);
doughnutSerie.DataPoints[2].DataFormat.Fill.ForeColor = Color.FromArgb(0, 176, 80);
doughnutSerie.DataPoints[3].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White;
doughnutSerie.DataPoints[3].DataFormat.Fill.Transparency = 1.0;
doughnutChart.HasLegend = false;
doughnutChart.ChartArea.Border.LineColor = Color.White;
doughnutChart.ChartArea.Fill.Transparency = 1.0;
doughnutChart.PlotArea.Fill.Transparency = 1.0;
doughnutChart.Left = 1;
doughnutChart.TopRow = 20;
doughnutChart.LeftColumn = 2;
doughnutChart.BottomRow = 32;
doughnutChart.RightColumn = 5;
//Pie Chart - Overall Satisfaction Score
IChartShape pieChart = dashboard.Charts.Add();
IChartSerie pieSerieOne = pieChart.Series.Add();
pieSerieOne.SerieType = ExcelChartType.Pie;
pieSerieOne.Values = calculation.Range["B26:B28"];
pieSerieOne.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270;
pieSerieOne.DataPoints[0].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White;
pieSerieOne.DataPoints[0].DataFormat.Fill.Transparency = 1.0;
pieSerieOne.DataPoints[1].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.Black;
pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineColor = Color.White;
pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineWeight = ExcelChartLineWeight.Narrow;
pieSerieOne.DataPoints[2].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White;
pieSerieOne.DataPoints[2].DataFormat.Fill.Transparency = 1.0;
pieChart.HasLegend = false;
pieChart.ChartArea.Border.LineColor = Color.White;
pieChart.ChartArea.Fill.Transparency = 1.0;
pieChart.PlotArea.Fill.Transparency = 1.0;
pieChart.Top = 1;
pieChart.Left = 1;
pieChart.TopRow = 20;
pieChart.LeftColumn = 2;
pieChart.BottomRow = 33;
pieChart.RightColumn = 5;
dashboard.Range["B30:C30"].Merge();
dashboard.Range["B30"].Text = "Satisafction Score:";
dashboard.Range["B30"].HorizontalAlignment = ExcelHAlign.HAlignRight;
dashboard.Range["B30"].VerticalAlignment = ExcelVAlign.VAlignCenter;
dashboard.Range["D30"].NumberFormat = "0.00";
dashboard.Range["D30"].Formula = "=Calculation!B9";
dashboard.Range["D30"].CellStyle.Font.Size = 18;
dashboard.Range["D30"].CellStyle.Font.Bold = true;
dashboard.Range["D30"].CellStyle.Font.RGBColor = Color.FromArgb(0, 112, 192);
dashboard.Range["D30"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
dashboard.Range["B18:H30"].BorderAround(ExcelLineStyle.Thin, Color.Black);
#endregion
//Disable the gridlines
dashboard.IsGridLinesVisible = false;
//Set the dashboard sheet as active sheet
dashboard.Activate();
#endregion
#region Save the Workbook
//Save the workbook to disk
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, "CallCenterDashboard.xlsm", ExcelSaveType.SaveAsMacro, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
#endregion
}
}
private async void Convert_Clicked(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Enabling formula calculation.
worksheet.EnableSheetCalculations();
// worksheet["E1"].Text = "BeaconID";
// worksheet["F1"].Text = "SignalStrength";
worksheet["A1"].Text = "UserID:"; //AHT
// worksheet["B1"].Text = TimeInPunches.UserName; //can't access? marked private...
worksheet["A2"].Text = "PhoneID:"; //AHT
// worksheet["B2"].Text = TimeInPunches.PhoneID; //can't access? marked private...
worksheet["A3"].Text = "TimeIn:"; //AHT
// worksheet["B3"].Text = TimeInPunches.TimeIn; //can't access? marked private...
// worksheet["B3"].Text = myTimeIns.Count.ToString();
worksheet["A4"].Text = "TimeOut:"; //AHT
// worksheet["B4"].Text = TimeInPunches.TimeOut; //can't access? marked private...
// worksheet["B4"].Text = myTimeOuts[0].ToString();
worksheet["A5"].Text = "Shift Dur:"; //AHT
// worksheet["B5"].Text = Convert.ToString.(TimeInPunches.TimeOut - TimeInPunches.TimeIn).hours; //AHT
worksheet["A6"].Text = "Record "; //AHT
worksheet["B6"].Text = "BeaconID"; //AHT
worksheet["C6"].Text = "RSSI"; //AHT
worksheet["D6"].Text = "Time"; //AHT
worksheet["E6"].Text = "UUID"; //AHT
var startRecordsRow = 7;
for (int i = 0; i < deviceNames.Count; i++)
{
// worksheet["A" + Convert.ToString(i + 2)].Text = myTimeIns[i].UserName;
// worksheet["B" + Convert.ToString(i + 2)].Text = myTimeIns[i].PhoneID;
// worksheet["C" + Convert.ToString(i + 2)].Text = myTimeIns[i].TimeIn.ToString();
// worksheet["D" + Convert.ToString(i + 2)].Text = myTimeIns[i].TimeOut.ToString();
// worksheet["E" + Convert.ToString(i + 2)].Text = myTimeIns[i].BeaconID;
// worksheet["F" + Convert.ToString(i + 2)].Text = myTimeIns[i].SignalStrength;
// worksheet["A" + Convert.ToString(i + 3)].Text = deviceNames[i]; //Orig from Paul
//Split comma-separated fields into Excel columns - AHT
List <string> deviceNameParsed = deviceNames[i].Split(',').ToList <string>(); //AHT
//Populate Excel spreadsheet
worksheet["A" + Convert.ToString(i + startRecordsRow)].Text = Convert.ToString(i); //AHT
worksheet["B" + Convert.ToString(i + startRecordsRow)].Text = deviceNameParsed[0]; //AHT
worksheet["C" + Convert.ToString(i + startRecordsRow)].Text = deviceNameParsed[1]; //AHT
worksheet["D" + Convert.ToString(i + startRecordsRow)].Text = deviceNameParsed[2]; //AHT
worksheet["E" + Convert.ToString(i + startRecordsRow)].Text = deviceNameParsed[3]; //AHT
}
worksheet["A1:A1"].ColumnWidth = 10; //AHT
worksheet["B1:B1"].ColumnWidth = 10; //AHT
worksheet["C1:C1"].ColumnWidth = 7; //AHT
worksheet["D1:D1"].ColumnWidth = 22; //AHT
worksheet["E1:E1"].ColumnWidth = 38; //AHT
IRange headingRange = worksheet["A6:E6"];
headingRange.CellStyle.Font.Bold = true;
headingRange.CellStyle.ColorIndex = ExcelKnownColors.Light_green;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
await Xamarin.Forms.DependencyService.Get <ISave>().SaveAndView("GettingStared.xlsx", "application/msexcel", stream);
}
}
internal void OnButtonClicked(object sender, EventArgs e)
{
// Workbook Initialize
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();
sheet["A40"].Activate();
// Worksheet Data
sheet.Name = "Inventory";
sheet.IsGridLinesVisible = false;
sheet["A1"].Text = "INVENTORY LIST";
sheet["A3"].Text = "TOTAL INVENTORY VALUE";
sheet["D3"].Text = "TOTAL ITEMS";
sheet["F3"].Text = "BIN COUNT";
sheet["A6"].Text = "ID";
sheet["B6"].Text = "Name";
sheet["C6"].Text = "Bin #";
sheet["D6"].Text = "Unit Price";
sheet["E6"].Text = "Quantity in Stock";
sheet["F6"].Text = "Inventory Value";
sheet["A7"].Text = "IN001";
sheet["B7"].Text = "Item 1";
sheet["C7"].Text = "T340";
sheet["D7"].Number = 51;
sheet["E7:E26"].Number = 25;
sheet["A8"].Text = "IN002";
sheet["B8"].Text = "Item 2";
sheet["C8"].Text = "T5780";
sheet["D8"].Number = 93;
sheet["A9"].Text = "IN003";
sheet["B9"].Text = "Item 3";
sheet["C9"].Text = "T340";
sheet["D9"].Number = 57;
sheet["A10"].Text = "IN004";
sheet["B10"].Text = "Item 4";
sheet["C10"].Text = "T908";
sheet["D10"].Number = 19;
sheet["A11"].Text = "IN005";
sheet["B11"].Text = "Item 5";
sheet["C11"].Text = "T9845";
sheet["D11"].Number = 75;
sheet["A12"].Text = "IN006";
sheet["B12"].Text = "Item 6";
sheet["C12"].Text = "T540";
sheet["D12"].Number = 11;
sheet["A13"].Text = "IN007";
sheet["B13"].Text = "Item 7";
sheet["C13"].Text = "T5780";
sheet["D13"].Number = 56;
sheet["A14"].Text = "IN008";
sheet["B14"].Text = "Item 8";
sheet["C14"].Text = "T340";
sheet["D14"].Number = 38;
sheet["A15"].Text = "IN009";
sheet["B15"].Text = "Item 9";
sheet["C15"].Text = "T908";
sheet["D15"].Number = 59;
sheet["A16"].Text = "IN010";
sheet["B16"].Text = "Item 10";
sheet["C16"].Text = "T9845";
sheet["D16"].Number = 50;
sheet["A17"].Text = "IN011";
sheet["B17"].Text = "Item 11";
sheet["C17"].Text = "T306";
sheet["D17"].Number = 59;
sheet["A18"].Text = "IN012";
sheet["B18"].Text = "Item 12";
sheet["C18"].Text = "T5780";
sheet["D18"].Number = 18;
sheet["A19"].Text = "IN013";
sheet["B19"].Text = "Item 13";
sheet["C19"].Text = "T306";
sheet["D19"].Number = 26;
sheet["A20"].Text = "IN014";
sheet["B20"].Text = "Item 14";
sheet["C20"].Text = "T908";
sheet["D20"].Number = 42;
sheet["A21"].Text = "IN015";
sheet["B21"].Text = "Item 15";
sheet["C21"].Text = "T9845";
sheet["D21"].Number = 32;
sheet["A22"].Text = "IN016";
sheet["B22"].Text = "Item 16";
sheet["C22"].Text = "T415";
sheet["D22"].Number = 90;
sheet["A23"].Text = "IN017";
sheet["B23"].Text = "Item 17";
sheet["C23"].Text = "T5780";
sheet["D23"].Number = 12;
sheet["A24"].Text = "IN018";
sheet["B24"].Text = "Item 18";
sheet["C24"].Text = "T340";
sheet["D24"].Number = 82;
sheet["A25"].Text = "IN019";
sheet["B25"].Text = "Item 19";
sheet["C25"].Text = "T908";
sheet["D25"].Number = 16;
sheet["A26"].Text = "IN020";
sheet["B26"].Text = "Item 20";
sheet["C26"].Text = "T9845";
sheet["D26"].Number = 11;
sheet["A27"].Text = "Total";
// Formatting
sheet.Range["A1:F1"].Merge();
sheet.Range["A4:B4"].Merge();
sheet["A1"].CellStyle.Font.Bold = true;
sheet["A1"].CellStyle.Font.FontName = "Calibri";
sheet["A1"].CellStyle.Font.Size = 18;
sheet.Range["A1:F1"].CellStyle.IncludeBorder = true;
sheet.Range["A1:F1"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = COLOR.FromArgb(0, 204, 153);
sheet.Range["A1:F1"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
sheet["A3:F3"].CellStyle.Font.Bold = true;
sheet["A3:F3"].CellStyle.Font.FontName = "Calibri";
sheet["A3:F3"].CellStyle.Font.Size = 9;
sheet["A4:F4"].CellStyle.Font.Bold = true;
sheet["A4:F4"].CellStyle.Font.FontName = "Calibri";
sheet["A4:F4"].CellStyle.Font.Size = 14;
sheet["A4:F4"].CellStyle.Font.RGBColor = COLOR.FromArgb(0, 204, 153);
sheet["A4"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet["A4"].NumberFormat = "_($* #,##0.00_)";
sheet["A6:F6"].CellStyle.Font.FontName = "Calibri";
sheet["A6:F6"].CellStyle.Font.Size = 11;
sheet["A6:F6"].CellStyle.Font.Bold = true;
sheet["A6:F6"].CellStyle.Font.RGBColor = COLOR.FromArgb(255, 255, 255);
sheet["A6:F6"].WrapText = true;
sheet["A6:F6"].VerticalAlignment = ExcelVAlign.VAlignTop;
sheet.Range["A6:F6"].CellStyle.Color = COLOR.FromArgb(0, 204, 153);
sheet["A6:F27"].CellStyle.Font.FontName = "Calibri";
sheet["A6:F27"].CellStyle.Font.Size = 11;
sheet["D7:F27"].NumberFormat = "_($* #,##0.00_)";
sheet["D4:F4"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet["A27:F27"].CellStyle.Font.FontName = "Calibri";
sheet["A27:F27"].CellStyle.Font.Size = 11;
sheet["A27:F27"].CellStyle.Font.Bold = true;
sheet.Range["A7:27"].CellStyle.IncludeBorder = true;
sheet.Range["A7:F27"].CellStyle.Borders.ColorRGB = COLOR.FromArgb(112, 173, 71);
sheet.Range["A7:F27"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
sheet.Range["A7:F27"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["A7:F27"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet["A26:F26"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Double;
sheet.Range["A1:F1"].RowHeight = 24;
sheet["A2"].RowHeight = 7.18;
sheet["A3"].RowHeight = 23.5;
sheet["A4"].RowHeight = 19.5;
sheet["A5"].RowHeight = 6.8;
sheet["A6"].RowHeight = 41.3;
sheet["A1:B1"].ColumnWidth = 7.18;
sheet["C1"].ColumnWidth = 6.91;
sheet["D1"].ColumnWidth = 9.55;
sheet["E1"].ColumnWidth = 8.91;
sheet["F1"].ColumnWidth = 11.18;
// Formula
sheet["D4"].Formula = "=COUNT(D7:D26)";
sheet["F4"].Formula = "=SUMPRODUCT(1/COUNTIF(C7:C26,C7:C26))";
for (int i = 7; i < 27; i++)
{
sheet["F" + i].Formula = "=D" + i + "*E" + i;
}
sheet["D27"].Formula = "SUM(D7:D26)";
sheet["E27"].Formula = "SUM(E7:E26)";
sheet["F27"].Formula = "SUM(F7:F26)";
sheet["A4"].Formula = "=F27";
// Save Workbook
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
engine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Formulas.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Formulas.xlsx", "application/msexcel", stream);
}
}
public static void criarModelo(string caminho, CSV csv)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
application.EnableIncrementalFormula = true;
IWorkbook workbook = application.Workbooks.Open(caminho, ExcelOpenType.Automatic);
IWorksheet planilha = workbook.Worksheets[0];
planilha.EnableSheetCalculations();
planilha.Name = ExcelLayoutService.verificarMes(csv.mes);
workbook.SaveAs(caminho);
gerarModeloBase(workbook, caminho);
Dictionary <string, int> linhas = new Dictionary <string, int>();
linhas.Add("ultimaLinha", planilha.Rows.Count());
linhas.Add("primeiraLinha", linhas["ultimaLinha"] + 4);
linhas.Add("linhaPis", linhas["primeiraLinha"] + 1);
linhas.Add("linhaCofins", linhas["primeiraLinha"] + 2);
linhas.Add("linhaCsll", linhas["primeiraLinha"] + 3);
linhas.Add("linhaIrpj", linhas["primeiraLinha"] + 4);
gerarHeader(workbook, csv, caminho);
ExcelLayoutService.formatarValores(workbook, caminho, linhas);
gerarColunaCliente(workbook, caminho, linhas, csv);
gerarLiquido(workbook, caminho, linhas);
gerarTotal(workbook, caminho, linhas);
if (csv.temExterior)
{
gerarNacional(workbook, caminho, linhas, csv);
}
gerarColunaTributo(workbook, caminho, linhas);
gerarColunaVrImposto(workbook, caminho, linhas, csv);
gerarColunaCompensar(workbook, caminho, linhas);
gerarColunaImposto(workbook, caminho, linhas);
//gerarColunaVencimento(workbook, caminho, linhas);
ExcelLayoutService.ajustarDatas(workbook, caminho, linhas);
ExcelLayoutService.aplicarEstilos(workbook, caminho, linhas);
ExcelLayoutService.autofitColunas(workbook, caminho, linhas);
ExcelLayoutService.alinharDados(workbook, caminho);
ExcelLayoutService.aplicarBorda(workbook, caminho, linhas);
planilha = workbook.Worksheets[0];
planilha.Zoom = 90;
workbook.SaveAs(caminho);
workbook.Close();
excelEngine.Dispose();
}
private void generarreporte(int selected)
{
dtConsulta = getventas(selected);
if (dtConsulta.Rows.Count > 0)
{
int sucursales = radios();
if (sucursales == 1)
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
//headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
//pStyle.Color = colorss(m_select);
pStyle.Color = System.Drawing.Color.DodgerBlue;
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 4);
worksheet.SetColumnWidth(19, 4);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 40);
worksheet.SetColumnWidth(22, 20);
worksheet.SetColumnWidth(23, 5);
worksheet.SetColumnWidth(24, 20);
worksheet.SetColumnWidth(26, 12);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string qty = "N" + rusia + "";
string fpl = "AA" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["AA2:" + fpl].NumberFormat = "#,##0.00";
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
else //base VPN
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
pStyle.Font.Color = ExcelKnownColors.White;
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
if (m_select == 2)
{
pStyle.Color = System.Drawing.Color.Orange;
}
else if (m_select == 3 || m_select == 4)
{
pStyle.Color = System.Drawing.Color.Red;
}
else
{
pStyle.Color = System.Drawing.Color.Gray;
}
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 40);
worksheet.SetColumnWidth(19, 20);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 17);
worksheet.SetColumnWidth(22, 7);
worksheet.SetColumnWidth(23, 15);
worksheet.SetColumnWidth(24, 15);
worksheet.SetColumnWidth(25, 15);
worksheet.SetColumnWidth(26, 7);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[7].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[21].HorizontalAlignment = ExcelHAlign.HAlignRight;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string ffecha = "B" + rusia + "";
string qty = "N" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
}
else
{
RadWindow radWindow = new RadWindow();
RadWindow.Alert(new DialogParameters()
{
Content = "No hay registros para mostrar.",
Header = "BIG",
DialogStartupLocation = WindowStartupLocation.CenterOwner
// IconContent = "";
});
}
}
static void Main(string[] args)
{
intEMPRESAID = Convert.ToByte(GetSetting(appName, section, "EmpresaID", String.Empty));
intSUCURSALID = Convert.ToByte(GetSetting(appName, section, "SucursalID", String.Empty));
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = GetExistenciaDataTable();
int osos = tabla.Rows.Count;
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:H2"];
string namesuc = GetName(intSUCURSALID);
worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al " + dateday + namesuc;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 13);
worksheet.SetColumnWidth(2, 50);
worksheet.SetColumnWidth(3, 17);
worksheet.SetColumnWidth(4, 28);
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUM(H2:H" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = "LRG-Existencias al " + namer + namesuc + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
}
}
/// <summary>
/// Creates the new Excel document
/// </summary>
private void CreateSpreadSheet()
{
MemoryStream stream = new MemoryStream();
//Creates a new instance for ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Assigns default application version as Excel 2013
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
//Access the first worksheet
IWorksheet sheet = workbook.Worksheets[0];
//Initializes Calculate Engine to perform calculation
sheet.EnableSheetCalculations();
//Modifying the column width
sheet.Range["A2"].ColumnWidth = 20;
sheet.Range["B2"].ColumnWidth = 13;
sheet.Range["C2"].ColumnWidth = 13;
sheet.Range["D2"].ColumnWidth = 13;
//Modifying the row height
sheet.Range["A2"].RowHeight = 34;
//Merging the cells
sheet.Range["A2:D2"].Merge(true);
sheet.Range["B7:D7"].Merge(true);
sheet.Range["B5:D5"].Merge(true);
sheet.Range["B4:D4"].Merge(true);
sheet.Range["B6:D6"].Merge(true);
//Formatting the cells
ApplyCellStyles(sheet);
//Applying date time number format
sheet.Range["B6"].NumberFormat = "m/d/yyyy";
//Applying currency number format to the cells
sheet.Range["B7"].NumberFormat = "$#,##0.00";
sheet.Range["B11:D20"].NumberFormat = "$#,##0.00";
//Setting text to the cells
SetTextInSheet(sheet);
//Setting number values to the cells
SetNumbersInSheet(sheet);
//Setting datetime in the cell
sheet.Range["B6"].DateTime = DateTime.Parse("10/10/2012");
//Setting formula in the cells
sheet.Range["B11"].Formula = "=(B7*B10)";
sheet.Range["B20"].Formula = "=SUM(B11:B19)";
sheet.Range["C11"].Formula = "=(B7*C10)";
sheet.Range["C20"].Formula = "=SUM(C11:C19)";
sheet.Range["D11"].Formula = "=(B7*D10)";
sheet.Range["D20"].Formula = "=SUM(D11:D19)";
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
// Saving the workbook in xlsx format
workbook.SaveAs(stream);
}
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("CreateSheet.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("CreateSheet.xlsx", "application/msexcel", stream);
}
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Generate Excel
#region Insert Array Formula
sheet.EnableSheetCalculations();
sheet.Range["A2"].Text = "Array formulas";
sheet.Range["B2:E2"].Number = 3;
sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
sheet.Range["B3:E3"].Number = 5;
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 14;
#endregion
#region Excel functions
sheet.Range["A5"].Text = "Formulas";
sheet.Range["B5"].Text = "Results";
sheet.Range["B5:C5"].Merge();
sheet.Range["B5:C5"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A7"].Text = "ABS(ABS(-B3))";
sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
sheet.Range["A9"].Text = "SUM(B3,C3)";
sheet.Range["B9"].Formula = "SUM(B3,C3)";
sheet.Range["A11"].Text = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["B11"].Formula = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["A13"].Text = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["B13"].Formula = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
#endregion
#region Simple formulas
sheet.Range["C7"].Number = 10;
sheet.Range["C9"].Number = 10;
sheet.Range["A15"].Text = "C7+C9";
sheet.Range["C15"].Formula = "C7+C9";
#endregion
sheet.Range["A1"].Text = "Excel formula support";
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["A1"].CellStyle.Font.Size = 14;
sheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range["A1:C1"].Merge();
sheet.Columns[0].ColumnWidth = 23;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 10;
sheet.Columns[4].ColumnWidth = 10;
#endregion
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Formulas.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Formulas.xlsx", "application/msexcel", stream);
}
}
/// <summary>
/// Creates a new Excel workbook with the formulas
/// </summary>
private void CreateFormulaExcelFile()
{
MemoryStream stream = new MemoryStream();
//Creates a new instance for ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Assigns default application version as Excel 2013
application.DefaultVersion = ExcelVersion.Excel2013;
//Creates a new Excel workbook
IWorkbook workbook = application.Workbooks.Create(1);
//Access the first worksheet
IWorksheet sheet = workbook.Worksheets[0];
//Initializes Calculate Engine to perform calculation
sheet.EnableSheetCalculations();
#region Set values and formattings in the cells
sheet.Range["A2"].Text = "Array formulas";
sheet.Range["B2:E2"].Number = 3;
sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
sheet.Range["B3:E3"].Number = 5;
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 14;
sheet.Range["A5"].Text = "Formulas";
sheet.Range["B5"].Text = "Results";
sheet.Range["B5:C5"].Merge();
sheet.Range["B5:C5"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
sheet.Range["A1"].Text = "Excel formula support";
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["A1"].CellStyle.Font.Size = 14;
sheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range["A1:C1"].Merge();
#endregion
sheet.Range["A7"].Text = "ABS(ABS(-B3))";
//Set ABS formula to the cell B7
sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
sheet.Range["A9"].Text = "SUM(B3,C3)";
//Set SUM formula to the cell B9
sheet.Range["B9"].Formula = "SUM(B3,C3)";
sheet.Range["A11"].Text = "MIN(10,20,30,5,15,35,6,16,36)";
//Set MIN function formula to the cell B11
sheet.Range["B11"].Formula = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["A13"].Text = "MAX(10,20,30,5,15,35,6,16,36)";
//Set MAX function formula to the cell B13
sheet.Range["B13"].Formula = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["C7"].Number = 10;
sheet.Range["C9"].Number = 10;
sheet.Range["A15"].Text = "C7+C9";
//Set addition formula to the cell C15
sheet.Range["C15"].Formula = "C7+C9";
//Modifying the column width
sheet.Columns[0].ColumnWidth = 23;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 10;
sheet.Columns[4].ColumnWidth = 10;
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
// Saving the workbook in xlsx format
workbook.SaveAs(stream);
}
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Formulas.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Formulas.xlsx", "application/msexcel", stream);
}
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ExternalFormula";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("ExternalFormula.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Assembly assembly = typeof(TemplateMarker).GetTypeInfo().Assembly;
//string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.ExternalFormula.xlsx";
//Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
//IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
string fullPath = Path.GetFullPath(DEFAULTPATH);
fullPath = fullPath.Replace("\\SampleBrowser\\SampleBrowser\\", "\\SampleBrowser\\XlsIO\\");
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.Range["A2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$2";
worksheet.Range["A3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$3";
worksheet.Range["A4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$4";
worksheet.Range["A5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$5";
worksheet.Range["A6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$6";
worksheet.Range["A7"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$7";
worksheet.Range["B1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$1";
worksheet.Range["B2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$2";
worksheet.Range["B3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$3";
worksheet.Range["B4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$4";
worksheet.Range["B5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$5";
worksheet.Range["B6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$6";
worksheet.Range["C1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$1";
worksheet.Range["C1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$1";
worksheet.Range["C2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$2";
worksheet.Range["C3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$3";
worksheet.Range["C4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$4";
worksheet.Range["C5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$5";
worksheet.Range["C6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$6";
worksheet.Range["D1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$1";
worksheet.Range["D2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$2";
worksheet.Range["D3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$3";
worksheet.Range["D4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$4";
worksheet.Range["D5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$5";
worksheet.Range["D6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$6";
worksheet.Range["E1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$1";
worksheet.Range["E2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$2";
worksheet.Range["E3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$3";
worksheet.Range["E4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$4";
worksheet.Range["E5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$5";
worksheet.Range["E6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$6";
worksheet.Range["F1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$F$1";
worksheet.Range["B7"].Formula = "=SUM(B2:B6)";
worksheet.Range["C7"].Formula = "=SUM(C2:C6)";
worksheet.Range["D7"].Formula = "=SUM(D2:D6)";
worksheet.Range["E7"].Formula = "=SUM(E2:E6)";
worksheet.Range["F7"].Formula = "=SUM(F2:F6)";
worksheet.Range["F2"].Formula = "=B2*C2+D2-E2";
worksheet.Range["F3"].Formula = "=B3*C3+D3-E3";
worksheet.Range["F4"].Formula = "=B4*C4+D4-E4";
worksheet.Range["F5"].Formula = "=B5*C5+D5-E5";
worksheet.Range["F6"].Formula = "=B6*C6+D6-E6";
worksheet.Range["A1:F7"].CellStyle.Font.FontName = "Verdana";
worksheet.Range["C2:F7"].NumberFormat = "_($* #,##0.00_)";
worksheet.Range["A1:F1"].CellStyle.Color = Windows.UI.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A7:F7"].CellStyle.Color = Windows.UI.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Font.Size = 11;
worksheet.Columns[0].ColumnWidth = 17;
worksheet.Columns[1].ColumnWidth = 13;
worksheet.Columns[2].ColumnWidth = 11;
worksheet.Columns[3].ColumnWidth = 11;
worksheet.Columns[4].ColumnWidth = 13;
worksheet.Columns[5].ColumnWidth = 13;
worksheet.EnableSheetCalculations();
worksheet["A1"].CalculatedValue.ToString();
//workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Automatic;
//workbook.CalculationOptions.RecalcOnSave = true;
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
