private static Row GetTitleRow(SheetConfiguration configuration, ExcelStyle style)
{
var row = new Row();
foreach (var column in configuration.Columns)
{
var cell = new Cell()
{
DataType = CellValues.InlineString,
InlineString = new InlineString()
{
Text = new Text(column.Header)
},
StyleIndex = style.CellFormatTitleRowId
};
row.AppendChild(cell);
}
return(row);
}
public static MemoryStream Generate(SheetConfiguration[] configurations, ExcelStyle style = null)
{
if (style == null)
{
style = new ExcelStyle();
}
var stream = new MemoryStream();
var spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookpart = spreadSheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorkbookStylesPart to the WorkbookPart
var stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = style.Stylesheet;
stylesPart.Stylesheet.Save();
// Add Sheets to the Workbook.
var sheets = spreadSheet.WorkbookPart.Workbook.
AppendChild(new Sheets());
uint sheetIdCounter = 1;
foreach (var sheetConfiguration in configurations)
{
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// building sheet columns must happen before creating sheet data
BuildSheetColumns(worksheetPart.Worksheet, sheetConfiguration);
worksheetPart.Worksheet.Append(new SheetData());
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//row start
sheetData.AppendChild(GetTitleRow(sheetConfiguration, style));
foreach (var record in sheetConfiguration.Data)
{
var row = new Row();
foreach (var column in sheetConfiguration.Columns)
{
var val = GetRawValue(record, column.PropertyPath);
var cell = BuildCell(val, column);
if (cell != null)
{
cell.StyleIndex = style.CellFormatDefaultId;
row.AppendChild(cell);
}
}
sheetData.AppendChild <Row>(row);
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetIdCounter++,
Name = sheetConfiguration.Name
};
sheets.Append(sheet);
}
workbookpart.Workbook.Save();
spreadSheet.Close();
return(stream);
}
public static MemoryStream Generate(SheetConfiguration configuration, ExcelStyle style = null)
{
return(Generate(new[] { configuration }, style));
}
