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