Kulingar Kulingar - 4 months ago 87
C# Question

Repaired Records : Cell information from worksheet created from scratch

I'm receiving an error when opening my OpenXML created spreadsheet. The error is as follows.

Repaired Records: Cell information from /xl/worksheets/sheet.xml part
Repaired Records: Cell information from /xl/worksheets/sheet2.xml part
Repaired Records: Cell information from /xl/worksheets/sheet3.xml part


The only thing I could find online that was helpful was this issue was the discussion of an algorithm which alters an individual cell multiple times causing the issue. Having said that, I'm going to link my Constructor for the SpreadsheetDocument as well as the three functions for updating a cell (which I do once).

I can supply any additional functions as needed, but I believe the problem is somewhere in the two listed below.

By the way,

GetWorksheetPartByName
InsertCellInWorksheet
GetCell


should all working as intended.

The Actual Program

static void Main(string[] args)
{
//Full path for File
const string newFile = "@C:\test.xlsx";

//Constructor creates default worksheet called "mySheet"
var spreadsheet = new XLSXHelper(newFile);

//updating some cells.
spreadsheet.UpdateCell("mySheet", "D2", "R", 2);
}


Constructor

public XLSXHelper(string filepath)
{
newFile = filepath;
spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
this.workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
this.worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}


Update Cell

public void UpdateCell(string worksheetName, string textToInsert, string columnName, uint rowIndex)
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(newFile, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, worksheetName);
if (worksheetPart != null)
{
InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
Cell cell = GetCell(worksheetPart.Worksheet,columnName, rowIndex);
cell.CellValue = new CellValue(textToInsert);
worksheetPart.Worksheet.Save();
}
}
}

Answer

If you are adding a string to a cell rather than a number (or a string that can be converted to a number) then you should use an inline string or a shared string instead of the CellValue. You can only use CellValue if the value is numeric.

The XML generated when using CellValue looks something like:

<x:row>
  <x:c>
    <x:v>12345</x:v>
  </x:c>
</x:row>

when you use an inline string it looks like:

<x:row>
  <x:c t="inlineStr">
    <x:is>
      <x:t>Foo</x:t>
    </x:is>
  </x:c>
</x:row>

note the "is" node for inline string and that the cell type attribute is set to "inlineStr".

Here is C# code to generate correct XML for a cell containing text:

cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString() { Text = new Text(textToInsert) };

From what I have read using shared strings is preferable but using inline strings avoids the error and looks just fine when you open the file in Excel.