/// <summary>
/// Initializes a new instance of the <see cref="InsertXmlData"/> class.
/// </summary>
public InsertXmlData()
{
File = null;
SheetName = string.Empty;
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
/// <summary>
/// Initializes a new instance of the <see cref="InsertDataTable"/> class.
/// </summary>
public InsertDataTable()
{
Data = null;
SheetName = string.Empty;
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
/// <summary>
/// Initializes a new instance of the <see cref="InsertText"/> class.
/// </summary>
public InsertText()
{
Data = string.Empty;
SheetName = string.Empty;
Style = XlsxCellStyle.Default;
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
/// <summary>
/// Initializes a new instance of the <see cref="InsertAggregateFunction"/> class.
/// </summary>
public InsertAggregateFunction()
{
SheetName = string.Empty;
Style = XlsxCellStyle.Default;
Aggegate = new QualifiedAggregateDefinition();
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
/// <summary>
/// Initializes a new instance of the <see cref="InsertEnumerable{Ti}"/> class.
/// </summary>
public InsertEnumerable()
{
Data = null;
ShowHeaders = YesNo.Yes;
SheetName = string.Empty;
Style = XlsxCellStyle.Default;
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Location == null)
{
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
if (Data == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Styles == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
return(InsertImpl(context, input, SheetName, (XlsxPointRange)Location, Data, Styles));
}
// Generates document
public static void Generate(ILogger logger)
{
#region Retrieve report data
//Informe reportData = BuildAverageByDaysReportData("EN");
Informe reportData = BuildAverageByDaysReportData("ES");
//Informe reportData = BuildSumByDaysReportData("EN");
//Informe reportData = BuildSumByDaysReportData("ES");
//Informe reportData = BuildAverageByMonthsReportData("EN");
//Informe reportData = BuildAverageByMonthsReportData("ES");
//Informe reportData = BuildSumByMonthsReportData("EN");
//Informe reportData = BuildSumByMonthsReportData("ES");
#endregion
#region Add dynamic xlsx styles
CellStylesTable.Add(
"ShortDateValue",
new XlsxCellStyle
{
Font =
{
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder
{
Show = YesNo.Yes,
Position = KnownBorderPosition.Right
}
},
Content =
{
DataType = new DateTimeDataType
{
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme)
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
});
// Add dynamic xlsx styles
CellStylesTable.Add(
"MonthYearDateValue",
new XlsxCellStyle
{
Font =
{
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder
{
Show = YesNo.Yes,
Position = KnownBorderPosition.Right
}
},
Content =
{
DataType = new DateTimeDataType
{
Format = KnownDateTimeFormat.YearMonthShortPattern,
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme),
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
});
CellStylesTable.Add(
"SummaryDateValue",
new XlsxCellStyle
{
Content =
{
DataType = new DateTimeDataType
{
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme)
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Left
}
}
});
#endregion
#region Defines custom styles to use
XlsxCellStyle dateAxisStyle =
reportData.ConfiguracionInforme.ValoresPor.Equals("DIA", StringComparison.OrdinalIgnoreCase)
? CellStylesTable["ShortDateValue"]
: CellStylesTable["MonthYearDateValue"];
#endregion
#region Defines localized literals
bool isSpanishLanguage = reportData.ConfiguracionInforme.IdiomaInforme.Equals("ES", StringComparison.OrdinalIgnoreCase);
#region Summary Sheet
string summarySheetName = "Hoja1";
if (!isSpanishLanguage)
{
summarySheetName = "Sheet1";
}
string summaryParameterLiteral = "Parámetro";
if (!isSpanishLanguage)
{
summaryParameterLiteral = "Parameter";
}
string summaryTypeLiteral = "Tipo";
if (!isSpanishLanguage)
{
summaryTypeLiteral = "Type";
}
string summaryValuesByLiteral = "Valores Por";
if (!isSpanishLanguage)
{
summaryValuesByLiteral = "Values By";
}
string summaryInitialDateLiteral = "Fecha Inicio";
if (!isSpanishLanguage)
{
summaryInitialDateLiteral = "Initial Date";
}
string summaryEndDateLiteral = "Fecha Fin";
if (!isSpanishLanguage)
{
summaryEndDateLiteral = "End Date";
}
#endregion
#region Rawdata Sheet
string rawDataSheetName = "Hoja2";
if (!isSpanishLanguage)
{
rawDataSheetName = "Sheet2";
}
string averageLiteral = "Media";
if (!isSpanishLanguage)
{
averageLiteral = "Average";
}
string sumLiteral = "Suma";
if (!isSpanishLanguage)
{
sumLiteral = "Sum";
}
#endregion
#endregion
#region Report type to generate
bool generateSumReport = reportData.ConfiguracionInforme.Tipo.Equals("SUMA", StringComparison.OrdinalIgnoreCase);
#endregion
#region Determines locals to include in report
IEnumerable <Local> localsToPrint = generateSumReport
? reportData.Locales.Where(local => local.Nombre.Equals("Suma", StringComparison.OrdinalIgnoreCase))
: reportData.Locales;
#endregion
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { summarySheetName, rawDataSheetName });
#endregion
#region Sheet 1
#region Sheet 1 > Populates configuration data
doc.Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["ReportTitle"],
Location = new XlsxPointRange {
Column = 2, Row = 1
},
Data = reportData.ConfiguracionInforme.TituloInforme
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 3
},
Data = summaryParameterLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 3
},
Data = reportData.ConfiguracionInforme.Parametro
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Data = summaryTypeLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 4
},
Data = reportData.ConfiguracionInforme.Tipo
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 5
},
Data = summaryValuesByLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 5
},
Data = reportData.ConfiguracionInforme.ValoresPor
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 6
},
Data = summaryInitialDateLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryDateValue"],
Location = new XlsxPointRange {
Column = 3, Row = 6
},
Data = reportData.ConfiguracionInforme.FechaInicioPeriodo
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 7
},
Data = summaryEndDateLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryDateValue"],
Location = new XlsxPointRange {
Column = 3, Row = 7
},
Data = reportData.ConfiguracionInforme.FechaFinPeriodo
});
#endregion
#endregion
#region Sheet 2
#region Sheet 2 > Populates data table
var localId = -1;
var headerLocation = new XlsxPointRange {
Column = 3, Row = 2
};
var dateAxisLocation = new XlsxPointRange {
Column = 2, Row = 3
};
var parameterValueLocation = new XlsxPointRange {
Column = 3, Row = 3
};
var localHeaderStyle = CellStylesTable["LocalHeader"];
foreach (var local in localsToPrint)
{
// Defines local header text
string localName = local.Nombre;
if (localName.Equals("Suma", StringComparison.OrdinalIgnoreCase))
{
localName = sumLiteral;
}
else if (localName.Equals("Media", StringComparison.OrdinalIgnoreCase))
{
localName = averageLiteral;
}
// Defines local style
localHeaderStyle.Name = $"LocalHeader{localId}";
localHeaderStyle.Content.Color = local.Color;
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = localName,
Location = headerLocation,
Style = localHeaderStyle
});
headerLocation.Offset(1, 0);
localId++;
var series = local.Series;
foreach (var serie in series)
{
if (localId == 0)
{
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = serie.Fecha,
Location = dateAxisLocation,
Style = dateAxisStyle
});
}
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = serie.Valor,
Location = parameterValueLocation,
Style = CellStylesTable["ParameterValue"]
});
dateAxisLocation.Offset(0, 1);
parameterValueLocation.Offset(0, 1);
}
parameterValueLocation = new XlsxPointRange {
Column = parameterValueLocation.Column + 1, Row = 3
};
}
#endregion
#region Sheet 2 > Insert autofilter
doc.Insert(new InsertAutoFilter
{
SheetName = rawDataSheetName,
Location = new XlsxStringRange {
Address = "C2:E2"
}
});
#endregion
#region Sheet 2 > Insert Mini-Charts
doc.Insert(new InsertMiniChart
{
SheetName = rawDataSheetName,
Location = new XlsxPointRange {
Column = 3, Row = 33
}, // B3:B33
MiniChart = new XlsxMiniChart
{
Name = "MiniChart1",
EmptyValueAs = MiniChartEmptyValuesAs.Gap,
ChartAxes =
{
Horizontal = { Type = MiniChartHorizontalAxisType.Date }
},
ChartSize =
{
VerticalCells = 3,
HorizontalCells = 3
},
ChartRanges =
{
Axis = new XlsxRange {
Start = { Column= 2, Row = 3 }, End = { Column = 2, Row = 32 }
}, //B3:B32
Data = new XlsxStringRange {
Address = "C3:C32"
}
},
ChartType =
{
Active = MiniChartType.Line,
Column =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
Line =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
WinLoss =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
}
}
}
});
#endregion
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true
});
if (!result.Success)
{
logger.Info(" > Error creating output result");
logger.Info($" > Error: {result.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Saves output result
var saveResult = result.Result.Action(new SaveToFile {
OutputPath = "~/Output/Sample05/Sample-05"
});
if (!saveResult.Success)
{
logger.Info(" > Error while saving to disk");
logger.Info($" > Error: {saveResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
logger.Info(" > Saved to disk correctly");
logger.Info(" > Path: ~/Output/Sample05/Sample-05.xlsx");
#endregion
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxPointRange location, Dictionary <string, object> data, DictionaryStyles styles)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// cell styles > Headers
XlsxCellStyle headerTextTextStyle = excel.CreateStyle(styles.Headers.Text);
// cell styles > Values
XlsxCellStyle valueTextTextStyle = excel.CreateStyle(styles.Values.Text);
XlsxCellStyle valueDatetimeCellStyle = excel.CreateStyle(styles.Values.DateTime);
XlsxCellStyle valueDecimalCellStyle = excel.CreateStyle(styles.Values.Decimal);
var keys = data.Keys;
foreach (var key in keys)
{
var isOdd = location.Row.IsOdd();
var headerCell = ws.Cells[location.Row, location.Column];
headerCell.StyleName = isOdd ? $"{headerTextTextStyle.Name}_Alternate" : headerTextTextStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
headerCell.Value = key;
var value = data[key];
var valueCell = ws.Cells[location.Row, location.Column + 1];
XlsxCellStyle styleToUse;
switch (value)
{
case string _:
styleToUse = valueTextTextStyle;
break;
case float _:
case double _:
styleToUse = valueDecimalCellStyle;
break;
case DateTime _:
styleToUse = valueDatetimeCellStyle;
break;
default:
styleToUse = valueTextTextStyle;
break;
}
valueCell.StyleName = isOdd ? $"{styleToUse.Name}_Alternate" : styleToUse.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
valueCell.Value = styleToUse.Content.DataType.GetFormattedDataValue(value.ToString()).FormattedValue;
location.Offset(0, 1);
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
