I'm having an issue with the DocumentFormat.OpenXml NuGet package. Although the CloseDocument() function shows that the data is getting into the excel workbook, when I try to load it up into Excel, it throws an error about the worksheet being full of errors and needing to be deleted. I've looked at multiple tutorials and other posts about issues with creating excel files, but none have helped resolve this issue.
Creating Excel File:
Excel NewExcel = new Excel(FileName);
NewExcel.CreateSheet("Test Sheet");
NewExcel.PrepareColumn(1, 80, true, 55.5);
NewExcel.CreateRow(20.2);
NewExcel.SetCell("Test 1", 1, "A");
NewExcel.SetCell(new DateTime(2010, 7, 14), 1, "B");
NewExcel.FinishSheet();
NewExcel.SaveFile();
NewExcel.CloseDocument();
Error:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error133640_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\Frasher Gray\Documents\NsPaintRequest-20240702.xlsx'</summary><removedRecords><removedRecord>Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)</removedRecord></removedRecords></recoveryLog>
Excel File Creation:
public class Excel
{
public string FileName { get; set; }
private SpreadsheetDocument ExcelWorkbook;
private WorksheetPart? CurrentWorksheet;
private Columns ColumnsInProgress;
private List<Row> RowsInProgress;
public Excel(string FileName = "Default File Name")
{
this.FileName = FileName;
ExcelWorkbook = SpreadsheetDocument.Create($"C://Users/Frasher Gray/Documents/{FileName}.xlsx", SpreadsheetDocumentType.Workbook);
ExcelWorkbook.AddWorkbookPart();
ExcelWorkbook.WorkbookPart.Workbook = new Workbook();
ColumnsInProgress = new Columns();
RowsInProgress = new List<Row>();
}
public void PrepareColumn(int FirstColumnAffected, int LastColumnAffected, bool UseCustomWidth = false, double CustomWidth = 0)
{
if (UseCustomWidth)
ColumnsInProgress.Append(new Column() { Min = new UInt32Value((uint)FirstColumnAffected), Max = new UInt32Value((uint)LastColumnAffected), CustomWidth = true, Width = new DoubleValue(CustomWidth) });
else
ColumnsInProgress.Append(new Column() { Min = new UInt32Value((uint)FirstColumnAffected), Max = new UInt32Value((uint)LastColumnAffected) });
}
public void CreateRow(double? CustomHeight = null)
{
SheetData SheetData = CurrentWorksheet.Worksheet.GetFirstChild<SheetData>() ?? CurrentWorksheet.Worksheet.AppendChild(new SheetData());
Row NewRow = new Row() { RowIndex = new UInt32Value((uint)SheetData.Count() + 1) };
if (CustomHeight != null)
NewRow.Height = CustomHeight;
RowsInProgress.Add(NewRow);
SheetData.Append(NewRow);
}
public void SetCell<T>(T CellValue, int Row, string Column)
{
Cell NewCell = new Cell() { CellReference = $"{Column}{Row}" };
if (CellValue.GetType() == typeof(string))
{
NewCell.DataType = new EnumValue<CellValues>(CellValues.String);
NewCell.CellValue = new CellValue(CellValue.ToString() ?? string.Empty);
}
else if (CellValue.GetType() == typeof(DateTime))
{
NewCell.DataType = new EnumValue<CellValues>(CellValues.Date);
NewCell.CellValue = new CellValue(Convert.ToDateTime(CellValue));
}
else
{
NewCell.DataType = new EnumValue<CellValues>(CellValues.Number);
NewCell.CellValue = new CellValue(Convert.ToDouble(CellValue));
}
if (RowsInProgress[Row - 1].Elements<Cell>().Count() > 0)
{
RowsInProgress[Row - 1].InsertAfter(NewCell, RowsInProgress[Row - 1].Elements<Cell>().Last());
}
else
{
RowsInProgress[Row - 1].AddChild(NewCell);
}
CurrentWorksheet.Worksheet.Save();
}
public void CreateSheet(string SheetName)
{
CurrentWorksheet = ExcelWorkbook.WorkbookPart.AddNewPart<WorksheetPart>();
CurrentWorksheet.Worksheet = new Worksheet(new SheetData());
Sheets Sheets = ExcelWorkbook.WorkbookPart.Workbook.GetFirstChild<Sheets>() ?? ExcelWorkbook.WorkbookPart.Workbook.AppendChild(new Sheets());
uint SheetId = (uint)(Sheets.Elements<Sheet>().Count() + 1);
Sheet Sheet = new Sheet() { Id = ExcelWorkbook.GetIdOfPart(ExcelWorkbook.WorkbookPart), SheetId = SheetId, Name = SheetName };
Sheets.Append(Sheet);
}
public void FinishSheet()
{
CurrentWorksheet.Worksheet.InsertAt(ColumnsInProgress, 0);
ColumnsInProgress = new Columns();
RowsInProgress.Clear();
CurrentWorksheet.Worksheet.Save();
}
public void SaveFile()
{
ExcelWorkbook.WorkbookPart.Workbook.Save();
}
public void CloseDocument()
{
foreach (Row R in ExcelWorkbook.WorkbookPart.WorksheetParts.FirstOrDefault().Worksheet.GetFirstChild<SheetData>())
{
foreach (Cell P in R.ChildElements)
{
Console.WriteLine(P.InnerText + " " + P.CellReference);
}
}
ExcelWorkbook.Dispose();
}
}
Adding in validation code I borrowed from this blogpost into the CloseDocument() function, extra error information is given:
There are 1 validation errors with document
Description: The relationship 'R2c7e7002bf544f0d' referenced by attribute 'http://schemas.openxmlformats.org/officeDocument/2006/relationships:id' does not exist.
ErrorType: Semantic
Node: DocumentFormat.OpenXml.Spreadsheet.Sheet
Path: /x:workbook[1]/x:sheets[1]/x:sheet[1]
Part: /xl/workbook.xml
I tried removing the ColumnsInProgress = new Columns() call after finishing the sheet, but that didn't change anything. I'm not knowledgeable enough to truly understand what the error means, unfortunately.
After cutting down on as many function calls as I could to see where the reference error starting appearing, I discovered that I was assigning the Id of the sheet completely incorrectly. It should be as so:
Sheet Sheet = new Sheet() { Id = ExcelWorkbook.WorkbookPart.GetIdOfPart(CurrentWorksheet), SheetId = SheetId, Name = SheetName };