private void AppendDataToExtendedSheet(Worksheet worksheet, SheetData sheetData,
ExcelData data)
{
UInt32 rowIdex = 0;
Row row;
MergeCells mergeCells = new MergeCells();
// Add sheet data
foreach (var rowData in data.Cells)
{
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var excelCell in rowData)
{
var cell = CreateCell(ColumnLetter(excelCell.ColumnIndex),
(uint)(excelCell.RowIndex + 1), excelCell.Content ?? string.Empty);
row.AppendChild(cell);
if (excelCell.ColSpan > 1 || excelCell.RowSpan > 1)
{
var mergeCell = CreateMergeCell(excelCell);
mergeCells.Append(mergeCell);
}
}
}
worksheet.InsertAfter(mergeCells, worksheet.Elements <SheetData>().First());
}
public byte[] GenerateExcel <T>(IGridColumnCollection <T> columns, IEnumerable <T> items)
{
ExcelData excelData = new ExcelData();
excelData.SheetName = Strings.Items;
var header = new List <ExcelCell>();
foreach (IGridColumn column in columns)
{
if (!column.Hidden)
{
header.Add(new ExcelCell(column.Title));
}
}
excelData.Cells.Add(header);
foreach (var item in items)
{
List <ExcelCell> row = new List <ExcelCell>();
foreach (IGridColumn column in columns)
{
if (!column.Hidden)
{
var cell = column.GetCell(item) as GridCell;
cell.Encode = false;
row.Add(new ExcelCell(cell.ToString()));
}
}
excelData.Cells.Add(row);
}
return(GenerateExcel(excelData));
}
private void AppendDataToSheet(SheetData sheetData, ExcelData data)
{
UInt32 rowIdex = 0;
Row row;
var cellIdex = 0;
// Add sheet data
foreach (var rowData in data.Cells)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var cellData in rowData)
{
var cell = CreateCell(ColumnLetter(cellIdex++), rowIdex,
cellData.Content ?? string.Empty);
row.AppendChild(cell);
}
}
}
public byte[] GenerateExcel(ExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
if (data.ExtendedSheet)
{
AppendDataToExtendedSheet(worksheetPart.Worksheet, sheetData, data);
}
else
{
AppendDataToSheet(sheetData, data);
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
