/// <summary>
/// Creazione riga Header
/// </summary>
/// <param name="worksheetExporter">Exporter (per stream)</param>
/// <param name="Headers">Colonne Header aggiuntive (da definizione tabelle)</param>
/// <param name="settings">Impostazioni esportazione - per future configurazioni</param>
private static void ExportHeaderRows(
IWorksheetExporter worksheetExporter,
string[] Headers,
dto.dtoEcoTableExportSettings settings,
string TableName)
{
// Nome tabella
if (!string.IsNullOrWhiteSpace(TableName))
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.HeaderRowHeight);
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(TableName.Replace(" ", " "));
}
}
}
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.HeaderRowHeight);
foreach (string hval in Headers)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(hval.Replace(" ", " "));
}
}
for (int i = 0; i < settings.HeadStrings.Length; i++)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(settings.HeadStrings[i]);
}
}
}
}
private void ExportHeaderRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
{
using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);
//Add Column Formatting
SpreadCellFormat format = new SpreadCellFormat
{
IsBold = true,
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(142, 196, 65)),
ForeColor = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
HorizontalAlignment = SpreadHorizontalAlignment.Center,
VerticalAlignment = SpreadVerticalAlignment.Center
};
//If the current exported grid is a detail grid leave a blank cell
if (isDetailGrid)
{
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(string.Empty);
}
var columnName = GetDetailGridColumn(data);
//Add Columns to Excel
for (int i = 0; i < columnHeaders.Length; i++)
{
//Ignore the column that is our detail grid
if (columnHeaders[i] == columnName)
{
continue;
}
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetFormat(format);
cellExporter.SetValue(columnHeaders[i]);
}
}
/// <summary>
/// Crea via stream un documento Excel (csv non previsto per via di fogli multipli) con tutte le tabelle economiche di una valutazione
/// </summary>
/// <param name="Eval">Dati valutazione (definizioni, tabelle, ammissioni, importi)</param>
/// <param name="documentStream">Stream output</param>
/// <param name="DocFormat">Formato - Solo XLSX</param>
/// <param name="settings">Impostazioni di esportazione (caratteri, larghezze, colori) - Per personalizzazioni future.</param>
public static void EcoEvalTableExportStream(
Eco.Domain.EconomicEvaluation Eval,
Stream documentStream,
SpreadDocumentFormat DocFormat = SpreadDocumentFormat.Xlsx,
dto.dtoEcoTableExportSettings settings = null)
{
if (settings == null)
{
settings = new dto.dtoEcoTableExportSettings();
}
int exportedCellsCount = 0;
SpreadDocumentFormat selectedDocumentFormat;
int totalCellsCount;
DateTime exportStarted;
bool canExport;
if (Eval == null || Eval.Tables == null || !Eval.Tables.Any())
{
return;
}
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(DocFormat, documentStream))
{
int SheetNumber = 0;
foreach (Eco.Domain.EconomicTable table in Eval.Tables)
{
if (table != null && table.FieldDefinition != null)
{
SheetNumber++;
int headCols = table.HeaderValues.Count();
int totalCols = headCols + 7;
string sheetName = string.Format("{0}-{1}", SheetNumber, table.FieldDefinition.Name);
if (sheetName.Length > 25)
{
sheetName = string.Format("{0}...", sheetName.Substring(0, 20));
}
sheetName = CleanFileName(sheetName);
using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter(sheetName))
{
for (int i = 0; i < totalCols; i++)
{
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
if (i >= headCols)
{
columnExporter.SetWidthInCharacters(settings.ColumnWidths[i - headCols]);
}
else
{
columnExporter.SetWidthInCharacters(settings.ColumnAddWidth);
}
}
}
ExportHeaderRows(worksheetExporter, table.HeaderValues, settings, table.FieldDefinition.Name);
foreach (AdvEconomic.Domain.EconomicItem itm in table.Items)
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.RowHeight);
int columnIndex = 0;
int iv = 0;
foreach (string value in itm.InfoValues)
{
iv++;
if (iv <= headCols)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue(value.Replace(" ", " "));
}
}
}
if (iv < headCols)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue("");
}
}
//Quantità
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestQuantity);
}
//Prezzo unitario
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestUnitPrice);
}
//Totale richiesto
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestTotal);
}
//Approvato
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue(itm.IsAdmit ? settings.BoolValue[1] : settings.BoolValue[0]);
}
//Quantità ammessa
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleFormat, itm.IsAdmit));
cellExporter.SetValue(itm.AdmitQuantity);
}
//Totale ammesso
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.AdmitTotal);
}
//Commenti
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
if (itm.Comment != null)
{
cellExporter.SetValue(itm.Comment.Replace(" ", " ").Replace("<br>", "\r\n"));
}
else
{
cellExporter.SetValue("");
}
}
}
}
}
}
}
}
}
private void ExportBodyRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
{
//Add Cell Formatting
SpreadCellFormat format = new SpreadCellFormat
{
FontSize = 10,
VerticalAlignment = SpreadVerticalAlignment.Center,
HorizontalAlignment = SpreadHorizontalAlignment.Center,
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(50, 190, 255)),
};
SpreadCellFormat detailFormat = new SpreadCellFormat
{
FontSize = 10,
VerticalAlignment = SpreadVerticalAlignment.Center,
HorizontalAlignment = SpreadHorizontalAlignment.Center
};
//Loop through data rows
foreach (var item in data)
{
//Create a new row
using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);
//If the current exported grid is a detail grid leave a blank cell
if (isDetailGrid)
{
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(string.Empty);
cellExporter.SetFormat(detailFormat);
}
//Add value to each column key
foreach (var key in columnHeaders)
{
try
{
//Get Type of current datasource
Type type = data.FirstOrDefault().GetType();
var prop = type.GetProperty(key);
var cellValue = prop.GetValue(item, null);
if (cellValue is null)
{
continue;
}
//check if the model has a List, which means it has a detail grid attached to it.
else if (cellValue is IList detailGrid)
{
//Dispose current rowExporter instance
rowExporter.Dispose();
ExportGrid(worksheetExporter, detailGrid.Cast <dynamic>(), true);
}
//Add value to Excel cell
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(cellValue.ToString());
cellExporter.SetFormat(isDetailGrid ? detailFormat : format);
}
catch (NullReferenceException exception)
{
Console.WriteLine(exception.Message);
}
}
}
}