SheetData sheetData = new SheetData();
Row headers = new Row();
headers.Append(new Cell
{
CellValue = new CellValue("Losses"), DataType = CellValues.String, StyleIndex = 2U,
CellReference = new StringValue("A1")
});
headers.Append(new Cell
{
CellValue = new CellValue("Target"), DataType = CellValues.String, StyleIndex = 2U,
CellReference = new StringValue("F1"),
});
sheetData.Append(headers);
// creating headers in one row
sheetData.Append(CreateHeaderRowForExcel(type));
byte level = 1;
foreach (MaterialLossDto obj in data)
{
sheetData.Append(AddRowsRecursion(obj, ref level, sheetData));
}
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell() {Reference = new StringValue("A1:D1")});
mergeCells.Append(new MergeCell() {Reference = new StringValue("F1:G1")});
sheetData.Append(mergeCells);
i create workSheet and step by step fill data in row. when i create headers, i want create one long header with merged cells. but it doesn't work for me. all rows are filled and my long header contains only one cell.
solution is not so obvious. creating merged cells need after all manipulations with WorkSheet. here is all my code
public class ReportLoaderBase<T> : IReportLoader<T> where T : class, IReportDto
{
private const string WorksheetPartId = "partId1";
private protected List<KeyValuePair<string, string>> cellsPair;
public string Download(ReportTypeGeneric<T> genericModel, string fileName)
{
string outputFilePath = $"{FileServiceHelper.OutputDirectory}\\{fileName}";
if (!Directory.Exists(FileServiceHelper.OutputDirectory))
Directory.CreateDirectory(FileServiceHelper.OutputDirectory);
CreateExcelFile(genericModel, outputFilePath);
return outputFilePath;
}
private void CreateExcelFile(ReportTypeGeneric<T> model, string outputFilePath)
{
using SpreadsheetDocument excelFile =
SpreadsheetDocument.Create(outputFilePath, SpreadsheetDocumentType.Workbook);
CreatePartsForExcel(excelFile, model);
}
private protected virtual void CreatePartsForExcel(SpreadsheetDocument excelFile,
ReportTypeGeneric<T> model)
{
SheetData sheetData = GenerateSheetDataForDetails(model.Elements, model.ElementsType);
ContinueCreatingPartsForExcel(excelFile, sheetData);
}
private protected void ContinueCreatingPartsForExcel(SpreadsheetDocument excelFile, SheetData sheetData)
{
WorkbookPart workbookPart = excelFile.AddWorkbookPart();
GenerateWorkbookPartContent(workbookPart);
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPartContentNew(workbookStylesPart);
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(WorksheetPartId);
GenerateWorksheetPartContent(worksheetPart, sheetData);
}
private protected virtual SheetData GenerateSheetDataForDetails(List<T> data, Type type)
{
SheetData sheetData = new SheetData();
// creating headers in one row
sheetData.Append(CreateHeaderRowForExcel(type));
foreach (var obj in data)
{
// generate values
Row rows = GenerateRowForChildPartDetail(obj);
sheetData.Append(rows);
}
return sheetData;
}
private protected virtual Row CreateHeaderRowForExcel(Type type)
{
Row row = new Row();
foreach (PropertyInfo propertyInfo in type.GetProperties())
{
if (propertyInfo.PropertyType.IsListType())
continue;
// 2U for header
Cell cell = CreateCell(propertyInfo.Name.Replace('_', ' '), 2U);
row.Append(cell);
}
return row;
}
private Cell CreateCell(string text)
{
Cell cell = new Cell();
cell.StyleIndex = 1U;
cell.DataType = ResolveCellDataTypeOnValue(text);
cell.CellValue = new CellValue(text);
return cell;
}
private protected Cell CreateCell(string text, uint styleIndex)
{
Cell cell = new Cell();
cell.StyleIndex = styleIndex;
cell.DataType = ResolveCellDataTypeOnValue(text);
cell.CellValue = new CellValue(text);
return cell;
}
private EnumValue<CellValues> ResolveCellDataTypeOnValue(string text)
{
int intVal;
double doubleVal;
if (int.TryParse(text, out intVal) || double.TryParse(text, out doubleVal))
{
return CellValues.Number;
}
return CellValues.String;
}
private protected virtual Row GenerateRowForChildPartDetail(object model)
{
Row row = new Row();
Type type = model.GetType();
foreach (PropertyInfo propertyInfo in type.GetProperties())
{
if (propertyInfo.PropertyType.IsListType())
continue;
var value = propertyInfo.GetValue(model);
// 1U for text
row.Append(CreateCell(value?.ToString() ?? "", 1U));
}
return row;
}
private void GenerateWorkbookPartContent(WorkbookPart workbookPart)
{
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {Name = "Sheet1", SheetId = 1, Id = WorksheetPartId};
sheets.Append(sheet);
workbook.Append(sheets);
workbookPart.Workbook = workbook;
}
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, SheetData sheetData)
{
Worksheet worksheet = new Worksheet
{
MCAttributes = new MarkupCompatibilityAttributes
{
Ignorable = "x14ac"
}
};
// configurations
worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension = new SheetDimension {Reference = "A1"};
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView {TabSelected = true, WorkbookViewId = 0U};
Selection selection = new Selection
{
ActiveCell = "A1",
SequenceOfReferences = new ListValue<StringValue>
{
InnerText = "A1"
}
};
sheetView.Append(selection);
sheetViews.Append(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties
{
DefaultRowHeight = 15D,
DyDescent = 0.25D
};
PageMargins pageMargins = new PageMargins
{
Left = 0.7D,
Right = 0.7D,
Top = 0.75D,
Bottom = 0.75D,
Header = 0.3D,
Footer = 0.3D
};
worksheet.Append(sheetDimension);
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
worksheet.Append(sheetData);
worksheet.Append(pageMargins);
worksheetPart.Worksheet = worksheet;
MergeCells(worksheetPart);
}
private void MergeCells(WorksheetPart worksheetPart)
{
if (cellsPair == null)
return;
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
foreach (KeyValuePair<string, string> pair in cellsPair)
{
mergeCells.Append(new MergeCell() {Reference = new StringValue($"{pair.Key}:{pair.Value}")});
}
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
}
private void GenerateWorkbookStylesPartContentNew(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet(new Fonts(
new Font(new FontSize() {Val = 14}, new Color() {Rgb = new HexBinaryValue() {Value = "000000"}},
new FontName() {Val = "Times New Roman"}),
new Font(new FontSize() {Val = 14}, new Color() {Rgb = new HexBinaryValue() {Value = "000000"}},
new FontName() {Val = "Times New Roman"}), new Font(new Bold(), new FontSize() {Val = 11},
new Color() {Rgb = new HexBinaryValue() {Value = "000000"}},
new FontName() {Val = "Times New Roman"})
),
new Fills(
new Fill(
new PatternFill()
{
PatternType = PatternValues.None
}),
new Fill(
new PatternFill()
{
PatternType = PatternValues.None
}),
new Fill(
new PatternFill(new ForegroundColor()
{
// change color
Rgb = new HexBinaryValue() {Value = "FFFFAAAA"}
})
{
PatternType = PatternValues.Solid
})
),
new Borders(
new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(),
new DiagonalBorder()),
new Border(new LeftBorder(new Color() {Auto = true}) {Style = BorderStyleValues.Medium},
new RightBorder(new Color() {Indexed = (UInt32Value) 64U}) {Style = BorderStyleValues.Medium},
new TopBorder(new Color() {Auto = true}) {Style = BorderStyleValues.Medium},
new BottomBorder(new Color() {Indexed = (UInt32Value) 64U}) {Style = BorderStyleValues.Medium},
new DiagonalBorder())
),
new CellFormats(new CellFormat() {FontId = 0, FillId = 0, BorderId = 0, ApplyFont = true},
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center,
WrapText = true
}) {FontId = 1, FillId = 0, BorderId = 1, ApplyFont = true},
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center,
WrapText = true
}) {FontId = 2, FillId = 2, BorderId = 1, ApplyFont = true})
);
workbookStylesPart.Stylesheet = stylesheet;
}
}
}