Repaired Records : Cell information from worksheet created from scratch

Question

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

Chondro

The error occurred in my code, using EPPlus, when I created a worksheet that has either a too long name and/or contained forbidden characters. The solution was to use a short, valid name for the worksheet. – Sidney

Answer 1

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.

Hambrick

Helped a lot thanks! Is there documentation on all the available types? Searched google but haven't come up with much... – Chud

Check out msdn.microsoft.com/en-us/library/office/… – Hambrick

Worked for me too. – Scarabaeoid

Ahh yes this removed that error away when opening the excel! Thank you so much!!!! – Upmost

This was my case. Thank you! I hated working with it. – Prognosticate

Answer 2

My issue was that I had been setting the name of the worksheet with a name that had a forward slash / in it.

Filicide

Thnx, came here from searching on the error from a file generated with help of SyncFusion lib, and was helped by this. For me the problem was "[]" in the name, that excel wanted to change to "()". – Kosher

I just want to note that this also fixed the issue when I got a slightly different error than the question originally states. "Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)" It wasn't an illegal character, but the Workbook name was too long. – Dupondius

Answer 3

Another late one - but check how you're adding cells to a row. Have you cut and paste add cell code in which there's a 'simple' compare with the existing cell reference (A1 etc) in the row? In which case if you have a cell beyond column Z - AA1 onwards - then you might end up trying to insert cell (eg) AB1) before cell B1. You'll then get this error on opening the written sheet in excel. Instead, if simply adding cell after cell along each row, just go straight to insert before with the reference cell set to null - ie. add new cell to end.

Hope that makes sense.

Coons

Answer 4

In my case, I was trying to set the data type as a date. on MSDN date is only available in Office2010. so i changed it back to string and my errors gone.

I changed:

cell.DataType = CellValues.Date;

to:

cell.DataType = CellValues.String;

Temper

Answer 5

The problem with me was that my application has an export module, and exported file from one environment was working fine, however from other it was not. It was the same error:

Repaired Records: Cell information from /xl/worksheets/sheet19.xml part

After doing a deep investigation, I found out that for one of the records in this sheet exceeding the cell limit of 32767 chars (excel limitation) and by reducing the size of texts on DB directly, solved the problem.

Coolish

This wasn't my exact problem, but it lead to understanding. In my case the issue was due to a decimal format that was too large for Excel. Thank you! – Irremovable

Answer 6

Difference in my case was it could not repair the file.

Issue was row index which I passed to InsertCellInWorksheet taken from example on MSDN.

Cell cell = InsertCellInWorksheet("A", lastRow.RowIndex, worksheetPart);

When inserting a row, make sure the index of the row is 1, not 0. However, 0 for the SheetData collection.

sheetData.InsertAt(new Row() { RowIndex = 1 }, 0);
lastRow = sheetData.Elements<Row>().LastOrDefault();

Interestingly enough, my integration test passed, however, when opening the file via Excel, it was showing the pop-up about corruption. Seems there is a row with index 0, however, bit secretive as you will not be able to open a file with Excel.

Clasping

Answer 7

I was facing this issue because I was not using the correct workbook object to write to the FileOutputStream.

Gorizia

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. – Contention

@mrun: Why do you think that this does not provide an answer to the question? This is one of the possible solutions of the problem mentioned in OP. – Gorizia

Answer 8

When you try to open an Excel file, if it says "Excel cannot open file", it means the file has been corrupted (cell values has been disturbed). In my case, when I removed all the styles (borders) which I have applied in the code, the file has opened properly without throwing any error.

Clairvoyance

Answer 9

In my case, I have a column with number and when filling data in this one, I get some data with NaN, so excel give me that error. My advice is set filter in each column first and pay attention about some special data

Hostetler

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Shoop

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Oppugn

I just give some advice to deal with the problem. thanks @@ – Hostetler

Answer 10

In my case, the SSRS place holder properties had custom number.

Fix:

1. GO to all the fields one by one in SSRS designer.
2. See if there are any alpha numeric values coming from backend. ( cross compare your data set with all the cells in the SSRS report.
3. double click on the field -> go to 'Number' tab
4. Click on the default -> ok

Thanks Umakanth Nelige

Stryker

Answer 11

In my case, some of the cells have double values which are infinity. Excel gives the unreadable content error. The Open XML productivity tool doesn't detect the error so it can be hard to detect the cause. If there is possibility of cell values to be infinity, the value should be converted to string before assigning the cellValue.

Hypsometry

Answer 12

I was having the same issue, if your values are being stored as shared strings you need to create a new id (index) in the Shared string table.

The implementation for the InsertSharedStringItem function is here: https://learn.microsoft.com/en-us/office/open-xml/working-with-the-shared-string-table.

The implementation for the InsertCellInWorksheet is here: https://learn.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet#sample-code

Those are sample functions so update them as needed.

var sstid = InsertSharedStringItem(textToInsert, sharedStringTable);
Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
cell.CellValue = new CellValue($"{sstid}");
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();

Purslane

Answer 13

In my situation, was the serialization of decimal numbers. We use decimal comma ",", but in xml of the excel file it always uses US (so point ".") regardless current culture info.

Solution is to use package 2.13.001-beta at least which solves that issue.

Eelgrass

Answer 14

I had something similar issue to Dinisha Surisetty. I remove the StyleIndex for my Cell and the excel opened with no warnings at all. Here I am sharing the working code to create a cell:

private Cell CreateCell(string text)
{
    Cell cell = new Cell();
    // cell.StyleIndex = 1U;  //After commenting this line, It worked.
    cell.DataType = ResolveCellDataTypeOnValue(text);
    if (cell.DataType == CellValues.Number)
    {
        cell.CellValue = new CellValue(text);
    }
    else
    {
        cell.InlineString = new InlineString() { Text = new Text(text) };
    }
    return cell;
}

Golightly

Answer 15

Probably already solved but adding my solution as I was getting the exact same error.

My error:

Repaired Part: /xl/worksheets/sheet1.xml part with XML error. HRESULT 0x8000ffff Line 1, column 0

The problem was for me in DataValidation, on some conditions I add data validations on a row. In a certain case I set up data validation and added an empty one. This causes that error too.

So in below (example) code the problem was 'addFormulaToRow3' was false and that caused an empty dataValidations object to be added.

DataValidations dataValidations = new DataValidations();
bool anyCreated = false; // set to true if any validations are added to dataValidations

...
if(addFormulaToRow3)
{
    ...
    anyCreated = true; // set to true as we adding validations
    dataValidation.Append(formula);
    dataValidations.Append(dataValidation);

}

...

if (anyCreated)
{
    mySheetPart.Worksheet.AppendChild(dataValidations);
}

Predisposition