OpenXML SDK - How To Create Excel Worksheet
1 // this is the path to your excel document
2 using (var doc = SpreadsheetDocument.Open(“test.xlsx”, true))
3 {
4 var newWorksheetPart = doc.AddNewWorksheet(“New worksheet”);
5 var sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>();
6
7 var row = new Row { RowIndex = 1 };
8 row.AddTextCell(“A”, row.RowIndex, “Hello”);
9 row.AddTextCell(“B”, row.RowIndex, “World”);
10
11 sheetData.AppendChild(row);
12 newWorksheetPart.Worksheet.Save();
13 doc.WorkbookPart.Workbook.Save();
14 }
Wrapper code
8 public static class SpreadsheetDocumentExtensions
9 {
10 public static WorksheetPart AddNewWorksheet(
11 this SpreadsheetDocument doc, string name)
12 {
13 if(null == doc) return null;
14
15 var newWp = doc.WorkbookPart.AddNewPart<WorksheetPart>();
16 var newWs = new Worksheet(new SheetData());
17 newWs.Save(newWp);
18
19 var newWpId = doc.WorkbookPart.GetIdOfPart(newWp);
20 var sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>();
21
22 uint sheetId = 1;
23 if (sheets.Elements<Sheet>().Count() > 0)
24 {
25 sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
26 }
27
28 var sheet = new Sheet
29 {
30 Id = newWpId,
31 SheetId = sheetId, Name = name
32 };
33
34 sheets.Append(sheet);
35 return newWp;
36 }
37
38
39 public static Cell AddTextCell(
40 this Row row, string column, uint index, string text)
41 {
42 if(null == row) return null;
43
44 var cell = new Cell
45 {
46 DataType = CellValues.InlineString,
47 CellReference = column + index
48 };
49
50 cell.AppendChild(new InlineString(new Text { Text = text }));
51 row.AppendChild(cell);
52 return cell;
53 }
54 }
