public virtual Lazaro.Pres.Spreadsheet.Workbook ToWorkbook(Lazaro.Pres.FieldCollection useFields) { Lazaro.Pres.Spreadsheet.Workbook Res = new Lazaro.Pres.Spreadsheet.Workbook(); Lazaro.Pres.Spreadsheet.Sheet Sheet = new Lazaro.Pres.Spreadsheet.Sheet(this.Text); Res.Sheets.Add(Sheet); // Exporto los encabezados de columna if (this.Definicion.KeyColumn.Printable) { Sheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader(this.Definicion.KeyColumn.Label, this.Definicion.KeyColumn.Width)); Sheet.ColumnHeaders[0].DataType = this.Definicion.KeyColumn.DataType; Sheet.ColumnHeaders[0].Format = this.Definicion.KeyColumn.Format; Sheet.ColumnHeaders[0].Printable = this.Definicion.KeyColumn.Printable; } int OrderColumn = -1; if (useFields != null) { for (int i = 0; i <= useFields.Count - 1; i++) { if (useFields[i].Printable) { Lazaro.Pres.Spreadsheet.ColumnHeader ColHead = new Lazaro.Pres.Spreadsheet.ColumnHeader(useFields[i].Label, useFields[i].Width); ColHead.Name = Lfx.Data.Field.GetNameOnly(useFields[i].Name); ColHead.TextAlignment = useFields[i].Alignment; ColHead.DataType = useFields[i].DataType; ColHead.Format = useFields[i].Format; ColHead.TotalFunction = useFields[i].TotalFunction; ColHead.Printable = useFields[i].Printable; Sheet.ColumnHeaders.Add(ColHead); if (ColHead.Name == this.Definicion.OrderBy) OrderColumn = Sheet.ColumnHeaders.Count - 1; if (ColHead.Name == this.GroupingColumnName) Sheet.ColumnHeaders.GroupingColumn = Sheet.ColumnHeaders.Count - 1; } } } // Exporto los renglones System.Data.DataTable Tabla = this.Connection.Select(this.SelectCommand()); foreach (System.Data.DataRow DtRow in Tabla.Rows) { Lfx.Data.Row Registro = (Lfx.Data.Row)DtRow; string NombreCampoId = Lfx.Data.Field.GetNameOnly(this.Definicion.KeyColumn.Name); int ItemId = Registro.Fields[NombreCampoId].ValueInt; Lazaro.Pres.Spreadsheet.Row Reng = this.FormatRow(ItemId, Registro, Sheet, useFields); Sheet.Rows.Add(Reng); } if (OrderColumn >= 0) { if (m_GroupingColumnName != null) { Sheet.SortByGroupAndColumn(OrderColumn, true); } else { if (OrderColumn >= 0) Sheet.Sort(OrderColumn, true); } } return Res; }
public Lazaro.Pres.Spreadsheet.Sheet Ejecutar(Dictionary <string, object> valores) { string ConsultaSelect = this.ConstruirConsulta(valores); // Remplazamos los {parametros} por los valores proporcionados por el usuario if (valores != null) { foreach (string ValorNombre in valores.Keys) { object ValorValor = valores[ValorNombre]; string ValorFormateado; if (ValorValor == null) { ValorFormateado = "NULL"; } else if (ValorValor is decimal || ValorValor is double || ValorValor is Single) { ValorFormateado = ((decimal)(ValorValor)).ToString("#.00000000"); } else if (ValorValor is DateTime) { ValorFormateado = ((DateTime)(ValorValor)).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat); } else { ValorFormateado = ValorValor.ToString(); } ConsultaSelect.Replace("{" + ValorNombre + "}", ValorFormateado); } } DataTable Resultados = this.Connection.Select(ConsultaSelect); Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet(); // Creo los encabezados de columna foreach (DataColumn Columna in Resultados.Columns) { Lazaro.Pres.Spreadsheet.ColumnHeader NuevaColumna = new Lazaro.Pres.Spreadsheet.ColumnHeader(Columna.ColumnName); switch (Columna.DataType.ToString()) { case "System.Int32": case "System.Int64": case "System.Decimal": case "System.Double": case "System.Single": NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far; NuevaColumna.Width = 120; break; case "System.DateTime": NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far; NuevaColumna.Width = 120; break; default: NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Near; NuevaColumna.Width = 320; break; } Res.ColumnHeaders.Add(NuevaColumna); } // Lleno la planilla con los registros foreach (DataRow Reg in Resultados.Rows) { Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row(); foreach (DataColumn Columna in Resultados.Columns) { switch (Columna.DataType.ToString()) { case "System.Decimal": case "System.Double": case "System.Single": Renglon.Cells.AddWithValue(System.Convert.ToDecimal(Reg[Columna.ColumnName])); break; case "System.DateTime": Renglon.Cells.AddWithValue(System.Convert.ToDateTime(Reg[Columna.ColumnName])); break; default: Renglon.Cells.AddWithValue(System.Convert.ToString(Reg[Columna.ColumnName])); break; } } Res.Rows.Add(Renglon); } return(Res); }
public Lazaro.Pres.Spreadsheet.Sheet Ejecutar(Dictionary<string, object> valores) { string ConsultaSelect = this.ConstruirConsulta(valores); // Remplazamos los {parametros} por los valores proporcionados por el usuario if (valores != null) { foreach (string ValorNombre in valores.Keys) { object ValorValor = valores[ValorNombre]; string ValorFormateado; if (ValorValor == null) { ValorFormateado = "NULL"; } else if (ValorValor is decimal || ValorValor is double || ValorValor is Single) { ValorFormateado = ((decimal)(ValorValor)).ToString("#.00000000"); } else if (ValorValor is DateTime) { ValorFormateado = ((DateTime)(ValorValor)).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat); } else { ValorFormateado = ValorValor.ToString(); } ConsultaSelect.Replace("{" + ValorNombre + "}", ValorFormateado); } } DataTable Resultados = this.Connection.Select(ConsultaSelect); Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet(); // Creo los encabezados de columna foreach(DataColumn Columna in Resultados.Columns) { Lazaro.Pres.Spreadsheet.ColumnHeader NuevaColumna = new Lazaro.Pres.Spreadsheet.ColumnHeader(Columna.ColumnName); switch(Columna.DataType.ToString()) { case "System.Int32": case "System.Int64": case "System.Decimal": case "System.Double": case "System.Single": NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far; NuevaColumna.Width = 120; break; case "System.DateTime": NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far; NuevaColumna.Width = 120; break; default: NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Near; NuevaColumna.Width = 320; break; } Res.ColumnHeaders.Add(NuevaColumna); } // Lleno la planilla con los registros foreach (DataRow Reg in Resultados.Rows) { Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row(); foreach (DataColumn Columna in Resultados.Columns) { switch (Columna.DataType.ToString()) { case "System.Decimal": case "System.Double": case "System.Single": Renglon.Cells.AddWithValue(System.Convert.ToDecimal(Reg[Columna.ColumnName])); break; case "System.DateTime": Renglon.Cells.AddWithValue(System.Convert.ToDateTime(Reg[Columna.ColumnName])); break; default: Renglon.Cells.AddWithValue(System.Convert.ToString(Reg[Columna.ColumnName])); break; } } Res.Rows.Add(Renglon); } return Res; }
public virtual Lazaro.Pres.Spreadsheet.Workbook ToWorkbookDetalle(int IDComprobante) { Lbl.Comprobantes.Factura selComprobante = new Lbl.Comprobantes.Factura(this.Connection, IDComprobante); Lazaro.Pres.Spreadsheet.Workbook Res = new Lazaro.Pres.Spreadsheet.Workbook(); Lazaro.Pres.Spreadsheet.Sheet Sheet = new Lazaro.Pres.Spreadsheet.Sheet(this.Text + " Comprobante"); Res.Sheets.Add(Sheet); int OrderColumn = -1; if (selComprobante != null) { for (int i = 0; i < 9; i++) { ExcelReg nexc = new ExcelReg(); switch (i) { case 0: nexc.name = "id_articulo"; nexc.desc = "Código"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 100; nexc.fieldtype = Lfx.Data.InputFieldTypes.Integer; break; case 1: nexc.name = "nombre"; nexc.desc = "Nombre"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 300; nexc.fieldtype = Lfx.Data.InputFieldTypes.Text; break; case 2: nexc.name = "descripcion"; nexc.desc = "Descripción"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 150; nexc.fieldtype = Lfx.Data.InputFieldTypes.Text; break; case 3: nexc.name = "cantidad"; nexc.desc = "Cantidad"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 80; nexc.fieldtype = Lfx.Data.InputFieldTypes.Numeric; break; case 4: nexc.name = "costo"; nexc.desc = "Costo"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 90; nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency; break; case 5: nexc.name = "precio"; nexc.desc = "Precio Uni."; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 120; nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency; break; case 6: nexc.name = "importe"; nexc.desc = "Importe"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 120; nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency; break; case 7: nexc.name = "recargo"; nexc.desc = "Recargo"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 100; nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency; break; case 8: nexc.name = "total"; nexc.desc = "Total"; nexc.alig = Lfx.Types.StringAlignment.Near; nexc.width = 120; nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency; break; } Lazaro.Pres.Spreadsheet.ColumnHeader ColHead = new Lazaro.Pres.Spreadsheet.ColumnHeader(nexc.desc, nexc.width); ColHead.Name = Lazaro.Orm.Data.ColumnValue.GetNameOnly(nexc.name); ColHead.TextAlignment = nexc.alig; ColHead.DataType = nexc.fieldtype; ColHead.Format = nexc.format; ColHead.Printable = true; if (i == 8) { ColHead.TotalFunction = Lazaro.Pres.Spreadsheet.QuickFunctions.Sum; } Sheet.ColumnHeaders.Add(ColHead); excReg.Add(nexc); } } // Exporto los renglones Lbl.Comprobantes.ColeccionDetalleArticulos detArt = selComprobante.Articulos; foreach (Lbl.Comprobantes.DetalleArticulo DtRow in detArt) { Lazaro.Pres.Spreadsheet.Row Reng = this.FormatArt(Sheet, DtRow); Sheet.Rows.Add(Reng); } if (OrderColumn >= 0) { if (m_GroupingColumnName != null) { Sheet.SortByGroupAndColumn(OrderColumn, true); } else { if (OrderColumn >= 0) { Sheet.Sort(OrderColumn, true); } } } return(Res); }
public override Lazaro.Pres.Spreadsheet.Workbook ToWorkbook(Lazaro.Pres.FieldCollection useFields) { Lazaro.Pres.Spreadsheet.Workbook Res = new Lazaro.Pres.Spreadsheet.Workbook(); Lazaro.Pres.Spreadsheet.Sheet Sheet = new Lazaro.Pres.Spreadsheet.Sheet(this.Text); Res.Sheets.Add(Sheet); // Exporto los encabezados de columna if (this.Definicion.KeyColumn.Printable) { Sheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader(this.Definicion.KeyColumn.Label, this.Definicion.KeyColumn.Width)); Sheet.ColumnHeaders[0].DataType = this.Definicion.KeyColumn.DataType; Sheet.ColumnHeaders[0].Format = this.Definicion.KeyColumn.Format; Sheet.ColumnHeaders[0].Printable = this.Definicion.KeyColumn.Printable; } int OrderColumn = -1; if (useFields != null) { for (int i = 0; i <= useFields.Count - 1; i++) { if (useFields[i].Printable) { Lazaro.Pres.Spreadsheet.ColumnHeader ColHead = new Lazaro.Pres.Spreadsheet.ColumnHeader(useFields[i].Label, useFields[i].Width); ColHead.Name = Lazaro.Orm.Data.ColumnValue.GetNameOnly(useFields[i].Name); ColHead.TextAlignment = useFields[i].Alignment; ColHead.DataType = useFields[i].DataType; ColHead.Format = useFields[i].Format; ColHead.TotalFunction = useFields[i].TotalFunction; ColHead.Printable = useFields[i].Printable; Sheet.ColumnHeaders.Add(ColHead); if (ColHead.Name == this.Definicion.OrderBy) { OrderColumn = Sheet.ColumnHeaders.Count - 1; } if (ColHead.Name == this.GroupingColumnName) { Sheet.ColumnHeaders.GroupingColumn = Sheet.ColumnHeaders.Count - 1; } } } } // Exporto los renglones System.Data.DataTable Tabla = this.Connection.Select(this.SelectCommand()); int lastItemId = -1; foreach (System.Data.DataRow DtRow in Tabla.Rows) { Lfx.Data.Row Registro = (Lfx.Data.Row)DtRow; string NombreCampoId = Lazaro.Orm.Data.ColumnValue.GetNameOnly(this.Definicion.KeyColumn.Name); int ItemId = Registro.Fields[NombreCampoId].ValueInt; lastItemId = ItemId; Lazaro.Pres.Spreadsheet.Row Reng = this.FormatRow(ItemId, Registro, Sheet, useFields); Sheet.Rows.Add(Reng); } Lazaro.Pres.Spreadsheet.Row RengSum = this.FormatRowSum(Sheet, useFields); Sheet.Rows.Add(RengSum); if (OrderColumn >= 0) { if (m_GroupingColumnName != null) { Sheet.SortByGroupAndColumn(OrderColumn, true); } else { if (OrderColumn >= 0) { Sheet.Sort(OrderColumn, true); } } } //Sheet.Workbook.Sheets.Find("").Rows.tot return(Res); }