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); }