open xml 2.0 - prompting for repair on opening excel

Question

Hello,

I encountered a strange behavior while using OpenXML 2.0.

Based on the MSDN sample (http://msdn.microsoft.com/en-us/library/dd452407(office.12).aspx), I created a wrapper class that

   (a) opens an existing excel file, 
   (b) writes data to cell and
   (c) closes it.

I can successfully create excel. But, under certain condition, it fails and displays an error dialog as below:

“Excel found unreadable content in 'new.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click “Yes”.”

When I try to recover excel by selecting “Yes”, the entire content is distorted.

Here are the cases:
   1. If I’m writing row-wise. Say, I’m writing the first line from A1-Z1 and then A2-Z2 and so on. In that case, I can successfully open excel file without any issues.
   2. If I’m writing column-wise (This is the place where exception starts), then I can successfully write the first column data i.e., say A1-A100. But, when I try to write in B1, the code executes successfully. But, opening excel will always throw above stated error.

Here is the code-block for those 2 cases:

OpenXMLAPI oxml = new OpenXMLAPI();
            oxml.Filename = path;
            oxml.SheetName = "Sheet1";
            oxml.Open();
           
            // Case 1: Insert data row-wise (not issues in reading)
            string cellValue = string.Empty;
            string address = string.Empty;

            int alphabet, row = 1, col = 1;

            for (row = 1; row <= 100; row++)
            {
                alphabet = 65;  // Start with 'A'

                for (col = 1; col <= 26; col++)
                {
                    address = string.Format("{0}{1}", (char)alphabet, row);
                    cellValue = "Cell_Value_" + address;

                    oxml.SetCellValue(address, cellValue, 0, true);

                    alphabet++;
                }
            }

            // Case 2: Insert data column wise
            oxml.SetCellValue("A1", "CELL_VALUE_A1", 0, true);
            oxml.SetCellValue("A2", "CELL_VALUE_A2", 0, true);
            // Can successfully set cell value upto A(n), n'th row of a column

            /* 
             * Uncommenting "B1", "B10", "C1" or any other column can cause corruption
             */
            //oxml.SetCellValue("B1", "CELL_VALUE_B1", 0, true);    // <-- Error
            //oxml.SetCellValue("B10", "CELL_VALUE_B10", 0, true);  // <-- Error
            //oxml.SetCellValue("C1", "CELL_VALUE_C1", 0, true);    // <-- Error
            
            oxml.Close();

Note: I’m not getting any exception via code. But, exception (recovery alert) is generated only when I open the excel file.

Here is the code-block for wrapper:

// Opens the spreadsheet
public void Open()
        {
            //Open the copied template workbook. 
            _oXLDocument = SpreadsheetDocument.Open(this.Filename, true);

            //Access the main Workbook part, which contains all references.
            WorkbookPart workbookPart = _oXLDocument.WorkbookPart;

            Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == this.SheetName).FirstOrDefault();
            if (sheet != null)
            {
                _oXLWSheet = ((WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;
            }
            else
            {
                throw new Exception("The specified sheet name:" + this.SheetName + " not found");
            }

            sheetData = _oXLWSheet.GetFirstChild<SheetData>();
                    }

// Sets cell value against an address
public void SetCellValue(string address, object value, UInt32Value styleIndex, bool isNonNumeric)
        {
            // Create Cell
            Cell c = new Cell();
            CellValue cv = new CellValue();
            InlineString inlineStr = new InlineString();
            Text t;
            
            c.CellReference = address;

            if (isNonNumeric)
            {
                c.DataType = CellValues.InlineString;

                //c.StyleIndex = styleIndex;

                inlineStr = new InlineString();
                t = new Text();
                t.Text = value.ToString();
                inlineStr.AppendChild(t);
                c.AppendChild(inlineStr);
            }
            else
            {
                c.DataType = CellValues.Number;

                cv = new CellValue();
                cv.Text = value.ToString();
                c.AppendChild(cv);
            }

            // Create Row
            Row r = new Row();
            r.RowIndex = GetRowIndex(address);
            r.Append(c);

            sheetData.Append(r);
        }

// Returns row index of an address
private UInt32 GetRowIndex(string address)
        {
            string rowPart;
            UInt32 l;
            UInt32 result = 0;

            for (int i = 0; i < address.Length; i++)
            {
                if (UInt32.TryParse(address.Substring(i, 1), out l))
                {
                    rowPart = address.Substring(i, address.Length - i);
                    if (UInt32.TryParse(rowPart, out l))
                    {
                        result = l;
                        break;
                    }
                }
            }
            return result;
        }

// Save and close the spreadsheet
public void Close()
        {
            if (_oXLWSheet != null)
                _oXLWSheet.Save();

            _oXLDocument.Close();
            _oXLDocument.Dispose();
        }

Does anyone had encountered the same issue before? I'm only 4 days old with OpenXML.

Any help will be appreciated.
Thanks,

---

Sometimes, Difficult things are possible... www.cametoofar.com

Answer 1

Hi abhilashca,

Thanks for posting in the MSDN Forum.

You need to analyze the cell address to found out whether there exists the row which have same row postion.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hello Tom,

Thanks for pointing out the glitch. I'll re-work my wrapper and will try to fix it.

Thanks.

---

Sometimes, Difficult things are possible... www.cametoofar.com

Answer 3

You need to analyze the cell address to found out whether there exists the row which have same row postion.

Hello Tom,

Can you please help how to analyse whether the row exists in the same row position for a cell address?

Thanks.

---

Sometimes, Difficult things are possible... www.cametoofar.com

Answer 4

Hi abhilashca,

You need access cellreference of the cell instance to see where they have same row number.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 5

You need access cellreference of the cell instance to see where they have same row number.

Hello Tom,

Can you please help me with the sample code? I'm almost unlucky here.

Thanks.

---

Sometimes, Difficult things are possible... www.cametoofar.com