/// <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 }
// // 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(ResolveApplicationDataPath(@"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 workbook.SaveAs("SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97); } //Save as .xlsx format else { workbook.Version = ExcelVersion.Excel2016; workbook.SaveAs("SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); return View(); }
// // GET: /FormControls/ public ActionResult FormControls(string SaveOption) { if (SaveOption == null) { return(View()); } onlinePayments = new string[] { "Credit Card", "Net Banking" }; // 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; // Check if user opts for XLSX if (SaveOption == "Xlsx") { application.DefaultVersion = ExcelVersion.Excel2016; color1 = Color.FromArgb(255, 255, 230); } // Check if user opts for XLS else { color1 = Color.FromArgb(255, 255, 204); } // A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] // Workbook created with two worksheets IWorkbook workbook = application.Workbooks.Create(2); // The first worksheet object in the worksheets collection is accessed. // (0 based index) IWorksheet sheet2 = workbook.Worksheets[1]; //Assigning the array content to cells // by passing row and column position for (int i = 0; i < onlinePayments.Length; i++) { sheet2.SetValue(i + 1, 1, onlinePayments[i]); } // The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; sheet.Pictures.AddPicture(2, 3, ResolveApplicationImagePath(@"contact_sales.gif")); sheet[4, 3].Text = "Phone"; sheet[4, 3].CellStyle.Font.Bold = true; sheet[5, 3].Text = "Toll Free"; sheet[5, 5].Text = "1-888-9DOTNET"; sheet[6, 5].Text = "1-888-936-8638"; sheet[7, 5].Text = "1-919-481-1974"; sheet[8, 3].Text = "Fax"; sheet[8, 5].Text = "1-919-573-0306"; sheet[9, 3].Text = "Email"; sheet[10, 3].Text = "Sales"; //Creating the hyperlink in the 10th column and //5th row of the sheet IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]); link.Type = ExcelHyperLinkType.Url; link.Address = "mailto:[email protected]"; sheet[12, 3].Text = "Please fill out all required fields."; sheet[14, 5].Text = "First Name*"; sheet[14, 5].CellStyle.Font.Bold = true; sheet[14, 8].Text = "Last Name*"; sheet[14, 8].CellStyle.Font.Bold = true; //Create textbox for respective field //textbox to get First Name ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; //textbox to get Last Name textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[17, 3].Text = "Company*"; textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[19, 3].Text = "Phone*"; textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[21, 3].Text = "Email*"; textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[23, 3].Text = "Website"; textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385); ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75); chkBoxProducts.Text = ""; sheet[25, 3].Text = "Multiple products?"; sheet[27, 3, 28, 3].Merge(); sheet[27, 3].Text = "Product(s)*"; sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; // Create a checkbox for each product ICheckBoxShape chkBoxProduct; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75); chkBoxProduct.Text = "Studio"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75); chkBoxProduct.Text = "Calculate"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75); chkBoxProduct.Text = "Chart"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75); chkBoxProduct.Text = "Diagram"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75); chkBoxProduct.Text = "Edit"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75); chkBoxProduct.Text = "XlsIO"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75); chkBoxProduct.Text = "Grid"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75); chkBoxProduct.Text = "Grouping"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75); chkBoxProduct.Text = "HTMLUI"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75); chkBoxProduct.Text = "PDF"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75); chkBoxProduct.Text = "Tools"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75); chkBoxProduct.Text = "DocIO"; chkBoxProducts.CheckState = ExcelCheckState.Mixed; //generate the link to linked cell property and formula GenerateFormula(excelEngine); sheet[30, 3].Text = "Selected Products Count"; //counts the selected product sheet[30, 5].Formula = "Sum(AA2:AA13)"; //align the cell content sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; //create the textbox for additional information sheet[35, 3].Text = "Additional Information"; textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385); if (!(SaveOption == "Xls")) { sheet[43, 3].Text = "Online Payment"; // Create combobox IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100); // Assign range to display in dropdown list comboBox1.ListFillRange = sheet2["A1:A2"]; // select 1st item from the list comboBox1.SelectedIndex = 1; sheet[46, 3].Text = "Card Type"; IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5); optionButton1.Text = "American Express"; optionButton1.CheckState = ExcelCheckState.Checked; optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7); optionButton1.Text = "Master Card"; optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9); optionButton1.Text = "Visa"; } //column alignment sheet.Columns[0].AutofitColumns(); sheet.Columns[3].ColumnWidth = 12; sheet.Columns[4].ColumnWidth = 10; sheet.Columns[5].ColumnWidth = 10; sheet.IsGridLinesVisible = false; sheet.DeleteRow(40); sheet.DeleteRow(41); sheet.DeleteRow(42); sheet.DeleteRow(45); try { // Save the file if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "FormControls.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "FormControls.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); return(View()); }
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; // Check if user opts for Excel 2007 if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; color1 = Color.FromArgb(255, 255, 230); } // Check if user opts for Excel 2010 else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; color1 = Color.FromArgb(255, 255, 230); } // Check if user opts for Excel 2013 else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; color1 = Color.FromArgb(255, 255, 230); } else { color1 = Color.FromArgb(255, 255, 204); } // A new workbook is created.[Equivalent to creating a new workbook in MS Excel] // Workbook created with two worksheets IWorkbook workbook = application.Workbooks.Create(2); // The first worksheet object in the worksheets collection is accessed. // (0 based index) IWorksheet worksheet2 = workbook.Worksheets[1]; //Assigning the array content to cells // by passing row and column position for (int i = 0; i < onlinePayments.Length; i++) { worksheet2.SetValue(i + 1, 1, onlinePayments[i]); } // The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Add Picture //Get the path of the input file string inputPath = GetTemplatePath("contact_sales.gif"); worksheet.Pictures.AddPicture(2, 3, inputPath); #endregion worksheet[4, 3].Text = "Phone"; worksheet[4, 3].CellStyle.Font.Bold = true; worksheet[5, 3].Text = "Toll Free"; worksheet[5, 5].Text = "1-888-9DOTNET"; worksheet[6, 5].Text = "1-888-936-8638"; worksheet[7, 5].Text = "1-919-481-1974"; worksheet[8, 3].Text = "Fax"; worksheet[8, 5].Text = "1-919-573-0306"; worksheet[9, 3].Text = "Email"; worksheet[10, 3].Text = "Sales"; #region Add Hyperlink //Creating the hyperlink in the 10th column and //5th row of the worksheet IHyperLink link = worksheet.HyperLinks.Add(worksheet[10, 5]); link.Type = ExcelHyperLinkType.Url; link.Address = "mailto:[email protected]"; #endregion worksheet[12, 3].Text = "Please fill out all required fields."; worksheet[14, 5].Text = "First Name*"; worksheet[14, 5].CellStyle.Font.Bold = true; worksheet[14, 8].Text = "Last Name*"; worksheet[14, 8].CellStyle.Font.Bold = true; #region Add TextBoxes //Create textbox for respective field //textbox to get First Name ITextBoxShape textBoxShape = worksheet.TextBoxes.AddTextBox(15, 5, 23, 190); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; //textbox to get Last Name textBoxShape = worksheet.TextBoxes.AddTextBox(15, 8, 23, 195); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; worksheet[17, 3].Text = "Company*"; textBoxShape = worksheet.TextBoxes.AddTextBox(17, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; worksheet[19, 3].Text = "Phone*"; textBoxShape = worksheet.TextBoxes.AddTextBox(19, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; worksheet[21, 3].Text = "Email*"; textBoxShape = worksheet.TextBoxes.AddTextBox(21, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; worksheet[23, 3].Text = "Website"; textBoxShape = worksheet.TextBoxes.AddTextBox(23, 5, 23, 385); #endregion #region Add CheckBoxes ICheckBoxShape chkBoxProducts = worksheet.CheckBoxes.AddCheckBox(25, 5, 20, 75); worksheet[25, 3].Text = "Multiple products?"; worksheet[27, 3, 28, 3].Merge(); worksheet[27, 3].Text = "Product(s)*"; worksheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; // Create a checkbox for each product ICheckBoxShape chkBoxProduct; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 5, 20, 75); chkBoxProduct.Text = "Studio"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 6, 20, 75); chkBoxProduct.Text = "Calculate"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 7, 20, 75); chkBoxProduct.Text = "Chart"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 8, 20, 75); chkBoxProduct.Text = "Diagram"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 9, 20, 75); chkBoxProduct.Text = "Edit"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 10, 20, 75); chkBoxProduct.Text = "XlsIO"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 5, 20, 75); chkBoxProduct.Text = "Grid"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 6, 20, 75); chkBoxProduct.Text = "Grouping"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 7, 20, 75); chkBoxProduct.Text = "HTMLUI"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 8, 20, 75); chkBoxProduct.Text = "PDF"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 9, 20, 75); chkBoxProduct.Text = "Tools"; chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 10, 20, 75); chkBoxProduct.Text = "DocIO"; chkBoxProducts.CheckState = ExcelCheckState.Mixed; #endregion //generate the link to linked cell property and formula GenerateFormula(excelEngine); worksheet[30, 3].Text = "Selected Products Count"; //counts the selected product worksheet[30, 5].Formula = "Sum(AA2:AA13)"; //align the cell content worksheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; #region Add TextBox //create the textbox for additional information worksheet[35, 3].Text = "Additional Information"; textBoxShape = worksheet.TextBoxes.AddTextBox(32, 5, 150, 385); #endregion if (!rdbExcel97.Checked) { worksheet[43, 3].Text = "Online Payment"; #region Add Combobox // Create combobox IComboBoxShape comboBox1 = worksheet.ComboBoxes.AddComboBox(43, 5, 20, 100); // Assign range to display in dropdown list comboBox1.ListFillRange = worksheet2["A1:A2"]; // select 1st item from the list comboBox1.SelectedIndex = 1; #endregion #region Add OptionButton worksheet[46, 3].Text = "Card Type"; IOptionButtonShape optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 5); optionButton1.Text = "American Express"; optionButton1.CheckState = ExcelCheckState.Checked; optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 7); optionButton1.Text = "Master Card"; optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 9); optionButton1.Text = "Visa"; #endregion } #region Column Alignment //column alignment worksheet.Columns[0].AutofitColumns(); worksheet.Columns[3].ColumnWidth = 12; worksheet.Columns[4].ColumnWidth = 10; worksheet.Columns[5].ColumnWidth = 10; #endregion worksheet.IsGridLinesVisible = false; #region Workbook Save // Assign the filename depends upon the version if ((this.rdbExcel2007.Checked) || (this.rdbExcel2010.Checked) || (this.rdbExcel2013.Checked)) { fileName = "FormControl.xlsx"; } else { fileName = "FormControl.xls"; } // Save the file workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose //closes the workbook workbook.Close(); excelEngine.Dispose(); #endregion #region View the Workbook // Message box confirmation to view the created document. 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(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } catch (Win32Exception ex) { MessageBox.Show("Excel 2007 is not installed in this system"); Console.WriteLine(ex.ToString()); } } else { this.Close(); } #endregion }