public void SaveTo(string fileName, SaveFormats format, Sheet sheet) { using (System.IO.StreamWriter wr = new System.IO.StreamWriter(new System.IO.FileStream(fileName, System.IO.FileMode.Create))) { switch (format) { case SaveFormats.ExcelXml: wr.Write(this.ToExcelXml(sheet)); break; case SaveFormats.Html: wr.Write(this.ToHtml(sheet)); break; } wr.Close(); } }
public Lazaro.Pres.Spreadsheet.Sheet ToWorkbookSheet() { Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet(Titulo); foreach (Lfx.Data.Aggregate Agru in this.Aggregates) { Agru.Reset(); } foreach (Lazaro.Pres.Field Field in this.Fields) { Res.ColumnHeaders.Add(new ColumnHeader(Field.Label, Field.Width, Field.Alignment)); } if (this.Grouping != null) this.Grouping.Reset(); qGen.Select Sel = this.SelectCommand.Clone(); if (this.Grouping != null) { if (Sel.Order == null || Sel.Order.Length == 0) Sel.Order = this.Grouping.FieldName; else Sel.Order = this.Grouping.FieldName + "," + Sel.Order; } System.Data.DataTable Tabla = DataBase.Select(Sel); foreach (System.Data.DataRow Registro in Tabla.Rows) { if (this.Grouping != null && Lfx.Types.Object.CompareByValue(this.Grouping.LastValue, Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)]) != 0) { // Agrego un renglón de subtotales if (this.Grouping.LastValue != null) { Lazaro.Pres.Spreadsheet.Row SubTotales; if (this.ExpandGroups) SubTotales = new Lazaro.Pres.Spreadsheet.AggregationRow(Res); else SubTotales = new Lazaro.Pres.Spreadsheet.Row(Res); for (int i = 0; i < this.Fields.Count; i++) { Lazaro.Pres.Spreadsheet.Cell FuncCell = null; if (this.Grouping != null && this.Fields[i].Name == this.Grouping.FieldName && this.ExpandGroups == false) { FuncCell = new Cell(this.Grouping.LastValue); } else { foreach (Lfx.Data.Aggregate SubtAgru in this.Aggregates) { if (SubtAgru.FieldName == this.Fields[i].Name) { switch (SubtAgru.Function) { case Lfx.Data.AggregationFunctions.Count: FuncCell = new Cell(SubtAgru.Count); SubtAgru.ResetCounters(); break; case Lfx.Data.AggregationFunctions.Sum: FuncCell = new Cell(SubtAgru.Sum); SubtAgru.ResetCounters(); break; default: FuncCell = new Cell("#undef#"); SubtAgru.ResetCounters(); break; } } } } if (FuncCell != null) SubTotales.Cells.Add(FuncCell); else SubTotales.Cells.Add(new Cell("")); } Res.Rows.Add(SubTotales); } this.Grouping.LastValue = Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)]; // Agrego un encabezado if (ExpandGroups) Res.Rows.Add(new Lazaro.Pres.Spreadsheet.HeaderRow(Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)].ToString())); } if (Aggregates != null) { // Calculo las funciones de agregación foreach (Lfx.Data.Aggregate Agru in this.Aggregates) { switch (Agru.Function) { case Lfx.Data.AggregationFunctions.Count: Agru.Count++; break; case Lfx.Data.AggregationFunctions.Sum: Agru.Sum += System.Convert.ToDecimal(Registro[Lfx.Data.Field.GetNameOnly(Agru.FieldName)]); break; } } } if (ExpandGroups) { Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row(); foreach (Lazaro.Pres.Field Field in this.Fields) { Lazaro.Pres.Spreadsheet.Cell Celda = new Cell(Registro[Lfx.Data.Field.GetNameOnly(Field.Name)]); Renglon.Cells.Add(Celda); } Res.Rows.Add(Renglon); } } return Res; }
public AggregationRow(Sheet sheet) : base(sheet) { }
protected override Lazaro.Pres.Spreadsheet.Row FormatRow(int itemId, Lfx.Data.Row row, Lazaro.Pres.Spreadsheet.Sheet sheet, Lazaro.Pres.FieldCollection useFields) { Lazaro.Pres.Spreadsheet.Row Res = base.FormatRow(itemId, row, sheet, useFields); switch (row.Fields["tipo_fac"].ValueString) { case "NCA": case "NCB": case "NCC": case "NCE": case "NCM": row.Fields["gravado"].Value = -row.Fields["gravado"].ValueDecimal; row.Fields["total"].Value = -row.Fields["total"].ValueDecimal; break; } if (row.Fields["anulada"].ValueInt != 0) { Res.Cells[4].Content = "ANULADA"; } return(Res); }
public Row(Sheet sheet) { this.ParentSheet = sheet; this.Cells = new CellCollection(this); }
public Lazaro.Pres.Spreadsheet.Sheet ToWorkbookSheet() { Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet(Titulo); foreach (Lfx.Data.Aggregate Agru in this.Aggregates) { Agru.Reset(); } foreach (Lazaro.Pres.Field Field in this.Fields) { Res.ColumnHeaders.Add(new ColumnHeader(Field.Label, Field.Width, Field.Alignment)); } if (this.Grouping != null) { this.Grouping.Reset(); } qGen.Select Sel = this.SelectCommand.Clone(); if (this.Grouping != null) { if (Sel.Order == null || Sel.Order.Length == 0) { Sel.Order = this.Grouping.FieldName; } else { Sel.Order = this.Grouping.FieldName + "," + Sel.Order; } } System.Data.DataTable Tabla = DataBase.Select(Sel); foreach (System.Data.DataRow Registro in Tabla.Rows) { if (this.Grouping != null && Lfx.Types.Object.CompareByValue(this.Grouping.LastValue, Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)]) != 0) { // Agrego un renglón de subtotales if (this.Grouping.LastValue != null) { Lazaro.Pres.Spreadsheet.Row SubTotales; if (this.ExpandGroups) { SubTotales = new Lazaro.Pres.Spreadsheet.AggregationRow(Res); } else { SubTotales = new Lazaro.Pres.Spreadsheet.Row(Res); } for (int i = 0; i < this.Fields.Count; i++) { Lazaro.Pres.Spreadsheet.Cell FuncCell = null; if (this.Grouping != null && this.Fields[i].Name == this.Grouping.FieldName && this.ExpandGroups == false) { FuncCell = new Cell(this.Grouping.LastValue); } else { foreach (Lfx.Data.Aggregate SubtAgru in this.Aggregates) { if (SubtAgru.FieldName == this.Fields[i].Name) { switch (SubtAgru.Function) { case Lfx.Data.AggregationFunctions.Count: FuncCell = new Cell(SubtAgru.Count); SubtAgru.ResetCounters(); break; case Lfx.Data.AggregationFunctions.Sum: FuncCell = new Cell(SubtAgru.Sum); SubtAgru.ResetCounters(); break; default: FuncCell = new Cell("#undef#"); SubtAgru.ResetCounters(); break; } } } } if (FuncCell != null) { SubTotales.Cells.Add(FuncCell); } else { SubTotales.Cells.Add(new Cell("")); } } Res.Rows.Add(SubTotales); } this.Grouping.LastValue = Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)]; // Agrego un encabezado if (ExpandGroups) { Res.Rows.Add(new Lazaro.Pres.Spreadsheet.HeaderRow(Registro[Lfx.Data.Field.GetNameOnly(this.Grouping.FieldName)].ToString())); } } if (Aggregates != null) { // Calculo las funciones de agregación foreach (Lfx.Data.Aggregate Agru in this.Aggregates) { switch (Agru.Function) { case Lfx.Data.AggregationFunctions.Count: Agru.Count++; break; case Lfx.Data.AggregationFunctions.Sum: Agru.Sum += System.Convert.ToDecimal(Registro[Lfx.Data.Field.GetNameOnly(Agru.FieldName)]); break; } } } if (ExpandGroups) { Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row(); foreach (Lazaro.Pres.Field Field in this.Fields) { Lazaro.Pres.Spreadsheet.Cell Celda = new Cell(Registro[Lfx.Data.Field.GetNameOnly(Field.Name)]); Renglon.Cells.Add(Celda); } Res.Rows.Add(Renglon); } } return(Res); }
public RowCollection(Sheet parent) { this.ParentSheet = parent; }
protected internal string ToHtml(Sheet sheet) { System.Text.StringBuilder Result = new StringBuilder(); Result.AppendLine(@"<!DOCTYPE html>"); Result.AppendLine(@"<html>"); Result.AppendLine(@"<head>"); if (sheet == null) { if (this.Sheets.Count > 0) { Result.AppendLine(@"<title>" + this.Sheets[0].Name + @"</title>"); } } else { Result.AppendLine(@"<title>" + sheet.Name + @"</title>"); } Result.AppendLine(@"<meta charset=""utf-8"" />"); Result.AppendLine(@"<meta name=""generator"" content=""Lázaro (www.lazarogestion.com)"">"); Result.AppendLine(@"<meta http-equiv=""Content-Type"" content=""text/html; charset=UTF-8"" />"); Result.AppendLine(@"<style>"); Result.AppendLine(@"@media print { body { margin: 0; padding: 0 } }"); Result.AppendLine(@"body, table, td { font-family: Open Sans, Segoe UI, Trebuchet MS, Helvetica, Sans Serif; font-size: 10pt; }"); Result.AppendLine(@".StyleTable, table { border-collapse: collapse; empty-cells: show; border: none; }"); Result.AppendLine(@".StyleTableCaption, caption { font-size: large; background-color: #C5D900; padding: 4px; }"); Result.AppendLine(@".StyleTableHead, thead { display: table-header-group; }"); Result.AppendLine(@".StyleColumnHeader, th { padding: 2px; background-color: #C5D9F1; font-weight: bold; }"); Result.AppendLine(@".StyleDataRow, tr { page-break-inside: avoid; background-color: white; border-bottom: 1px solid silver; }"); Result.AppendLine(@".StyleDataCell, td { padding: 2px; }"); Result.AppendLine(@"</style>"); Result.AppendLine(@"</head>"); Result.AppendLine("<body>"); if (sheet == null) { //All sheets foreach (Sheet sht in this.Sheets) { Result.AppendLine(sht.ToHtml()); } } else { //One sheet Result.AppendLine(sheet.ToHtml()); } Result.AppendLine("</body>"); Result.AppendLine("</html>"); return Result.ToString(); }
protected internal string ToExcelXml(Sheet sheet) { System.Text.StringBuilder Result = new StringBuilder(); Result.AppendLine(@"<?xml version=""1.0"" encoding=""UTF-8""?>"); Result.AppendLine(@"<?mso-application progid=""Excel.Sheet""?>"); Result.AppendLine(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:c=""urn:schemas-microsoft-com:office:component:spreadsheet"" xmlns:html=""http://www.w3.org/TR/REC-html40"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:x2=""http://schemas.microsoft.com/office/excel/2003/xml"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"); Result.AppendLine(@" <Styles> <Style ss:ID=""Default"" ss:Name=""Normal""> <Alignment ss:Vertical=""Bottom"" /> </Style> <Style ss:ID=""StyleData""> <Font x:FontName=""Segoe IU"" /> </Style> <Style ss:ID=""StyleHeader""> <Font x:FontName=""Segoe IU"" ss:Bold=""1"" /> <Interior ss:Color=""#C5D9F1"" ss:Pattern=""Solid"" /> </Style> </Styles>"); if (sheet == null) { //All sheets foreach (Sheet sht in Sheets) { Result.AppendLine(sht.ToExcelXml()); } } else { //One sheet Result.AppendLine(sheet.ToExcelXml()); } Result.AppendLine("</Workbook>"); return Result.ToString(); }