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
}
