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