private SLStyle cabecalhoStyleAux() { SLStyle sr = new SLStyle(); sr.SetFontBold(true); sr.SetFont("Microsoft Sans Serif", 10); sr.SetHorizontalAlignment(HorizontalAlignmentValues.Center); return(sr); }
public void StoreCollectionA(List <ExcelA> collection, String month) { int i = 2, j = 1; slDocExcelA.SelectWorksheet(month); for (int x = 0; x < 50; x++) { if (x < collection.Count) { SLStyle style = slDocExcelA.CreateStyle(); style.SetFontBold(true); slDocExcelA.SetCellStyle("B1", style); slDocExcelA.SetCellValue("B1", "This is a Caption"); slDocExcelA.SetCellValue("A" + i.ToString(), j.ToString()); slDocExcelA.SetCellValue("B" + i.ToString(), collection.ElementAt(x).Val1); slDocExcelA.SetCellValue("C" + i.ToString(), collection.ElementAt(x).Val2); slDocExcelA.SetCellValue("D" + i.ToString(), collection.ElementAt(x).Val3); slDocExcelA.SetCellValue("E" + i.ToString(), collection.ElementAt(x).Val4); } else { slDocExcelA.SetCellValue("A" + i.ToString(), j.ToString()); slDocExcelA.SetCellValue("B" + i.ToString(), ""); slDocExcelA.SetCellValue("C" + i.ToString(), ""); slDocExcelA.SetCellValue("D" + i.ToString(), ""); } i++; j++; } slDocExcelA.Save(); slDocExcelA = new SLDocument("ExcelA.xlsx"); }
private void GeraArquivo(System.Data.DataTable dt, string dataInicial, string dataFinal) { SLDocument sl = new SLDocument(); int linha = 1; //Set das configurações do arquivos e variaveis de style SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); SLStyle bordaAcima = sl.CreateStyle(); bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultBold = sl.CreateStyle(); resultBold.SetFontBold(true); resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultRed = sl.CreateStyle(); resultRed.SetFontBold(true); resultRed.SetFontColor(System.Drawing.Color.Red); resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle bordaContorno = sl.CreateStyle(); bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle alinhadoCentro = sl.CreateStyle(); alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center; alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center; alinhadoCentro.SetWrapText(true); sl.SetCellValue(linha, 1, "Código Empresa"); sl.SetCellValue(linha, 2, "Número do registro do empregado"); sl.SetCellValue(linha, 3, "Nome"); sl.SetCellValue(linha, 4, "E-mail"); sl.SetCellValue(linha, 5, "Data da compra"); sl.SetCellValue(linha, 6, "Valor da compra"); sl.SetCellValue(linha, 7, "Quantidade de ingressos"); sl.SetCellValue(linha, 8, "Parcelas"); sl.SetCellValue(linha, 9, "Forma de pagamento"); sl.SetCellValue(linha, 10, "Data de vencimento"); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); sl.SetCellStyle(linha, 5, bordaContorno); sl.SetCellStyle(linha, 6, bordaContorno); sl.SetCellStyle(linha, 7, bordaContorno); sl.SetCellStyle(linha, 8, bordaContorno); sl.SetCellStyle(linha, 9, bordaContorno); sl.SetCellStyle(linha, 10, bordaContorno); sl.SetCellStyle(linha, 1, resultBold); sl.SetCellStyle(linha, 2, resultBold); sl.SetCellStyle(linha, 3, resultBold); sl.SetCellStyle(linha, 4, resultBold); sl.SetCellStyle(linha, 5, resultBold); sl.SetCellStyle(linha, 6, resultBold); sl.SetCellStyle(linha, 7, resultBold); sl.SetCellStyle(linha, 8, resultBold); sl.SetCellStyle(linha, 9, resultBold); sl.SetCellStyle(linha, 10, resultBold); linha++; foreach (DataRow dr in dt.Rows) { sl.SetCellValue(linha, 1, dr[0].ToString()); sl.SetCellValue(linha, 2, dr[1].ToString()); sl.SetCellValue(linha, 3, dr[2].ToString()); sl.SetCellValue(linha, 4, dr[3].ToString()); sl.SetCellValue(linha, 5, dr[4].ToString()); sl.SetCellValue(linha, 6, dr[5].ToString()); sl.SetCellValue(linha, 7, dr[6].ToString()); sl.SetCellValue(linha, 8, dr[7].ToString()); sl.SetCellValue(linha, 9, dr[8].ToString()); sl.SetCellValue(linha, 10, dr[9].ToString()); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); sl.SetCellStyle(linha, 5, bordaContorno); sl.SetCellStyle(linha, 6, bordaContorno); sl.SetCellStyle(linha, 7, bordaContorno); sl.SetCellStyle(linha, 8, bordaContorno); sl.SetCellStyle(linha, 9, bordaContorno); sl.SetCellStyle(linha, 10, bordaContorno); linha++; } sl.AutoFitColumn(1); sl.AutoFitColumn(2); sl.AutoFitColumn(3); sl.AutoFitColumn(4); sl.AutoFitColumn(5); sl.AutoFitColumn(6); sl.AutoFitColumn(7); sl.AutoFitColumn(8); sl.AutoFitColumn(9); sl.AutoFitColumn(10); string arquivo; if (!String.IsNullOrEmpty(dataInicial) && !String.IsNullOrEmpty(dataFinal)) { arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados " + dataInicial + " á " + dataFinal + ".xlsx"); } else { arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados_Geral.xlsx"); } sl.SaveAs(arquivo); }
/// ----------------------------------------------------------------------------------------------- /// <summary> /// This setting creator has user-defined styles and columns for the four data-tables in /// mock data; Directors, Managers, Team Leads & Associates. /// Displays a variety of ways to access the Export library with Constructor and /// Property Dependency Injection. /// </summary> /// <returns>Settings: Custom Styling</returns> /// ----------------------------------------------------------------------------------------------- public static Settings SetupCustomStyles() { try { var childList = new List <ChildSetting>(); /* ------------------------------------------------------------- * Setup the column header base style for the child datasets * -----------------------------------------------------------*/ var baseColumnHeaderStyle = new SLStyle(); baseColumnHeaderStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); baseColumnHeaderStyle.SetVerticalAlignment(VerticalAlignmentValues.Center); baseColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DimGray, Color.White); baseColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetTopBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetFont("Britannic Bold", 12); baseColumnHeaderStyle.SetFontColor(Color.White); baseColumnHeaderStyle.SetFontBold(true); /* ------------------------------------------------------------- * Setup the odd row style for the child datasets * -----------------------------------------------------------*/ var oddRowStyle = new SLStyle(); oddRowStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left); oddRowStyle.SetVerticalAlignment(VerticalAlignmentValues.Center); oddRowStyle.Fill.SetPattern(PatternValues.Solid, Color.White, Color.Black); oddRowStyle.SetFont("Helvetica", 10); oddRowStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Setup the even row style derived from the odd, * change only what is necessary. * -----------------------------------------------------------*/ var evenRowStyle = oddRowStyle.Clone(); evenRowStyle.Fill.SetPattern(PatternValues.Solid, Color.WhiteSmoke, Color.Black); /* ------------------------------------------------------------ * Create the user-defined columns with property dependency * injection for the base dataset. * With this method hover the cursor over the property and * intellisense will show the comments for it. * ----------------------------------------------------------*/ var columns = new List <Column> { // Since this id column is not set to visible, you can just leave it out and it will be ignored new Column { BoundColumnName = "DID", UserDefinedColumnName = "ID", NumberFormat = NumberFormats.General, HorizontalAlignment = HorizontalAlignmentValues.Center, ShowField = false, FieldOrder = 0 }, new Column { BoundColumnName = "SheetName", UserDefinedColumnName = "Director", NumberFormat = NumberFormats.General, HorizontalAlignment = HorizontalAlignmentValues.Left, ShowField = true, FieldOrder = 1 }, new Column { BoundColumnName = "Age", UserDefinedColumnName = "Chronology", NumberFormat = NumberFormats.Decimal0, HorizontalAlignment = HorizontalAlignmentValues.Center, ShowField = true, FieldOrder = 2 }, new Column { BoundColumnName = "Income", UserDefinedColumnName = "Compensation", NumberFormat = NumberFormats.Accounting2Red, HorizontalAlignment = HorizontalAlignmentValues.Right, ShowField = true, FieldOrder = 3 }, new Column { BoundColumnName = "Member", UserDefinedColumnName = "Member ?", NumberFormat = NumberFormats.General, HorizontalAlignment = HorizontalAlignmentValues.Center, ShowField = true, FieldOrder = 4 }, new Column { BoundColumnName = "Registered", UserDefinedColumnName = "Date Registered", NumberFormat = NumberFormats.DateShort5, HorizontalAlignment = HorizontalAlignmentValues.Center, ShowField = true, FieldOrder = 5 } }; /* ------------------------------------------------------------- * Define and style base child settings. * This Child will always be present, it represents the * primary dataset for every export and is not really a child. * Using Property Injection Technique * -----------------------------------------------------------*/ childList.Add(new ChildSetting { // Optional name SheetName = "Directors", // Set column visibility ShowColumnHeader = true, // Make the base column header row a little larger // so it will stand out. Value is in pixels ColumnHeaderRowHeight = 25, // Setup the style for Column Headers ColumnHeaderStyle = baseColumnHeaderStyle, // Row and Alternating Row Styles // If set to false then the odd row style will be overall row style ShowAlternatingRows = false, // Setup the style for all rows OddRowStyle = oddRowStyle, EvenRowStyle = null, // Add the user-defined columns UserDefinedColumns = columns }); /* ------------------------------------------------------------ * The first child column headers stylings will be derived * from the base, change only what needs to be changed. * ----------------------------------------------------------*/ var firstColumnHeaderStyle = baseColumnHeaderStyle.Clone(); firstColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DarkGray, Color.Black); firstColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetTopBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetFont("Helvetica", 10); firstColumnHeaderStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------ * Create the user-defined columns with constructor dependency * injection for the base dataset. * Hover the cursor over the property and intellisense will * show the comments for it. * ----------------------------------------------------------*/ columns = new List <Column> { new Column ( "SheetName", "Managers", NumberFormats.General, HorizontalAlignmentValues.Left, true, 1 ), new Column ( "Age", "Age", NumberFormats.Decimal0, HorizontalAlignmentValues.Center, true, 2 ), new Column ( "Income", "Compensation", NumberFormats.Currency0Black, HorizontalAlignmentValues.Right, true, 3 ), new Column ( "Registered", "Date Registered", NumberFormats.DateShort1, HorizontalAlignmentValues.Center, true, 5 ) }; /* ------------------------------------------------------------- * Define and add the first child * Using Constructor dependency injection * -----------------------------------------------------------*/ childList.Add(new ChildSetting ( "Managers", // SheetName true, // Show Column Headers 1, // Column Offset to the Right null, // Column Header Row Height firstColumnHeaderStyle, // Column Header Style false, // Show Alternating Rows, false will default to Odd oddRowStyle, // Odd Row Style null, // Even Row Style columns // User-Defined Column (UDCs) )); /* ------------------------------------------------------------- * The second child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var secondColumnHeaderStyle = firstColumnHeaderStyle.Clone(); secondColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.CadetBlue, Color.White); secondColumnHeaderStyle.SetFontColor(Color.White); /* ------------------------------------------------------------- * Define and add the second child * Using Constructor dependency injection * -----------------------------------------------------------*/ childList.Add(new ChildSetting( "Team Leads", // SheetName true, // Show Column Headers 2, // Column Offset to the Right null, // Column Header Row Height secondColumnHeaderStyle, // Column Header Style false, // Show Alternating Rows, false will default to Odd oddRowStyle, // Odd Row Style null, // Even Row Style new List <Column> // User-Defined Column (UDCs) { new Column("TLID", "Team Lead ID", NumberFormats.General, HorizontalAlignmentValues.Left, true, 6), new Column("Registered", "Registration Date", NumberFormats.UserDefined, HorizontalAlignmentValues.Center, true, 2, "d-mmm-yy"), new Column("SheetName", "Team Leads", NumberFormats.General, HorizontalAlignmentValues.Left, true, 0), new Column("Age", "How Old?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 1), new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 3), new Column("Income", "Income", NumberFormats.Accounting2Red, HorizontalAlignmentValues.Right, true, 4), new Column("MID", "Foreign Key", NumberFormats.General, HorizontalAlignmentValues.Right, false) } )); /* ------------------------------------------------------------- * The third child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var thirdColumnHeaderStyle = firstColumnHeaderStyle.Clone(); thirdColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Aqua, Color.Black); thirdColumnHeaderStyle.SetFont("Blackadder ITC", 11); thirdColumnHeaderStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Define and add the third child * Constructor Injection on all * -----------------------------------------------------------*/ childList.Add(new ChildSetting("Associates", true, 3, 30, thirdColumnHeaderStyle, true, oddRowStyle, evenRowStyle, new List <Column> { new Column("Registered", "Date", NumberFormats.TimeStamp124, HorizontalAlignmentValues.Left, true, 3), new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 2), new Column("SheetName", "Associate", NumberFormats.General, HorizontalAlignmentValues.Left, true, 0) } )); /* ------------------------------------------------------------- * Setup and return the primary container for the child datasets * Using Constructor Injection as well * -----------------------------------------------------------*/ return(new Settings("Organization", childList)); } catch (Exception ex) { Assert.Fail("Exception Fail: " + ex.Message); } return(null); }
private void BotonExportar_Click(object sender, RoutedEventArgs e) { if (RadioExcel.IsChecked == true) { var excelD = new SLDocument(); SLStyle StyleHeaders = excelD.CreateStyle(); StyleHeaders.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(184, 204, 228), System.Drawing.Color.FromArgb(184, 204, 228) ); StyleHeaders.SetFontBold(true); InsertDataTableIntoExcelDocument(ref excelD, TablaParaExportar, 1, 1, true); for (int i = 1; i < TablaParaExportar.Columns.Count + 1; i++) { excelD.SetCellStyle(1, i, StyleHeaders); if (i != 1) { excelD.AutoFitColumn(i); } } var TablaCMD = excelD.CreateTable(1, 1, TablaParaExportar.Rows.Count, TablaParaExportar.Columns.Count); TablaCMD.HasTotalRow = false; TablaCMD.HasAutoFilter = true; TablaCMD.DisplayName = "TablaDeComandos"; excelD.InsertTable(TablaCMD); excelD.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Comandos"); excelD.AddWorksheet("Datos"); //Busqueda de columnas var ListaDeColumnas = new List <string>(); var OnlyDataT = new DataTable(); Data2Show.ForEach(delegate(SortedList <String, String> _Registro) { var ColumnasNoAgregadas = (from V in _Registro.Keys.ToList <string>() where !ListaDeColumnas.Contains(V) select V).ToList(); ColumnasNoAgregadas.ForEach(delegate(string Cname) { ListaDeColumnas.Add(Cname); OnlyDataT.Columns.Add(Cname); }); }); //Procesando la información para generalizar los casos mas comunes Data2Show.ForEach(delegate(SortedList <String, String> _Registro) { var NuevaFila = OnlyDataT.NewRow(); _Registro.Keys.ToList <string>().ForEach( delegate(string Cname) { NuevaFila[Cname] = _Registro[Cname]; }); OnlyDataT.Rows.Add(NuevaFila); }); InsertDataTableIntoExcelDocument(ref excelD, OnlyDataT, 1, 1, true); for (int i = 1; i < OnlyDataT.Columns.Count + 1; i++) { excelD.SetCellStyle(1, i, StyleHeaders); excelD.AutoFitColumn(i); } var Tabla = excelD.CreateTable(1, 1, OnlyDataT.Rows.Count, OnlyDataT.Columns.Count); Tabla.HasTotalRow = false; Tabla.HasAutoFilter = true; Tabla.DisplayName = "TablaDeDatos"; excelD.InsertTable(Tabla); try { excelD.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\ReporteDeCargaArduino2DB.xlsx"); MessageBox.Show("Se ha exportado correctamente el reporte al escritorio con el nombre de ReporteDeCargaArduino2DB"); } catch (Exception MyE) { MessageBox.Show("Se ha detectado un error al crear el documento de excel, " + MyE.Message.ToString()); } } if (RadioCSV.IsChecked == true) { ToCSV(TablaParaExportar, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\CsvReporteDeCarga.csv"); //Busqueda de columnas var ListaDeColumnas = new List <string>(); var OnlyDataT = new DataTable(); Data2Show.ForEach(delegate(SortedList <String, String> _Registro) { var ColumnasNoAgregadas = (from V in _Registro.Keys.ToList <string>() where !ListaDeColumnas.Contains(V) select V).ToList(); ColumnasNoAgregadas.ForEach(delegate(string Cname) { ListaDeColumnas.Add(Cname); OnlyDataT.Columns.Add(Cname); }); }); //Procesando la información para generalizar los casos mas comunes Data2Show.ForEach(delegate(SortedList <String, String> _Registro) { var NuevaFila = OnlyDataT.NewRow(); _Registro.Keys.ToList <string>().ForEach( delegate(string Cname) { NuevaFila[Cname] = _Registro[Cname]; }); OnlyDataT.Rows.Add(NuevaFila); }); ToCSV(OnlyDataT, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\CsvData.csv"); } }
protected void geraArquivo(DataTable dt, int tipo) { SLDocument documento = new SLDocument(); int linha = 1; SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; documento.SetPageSettings(ps); SLStyle row = new SLStyle(); row.SetFontBold(true); documento.SetCellValue(linha, 1, "NUM_BORDERO"); documento.SetCellValue(linha, 2, "DT_BORDERO"); documento.SetCellValue(linha, 3, "TIPO"); documento.SetCellValue(linha, 4, "BANCO_BORD"); documento.SetCellValue(linha, 5, "AGENCIA_BORD"); documento.SetCellValue(linha, 6, "NUMCONTA_BORD"); documento.SetCellValue(linha, 7, "PREFIXO"); documento.SetCellValue(linha, 8, "NUM_TITULO"); documento.SetCellValue(linha, 9, "PARCELA"); documento.SetCellValue(linha, 10, "FORNECEDOR"); documento.SetCellValue(linha, 11, "NOME"); documento.SetCellValue(linha, 12, "CNPJ"); documento.SetCellValue(linha, 13, "DT_EMISSAO"); documento.SetCellValue(linha, 14, "DT_VENCIMENTO"); documento.SetCellValue(linha, 15, "VLR_BRUTO"); documento.SetCellValue(linha, 16, "VLR_ISS"); documento.SetCellValue(linha, 17, "VLR_IRRF"); documento.SetCellValue(linha, 18, "VLR_PIS"); documento.SetCellValue(linha, 19, "VLR_COFINS"); documento.SetCellValue(linha, 20, "VLR_CSLL"); documento.SetCellValue(linha, 21, "VLR_LIQ"); documento.SetCellValue(linha, 22, "COD_NATUREZA"); documento.SetCellValue(linha, 23, "DESC_NATUREZA"); documento.SetCellValue(linha, 24, "CONTA_CONTABIL"); documento.SetCellValue(linha, 25, "CENTRO_CUSTO"); documento.SetCellValue(linha, 26, "PATROCINADOR"); documento.SetCellValue(linha, 27, "PLANO"); documento.SetCellValue(linha, 28, "SUBMASSA"); documento.SetCellValue(linha, 29, "VALOR_RATEADO"); documento.SetCellValue(linha, 30, "PERCENTUAL_RATEADO"); documento.SetCellValue(linha, 31, "COD_FORMA_PAG"); documento.SetCellValue(linha, 32, "DESC_FORMA_LIQUID"); documento.SetCellValue(linha, 33, "COD_BARRAS"); documento.SetCellValue(linha, 34, "PROJETO"); documento.SetRowStyle(linha, row); linha++; foreach (DataRow dr in dt.Rows) { documento.SetCellValue(linha, 1, dr[0].ToString()); documento.SetCellValue(linha, 2, string.IsNullOrEmpty(dr[1].ToString()) ? "" : Convert.ToDateTime(dr[1]).ToShortDateString()); documento.SetCellValue(linha, 3, dr[2].ToString()); documento.SetCellValue(linha, 4, dr[3].ToString()); documento.SetCellValue(linha, 5, dr[4].ToString()); documento.SetCellValue(linha, 6, dr[5].ToString()); documento.SetCellValue(linha, 7, dr[6].ToString()); documento.SetCellValue(linha, 8, dr[7].ToString()); documento.SetCellValue(linha, 9, dr[8].ToString()); documento.SetCellValue(linha, 10, dr[9].ToString()); documento.SetCellValue(linha, 11, dr[10].ToString()); documento.SetCellValue(linha, 12, dr[11].ToString()); documento.SetCellValue(linha, 13, string.IsNullOrEmpty(dr[12].ToString()) ? "" : Convert.ToDateTime(dr[12]).ToShortDateString()); // documento.SetCellValue(linha, 14, string.IsNullOrEmpty(dr[13].ToString()) ? "" : Convert.ToDateTime(dr[13]).ToShortDateString()); documento.SetCellValue(linha, 14, Convert.ToDateTime(dr[13].ToString())); documento.SetCellValue(linha, 15, dr[14].ToString()); documento.SetCellValue(linha, 16, dr[15].ToString()); documento.SetCellValue(linha, 17, dr[16].ToString()); documento.SetCellValue(linha, 18, dr[17].ToString()); documento.SetCellValue(linha, 19, dr[18].ToString()); documento.SetCellValue(linha, 20, dr[19].ToString()); documento.SetCellValue(linha, 21, dr[20].ToString()); documento.SetCellValue(linha, 22, dr[21].ToString()); documento.SetCellValue(linha, 23, dr[22].ToString()); documento.SetCellValue(linha, 24, dr[23].ToString()); documento.SetCellValue(linha, 25, dr[24].ToString()); documento.SetCellValue(linha, 26, dr[25].ToString()); documento.SetCellValue(linha, 27, dr[26].ToString()); documento.SetCellValue(linha, 28, dr[27].ToString()); documento.SetCellValue(linha, 29, dr[28].ToString()); documento.SetCellValue(linha, 30, dr[29].ToString()); documento.SetCellValue(linha, 31, dr[30].ToString()); documento.SetCellValue(linha, 32, dr[31].ToString()); documento.SetCellValue(linha, 33, dr[32].ToString()); documento.SetCellValue(linha, 34, dr[33].ToString()); linha++; } string caminho = ""; if (tipo == 0) { caminho = Server.MapPath(@"UploadFile\\" + "BorderoPagarSemRateio.xls"); } else if (tipo == 1) { caminho = Server.MapPath(@"UploadFile\\" + "BorderoPagarComRateio.xls"); } documento.SaveAs(caminho); //documento.column }
/// ----------------------------------------------------------------------------------------------- /// <summary> /// Sets up the default styling when user does not predefine styles with a Settings configuration /// </summary> /// <returns>Settings: Default Styling</returns> /// ----------------------------------------------------------------------------------------------- public static Settings SetupDefaultStyles() { try { /* ------------------------------------------------------------- * Setup primary container for the child datasets * -----------------------------------------------------------*/ var settings = new Settings { // Optional name Name = "Default Settings Container" }; /* ------------------------------------------------------------- * Setup the column header base style for the child datasets * -----------------------------------------------------------*/ var baseColumnHeaderStyle = new SLStyle(); baseColumnHeaderStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); baseColumnHeaderStyle.SetVerticalAlignment(VerticalAlignmentValues.Center); baseColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DimGray, Color.White); baseColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetTopBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Medium, Color.Black); baseColumnHeaderStyle.SetFont("Helvetica", 11); baseColumnHeaderStyle.SetFontColor(Color.White); baseColumnHeaderStyle.SetFontBold(true); /* ------------------------------------------------------------- * Setup the odd row style for the child datasets * -----------------------------------------------------------*/ var oddRowStyle = new SLStyle(); oddRowStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left); oddRowStyle.SetVerticalAlignment(VerticalAlignmentValues.Center); oddRowStyle.Fill.SetPattern(PatternValues.Solid, Color.White, Color.Black); oddRowStyle.SetFont("Helvetica", 10); oddRowStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Setup the even row style derived from the odd, * change only what is necessary. * -----------------------------------------------------------*/ var evenRowStyle = oddRowStyle.Clone(); evenRowStyle.Fill.SetPattern(PatternValues.Solid, Color.WhiteSmoke, Color.Black); /* ------------------------------------------------------------- * Define and style base child settings. * This Child will always be present, it represents the * primary dataset for every export. * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( // SheetName (Optional) "Default Base Child Settings", // Set Overall Column Visibility true, // Column offset to the right 0, // Make the base column header row a little larger // so it will stand out. Value is in pixels 25, // Setup the style for Column Headers baseColumnHeaderStyle, // Row and Alternating Row Styles // If set to false then the odd row style will be overall row style true, // Setup the style for odd & even rows oddRowStyle, evenRowStyle, // No User-Defined column headers null )); /* ------------------------------------------------------------ * The first child column headers stylings will be derived * from the base, change only what needs to be changed. * ----------------------------------------------------------*/ var firstColumnHeaderStyle = baseColumnHeaderStyle.Clone(); firstColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DarkGray, Color.Black); firstColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetTopBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Thin, Color.DarkSlateGray); firstColumnHeaderStyle.SetFont("Helvetica", 10); firstColumnHeaderStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Define and add the stylings for the first child, which is * a child of the base data-set * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( "Default First Child Settings", true, null, null, firstColumnHeaderStyle, true, oddRowStyle, evenRowStyle, null )); /* ------------------------------------------------------------- * The second child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var secondColumnHeaderStyle = firstColumnHeaderStyle.Clone(); secondColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.CadetBlue, Color.White); secondColumnHeaderStyle.SetFontColor(Color.White); /* ------------------------------------------------------------- * Define and add the stylings for the second child, which is * a child of the first data-set * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( "Default Second Child Settings", true, null, null, secondColumnHeaderStyle, true, oddRowStyle, evenRowStyle, null )); /* ------------------------------------------------------------- * The third child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var thirdColumnHeaderStyle = firstColumnHeaderStyle.Clone(); thirdColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Aqua, Color.Black); thirdColumnHeaderStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Define and add the stylings for the third child, which is * a child of the second data-set * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( "Default Third Child Settings", true, null, null, thirdColumnHeaderStyle, true, oddRowStyle, evenRowStyle, null )); /* ------------------------------------------------------------- * The forth child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var fourthColumnHeaderStyle = firstColumnHeaderStyle.Clone(); fourthColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Chartreuse, Color.Black); fourthColumnHeaderStyle.SetFontColor(Color.Black); /* ------------------------------------------------------------- * Define and add the stylings for the fourth child, which is * a child of the third data-set * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( "Default Fourth Child Settings", true, null, null, fourthColumnHeaderStyle, true, oddRowStyle, evenRowStyle, null )); /* ------------------------------------------------------------- * If five deep isn't enough let's add a sixth one. * The fifth child column headers stylings will be derived * from the first, change only what needs to be changed. * -----------------------------------------------------------*/ var fifthColumnHeaderStyle = firstColumnHeaderStyle.Clone(); fifthColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.BlueViolet, Color.Black); fifthColumnHeaderStyle.SetFontColor(Color.White); /* ------------------------------------------------------------- * Define and add the stylings for the fifth child, which is * a child of the fourth data-set * -----------------------------------------------------------*/ settings.ChildSettings.Add(new ChildSetting ( "Default Fifth Child Settings", true, null, null, fifthColumnHeaderStyle, true, oddRowStyle, evenRowStyle, null )); return(settings); } catch (Exception ex) { Log.Error("SpreadsheetLightWrapper.Export.DefaultStyles.SetupDefaultStyles -> " + ex.Message + ": " + ex); } return(null); }
private void ExportToExcel() { #region Variables DBConnection dbCon = null; SLDocument slDoc = new SLDocument(); SLStyle slStyle = null; DataSet ds = null; DataTable dt = null; int intCriteria = 0; int intLineStart = 3; int intLine = intLineStart; string[,] arrCriteria = new string[16, 2]; string strAppPath = Server.MapPath("~"); string strFileName = string.Empty; string strFolder = ConfigurationManager.AppSettings[utilSOCYWeb.cWCKReportFolder].ToString(); #endregion Variables //lblError.Text = ""; #region Export to Excel slDoc.SetCellValue("A1", lblReportTitle.Text); slStyle = slDoc.CreateStyle(); slStyle.SetFontBold(true); slStyle.Font.FontSize = 14; slDoc.SetCellStyle("A1", slStyle); slDoc.SetCellValue("F1", "Report Date: " + DateTime.Now.ToString("dd MMM yyyy HH:mm:ss")); #region Criteria slDoc.SetCellValue("A2", "Report Criteria"); //set report heading slStyle = slDoc.CreateStyle(); slStyle.SetFontBold(true); slDoc.SetCellStyle("A2", slStyle); arrCriteria[intCriteria, 0] = "prt_id"; if (cboPartner.Enabled && cboPartner.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblPartner.Text); slDoc.SetCellValue("B" + intLine, cboPartner.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; arrCriteria[intCriteria, 0] = "cso_id"; if (cboCSO.Enabled && cboCSO.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblCSO.Text); slDoc.SetCellValue("B" + intLine, cboCSO.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; arrCriteria[intCriteria, 0] = "rgn_id"; if (cboRegion.Enabled && cboRegion.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblRegion.Text); slDoc.SetCellValue("B" + intLine, cboRegion.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; arrCriteria[intCriteria, 0] = "dst_id"; if (cboDistrict.Enabled && cboDistrict.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblDistrict.Text); slDoc.SetCellValue("B" + intLine, cboDistrict.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; arrCriteria[intCriteria, 0] = "sct_id"; if (cboSubCounty.Enabled && cboSubCounty.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblSubcounty.Text); slDoc.SetCellValue("B" + intLine, cboSubCounty.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; arrCriteria[intCriteria, 0] = "wrd_id"; if (cboParish.Enabled && cboParish.SelectedIndex != 0) { slDoc.SetCellValue("A" + intLine, lblParish.Text); slDoc.SetCellValue("B" + intLine, cboParish.SelectedItem.Text); intLine++; } else { arrCriteria[intCriteria, 1] = ""; } intCriteria++; #endregion Criteria #region Report dbCon = new DBConnection(utilSOCYWeb.cWCKConnection); try { switch (Session["reportid"].ToString()) { case utilSOCYWeb.CRHomeVisitAggregate: ReportsCapturedDataDB.reportType = utilSOCYWeb.CRHomeVisitAggregate; ReportsCapturedDataDB.prt_id = cboPartner.SelectedValue.ToString() != string.Empty ? cboPartner.SelectedValue.ToString() : null; ReportsCapturedDataDB.cso = cboCSO.SelectedValue != string.Empty ? cboCSO.SelectedValue.ToString() : null; ReportsCapturedDataDB.region = cboRegion.SelectedValue != string.Empty ? cboRegion.SelectedValue.ToString() : null; ReportsCapturedDataDB.district = cboDistrict.SelectedValue != string.Empty ? cboDistrict.SelectedValue.ToString() : null; ReportsCapturedDataDB.subcounty = cboSubCounty.SelectedValue != string.Empty ? cboSubCounty.SelectedValue.ToString() : null; ReportsCapturedDataDB.parish = cboParish.SelectedValue != string.Empty ? cboParish.SelectedValue.ToString() : null; #region Dates if (txtCreateDateFrom.Text != string.Empty) { ReportsCapturedDataDB.datecreateFrom = Convert.ToDateTime(txtCreateDateFrom.Text); } else { ReportsCapturedDataDB.datecreateFrom = null; } if (txtCreateDateTo.Text != string.Empty) { ReportsCapturedDataDB.datecreateTo = Convert.ToDateTime(txtCreateDateTo.Text); } else { ReportsCapturedDataDB.datecreateTo = null; } #endregion Dates ds = ReportsCapturedDataDB.GetReportData(); break; } if (ds != null) { for (int intCount = 0; intCount < ds.Tables.Count; intCount++) { #region Sheet Setup dt = ds.Tables[intCount]; if (intCount == 0) { slDoc.RenameWorksheet(SLDocument.DefaultFirstSheetName, dt.TableName); } else { slDoc.AddWorksheet(dt.TableName); intLine = 1; } #endregion Sheet Setup #region Headers for (int intCol = 0; intCol < dt.Columns.Count; intCol++) { slDoc.SetCellValue(Number2ExcelColumn(intCol) + intLine, dt.Columns[intCol].ColumnName); slStyle = slDoc.CreateStyle(); slStyle.SetFontBold(true); slDoc.SetCellStyle(Number2ExcelColumn(intCol) + intLine, slStyle); } intLine++; #endregion Headers #region Data for (int intRow = 0; intRow < dt.Rows.Count; intRow++) { for (int intCol = 0; intCol < dt.Columns.Count; intCol++) { slDoc.SetCellValue(Number2ExcelColumn(intCol) + (intLine + intRow), dt.Rows[intRow][intCol].ToString()); } } intLine = intLine + dt.Rows.Count; #endregion Data } } } finally { dbCon.Dispose(); } #endregion Report #region Save File #region File Name strFileName = "Captured_Data_" + Session["reportid"].ToString() + "_" + DateTime.Now.ToString("yyyyMMMddHHmmss"); #endregion File Name try { slDoc.SaveAs(strAppPath + "\\" + strFolder + "\\" + strFileName + ".xlsx"); Response.Redirect(strFolder + "\\" + strFileName + ".xlsx"); //reset the report type Session["reportid"] = null; } catch (OutOfMemoryException exc) { string script = "alert(\"Not enough Server Memory to process request. Please add additional Report Criteria to reduce the number of records returned\");"; ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", script, true); } slDoc.Dispose(); #endregion Save File #endregion Export to Excel }
private void GeraArquivoGeral(DataSet ds, string mes, string ano) { string dtInicio = Convert.ToDateTime(txtDtInicioRelEstouro.Text).ToString("dd-MM-yyyy"); string dtFinal = Convert.ToDateTime(txtDtFinalRelEstouro.Text).ToString("dd-MM-yyyy"); int qtdestouro = 0, resperiodo = 0, linha = 6; int previsao = objBLL.GeraTotalEstouros(Convert.ToDateTime(dtInicio), Convert.ToDateTime(dtFinal)); SLDocument sl = new SLDocument(); //Set das configurações do arquivos e variaveis de style SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); SLStyle bordaAcima = sl.CreateStyle(); bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultBold = sl.CreateStyle(); resultBold.SetFontBold(true); resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultRed = sl.CreateStyle(); resultRed.SetFontBold(true); resultRed.SetFontColor(System.Drawing.Color.Red); resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle bordaContorno = sl.CreateStyle(); bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle alinhadoCentro = sl.CreateStyle(); alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center; alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center; alinhadoCentro.SetWrapText(true); //Loops de preenchimento do arquivo foreach (DataRow LAreaRel in ds.Tables["AREAS_REL"].Rows) { foreach (DataRow LQtdEstouro in ds.Tables["QTD_ESTOURO"].Rows) { if (LAreaRel[0].ToString() == LQtdEstouro[0].ToString()) { qtdestouro = Convert.ToInt32(LQtdEstouro[1]); break; } qtdestouro = 0; } foreach (DataRow LRespPeriodo in ds.Tables["RESP_PERIODO"].Rows) { if (LAreaRel[0].ToString() == LRespPeriodo[0].ToString()) { resperiodo = Convert.ToInt32(LRespPeriodo[1]); break; } resperiodo = 0; } sl.SetCellValue(linha, 1, LAreaRel[0].ToString()); sl.SetCellValue(linha, 2, qtdestouro); sl.SetCellValue(linha, 3, resperiodo); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); if (qtdestouro != 0 && resperiodo != 0) { sl.SetCellValue(linha, 4, "=ROUND(((B" + linha + "/C" + linha + ")*100),2)"); } else { sl.SetCellValue(linha, 4, 0); } linha++; } //Atribuindo valores fixos e stylo das celulas int auxlinha = linha - 1; sl.SetCellValue(1, 1, "Manifestações com previsão de encerramento entre " + dtInicio + " e " + dtFinal); sl.SetCellValue(1, 2, previsao.ToString()); sl.SetCellStyle(1, 1, alinhadoCentro); sl.SetCellStyle(1, 2, alinhadoCentro); sl.SetCellValue(2, 1, "Respostas das Áreas Gerenciadoras:"); sl.SetCellValue(2, 2, "=C" + linha + @"&"" ou ""&ROUND(B1/C" + linha + @",4)*100&""%"""); sl.SetCellValue(3, 1, "Manifestações encerradas após a previsão:"); sl.SetCellValue(3, 2, "=B" + linha + @"&"" ou ""&ROUND(B" + linha + @"/B1,4)*100&""%"""); sl.SetCellValue(5, 1, "Áreas Gerenciadoras"); sl.SetCellValue(5, 2, "Quantidade de estouros dos prazos"); sl.SetCellValue(5, 3, "Respostas no período"); sl.SetCellValue(5, 4, "%"); sl.SetCellStyle(5, 1, resultBold); sl.SetCellStyle(5, 2, resultBold); sl.SetCellStyle(5, 3, resultBold); sl.SetCellStyle(5, 4, resultBold); sl.SetCellValue(linha, 1, "Total"); sl.SetCellValue(linha, 2, "=SUM(B6:B" + auxlinha + ")"); sl.SetCellValue(linha, 3, "=SUM(C6:C" + auxlinha + ")"); sl.SetCellStyle(linha, 1, resultBold); sl.SetCellStyle(linha, 2, resultRed); sl.SetCellStyle(linha, 3, resultRed); sl.SetRowHeight(5, 38); sl.SetColumnWidth(1, 39); sl.SetRowHeight(1, 40); sl.AutoFitColumn(2); sl.AutoFitColumn(3); sl.SetColumnWidth(4, 7); sl.SetColumnStyle(1, alinhadoCentro); sl.SetColumnStyle(2, alinhadoCentro); sl.SetColumnStyle(3, alinhadoCentro); sl.SetColumnStyle(4, alinhadoCentro); string caminho = Server.MapPath(@"UploadFile\\" + "Relatorio_Estouro " + mes + " " + ano + ".xlsx"); sl.SaveAs(caminho); }