public static void WriteDataInExcelFile(ResultTable results, Stream stream)
{
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartByName(ExcelMessage.Data.NiceToString());
CellBuilder cb = PlainExcelGenerator.CellBuilder;
SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().SingleEx();
List <ColumnData> columnEquivalences = GetColumnsEquivalences(document, sheetData, results);
UInt32Value headerStyleIndex = worksheetPart.Worksheet.FindCell("A1").StyleIndex;
//Clear sheetData from the template sample data
sheetData.InnerXml = "";
sheetData.Append(new Sequence <Row>()
{
(from columnData in columnEquivalences
select cb.Cell(columnData.Column.Column.DisplayName, headerStyleIndex)).ToRow(),
from r in results.Rows
select(from columnData in columnEquivalences
select cb.Cell(r[columnData.Column], cb.GetTemplateCell(columnData.Column.Column.Type), columnData.StyleIndex)).ToRow()
}.Cast <OpenXmlElement>());
var pivotTableParts = workbookPart.PivotTableCacheDefinitionParts
.Where(ptpart => ptpart.PivotCacheDefinition.Descendants <WorksheetSource>()
.Any(wss => wss.Sheet.Value == ExcelMessage.Data.NiceToString()));
foreach (PivotTableCacheDefinitionPart ptpart in pivotTableParts)
{
PivotCacheDefinition pcd = ptpart.PivotCacheDefinition;
WorksheetSource wss = pcd.Descendants <WorksheetSource>().FirstEx();
wss.Reference.Value = "A1:" + GetExcelColumn(columnEquivalences.Count(ce => !ce.IsNew) - 1) + (results.Rows.Count() + 1).ToString();
pcd.RefreshOnLoad = true;
pcd.SaveData = false;
pcd.Save();
}
workbookPart.Workbook.Save();
document.Close();
}
}
private static List <ColumnData> GetColumnsEquivalences(this SpreadsheetDocument document, SheetData sheetData, ResultTable results)
{
var resultsCols = results.Columns.ToDictionary(c => c.Column.DisplayName);
var headerCells = sheetData.Descendants <Row>().FirstEx().Descendants <Cell>().ToList();
var templateCols = headerCells.ToDictionary(c => document.GetCellValue(c));
var rowDataCellTemplates = sheetData.Descendants <Row>()
.FirstEx(r => IsValidRowDataTemplate(r, headerCells))
.Descendants <Cell>().ToList();
var dic = templateCols.OuterJoinDictionaryCC(resultsCols, (name, cell, resultCol) =>
{
if (resultCol == null)
{
throw new ApplicationException(ExcelMessage.TheExcelTemplateHasAColumn0NotPresentInTheFindWindow.NiceToString().FormatWith(name));
}
if (cell != null)
{
return(new ColumnData
{
IsNew = false,
StyleIndex = rowDataCellTemplates[headerCells.IndexOf(cell)].StyleIndex,
Column = resultCol,
});
}
else
{
CellBuilder cb = PlainExcelGenerator.CellBuilder;
return(new ColumnData
{
IsNew = true,
StyleIndex = 0,
Column = resultCol,
});
}
});
return(dic.Values.ToList());
}
public static void SetTemplate(Stream templateStream)
{
Template = templateStream.ReadAllBytes();
MemoryStream memoryStream = new MemoryStream();
memoryStream.WriteAllBytes(Template);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(memoryStream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
Worksheet worksheet = worksheetPart.Worksheet;
CellBuilder = new CellBuilder()
{
DefaultStyles = new Dictionary <TemplateCells, UInt32Value>
{
{ TemplateCells.Title, worksheet.FindCell("A1").StyleIndex },
{ TemplateCells.Header, worksheet.FindCell("A2").StyleIndex },
{ TemplateCells.Date, worksheet.FindCell("B3").StyleIndex },
{ TemplateCells.DateTime, worksheet.FindCell("C3").StyleIndex },
{ TemplateCells.Text, worksheet.FindCell("D3").StyleIndex },
{ TemplateCells.General, worksheet.FindCell("E3").StyleIndex },
{ TemplateCells.Boolean, worksheet.FindCell("E3").StyleIndex },
{ TemplateCells.Enum, worksheet.FindCell("E3").StyleIndex },
{ TemplateCells.Number, worksheet.FindCell("F3").StyleIndex },
{ TemplateCells.Decimal, worksheet.FindCell("G3").StyleIndex },
{ TemplateCells.DecimalEuro, worksheet.FindCell("H3").StyleIndex },
{ TemplateCells.DecimalDollar, worksheet.FindCell("I3").StyleIndex },
{ TemplateCells.DecimalPound, worksheet.FindCell("J3").StyleIndex },
{ TemplateCells.DecimalYuan, worksheet.FindCell("K3").StyleIndex },
}
};
}
}
public static void SetTemplate(Stream templateStream)
{
Template = templateStream.ReadAllBytes();
MemoryStream memoryStream = new MemoryStream();
memoryStream.WriteAllBytes(Template);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(memoryStream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart !;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
Worksheet worksheet = worksheetPart.Worksheet;
CellBuilder = new CellBuilder()
{
CellFormatCount = document.WorkbookPart !.WorkbookStylesPart !.Stylesheet.CellFormats !.Count !,
DefaultStyles = new Dictionary <DefaultStyle, UInt32Value>
{
{ DefaultStyle.Title, worksheet.FindCell("A1").StyleIndex ! },