private void BtnExportExcel_Click(object sender, EventArgs e) { var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, ExcelExportingOptions1()); var workBook = excelEngine.Excel.Workbooks[0]; IWorksheet sheet = workBook.Worksheets[0]; sheet.InsertColumn(1, 1, ExcelInsertOptions.FormatDefault); var rowcount = this.sfDataGrid.RowCount; for (int i = 1; i < rowcount; i++) { sheet.Range["A" + (i + 1).ToString()].Number = (i - 1); } SaveFileDialog saveFilterDialog = new SaveFileDialog { FilterIndex = 2, Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx", FileName = "Sample1" }; if (saveFilterDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { using (Stream stream = saveFilterDialog.OpenFile()) { if (saveFilterDialog.FilterIndex == 1) { workBook.Version = ExcelVersion.Excel97to2003; } else if (saveFilterDialog.FilterIndex == 2) { workBook.Version = ExcelVersion.Excel2016; } else { workBook.Version = ExcelVersion.Excel2013; } workBook.SaveAs(stream); } //Message box confirmation to view the created workbook. if (MessageBox.Show(this.sfDataGrid, "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(saveFilterDialog.FileName); } } }
private static void gerarModeloBase(IWorkbook wb, string caminho) { IWorksheet planilha = wb.Worksheets[0]; int qtdTamanhoClienteNome = 3; for (int i = 1; i <= qtdTamanhoClienteNome; i++) { planilha.InsertColumn(4); } int qtdLinhasCabecalho = 5; for (int i = 1; i <= qtdLinhasCabecalho; i++) { planilha.InsertRow(1); } wb.SaveAs(caminho); }
// // GET: /RowColumnManipulation/ public ActionResult RowColumnManipulation(string Saveoption, string button) { if (Saveoption == 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; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"monthly_sales.xlsx")); return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } 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; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 3 worksheets IWorkbook workbook; if (Saveoption == "Xlsx") { application.DefaultVersion = ExcelVersion.Excel2016; workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xlsx")); } else { workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xls")); } //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; #region Grouping and ungrouping // Grouping by Rows sheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows); // Grouping by Columns sheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns); #endregion #region Hiding unhiding // Hiding fifth and sixth Column sheet.ShowColumn(5, false); sheet.ShowColumn(6, false); //Showing the 28th row sheet.ShowRow(28, true); #endregion #region Insert and delete //Deleting Row sheet.DeleteRow(25); //Inserting Column sheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore); sheet.Range["G4"].Text = "Loss/Gain"; //Deleting Column sheet.DeleteColumn(8); #endregion #region ColumnWidth and RowHeight // Changing the Column Width sheet.Range["D5"].ColumnWidth = 15; // Changing the Row Height sheet.Range["D29"].RowHeight = 25; #endregion try { // Save the file if (Saveoption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
// // GET: /Default/ public ActionResult Default(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; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 12 worksheets IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls")); IWorksheet sheet = workbook.Worksheets[1]; sheet.FirstVisibleRow = 3; IFont font = workbook.CreateFont(); font.Bold = true; #region TextBox ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140); textbox.Text = "Quick Budget"; textbox.RichText.SetFont(0, 11, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(204, 204, 255); textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140); textbox.Text = "Income"; textbox.RichText.SetFont(0, 5, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140); textbox.Text = "Spending"; textbox.RichText.SetFont(0, 7, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); #endregion #region Write Text and Numbers sheet.Range["O6"].Text = "Weekly"; sheet.Range["E7"].Text = "Frequency"; sheet.Range["F7"].Text = "Amount"; sheet.Range["G7"].Text = "Monthly"; sheet.Range["H7"].Text = "Yearly"; sheet.Range["B8"].Text = "Total Income"; sheet.Range["C9"].Text = "Salary/Wages"; sheet.Range["C10"].Text = "Salary/Wages(Spouse)"; sheet.Range["C11"].Text = "Other"; sheet.Range["C12"].Text = "Other"; sheet.Range["C13"].Text = "Other"; sheet.Range["B17"].Text = "Transportation"; sheet.Range["F25"].Number = 3000; sheet.Range["F9"].Number = 55000; sheet.Range["F10"].Number = 35000; sheet.Range["C18"].Text = "Auto Loan/Lease"; sheet.Range["C19"].Text = "Insurance"; sheet.Range["C20"].Text = "Gas "; sheet.Range["C21"].Text = "Maintenance "; sheet.Range["C22"].Text = "Registration/Inspection"; sheet.Range["C23"].Text = "Bill's train pass"; sheet.Range["C24"].Text = "Jane's bus pass"; sheet.Range["C25"].Text = "Other"; sheet.Range["E16"].Text = "Total"; sheet.Range["N6"].Text = "Chart"; sheet.Range["B27"].Text = "Home"; sheet.Range["F28"].Number = 20000; sheet.Range["F29"].Number = 5000; sheet.Range["F33"].Number = 5000; sheet.Range["C28"].Text = "EMI"; sheet.Range["C29"].Text = "Rent"; sheet.Range["C30"].Text = "Maintanence"; sheet.Range["C31"].Text = "Insurance"; sheet.Range["C32"].Text = "Furniture"; sheet.Range["C33"].Text = "Household Supplies"; sheet.Range["C34"].Text = "Groceries"; sheet.Range["C35"].Text = "Real Estate Tax"; sheet.Range["C36"].Text = "Other"; sheet.Range["B39"].Text = "Utilities"; sheet.Range["F41"].Number = 1000; sheet.Range["F42"].Number = 250; sheet.Range["F43"].Number = 150; sheet.Range["F45"].Number = 175; sheet.Range["C40"].Text = "Phone - Home"; sheet.Range["C41"].Text = "Phone - Cell"; sheet.Range["C42"].Text = "Cable"; sheet.Range["C43"].Text = "Gas"; sheet.Range["C44"].Text = "Water"; sheet.Range["C45"].Text = "Electricity"; sheet.Range["C46"].Text = "Internet"; sheet.Range["C47"].Text = "Other"; sheet.Range["B50"].Text = "Health"; sheet.Range["F53"].Number = 500; sheet.Range["C51"].Text = "Dental"; sheet.Range["C52"].Text = "Medical"; sheet.Range["C53"].Text = "Medication"; sheet.Range["C54"].Text = "Vision/contacts"; sheet.Range["C55"].Text = "Life Insurance"; sheet.Range["C56"].Text = "Electricity"; sheet.Range["C57"].Text = "Other"; #endregion #region Cell styles IStyle tableStyle = workbook.Styles.Add("TableStyle"); tableStyle.BeginUpdate(); tableStyle.Color = Color.White; tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableStyle.EndUpdate(); sheet.Range["E7:H7"].CellStyle.Font.Bold = true; sheet.Range["B17"].CellStyle.Font.Bold = true; sheet.Range["B27"].CellStyle.Font.Bold = true; sheet.Range["B39"].CellStyle.Font.Bold = true; sheet.Range["B50"].CellStyle.Font.Bold = true; sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single; sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C9:C13"].CellStyle = tableStyle; sheet.Range["E9:F13"].CellStyle = tableStyle; sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["B17:C17"].CellStyle.Color = Color.White; sheet.Range["C18:C25"].CellStyle = tableStyle; sheet.Range["O6"].CellStyle = tableStyle; sheet.Range["E18:F25"].CellStyle = tableStyle; sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C28:C36"].CellStyle = tableStyle; sheet.Range["B27:C27"].CellStyle.Color = Color.White; sheet.Range["E28:F36"].CellStyle = tableStyle; sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C40:C47"].CellStyle = tableStyle; sheet.Range["B39:C39"].CellStyle.Color = Color.White; sheet.Range["E40:F47"].CellStyle = tableStyle; sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C51:C57"].CellStyle = tableStyle; sheet.Range["B50:C50"].CellStyle.Color = Color.White; sheet.Range["E51:F57"].CellStyle = tableStyle; #endregion #region Data Validation IDataValidation validation = sheet.Range["E9:E13"].DataValidation; sheet.Range["E9:E13"].Text = "Monthly"; validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation; sheet.Range["E18:E25"].Text = "Monthly"; validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation2 = sheet.Range["O6"].DataValidation; validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" }; IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation; sheet.Range["E28:E36"].Text = "Monthly"; validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation; sheet.Range["E40:E47"].Text = "Monthly"; validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation; sheet.Range["E51:E57"].Text = "Monthly"; validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; #endregion #region Formulas sheet.Range["G8"].Formula = "SUM(G9:G13)"; sheet.Range["H8"].Formula = "SUM(H9:H13)"; sheet.Range["G17"].Formula = "SUM(G18:G25)"; sheet.Range["H17"].Formula = "SUM(H18:H25)"; sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71"; sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71"; for (int i = 9; i <= 13; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } for (int i = 18; i <= 25; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G27"].Formula = "SUM(G28:G36)"; sheet.Range["H27"].Formula = "SUM(H28:H37)"; for (int i = 28; i <= 36; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G39"].Formula = "SUM(G40:G47)"; sheet.Range["H39"].Formula = "SUM(H40:H47)"; for (int i = 40; i <= 47; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G50"].Formula = "SUM(G51:G57)"; sheet.Range["H50"].Formula = "SUM(H51:H57)"; for (int i = 51; i <= 57; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } #endregion #region SummaryChart //Clustered Column Chart IChartShape chart = sheet.Charts.Add(); //Set Chart Type chart.ChartType = ExcelChartType.Bar_Clustered; //Set DataRange. chart.Series.Add("Expense"); chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"]; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Income"); chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"]; chart.Series[1].DataPoints[0].DataLabels.IsValue = true; chart.Series[1].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Balance"); chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"]; chart.Series[2].DataPoints[0].DataLabels.IsValue = true; chart.Series[2].DataPoints[0].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; chart.PrimaryCategoryAxis.Visible = false; //Format Chart Area IChartFrameFormat chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area IChartFrameFormat chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 7; chart.BottomRow = 22; chart.LeftColumn = 9; chart.RightColumn = 16; chart.ChartTitle = "Budget Summary"; chart.ChartTitleArea.Bold = true; #endregion #region SpendingChart chart = sheet.Charts.Add(); chart.ChartTitle = "Spending Summary"; chart.ChartTitleArea.Bold = true; //Set Chart Type chart.ChartType = ExcelChartType.Pie_3D; //Set DataRange. chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"]; chart.IsSeriesInRows = false; chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"]; chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"]; chart.Series[0].Name = "Spending summary"; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series[0].DataPoints[1].DataLabels.IsValue = true; chart.Series[0].DataPoints[1].DataLabels.Size = 7f; chart.Series[0].DataPoints[2].DataLabels.IsValue = true; chart.Series[0].DataPoints[2].DataLabels.Size = 7f; chart.Series[0].DataPoints[3].DataLabels.IsValue = true; chart.Series[0].DataPoints[3].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; //Format Chart Area chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223); chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 25; chart.BottomRow = 42; chart.LeftColumn = 9; chart.RightColumn = 16; #endregion #region Sheet View workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden; workbook.Worksheets[0].Activate(); workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue; workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue; workbook.Worksheets[1].IsRowColumnHeadersVisible = false; sheet.InsertColumn(9); #endregion try { //Saving the workbook to disk. if (SaveOption == "Xls") { //Save as .xls format return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } //Save as .xlsx format else { workbook.Version = ExcelVersion.Excel2016; return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); return(View()); }
/// <summary> /// Gets Excel file for invoice using Syncfusion.XlsIO /// </summary> /// <param name="invoiceId">invoice Id for which file should be generated</param> /// <returns>Excel file for invoice</returns> public FileStreamResult GetInvoiceExcel(string invoiceId) { var invoice = InvoiceManager.GetInvoiceViewModelById(invoiceId); invoice.InvoiceLines = InvoiceManager.GetInvoiceLineViewModels(invoiceId).OrderBy(x => x.LineNumber); //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; // Creating new workbook IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = $"Faktura_{invoice.InvoiceNumber.Replace("/", "_")}"; sheet.Name = sheet.Name.Length > 31 ? sheet.Name.Substring(0, 31) : sheet.Name; #region Generate Excel sheet.Range["A2"].ColumnWidth = 15; sheet.Range["B2"].ColumnWidth = 15; sheet.Range["C2"].ColumnWidth = 25; sheet.Range["D2"].ColumnWidth = 15; sheet.Range["E2"].ColumnWidth = 15; sheet.Range["F2"].ColumnWidth = 15; sheet.Range["G2"].ColumnWidth = 15; sheet.Range["H2"].ColumnWidth = 15; sheet.Range["I2"].ColumnWidth = 15; sheet.Range["J2"].ColumnWidth = 15; sheet.Range["K2"].ColumnWidth = 15; sheet.Range["L2"].ColumnWidth = 15; sheet.Range["M2"].ColumnWidth = 15; sheet.Range["N2"].ColumnWidth = 15; sheet.Range["O2"].ColumnWidth = 15; sheet.Range["A2:O2"].Merge(true); sheet.Range["A2:O2"].BorderAround(ExcelLineStyle.Thick, Color.FromArgb(47, 117, 181)); sheet.Range["A2:O2"].CellStyle.Color = Color.FromArgb(242, 242, 242); //Invoice Header sheet.Range["A2"].Text = invoice.InvoiceNumber; 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.FromArgb(0, 0, 112, 192); sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A5:B5"].Merge(true); sheet.Range["A5"].Text = "Numer referencyjny"; sheet.Range["C5"].Text = invoice.ReferenceNumber; sheet.Range["A6:B6"].Merge(true); sheet.Range["A6"].Text = "Klient"; sheet.Range["C6"].Text = invoice.Customer.Name; sheet.Range["A7:B7"].Merge(true); sheet.Range["A7"].Text = "Domyślny budżet"; sheet.Range["C7"].Text = invoice.Budget.BudgetNumber; sheet.Range["A8:B8"].Merge(true); sheet.Range["A8"].Text = "Domyślna waluta"; sheet.Range["C8"].Text = invoice.Currency; sheet.Range["A9:B9"].Merge(true); sheet.Range["A9"].Text = "Metoda płatności"; sheet.Range["C9"].Text = invoice.PaymentMethod; sheet.Range["C11:C13"].NumberFormat = "yyyy/mm/dd"; sheet.Range["A11:B11"].Merge(true); sheet.Range["A11"].Text = "Data płatności"; sheet.Range["C11"].DateTime = invoice.PaymentDueDate; sheet.Range["A12:B12"].Merge(true); sheet.Range["A12"].Text = "Data wydania"; sheet.Range["C12"].DateTime = invoice.IssueDate; sheet.Range["A13:B13"].Merge(true); sheet.Range["A13"].Text = "Data przyjęcia"; sheet.Range["C13"].DateTime = invoice.ReceiveDate; sheet.Range["A5:C13"].CellStyle.Font.FontName = "Verdana"; sheet.Range["A5:C13"].CellStyle.Font.Bold = true; sheet.Range["A5:C13"].CellStyle.Font.Size = 11; sheet.Range["A5:C13"].CellStyle.Font.RGBColor = Color.FromArgb(0, 128, 128, 128); sheet.Range["A5:C13"].HorizontalAlignment = ExcelHAlign.HAlignLeft; sheet.Range["B5:C13"].CellStyle.Font.RGBColor = Color.FromArgb(0, 174, 170, 170); sheet.Range["B5:C13"].HorizontalAlignment = ExcelHAlign.HAlignRight; //Invoice lines header var invoiceLineHdgIndex = 15; sheet.Range[$"A{invoiceLineHdgIndex}"].Text = "Numer linii"; sheet.Range[$"B{invoiceLineHdgIndex}"].Text = "Kod pozycji"; sheet.Range[$"C{invoiceLineHdgIndex}"].Text = "Opis"; sheet.Range[$"D{invoiceLineHdgIndex}"].Text = "Ilość"; sheet.Range[$"E{invoiceLineHdgIndex}"].Text = "Cena jedn."; sheet.Range[$"F{invoiceLineHdgIndex}"].Text = "Waluta"; sheet.Range[$"G{invoiceLineHdgIndex}"].Text = "Kurs waluty"; sheet.Range[$"H{invoiceLineHdgIndex}"].Text = "Stawka Vat(%)"; sheet.Range[$"I{invoiceLineHdgIndex}"].Text = "Netto (waluta)"; sheet.Range[$"J{invoiceLineHdgIndex}"].Text = "VAT (waluta)"; sheet.Range[$"K{invoiceLineHdgIndex}"].Text = "Brutto (waluta)"; sheet.Range[$"L{invoiceLineHdgIndex}"].Text = "Netto (PLN)"; sheet.Range[$"M{invoiceLineHdgIndex}"].Text = "VAT (PLN)"; sheet.Range[$"N{invoiceLineHdgIndex}"].Text = "Brutto (PLN)"; sheet.Range[$"O{invoiceLineHdgIndex}"].Text = "Kod budżetu"; sheet.Range[$"A{invoiceLineHdgIndex}"].RowHeight = 30; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].WrapText = true; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].BorderInside(ExcelLineStyle.Thin, ExcelKnownColors.White); sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Font.Bold = true; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Color = Color.FromArgb(47, 117, 181); sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].VerticalAlignment = ExcelVAlign.VAlignCenter; //Invoice lines details int i = 1; foreach (var line in invoice.InvoiceLines) { if (i % 2 == 0) { sheet.Range[$"A{invoiceLineHdgIndex + i}:O{invoiceLineHdgIndex + i}"].CellStyle.Color = Color.FromArgb(230, 230, 230); } sheet.Range[$"A{invoiceLineHdgIndex + i}"].Number = line.LineNumber; sheet.Range[$"B{invoiceLineHdgIndex + i}"].Text = line.ItemName; sheet.Range[$"C{invoiceLineHdgIndex + i}"].Text = line.Description; sheet.Range[$"D{invoiceLineHdgIndex + i}"].Number = line.Quantity; sheet.Range[$"E{invoiceLineHdgIndex + i}"].Number = (double)line.Price; sheet.Range[$"E{invoiceLineHdgIndex + i}"].NumberFormat = "#,###0.00"; sheet.Range[$"F{invoiceLineHdgIndex + i}"].Text = line.Currency; sheet.Range[$"G{invoiceLineHdgIndex + i}"].Number = (double)line.CurrencyRate; sheet.Range[$"G{invoiceLineHdgIndex + i}"].NumberFormat = "#,###0.0000"; sheet.Range[$"H{invoiceLineHdgIndex + i}"].Number = (double)line.TaxRate; sheet.Range[$"I{invoiceLineHdgIndex + i}"].Number = (double)line.Netto; sheet.Range[$"J{invoiceLineHdgIndex + i}"].Number = (double)line.Tax; sheet.Range[$"K{invoiceLineHdgIndex + i}"].Number = (double)line.Gross; sheet.Range[$"L{invoiceLineHdgIndex + i}"].Number = (double)line.BaseNetto; sheet.Range[$"M{invoiceLineHdgIndex + i}"].Number = (double)line.BaseTax; sheet.Range[$"N{invoiceLineHdgIndex + i}"].Number = (double)line.BaseGross; sheet.Range[$"I{invoiceLineHdgIndex + i}:N{15 + i}"].NumberFormat = "#,###0.00"; sheet.Range[$"O{invoiceLineHdgIndex + i}"].Text = line.Budget.BudgetNumber; i++; } sheet.Range[$"A{invoiceLineHdgIndex + i - 1}:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet.Range[$"A15:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Font.FontName = "Verdana"; sheet.Range[$"A15:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Font.Size = 11; sheet.InsertColumn(1, 1, ExcelInsertOptions.FormatDefault); sheet.IsGridLinesVisible = false; #endregion workbook.Version = ExcelVersion.Excel2013; var contentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var fileName = $"{invoice.InvoiceNumber.Replace("/", "_")}_{DateHelper.GetCurrentDatetime():yyyyMMdd}.xlsx"; MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; FileStreamResult fileStreamResult = new FileStreamResult(ms, contentType); fileStreamResult.FileDownloadName = fileName; return(fileStreamResult); }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Workbook Initialization //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 file path of input file string inputPath = GetFullTemplatePath("monthly_sales.xls"); //Open an existing spreadsheet which will be used as a template for creating new worksheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. //IWorkbook workbook = application.Workbooks.Open(inputPath); IWorkbook workbook = application.Workbooks.Open(inputPath); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Row and Column Manipulations #region Grouping and ungrouping // Grouping by Rows worksheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows); // Grouping by Columns worksheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns); #endregion #region Hiding unhiding // Hiding fifth and sixth Column worksheet.ShowColumn(5, false); worksheet.ShowColumn(6, false); //Showing the 28th row worksheet.ShowRow(28, true); #endregion #region Insert and delete //Deleting Row worksheet.DeleteRow(25); //Inserting Column worksheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore); worksheet.Range["G4"].Text = "Loss/Gain"; //Deleting Column worksheet.DeleteColumn(8); #endregion #region ColumnWidth and RowHeight // Changing the Column Width worksheet.Range["D5"].ColumnWidth = 15; // Changing the Row Height worksheet.Range["D29"].RowHeight = 25; #endregion #endregion #region Workbook Save //Set the default version as Excel 97to2003 if (this.rdbExcel97.Checked) { workbook.Version = ExcelVersion.Excel97to2003; fileName = "Sample.xls"; } //Set the default version as Excel 2007 else if (this.rdbExcel2007.Checked) { workbook.Version = ExcelVersion.Excel2007; fileName = "Sample.xlsx"; } //Set the default version as Excel 2010 else if (this.rdbExcel2010.Checked) { workbook.Version = ExcelVersion.Excel2010; fileName = "Sample.xlsx"; } //Set the default version as Excel 2010 else if (this.rdbExcel2013.Checked) { workbook.Version = ExcelVersion.Excel2013; fileName = "Sample.xlsx"; } //Saving the workbook to disk. workbook.SaveAs(fileName); #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 #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] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
/// <summary> /// Creates Spreadsheet with Styles and Formatting /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnCreate_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; //Get the path of the input file string inputPath = GetFullTemplatePath("BudgetPlanner.xls"); //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 12 worksheets IWorkbook workbook = application.Workbooks.Open(inputPath); IWorksheet worksheet = workbook.Worksheets[1]; worksheet.FirstVisibleRow = 3; //Set the Default version as Excel 97to2003 if (this.rdbExcel97.Checked) { workbook.Version = ExcelVersion.Excel97to2003; fileName = "BudgetPlannerOutput.xls"; } //Set the Default version as Excel 2007 else if (this.rdbExcek2007.Checked) { workbook.Version = ExcelVersion.Excel2007; fileName = "BudgetPlannerOutput.xlsx"; } //Set the Default version as Excel 2010 else if (this.rdbExcel2010.Checked) { workbook.Version = ExcelVersion.Excel2010; fileName = "BudgetPlannerOutput.xlsx"; } //Set the Default version as Excel 2013 else if (this.rdbExcel2013.Checked) { workbook.Version = ExcelVersion.Excel2013; fileName = "BudgetPlannerOutput.xlsx"; } IFont font = workbook.CreateFont(); font.Bold = true; #endregion #region TextBox ITextBoxShape textbox = worksheet.TextBoxes.AddTextBox(5, 2, 40, 140); textbox.Text = "Quick Budget"; textbox.RichText.SetFont(0, 11, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(204, 204, 255); textbox = worksheet.TextBoxes.AddTextBox(7, 2, 25, 140); textbox.Text = "Income"; textbox.RichText.SetFont(0, 5, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); textbox = worksheet.TextBoxes.AddTextBox(16, 2, 25, 140); textbox.Text = "Spending"; textbox.RichText.SetFont(0, 7, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); #endregion #region Write Text and Numbers worksheet.Range["O6"].Text = "Weekly"; worksheet.Range["E7"].Text = "Frequency"; worksheet.Range["F7"].Text = "Amount"; worksheet.Range["G7"].Text = "Monthly"; worksheet.Range["H7"].Text = "Yearly"; worksheet.Range["B8"].Text = "Total Income"; worksheet.Range["C9"].Text = "Salary/Wages"; worksheet.Range["C10"].Text = "Salary/Wages(Spouse)"; worksheet.Range["C11"].Text = "Other"; worksheet.Range["C12"].Text = "Other"; worksheet.Range["C13"].Text = "Other"; worksheet.Range["B17"].Text = "Transportation"; worksheet.Range["F25"].Number = 3000; worksheet.Range["F9"].Number = 55000; worksheet.Range["F10"].Number = 35000; worksheet.Range["C18"].Text = "Auto Loan/Lease"; worksheet.Range["C19"].Text = "Insurance"; worksheet.Range["C20"].Text = "Gas "; worksheet.Range["C21"].Text = "Maintenance "; worksheet.Range["C22"].Text = "Registration/Inspection"; worksheet.Range["C23"].Text = "Bill's train pass"; worksheet.Range["C24"].Text = "Jane's bus pass"; worksheet.Range["C25"].Text = "Other"; worksheet.Range["E16"].Text = "Total"; worksheet.Range["N6"].Text = "Chart"; worksheet.Range["B27"].Text = "Home"; worksheet.Range["F28"].Number = 20000; worksheet.Range["F29"].Number = 5000; worksheet.Range["F33"].Number = 5000; worksheet.Range["C28"].Text = "EMI"; worksheet.Range["C29"].Text = "Rent"; worksheet.Range["C30"].Text = "Maintanence"; worksheet.Range["C31"].Text = "Insurance"; worksheet.Range["C32"].Text = "Furniture"; worksheet.Range["C33"].Text = "Household Supplies"; worksheet.Range["C34"].Text = "Groceries"; worksheet.Range["C35"].Text = "Real Estate Tax"; worksheet.Range["C36"].Text = "Other"; worksheet.Range["B39"].Text = "Utilities"; worksheet.Range["F41"].Number = 1000; worksheet.Range["F42"].Number = 250; worksheet.Range["F43"].Number = 150; worksheet.Range["F45"].Number = 175; worksheet.Range["C40"].Text = "Phone - Home"; worksheet.Range["C41"].Text = "Phone - Cell"; worksheet.Range["C42"].Text = "Cable"; worksheet.Range["C43"].Text = "Gas"; worksheet.Range["C44"].Text = "Water"; worksheet.Range["C45"].Text = "Electricity"; worksheet.Range["C46"].Text = "Internet"; worksheet.Range["C47"].Text = "Other"; worksheet.Range["B50"].Text = "Health"; worksheet.Range["F53"].Number = 500; worksheet.Range["C51"].Text = "Dental"; worksheet.Range["C52"].Text = "Medical"; worksheet.Range["C53"].Text = "Medication"; worksheet.Range["C54"].Text = "Vision/contacts"; worksheet.Range["C55"].Text = "Life Insurance"; worksheet.Range["C56"].Text = "Electricity"; worksheet.Range["C57"].Text = "Other"; #endregion #region Cell styles IStyle tableStyle = workbook.Styles.Add("TableStyle"); tableStyle.BeginUpdate(); tableStyle.Color = Color.White; tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableStyle.EndUpdate(); worksheet.Range["E7:H7"].CellStyle.Font.Bold = true; worksheet.Range["B17"].CellStyle.Font.Bold = true; worksheet.Range["B27"].CellStyle.Font.Bold = true; worksheet.Range["B39"].CellStyle.Font.Bold = true; worksheet.Range["B50"].CellStyle.Font.Bold = true; worksheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single; worksheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223); worksheet.Range["C9:C13"].CellStyle = tableStyle; worksheet.Range["E9:F13"].CellStyle = tableStyle; worksheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223); worksheet.Range["B17:C17"].CellStyle.Color = Color.White; worksheet.Range["C18:C25"].CellStyle = tableStyle; worksheet.Range["O6"].CellStyle = tableStyle; worksheet.Range["E18:F25"].CellStyle = tableStyle; worksheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223); worksheet.Range["C28:C36"].CellStyle = tableStyle; worksheet.Range["B27:C27"].CellStyle.Color = Color.White; worksheet.Range["E28:F36"].CellStyle = tableStyle; worksheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223); worksheet.Range["C40:C47"].CellStyle = tableStyle; worksheet.Range["B39:C39"].CellStyle.Color = Color.White; worksheet.Range["E40:F47"].CellStyle = tableStyle; worksheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223); worksheet.Range["C51:C57"].CellStyle = tableStyle; worksheet.Range["B50:C50"].CellStyle.Color = Color.White; worksheet.Range["E51:F57"].CellStyle = tableStyle; #endregion #region Data Validation IDataValidation validation = worksheet.Range["E9:E13"].DataValidation; worksheet.Range["E9:E13"].Text = "Monthly"; validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation1 = worksheet.Range["E18:E25"].DataValidation; worksheet.Range["E18:E25"].Text = "Monthly"; validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation2 = worksheet.Range["O6"].DataValidation; validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" }; IDataValidation validation3 = worksheet.Range["E28:E37"].DataValidation; worksheet.Range["E28:E36"].Text = "Monthly"; validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation4 = worksheet.Range["E40:E47"].DataValidation; worksheet.Range["E40:E47"].Text = "Monthly"; validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation5 = worksheet.Range["E51:E57"].DataValidation; worksheet.Range["E51:E57"].Text = "Monthly"; validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; #endregion #region Formulas worksheet.Range["G8"].Formula = "SUM(G9:G13)"; worksheet.Range["H8"].Formula = "SUM(H9:H13)"; worksheet.Range["G17"].Formula = "SUM(G18:G25)"; worksheet.Range["H17"].Formula = "SUM(H18:H25)"; worksheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71"; worksheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71"; for (int i = 9; i <= 13; i++) { worksheet.Range["G" + i].Formula = "F" + i + "*A" + i; worksheet.Range["H" + i].Formula = "G" + i + "*12"; } for (int i = 18; i <= 25; i++) { worksheet.Range["G" + i].Formula = "F" + i + "*A" + i; worksheet.Range["H" + i].Formula = "G" + i + "*12"; } worksheet.Range["G27"].Formula = "SUM(G28:G36)"; worksheet.Range["H27"].Formula = "SUM(H28:H37)"; for (int i = 28; i <= 36; i++) { worksheet.Range["G" + i].Formula = "F" + i + "*A" + i; worksheet.Range["H" + i].Formula = "G" + i + "*12"; } worksheet.Range["G39"].Formula = "SUM(G40:G47)"; worksheet.Range["H39"].Formula = "SUM(H40:H47)"; for (int i = 40; i <= 47; i++) { worksheet.Range["G" + i].Formula = "F" + i + "*A" + i; worksheet.Range["H" + i].Formula = "G" + i + "*12"; } worksheet.Range["G50"].Formula = "SUM(G51:G57)"; worksheet.Range["H50"].Formula = "SUM(H51:H57)"; for (int i = 51; i <= 57; i++) { worksheet.Range["G" + i].Formula = "F" + i + "*A" + i; worksheet.Range["H" + i].Formula = "G" + i + "*12"; } #endregion #region SummaryChart //Clustered Column Chart IChartShape chart = worksheet.Charts.Add(); //Set Chart Type chart.ChartType = ExcelChartType.Bar_Clustered; //Set DataRange. chart.Series.Add("Expense"); chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"]; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Income"); chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"]; chart.Series[1].DataPoints[0].DataLabels.IsValue = true; chart.Series[1].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Balance"); chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"]; chart.Series[2].DataPoints[0].DataLabels.IsValue = true; chart.Series[2].DataPoints[0].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; chart.PrimaryCategoryAxis.Visible = false; //Format Chart Area IChartFrameFormat chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area IChartFrameFormat chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 7; chart.BottomRow = 22; chart.LeftColumn = 9; chart.RightColumn = 16; chart.ChartTitle = "Budget Summary"; chart.ChartTitleArea.Bold = true; #endregion #region SpendingChart chart = worksheet.Charts.Add(); chart.ChartTitle = "Spending Summary"; chart.ChartTitleArea.Bold = true; //Set Chart Type chart.ChartType = ExcelChartType.Pie_3D; //Set DataRange. chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"]; chart.IsSeriesInRows = false; chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"]; chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"]; chart.Series[0].Name = "Spending summary"; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series[0].DataPoints[1].DataLabels.IsValue = true; chart.Series[0].DataPoints[1].DataLabels.Size = 7f; chart.Series[0].DataPoints[2].DataLabels.IsValue = true; chart.Series[0].DataPoints[2].DataLabels.Size = 7f; chart.Series[0].DataPoints[3].DataLabels.IsValue = true; chart.Series[0].DataPoints[3].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; //Format Chart Area chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223); chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 25; chart.BottomRow = 42; chart.LeftColumn = 9; chart.RightColumn = 16; #endregion #region Sheet View workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden; workbook.Worksheets[0].Activate(); workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue; workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue; workbook.Worksheets[1].IsRowColumnHeadersVisible = false; worksheet.InsertColumn(9); #endregion #region Save the Workbook //Saving the workbook to disk. workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose workbook.Close(); 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] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }