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);
}
