static void Main()
{
var def = new WorkBookDefinition
{
ColumnsNames = new[] { "ColA", "ColB", "ColC" },
Rows = new[] {
new [] { "A1", "B1", "C1" },
new [] { "A2", "B2", "C2" },
},
SheetName = "MySheetName",
FileName = @"C:\temp\Sample.xlsx"
};
var engine = new ExcelHelper();
engine.Write(def);
}
public void Write(WorkBookDefinition conf)
{
using (var fs = File.Open(conf.FileName, FileMode.OpenOrCreate))
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fs, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
workbookPart.Workbook.AppendChild(new Sheets());
var stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStyleSheet();
stylePart.Stylesheet.Save();
var worksheetPart = InsertWorksheet(document.WorkbookPart, conf.SheetName, conf.Rows.Length, conf);
var rowIdx = 1;
var col = 'A';
foreach (var colName in conf.ColumnsNames)
{
WriteCellContent(col.ToString(), rowIdx, colName, worksheetPart);
col++;
}
rowIdx++;
foreach (var row in conf.Rows)
{
col = 'A';
foreach (var colVal in row)
{
WriteCellContent(col.ToString(), rowIdx, colVal, worksheetPart);
col++;
}
rowIdx++;
}
worksheetPart.Worksheet.Save();
}
}
}
// Given a WorkbookPart, inserts a new worksheet.
private WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName, int linesCount, WorkBookDefinition conf)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Columns columns = new Columns();
columns.Append(conf.ColumnsNames.Select((c, idx) => new Column()
{
Min = (uint)(idx + 1), Max = (uint)(idx + 1),
Width = 10, CustomWidth = true
}));
newWorksheetPart.Worksheet = new Worksheet();
newWorksheetPart.Worksheet.Append(columns);
newWorksheetPart.Worksheet.AppendChild(new SheetData());
var endColLetter = (char)('A' + conf.ColumnsNames.Length);
AutoFilter autoFilter = new AutoFilter()
{
Reference = "A1:" + (endColLetter.ToString()) + linesCount
};
newWorksheetPart.Worksheet.AppendChild(autoFilter);
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(newWorksheetPart);
}
