Inserting a string array as a row into an Excel document using the Open XML SDK 2.0
- by Sam
The code runs, but corrupts my excel document. Any help would be mucho appreciated! I used this as a reference.
public void AddRow(string fileName, string[] values) {
using (SpreadsheetDocument doc =
SpreadsheetDocument.Open(fileName, true)) {
SharedStringTablePart sharedStringPart =
GetSharedStringPart(doc);
WorksheetPart worksheetPart =
doc.WorkbookPart.WorksheetParts.First();
uint rowIdx = AppendRow(worksheetPart);
for (int i = 0; i < values.Length; ++i) {
int stringIdx = InsertSharedString(values[i],
sharedStringPart);
Cell cell = InsertCell(i, rowIdx, worksheetPart);
cell.CellValue = new CellValue(stringIdx.ToString());
cell.DataType = new EnumValue<CellValues>(
CellValues.SharedString);
worksheetPart.Worksheet.Save();
}
}
}
private SharedStringTablePart GetSharedStringPart(
SpreadsheetDocument doc) {
if (doc.WorkbookPart.
GetPartsCountOfType<SharedStringTablePart>() > 0)
return doc.WorkbookPart.
GetPartsOfType<SharedStringTablePart>().First();
else
return doc.WorkbookPart.
AddNewPart<SharedStringTablePart>();
}
private uint AppendRow(WorksheetPart worksheetPart) {
SheetData sheetData = worksheetPart.Worksheet.
GetFirstChild<SheetData>();
uint rowIndex = (uint)sheetData.Elements<Row>().Count();
Row row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
return rowIndex;
}
private int InsertSharedString(string s,
SharedStringTablePart sharedStringPart) {
if (sharedStringPart.SharedStringTable == null)
sharedStringPart.SharedStringTable =
new SharedStringTable();
int i = 0;
foreach (SharedStringItem item in
sharedStringPart.SharedStringTable.
Elements<SharedStringItem>()) {
if (item.InnerText == s)
return i;
++i;
}
sharedStringPart.SharedStringTable.AppendChild(
new Text(s));
sharedStringPart.SharedStringTable.Save();
return i;
}
private Cell InsertCell(int i, uint rowIdx,
WorksheetPart worksheetPart) {
SheetData sheetData = worksheetPart.Worksheet.
GetFirstChild<SheetData>();
string cellReference = AlphabetMap.Instance[i] + rowIdx;
Cell cell = new Cell() { CellReference = cellReference };
Row row = sheetData.Elements<Row>().ElementAt((int)rowIdx);
row.InsertAt(cell, i);
worksheetPart.Worksheet.Save();
return cell;
}
