/// <summary> /// Formata as células do excel /// </summary> /// <param name="workbook">Representa um MS Excel workbook</param> /// <param name="worksheet">Representa uma Planilha no workbook</param> private static void FormatCells(IWorkbook workbook, IWorksheet worksheet) { IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Color.FromArgb(255, 174, 33); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); //Add custom colors to the palette workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); //Defining body style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); //Apply Header style worksheet.Rows[0].CellStyle = headerStyle; //Apply Body Style worksheet.Range["A2:D101"].CellStyle = bodyStyle; //Auto-fit the columns worksheet.UsedRange.AutofitColumns(); }
private IStyle AddHeaderStyle(IWorkbook workbook) { // Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Color.FromArgb(234, 112, 33); headerStyle.Font.Color = ExcelKnownColors.White; headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); return(headerStyle); }
private void ConvertToXlsx() { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Preserve data types as per the value application.PreserveCSVDataTypes = true; //Read my CSV file Stream csvStream = File.OpenRead(Path.GetFullPath(saveFile.FileName)); //Reads CSV stream as a workbook IWorkbook workbook = application.Workbooks.Open(csvStream); IWorksheet sheet = workbook.Worksheets[0]; // Formatting CSV data as a table IListObject table = sheet.ListObjects.Create("PortsTable", sheet.UsedRange); table.BuiltInTableStyle = TableBuiltInStyles.TableStyleDark10; IRange location = table.Location; location.AutofitColumns(); // Define/apply header style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Syncfusion.Drawing.Color.LightGreen; headerStyle.Font.FontName = "Consolas"; headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.Dark_blue; headerStyle.Font.Bold = true; headerStyle.Font.Size = 16; headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; headerStyle.EndUpdate(); // Define and apply a body style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Font.FontName = "Cambria"; bodyStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.BlackCustom; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium; bodyStyle.EndUpdate(); // Define a few additonal tweaks IStyle increaseStyle = workbook.Styles.Add("IncreaseStyle"); increaseStyle.BeginUpdate(); increaseStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue; increaseStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; increaseStyle.Font.Size = 14; increaseStyle.Font.Bold = true; // Modify this range seperately so it doesn't get messed up sheet.Range["A1:A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A2:A2"].DateTime.ToShortDateString(); sheet.Range["A2:A2"].CellStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue; sheet.Range["A2:A2"].CellStyle.Font.Bold = true; sheet.Range["A2:A2"].CellStyle.Font.Size = 14; sheet.Range["A2:A4"].HorizontalAlignment = ExcelHAlign.HAlignLeft; sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium; sheet.Range["A2:A2"].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; sheet.Range["A3:A4"].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; sheet.Range["A3:A3"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["B1:B100"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["B2:B3"].CellStyle.Color = Syncfusion.Drawing.Color.Black; // Custom widths sheet.Columns[0].ColumnWidth = 20.00; // Then Apply header style sheet.Range["A1:A1"].CellStyle = headerStyle; sheet.Range["A3:A3"].CellStyle = headerStyle; sheet.Range["B1:B1"].CellStyle = headerStyle; // And the body style sheet.Range["A4:A4"].CellStyle = bodyStyle; sheet.Range["B4:B100"].CellStyle = bodyStyle; // Additions sheet.Range["A2:A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A4:A4"].CellStyle = increaseStyle; sheet.Range["A4:A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; // Apply a conditional format for cells with the text 'Open' IConditionalFormats condition = sheet.Range["B4:B100"].ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.SpecificText; condition1.Text = "Open"; condition1.Operator = ExcelComparisonOperator.ContainsText; condition1.BackColor = ExcelKnownColors.Red; // Save file in the same directory as the initial raw CSV from StreamWriter Stream excelStream; string makeoverPath = Environment.ExpandEnvironmentVariables(@"C:\Users\%USERNAME%\Desktop\makeover.xlsx"); excelStream = File.Create(Path.GetFullPath(makeoverPath)); workbook.SaveAs(excelStream); // Release all resources => IMPORTANT! excelStream.Dispose(); MessageBox.Show("SUCCESS! Your original file " + saveFile.FileName + " has been converted to .XLSX. You can view it at: " + makeoverPath); // Flip this on so that below I can safely delete the ugly .CSV original file prettified = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void generarreporte(int selected) { dtConsulta = getventas(selected); if (dtConsulta.Rows.Count > 0) { int sucursales = radios(); if (sucursales == 1) { string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MM yyyy"); string closure = date.ToString("dd MM yyyy HH mm"); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = dtConsulta; int osos = tabla.Rows.Count; worksheet.Name = m_nombreexterno; worksheet.ImportDataTable(tabla, true, 2, 1, true); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"]; worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); #region IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); //headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Color = System.Drawing.Color.Gray; headerStyle.Font.Bold = true; headerStyle.Font.Color = ExcelKnownColors.White; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); //workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); //pStyle.Color = colorss(m_select); pStyle.Color = System.Drawing.Color.DodgerBlue; pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); #endregion #region //ancho y alineacion columnas worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 10); worksheet.SetColumnWidth(3, 10); worksheet.SetColumnWidth(4, 10); worksheet.SetColumnWidth(5, 12);//Estado worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 10); worksheet.SetColumnWidth(9, 45); worksheet.SetColumnWidth(10, 10);//Linea worksheet.SetColumnWidth(11, 12); worksheet.SetColumnWidth(12, 7); worksheet.SetColumnWidth(13, 10); worksheet.SetColumnWidth(14, 5); worksheet.SetColumnWidth(15, 15); worksheet.SetColumnWidth(16, 5); worksheet.SetColumnWidth(17, 4); worksheet.SetColumnWidth(18, 4); worksheet.SetColumnWidth(19, 4); worksheet.SetColumnWidth(20, 4); worksheet.SetColumnWidth(21, 40); worksheet.SetColumnWidth(22, 20); worksheet.SetColumnWidth(23, 5); worksheet.SetColumnWidth(24, 20); worksheet.SetColumnWidth(26, 12); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft; pStyles.EndUpdate(); #endregion // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "O2,O" + cossacks + ""; string russevel = "O" + rusia + ""; string ftotal = "O" + rusia + ""; string qty = "N" + rusia + ""; string fpl = "AA" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; worksheet.Range["AA2:" + fpl].NumberFormat = "#,##0.00"; worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00"; string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")"; worksheet.Range[qty].Formula = sumqty; worksheet.Range[qty].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); //workbook.Close(); //excelEngine.Dispose(); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } } else //base VPN { string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MM yyyy"); string closure = date.ToString("dd MM yyyy HH mm"); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = dtConsulta; int osos = tabla.Rows.Count; worksheet.Name = m_nombreexterno; worksheet.ImportDataTable(tabla, true, 2, 1, true); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"]; worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); #region IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = System.Drawing.Color.Gray; headerStyle.Font.Bold = true; headerStyle.Font.Color = ExcelKnownColors.White; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); pStyle.Font.Color = ExcelKnownColors.White; //workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); if (m_select == 2) { pStyle.Color = System.Drawing.Color.Orange; } else if (m_select == 3 || m_select == 4) { pStyle.Color = System.Drawing.Color.Red; } else { pStyle.Color = System.Drawing.Color.Gray; } pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); #endregion #region //ancho y alineacion columnas worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 10); worksheet.SetColumnWidth(3, 10); worksheet.SetColumnWidth(4, 10); worksheet.SetColumnWidth(5, 12);//Estado worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 10); worksheet.SetColumnWidth(9, 45); worksheet.SetColumnWidth(10, 10);//Linea worksheet.SetColumnWidth(11, 12); worksheet.SetColumnWidth(12, 7); worksheet.SetColumnWidth(13, 10); worksheet.SetColumnWidth(14, 5); worksheet.SetColumnWidth(15, 15); worksheet.SetColumnWidth(16, 5); worksheet.SetColumnWidth(17, 4); worksheet.SetColumnWidth(18, 40); worksheet.SetColumnWidth(19, 20); worksheet.SetColumnWidth(20, 4); worksheet.SetColumnWidth(21, 17); worksheet.SetColumnWidth(22, 7); worksheet.SetColumnWidth(23, 15); worksheet.SetColumnWidth(24, 15); worksheet.SetColumnWidth(25, 15); worksheet.SetColumnWidth(26, 7); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[7].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[21].HorizontalAlignment = ExcelHAlign.HAlignRight; pStyles.EndUpdate(); #endregion // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "O2,O" + cossacks + ""; string russevel = "O" + rusia + ""; string ftotal = "O" + rusia + ""; string ffecha = "B" + rusia + ""; string qty = "N" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00"; string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")"; worksheet.Range[qty].Formula = sumqty; worksheet.Range[qty].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); //workbook.Close(); //excelEngine.Dispose(); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } } } else { RadWindow radWindow = new RadWindow(); RadWindow.Alert(new DialogParameters() { Content = "No hay registros para mostrar.", Header = "BIG", DialogStartupLocation = WindowStartupLocation.CenterOwner // IconContent = ""; }); } }
// // 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()); }
private ActionResult ExportExcelDailyCOD(DateTime dateFrom, DateTime dateTo, List <DailyCOD> Items) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; // Creating new workbook IWorkbook workbook = application.Workbooks.Create(new[] { "Daily COD" }); IWorksheet worksheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel2013; worksheet.Range["A1:A2"].Merge(); worksheet.Range["B1:B2"].Merge(); worksheet.Range["C1:F1"].Merge(); worksheet.Range["G1:J1"].Merge(); // Header First worksheet.Range["A1"].Text = "Branch"; worksheet.Range["B1"].Text = "ERP_ID"; worksheet.Range["C1"].Text = $"{dateTo.ToString("dd/MM/yyyy", enUS)} to {dateTo.ToString("dd/MM/yyyy", enUS)}"; worksheet.Range["G1"].Text = $"As of {dateTo.ToString("dd/MM/yyyy", enUS)}"; worksheet.Range["C2"].Text = "Consignment"; worksheet.Range["D2"].Text = "Amount"; worksheet.Range["E2"].Text = "Surcharge"; worksheet.Range["F2"].Text = "Amount/Con"; worksheet.Range["G2"].Text = "Consignment"; worksheet.Range["H2"].Text = "amount"; worksheet.Range["I2"].Text = "Surcharge"; worksheet.Range["J2"].Text = "Amount/Con"; // Header Style IStyle headerStyleBranch = workbook.Styles.Add("HeaderStyleBranch"); headerStyleBranch.BeginUpdate(); headerStyleBranch.Color = Color.FromArgb(224, 224, 224); headerStyleBranch.Font.Color = ExcelKnownColors.Black; headerStyleBranch.Font.Bold = true; headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleBranch.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleBranch.EndUpdate(); worksheet.Range["A1:B2"].CellStyle = headerStyleBranch; IStyle headerStyleMtd = workbook.Styles.Add("HeaderStyleMtd"); headerStyleMtd.BeginUpdate(); headerStyleMtd.Color = Color.FromArgb(187, 222, 251); headerStyleMtd.Font.Color = ExcelKnownColors.Black; headerStyleMtd.Font.Bold = true; headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleMtd.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyleMtd.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleMtd.EndUpdate(); worksheet.Range["C1:F1"].CellStyle = headerStyleMtd; IStyle headerStyleDaily = workbook.Styles.Add("HeaderStyleDaily"); headerStyleDaily.BeginUpdate(); headerStyleDaily.Color = Color.FromArgb(225, 248, 225); headerStyleDaily.Font.Color = ExcelKnownColors.Black; headerStyleDaily.Font.Bold = true; headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleDaily.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyleDaily.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleDaily.EndUpdate(); worksheet.Range["G1:J1"].CellStyle = headerStyleDaily; IStyle headerStyle = AddHeaderStyle(workbook); worksheet.Range["C2:J2"].CellStyle = headerStyle; // Header Style worksheet.ImportData(Items.Select(d => new { d.BranchID, d.ERPID, d.MonthlyCODConsignment, d.MonthlyCODAmount, d.MonthlyCODSurcharge, d.MonthlyAmountPerConsignment, d.DailyCODConsignment, d.DailyCODAmount, d.DailyCODSurcharge, d.DailyAmountPerConsignment, }), 3, 1, false); worksheet.Columns[1].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[5].CellStyle.NumberFormat = "#,##0"; for (int i = 2; i < 5; i++) { worksheet.Columns[i].CellStyle.NumberFormat = "#,##0.00"; } for (int i = 6; i < 9; i++) { worksheet.Columns[i].CellStyle.NumberFormat = "#,##0.00"; } worksheet.Range["C3"].FreezePanes(); MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, "Application/msexcel", "KE_PDC_DailyCOD_Report_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xlsx")); }
private async void ExportCommandExecute(SessionModel session) { using (ExcelEngine excelEngine = new ExcelEngine()) { Loading = true; await Task.Delay(200); List <SessionInit> lSessionInit = App.Database.GetSessionInit(session.ID); if (lSessionInit == null || lSessionInit.Count == 0) { Loading = false; await Application.Current.MainPage.DisplayAlert("No hay datos", "La sesión no se puede exportar, aún no contiene datos", "Aceptar"); } else { //Set the default application version as Excel 2013. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; //Create a workbook with a worksheet IWorkbook workbook = excelEngine.Excel.Workbooks.Create(lSessionInit.Count); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(29, 161, 242); headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White; headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Color = Syncfusion.Drawing.Color.White; bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); //Access first worksheet from the workbook instance. int cont = 0; foreach (SessionInit sessionInit in lSessionInit) { IWorksheet worksheet = workbook.Worksheets[cont]; //Adding text to a cell worksheet[1, 1].Value = "Sesión"; worksheet[1, 2].Value = "Actividad"; worksheet[1, 3].Value = "Profesional"; worksheet[1, 4].Value = "Alumno"; UserModel user = App.Database.GetUser(App.Database.GetSession(sessionInit.SessionId).UserID); worksheet[2, 1].Value = session.Name; worksheet[2, 2].Value = session.ActivityName; worksheet[2, 3].Value = user.UserName; worksheet[2, 4].Value = sessionInit.StudentCode; worksheet.Name = cont.ToString() + " - " + sessionInit.StudentCode; worksheet.UsedRange.AutofitColumns(); worksheet.Rows[0].CellStyle = headerStyle; List <SessionData> sessionsData = App.Database.GetSessionData(sessionInit.ID); int i = 4; foreach (SessionData sd in sessionsData) { worksheet[i, 1].Value = sd.Data; worksheet.Range["$A$" + i + ":$F$" + i].Merge(); i++; } worksheet.Range["A4:F" + i].CellStyle = bodyStyle; cont++; } //Save the workbook to stream in xlsx format. MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); //Save the stream as a file in the device and invoke it for viewing string filepath = Xamarin.Forms.DependencyService.Get <ISave>().SaveAndView(session.Name.Replace(" ", "") + ".xlsx", "application/msexcel", stream); Mail mail = new Mail(filepath, user); Loading = false; } } }
private FileStreamResult ExportExcelShopDailyRevenue(DateTime dateFrom, DateTime dateTo, List <ShopDailyRevenue> Items) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; // Creating new workbook IWorkbook workbook = application.Workbooks.Create(new[] { "ShopDailyRevenue" }); IWorksheet worksheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel2013; worksheet.Range["A1:A2"].Merge(); worksheet.Range["B1:B2"].Merge(); worksheet.Range["C1:H1"].Merge(); worksheet.Range["I1:N1"].Merge(); // Header First worksheet.Range["A1"].Text = "Branch"; worksheet.Range["B1"].Text = "ERP_ID"; worksheet.Range["C1"].Text = $"{dateTo.ToString("dd/MM/yyyy", enUS)} to {dateTo.ToString("dd/MM/yyyy", enUS)}"; worksheet.Range["I1"].Text = $"As of {dateTo.ToString("dd/MM/yyyy", enUS)}"; //worksheet.Range["A2"].Text = "BranchID"; worksheet.Range["C2"].Text = "Sender"; worksheet.Range["D2"].Text = "Con"; worksheet.Range["E2"].Text = "Box"; worksheet.Range["F2"].Text = "Revenue"; worksheet.Range["G2"].Text = "YPC"; worksheet.Range["H2"].Text = "YPB"; worksheet.Range["I2"].Text = "Sender"; worksheet.Range["J2"].Text = "Con"; worksheet.Range["K2"].Text = "Box"; worksheet.Range["L2"].Text = "Revenue"; worksheet.Range["M2"].Text = "YPC"; worksheet.Range["N2"].Text = "YPB"; IStyle headerStyleBranch = workbook.Styles.Add("HeaderStyleBranch"); headerStyleBranch.BeginUpdate(); headerStyleBranch.Color = Color.FromArgb(224, 224, 224); headerStyleBranch.Font.Color = ExcelKnownColors.Black; headerStyleBranch.Font.Bold = true; headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleBranch.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleBranch.EndUpdate(); worksheet.Range["A1:B2"].CellStyle = headerStyleBranch; IStyle headerStyleMtd = workbook.Styles.Add("HeaderStyleMtd"); headerStyleMtd.BeginUpdate(); headerStyleMtd.Color = Color.FromArgb(187, 222, 251); headerStyleMtd.Font.Color = ExcelKnownColors.Black; headerStyleMtd.Font.Bold = true; headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleMtd.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyleMtd.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleMtd.EndUpdate(); worksheet.Range["C1:H2"].CellStyle = headerStyleMtd; IStyle headerStyleDaily = workbook.Styles.Add("HeaderStyleDaily"); headerStyleDaily.BeginUpdate(); headerStyleDaily.Color = Color.FromArgb(225, 248, 225); headerStyleDaily.Font.Color = ExcelKnownColors.Black; headerStyleDaily.Font.Bold = true; headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191); headerStyleDaily.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyleDaily.VerticalAlignment = ExcelVAlign.VAlignCenter; headerStyleDaily.EndUpdate(); worksheet.Range["I1:N2"].CellStyle = headerStyleDaily; worksheet.ImportData(Items, 3, 1, false); worksheet.Columns[1].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[2].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[3].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[4].CellStyle.NumberFormat = "#,##0.00"; worksheet.Columns[5].CellStyle.NumberFormat = "#,##0.00"; worksheet.Columns[6].CellStyle.NumberFormat = "#,##0.00"; worksheet.Columns[7].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[8].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[9].CellStyle.NumberFormat = "#,##0"; worksheet.Columns[10].CellStyle.NumberFormat = "#,##0.00"; worksheet.Columns[11].CellStyle.NumberFormat = "#,##0.00"; worksheet.Columns[12].CellStyle.NumberFormat = "#,##0.00"; worksheet.Range["C3"].FreezePanes(); MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, "Application/msexcel", "KE_PDC_ShopDailyRevenue_Report_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xlsx")); }
static void Main(string[] args) { intEMPRESAID = Convert.ToByte(GetSetting(appName, section, "EmpresaID", String.Empty)); intSUCURSALID = Convert.ToByte(GetSetting(appName, section, "SucursalID", String.Empty)); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MMMM yyyy HH mm "); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = GetExistenciaDataTable(); int osos = tabla.Rows.Count; worksheet.ImportDataTable(tabla, true, 2, 1); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:H2"]; string namesuc = GetName(intSUCURSALID); worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al " + dateday + namesuc; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227); pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 13); worksheet.SetColumnWidth(2, 50); worksheet.SetColumnWidth(3, 17); worksheet.SetColumnWidth(4, 28); // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "H2,H" + cossacks + ""; string russevel = "H" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUM(H2:H" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = "LRG-Existencias al " + namer + namesuc + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); workbook.Close(); excelEngine.Dispose(); } }
/// <summary> /// Creates 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 }
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir) { ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version))); DateTime begin = DateTime.Now; ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = version; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Header IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); headerStyle.Color = Color.FromArgb(239, 243, 247); //headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Font.Bold = true; headerStyle.Font.Size = 12; headerStyle.Color = Color.FromArgb(192, 192, 192); headerStyle.Locked = true; headerStyle.Font.FontName = "Arial"; headerStyle.Font.Color = ExcelKnownColors.Black; headerStyle.Borders.LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; headerStyle.EndUpdate(); worksheet.SetDefaultRowStyle(1, headerStyle); IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.RowHeight = 36.75; header.VerticalAlignment = ExcelVAlign.VAlignCenter; //Body IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204)); bodyStyle.Color = Color.FromArgb(255, 255, 204); bodyStyle.Font.Size = 10; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Font.FontName = "Arial"; bodyStyle.Font.Bold = false; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; //bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); data.RowHeight = 31.50; //IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle; //DateTime beginFillPattern = DateTime.Now; //data.CellStyle.FillPattern = ExcelPattern.Solid; //DateTime endFillPattern = DateTime.Now; //messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern))); //worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm"; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); } worksheet.SetColumnWidth(5, 18.00); MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); workbook.Close(); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb)); }
/// <summary> /// Convert the Excel document to JSON /// </summary> /// <returns>Return the JSON document as stream</returns> public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable) { if (button == "Input Document") { //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 using (ExcelEngine excelEngine = new ExcelEngine()) { //Step 2 : Instantiate the excel application object IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Opening the encrypted Workbook FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default); //Save the document as a stream and retrun the stream using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream workbook.SaveAs(stream); return(stream); } } } 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 using (ExcelEngine excelEngine = new ExcelEngine()) { //Exports the DataTable to a spreadsheet. string fileName = string.Empty; #region Workbook Initialize //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Set the Workbook version as Excel 97to2003 if (option == "XLS") { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "ExportToExcel.xls"; } //Set the Workbook version as Excel 2007 else { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "ExportToExcel.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Export DataTable to Excel //Export DataTable. if (dataTable != null) { worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1); } #endregion #region Formatting the Report //Formatting the Report #region Applying Body Stlye //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247)); bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.UsedRange.CellStyleName = "BodyStyle"; bodyStyle.EndUpdate(); #endregion #region Applying Header Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218)); headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle"; headerStyle.EndUpdate(); #endregion //Remove grid lines in the worksheet. worksheet.IsGridLinesVisible = false; //Autofit Rows and Columns worksheet.UsedRange.AutofitRows(); worksheet.UsedRange.AutofitColumns(); //Adjust Row Height. worksheet.Rows[1].RowHeight = 25; //Freeze header row. worksheet.Range["A4"].FreezePanes(); worksheet.Range["C2"].Text = "Customer Details"; worksheet.Range["C2:D2"].Merge(); worksheet.Range["C2"].CellStyle.Font.Size = 14; worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; #endregion #region Workbook Save and Close //Saving the workbook to disk. //Save the document as a stream and retrun the stream using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream workbook.SaveAs(stream); return(stream); } #endregion } } }
public void WriteDataToWorksheet(IWorksheet worksheet, IStyle headerStyle) { try { //Assign some text in a cell worksheet.Range["A2"].Text = "NHI"; worksheet.Range["B2"].Text = "Referral Source"; worksheet.Range["C2"].Text = "Last Review Date"; worksheet.Range["D2"].Text = "Entry Date to Support Hours"; worksheet.Range["E2"].Text = "Weekly Allocated Hours"; worksheet.Range["F2"].Text = "Weekly Allocated Travel Time"; worksheet.Range["G2"].Text = "Exit Date"; worksheet.Range["H2"].Text = "Child & Youth Client"; worksheet.Range["I2"].Text = "Support hours narrative"; headerStyle.BeginUpdate(); headerStyle.Color = Color.Aqua; headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[0].CellStyle = headerStyle; var count = 3; foreach (var service in Vm.Services) { count++; foreach (var cl in service.Clients) { worksheet.Range[$"A{count}"].Text = string.IsNullOrEmpty(cl.NHINumber) ? cl.ClientName : cl.NHINumber; //Referral Source worksheet.Range[$"B{count}"].Text = cl.ReferralData.ReferrerOrganisation; //Last Review Date worksheet.Range[$"C{count}"].Text = cl.ReferralData.ReferralUpdated; //Entry Date to Support Hours worksheet.Range[$"D{count}"].Text = cl.ReferralData.ReferralDate; //Weekly Allocated Hours worksheet.Range[$"E{count}"].Text = cl.ServiceData.EngagementStatus; //Weekly Allocated Time //TODO Find out what this is?? //Exit Date worksheet.Range[$"G{count}"].Text = string.IsNullOrEmpty(cl.ServiceData.ServiceEndDate) ? "Still Active" : cl.ServiceData.ServiceEndDate; //Child & Youth if (cl.ReferralData.ReferrerOrganisation == Youth) { worksheet.Range[$"H{count}"].Text = "Yes"; } worksheet.Range[$"I{count}"].Text = service.ServiceName; count++; } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }
private void btnCreate_Click(object sender, System.EventArgs e) { if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount))) { MessageBox.Show("Enter Numerical Value"); return; } if (rowCount <= 0) { MessageBox.Show("Invalid row count"); return; } if (colCount <= 0) { MessageBox.Show("Invalid column count"); return; } if (rdbExcel97.Checked) { if (colCount > 256) { MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format."); return; } if (rowCount > 65536) { MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format."); return; } } if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked) { if (rowCount > 100001) { MessageBox.Show("Row count must be less than or equal to 100,000."); return; } if (colCount > 151) { MessageBox.Show("Column count must be less than or equal to 151."); return; } } #region Starttime //Start Time #endregion #region Initialize Workbook //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; //Set the Default version as Excel 97to2003 if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "PerformanceChecking.xls"; } //Set the Default version as Excel 2007 else if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "PerformanceChecking.xlsx"; } //Set the Default version as Excel 2010 else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "PerformanceChecking.xlsx"; } //Set the Default version as Excel 2013 else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "PerformanceChecking.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(3); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; startTime = DateTime.Now; workbook.DetectDateTimeInValue = false; #endregion if (chbColumnStyle.Checked) { //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle); } if (this.chkImportOnSave.Checked) { DataTable dataTable = new DataTable(); for (int column = 1; column <= colCount; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rowCount; row++) { dataTable.Rows.Add(); for (int column = 1; column <= colCount; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } startTime = DateTime.Now; worksheet.ImportDataTable(dataTable, 1, 1, true, true); } else { #region Apply Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33)); headerStyle.Color = Color.FromArgb(255, 174, 33); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); #endregion IMigrantRange migrantRange = worksheet.MigrantRange; for (int column = 1; column <= colCount; column++) { migrantRange.ResetRowColumn(1, column); migrantRange.Text = "Column: " + column.ToString(); migrantRange.CellStyle = headerStyle; } #region Insert Data //Writing Data using normal interface for (int row = 2; row <= rowCount; row++) { //double columnSum = 0.0; for (int column = 1; column <= colCount; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.Number = row * column; } } } #endregion #region Workook Save workbook.SaveAs(fileName); #endregion #region Workbook Save and Dispose //Close the workbook workbook.Close(); //Dispose the Excel Engine excelEngine.Dispose(); #endregion #region Set EndTime and get LogDetails //End Time endTime = DateTime.Now - startTime; LogDetails(endTime); #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 } #endregion }
private void generarreporte() { DataTable tabla = dtblcatalogo; //Refresh(CashierBusyIndicator); //generar(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MMMM yyyy HH mm ").ToUpper(); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); int osos = tabla.Rows.Count; worksheet.Name = "Servicios"; worksheet.ImportDataTable(tabla, true, 2, 1); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:K2"]; worksheet.Range["A1"].Text = "CATÁLOGO DE SERVICIOS AL " + dateday; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227); pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 30); worksheet.SetColumnWidth(3, 15); worksheet.SetColumnWidth(4, 20); worksheet.SetColumnWidth(5, 7); worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 15); worksheet.SetColumnWidth(11, 15); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; pStyles.EndUpdate(); // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "H2,H" + cossacks + ""; string russevel = "H" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\CATÁLOGO DE SERVICIOS AL " + dateday + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } }
private void btnExport_Click(object sender, System.EventArgs e) { //Exports the DataTable to a spreadsheet. #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; //Set the Workbook version as Excel 97to2003 if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "ExportToExcel.xls"; } //Set the Workbook version as Excel 2007 else if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "ExportToExcel.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "ExportToExcel.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "ExportToExcel.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Export DataTable to Excel //Export DataTable. if (this.dataGrid1.DataSource != null) { worksheet.ImportDataTable((DataTable)this.dataGrid1.DataSource, true, 3, 1, -1, -1); } else { MessageBox.Show("There is no datatable to export, Please import a datatable first", "Error"); //Close the workbook. workbook.Close(); return; } #endregion #region Formatting the Report //Formatting the Report #region Applying Body Stlye //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Color.FromArgb(239, 242, 247)); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.UsedRange.CellStyleName = "BodyStyle"; bodyStyle.EndUpdate(); #endregion #region Applying Header Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Color.FromArgb(182, 189, 218)); headerStyle.Color = Color.FromArgb(182, 189, 218); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle"; headerStyle.EndUpdate(); #endregion //Remove grid lines in the worksheet. worksheet.IsGridLinesVisible = false; //Autofit Rows and Columns worksheet.UsedRange.AutofitRows(); worksheet.UsedRange.AutofitColumns(); //Adjust Row Height. worksheet.Rows[1].RowHeight = 25; //Freeze header row. worksheet.Range["A4"].FreezePanes(); worksheet.Range["C2"].Text = "Customer Details"; worksheet.Range["C2:D2"].Merge(); worksheet.Range["C2"].CellStyle.Font.Size = 14; worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; #endregion #region Workbook Save and Close //Saving the workbook to disk. workbook.SaveAs(fileName); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] System.Diagnostics.Process.Start(fileName); //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
private async void ExportCommandExecute(SessionModel session) { using (ExcelEngine excelEngine = new ExcelEngine()) { Loading = true; //await Task.Delay(200); await Task.Run(() => { List <SessionInit> lSessionInit = App.Database.GetSessionInit(session.ID); if (lSessionInit == null || lSessionInit.Count == 0) { Loading = false; Device.BeginInvokeOnMainThread(async() => { Application.Current.MainPage.DisplayAlert("No hay datos", "La sesión no se puede exportar, aún no contiene datos", "Aceptar"); }); } else { //Set the default application version as Excel 2013. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; //Create a workbook with a worksheet IWorkbook workbook = excelEngine.Excel.Workbooks.Create(lSessionInit.Count); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(29, 161, 242); headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White; headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Color = Syncfusion.Drawing.Color.White; bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); //Access first worksheet from the workbook instance. ActivityModel activity = Json.GetActivityByName(session.ActivityName); int cont = 0; foreach (SessionInit sessionInit in lSessionInit) { IWorksheet worksheet = workbook.Worksheets[cont]; //Adding text to a cell worksheet[1, 1].Value = "Sesión"; worksheet[2, 1].Value = "Actividad"; worksheet[3, 1].Value = "Fecha"; worksheet[4, 1].Value = "Duración"; worksheet[5, 1].Value = "Profesional"; worksheet[6, 1].Value = "Alumno"; UserModel user = App.Database.GetUser(App.Database.GetSession(sessionInit.SessionId).UserID); worksheet[1, 2].Value = session.Name; worksheet[2, 2].Value = session.ActivityName; worksheet[3, 2].Text = sessionInit.Date.ToString("dd/MM/yyyy HH:mm:ss"); worksheet[4, 2].Text = sessionInit.Time; worksheet[5, 2].Value = user.UserName; worksheet[6, 2].Value = sessionInit.StudentCode; worksheet.Name = cont.ToString() + " - " + sessionInit.StudentCode; worksheet[1, 1].CellStyle = headerStyle; worksheet[2, 1].CellStyle = headerStyle; worksheet[3, 1].CellStyle = headerStyle; worksheet[4, 1].CellStyle = headerStyle; worksheet[5, 1].CellStyle = headerStyle; worksheet[6, 1].CellStyle = headerStyle; int column = 1; foreach (MessageDevice message in activity.Messages) { foreach (FieldMessage field in message.Fields) { worksheet[9, column].Value = field.Description; worksheet[9, column].CellStyle = headerStyle; column++; } } Dictionary <MessageDevice, int> messagesColumns = new Dictionary <MessageDevice, int>(); int calculateColumn = 1; foreach (MessageDevice message in activity.Messages) { messagesColumns.Add(message, calculateColumn); calculateColumn += message.Fields.Count(); } List <SessionData> sessionsData = App.Database.GetSessionData(sessionInit.ID); int row = 10; column = 1; int numByte = 0; for (int j = 0; j < sessionsData.Count; j++) { MessageDevice messageType1 = activity.Messages.Find(m => m.Fields.Sum(f => f.Bytes) == sessionsData[j].Data.Length); if (messageType1 == null) { int size = Convert.ToInt32(string.Concat(sessionsData[j].Data[3].ToString("X2")), 16) + 4; SessionData sessionDataComplementary = sessionsData.Find(s => s.Data.Length + sessionsData[j].Data.Length == size && s.DeviceName.Equals(sessionsData[j].DeviceName)); byte[] messageTemp = null; if (sessionDataComplementary != null) { messageTemp = new byte[sessionsData[j].Data.Length + sessionDataComplementary.Data.Length]; Array.Copy(sessionsData[j].Data, 0, messageTemp, 0, sessionsData[j].Data.Length); Array.Copy(sessionDataComplementary.Data, 0, messageTemp, sessionsData[j].Data.Length, sessionDataComplementary.Data.Length); messageType1 = activity.Messages.Find(m => m.Fields.Sum(f => f.Bytes) == messageTemp.Length); } if (messageType1 != null) { column = messagesColumns[messageType1]; numByte = 0; foreach (FieldMessage field in messageType1.Fields) { string m = Convert.ToInt32(string.Concat(messageTemp.Skip(numByte).Take(field.Bytes).ToArray().Select(b => b.ToString("X2"))), 16).ToString(); if (field.Format != null && field.Format.Equals("ms")) { double millis = Convert.ToDouble(m); worksheet[row, column].NumberFormat = "###,###,##0.0#########"; worksheet[row, column].Number = millis / 1000.0;; } else { worksheet[row, column].Text = m; } worksheet[row, column].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight; column++; numByte += field.Bytes; } row++; } } else { column = messagesColumns[messageType1]; numByte = 0; foreach (FieldMessage field in messageType1.Fields) { string m = Convert.ToInt32(string.Concat(sessionsData[j].Data.Skip(numByte).Take(field.Bytes).ToArray().Select(b => b.ToString("X2"))), 16).ToString(); worksheet[row, column].Value = m; column++; numByte += field.Bytes; } row++; } } worksheet.UsedRange.AutofitColumns(); cont++; } MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); string filepath = DependencyService.Get <ISave>().Save("SmartGames_" + session.Name.Replace(" ", "") + ".xlsx", "application/msexcel", stream); Mail mail = new Mail(filepath, User); } }); Loading = false; } }