Open XML SDK 2.0 xlsx cell hyperlink works in Excel 2010 but in Excel 2007 there is an error

Question

Hello,

I have created myself with the Open XML SDK v2.0.5022.0 one xlsx Excel document that contains links to image files. Xlsx document that I can open in Excel 2010 with no problems and the links work wonderfully, as it should be.

I open the document in Excel 2007, I get the error:

"From Excel unreadable content in DateiXY.xlsx was a template. Want to restore the contents of the workbook?"

Then comes the message:

log file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
	<logFileName>error045280_01.xml</logFileName>
	<summary>Fehler in Datei 'E:\Series.xlsx'</summary>
	<additionalInfo>
		<info>Excel hat die Überprüfung und Reparatur auf Dateiebene abgeschlossen. Einige Teile dieser Arbeitsmappe wurden repariert oder verworfen.</info>
	</additionalInfo>
	<repairedParts summary="Die folgenden Reparaturen wurden durchgeführt:_x000d__x000a__x000d__x000a_">
		<repairedPart>Reparierter Teil: Teil /xl/worksheets/sheet.xml.</repairedPart>
	</repairedParts>
	<removedFeatures summary="Folgende Features wurden entfernt:">
		<removedFeature>Entferntes Feature: Hyperlinks von /xl/worksheets/sheet.xml-Part</removedFeature>
	</removedFeatures>
</recoveryLog>

C# Code:

// --------------------------------------------------------------------------------------------------------------------
// <copyright file="Class1.cs" company="Private">
//   ©  2012 Private
// </copyright>
// <summary>
//   Class to help to build an Xlsx File
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace DatenExportModul.Excel
{
    using System;
    using System.IO;
    using System.Linq;

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;

    using global::Konfiguration.Export;

    /// <summary>
    /// Class to help to build an Xlsx File
    /// </summary>
    public static class XlsxFormat
    {
        /// <summary>
        /// Creates the workbook
        /// </summary>
        /// <param name="fileName">Filename of the workbook</param>
        /// <returns>Spreadsheet created</returns>
        private static SpreadsheetDocument CreateWorkbook(string fileName)
        {
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false);

            spreadSheet.AddWorkbookPart();
            spreadSheet.WorkbookPart.Workbook = new Workbook();
            spreadSheet.WorkbookPart.Workbook.Save();

            SharedStringTablePart sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringTablePart.SharedStringTable = new SharedStringTable();
            sharedStringTablePart.SharedStringTable.Save();

            spreadSheet.WorkbookPart.Workbook.Sheets = new Sheets();
            spreadSheet.WorkbookPart.Workbook.Save();

            WorkbookStylesPart workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            workbookStylesPart.Stylesheet = new Stylesheet();
            workbookStylesPart.Stylesheet.Save();

            return spreadSheet;
        }

        /// <summary>
        /// Load the workbook
        /// </summary>
        /// <param name="fileName">Filename of the workbook</param>
        /// <returns>Spreadsheet loaded</returns>
        private static SpreadsheetDocument OpenWorkbook(string fileName)
        {
            return SpreadsheetDocument.Open(fileName, true);
        }

        /// <summary>
        /// Adds a new worksheet to the workbook
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="name">Name of the worksheet</param>
        /// <returns>True if succesful</returns>
        private static bool AddWorksheet(SpreadsheetDocument spreadsheet, string name)
        {
            Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            Sheet sheet;
            WorksheetPart worksheetPart;

            worksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());
            worksheetPart.Worksheet.Save();

            sheet = new Sheet
            {
                Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
                Name = name
            };
            sheets.Append(sheet);

            spreadsheet.WorkbookPart.Workbook.Save();

            return true;
        }

        /// <summary>
        /// Get the first worksheet element
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <returns>First Worksheet</returns>
        private static Worksheet FirstWorksheet(SpreadsheetDocument spreadsheet)
        {
            return spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
        }

        /// <summary>
        /// Adds the basic styles to the workbook
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <returns>True if succesful</returns>
        private static bool AddBasicStyles(SpreadsheetDocument spreadsheet)
        {
            Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            stylesheet.InsertAt(new NumberingFormats(), 0);

            stylesheet.InsertAt(new Fonts(), 1);
            stylesheet.GetFirstChild<Fonts>().InsertAt<Font>(new Font
            {
                FontSize = new FontSize { Val = 11 },
                FontName = new FontName { Val = "Calibri" }
            }, 0);

            stylesheet.GetFirstChild<Fonts>().InsertAt<Font>(new Font
            {
                FontSize = new FontSize { Val = 11 },
                FontName = new FontName { Val = "Calibri" },
                Color = new Color { Rgb = HexBinaryValue.FromString("FFF00000") }
            }, 1);

            stylesheet.InsertAt(new Fills(), 2);
            stylesheet.GetFirstChild<Fills>().InsertAt<Fill>(new Fill
            {
                PatternFill = new PatternFill { PatternType = new EnumValue<PatternValues> { Value = PatternValues.None } }
            }, 0);


            // Dummy
            stylesheet.GetFirstChild<Fills>().InsertAt<Fill>(new Fill
            {
                PatternFill = new PatternFill { PatternType = new EnumValue<PatternValues> { Value = PatternValues.None } }
            }, 1);

            stylesheet.InsertAt(new Borders(), 3);
            stylesheet.GetFirstChild<Borders>().InsertAt<Border>(new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            }, 0);

            stylesheet.InsertAt(new CellStyleFormats(), 4);
            stylesheet.GetFirstChild<CellStyleFormats>().InsertAt<CellFormat>(new CellFormat
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
            }, 0);

            stylesheet.InsertAt(new CellFormats(), 5);

            stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(new CellFormat
            {
                FormatId = 0,
                NumberFormatId = 0
            }, 0);

            stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(new CellFormat
            {
                FormatId = 0,
                NumberFormatId = 0,
                FontId = 1,
                FillId = 0,
                BorderId = 0
            }, 1);

            stylesheet.Save();

            return true;
        }

        /// <summary>
        /// Adds a cell style foreground color
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="rgbColor">Color as rgb hex code</param>
        /// <param name="save">Save the shared string table</param>
        /// <returns>Cell style index</returns>
        private static int AddCellStylesForegroundColor(SpreadsheetDocument spreadsheet, HexBinaryValue rgbColor, bool save)
        {
            Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            Fill fill = new Fill
            {
                PatternFill = new PatternFill
                {
                    PatternType = new EnumValue<PatternValues> { Value = PatternValues.Solid },
                    ForegroundColor = new ForegroundColor { Rgb = rgbColor }
                }
            };

            bool found = false;
            int fillIndex = 0;
            foreach (Fill item in stylesheet.GetFirstChild<Fills>().Elements<Fill>())
            {
                if (item.PatternFill.PatternType.Value.Equals(fill.PatternFill.PatternType.Value))
                {
                    if (item.PatternFill.ForegroundColor.Rgb.Value.Equals(fill.PatternFill.ForegroundColor.Rgb.Value))
                    {
                        found = true;
                        break;
                    }
                }

                fillIndex++;
            }

            if (!found)
            {
                stylesheet.GetFirstChild<Fills>().InsertAt<Fill>(fill, fillIndex);
            }

            CellFormat cellFormat = new CellFormat
            {
                FillId = (uint)fillIndex
            };

            found = false;
            int cellFormatIndex = 0;
            foreach (CellFormat item in stylesheet.GetFirstChild<CellFormats>().Elements<CellFormat>())
            {
                if (item.FillId == null)
                {
                    cellFormatIndex++;
                    continue;
                }

                if (item.FillId.Value.Equals(cellFormat.FillId.Value))
                {
                    found = true;
                    break;
                }

                cellFormatIndex++;
            }

            if (!found)
            {
                stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(cellFormat, cellFormatIndex);
            }

            if (save)
            {
                stylesheet.Save();
            }

            return cellFormatIndex;
        }

        /// <summary>
        /// Add a single string to shared strings table.
        /// Shared string table is created if it doesn't exist.
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="stringItem">string to add</param>
        /// <param name="save">Save the shared string table</param>
        /// <returns></returns>
        private static bool AddSharedString(SpreadsheetDocument spreadsheet, string stringItem, bool save)
        {
            SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;

            if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
            {
                sharedStringTable.AppendChild(new SharedStringItem(new Text(stringItem)));

                if (save)
                {
                    sharedStringTable.Save();
                }
            }

            return true;
        }

        /// <summary>
        /// Returns the index of a shared string.
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="stringItem">String to search for</param>
        /// <returns>Index of a shared string. -1 if not found</returns>
        private static int IndexOfSharedString(SpreadsheetDocument spreadsheet, string stringItem)
        {
            SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
            bool found = false;
            int index = 0;

            foreach (SharedStringItem sharedString in sharedStringTable.Elements<SharedStringItem>())
            {
                if (sharedString.InnerText == stringItem)
                {
                    found = true;
                    break;
                }
                index++;
            }

            if (found)
            {
                return index;
            }

            return -1;
        }

        /// <summary>
        /// Get the index of last row element
        /// </summary>
        /// <param name="worksheet">Worksheet to use</param>
        /// <returns>Index of last row</returns>
        private static UInt32Value NextRowIndex(Worksheet worksheet)
        {
            if (worksheet.GetFirstChild<SheetData>().Elements<Row>().Count() != 0)
            {
                return worksheet.GetFirstChild<SheetData>().Elements<Row>().Last<Row>().RowIndex + 1;
            }

            return 1;
        }

        /// <summary>
        /// Converts a column number to column name (i.e. A, B, C..., AA, AB...)
        /// </summary>
        /// <param name="columnIndex">Index of the column</param>
        /// <returns>Column name</returns>
        private static string ColumnNameFromIndex(uint columnIndex)
        {
            string columnName = "";

            while (columnIndex > 0)
            {
                uint remainder = (columnIndex - 1) % 26;
                columnName = Convert.ToChar(65 + remainder).ToString() + columnName;
                columnIndex = (uint)((columnIndex - remainder) / 26);
            }

            return columnName;
        }

        /// <summary>
        /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
        /// </summary>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="valueType">Type of the value</param>
        /// <param name="value">The actual value</param>
        /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
        /// <param name="save">Save the worksheet?</param>
        /// <returns>True if succesful</returns>
        private static bool SetCellValue(Worksheet worksheet, uint columnIndex, uint rowIndex, CellValues valueType, string value, uint? styleIndex, bool save)
        {
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            Row row;
            Row previousRow = null;
            Cell cell;
            Cell previousCell = null;
            Columns columns;
            Column previousColumn = null;
            string cellAddress = ColumnNameFromIndex(columnIndex) + rowIndex;

            if (sheetData.Elements<Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(item => item.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row { RowIndex = rowIndex };

                for (uint counter = rowIndex - 1; counter > 0; counter--)
                {
                    previousRow = sheetData.Elements<Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
                    if (previousRow != null)
                    {
                        break;
                    }
                }

                sheetData.InsertAfter(row, previousRow);
            }

            if (row.Elements<Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
            {
                cell = row.Elements<Cell>().Where(item => item.CellReference.Value == cellAddress).First();
            }
            else
            {
                for (uint counter = columnIndex - 1; counter > 0; counter--)
                {
                    previousCell = row.Elements<Cell>().Where(item => item.CellReference.Value == ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
                    if (previousCell != null)
                    {
                        break;
                    }
                }

                cell = new Cell { CellReference = cellAddress, StyleIndex = styleIndex };

                row.InsertAfter(cell, previousCell);
            }

            columns = worksheet.Elements<Columns>().FirstOrDefault();
            if (columns == null)
            {
                columns = worksheet.InsertAt(new Columns(), 0);
            }

            if (columns.Elements<Column>().Where(item => item.Min == columnIndex).Count() == 0)
            {
                for (uint counter = columnIndex - 1; counter > 0; counter--)
                {
                    previousColumn = columns.Elements<Column>().Where(item => item.Min == counter).FirstOrDefault();
                    if (previousColumn != null)
                    {
                        break;
                    }
                }

                columns.InsertAfter(new Column
                {
                    Min = columnIndex,
                    Max = columnIndex,
                    CustomWidth = true,
                    Width = 9
                }, previousColumn);
            }

            cell.CellValue = new CellValue(value);
            if (styleIndex != null)
            {
                cell.StyleIndex = styleIndex;
            }

            if (valueType != CellValues.Date)
            {
                cell.DataType = new EnumValue<CellValues>(valueType);
            }

            if (save)
            {
                worksheet.Save();
            }

            return true;
        }

        /// <summary>
        /// Sets a string value to a cell
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="stringValue">String value to set</param>
        /// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param>
        /// <param name="styleIndex">Style to use</param>
        /// <param name="save">Save the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool SetCellValue(SpreadsheetDocument spreadsheet, Worksheet worksheet, uint columnIndex, uint rowIndex, string stringValue, bool useSharedString, uint? styleIndex, bool save)
        {
            string columnValue = stringValue;
            CellValues cellValueType;

            if (useSharedString)
            {
                if (IndexOfSharedString(spreadsheet, stringValue) == -1)
                {
                    AddSharedString(spreadsheet, stringValue, true);
                }

                columnValue = IndexOfSharedString(spreadsheet, stringValue).ToString();
                cellValueType = CellValues.SharedString;
            }
            else
            {
                cellValueType = CellValues.String;
            }

            return SetCellValue(worksheet, columnIndex, rowIndex, cellValueType, columnValue, styleIndex, save);
        }

        /// <summary>
        /// Sets a cell hyperlink
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Address of the cell</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="linkValue">Link for the cell</param>
        /// <param name="save">Save the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool SetCellHyperlink(SpreadsheetDocument spreadsheet, Worksheet worksheet, uint columnIndex, uint rowIndex, string linkValue, bool save)
        {
            HyperlinkRelationship hyperlinkRelationship = SetHyperlinkRelationship(worksheet, linkValue);

            Hyperlinks hyperlinks;
            if (!worksheet.Elements<Hyperlinks>().Any())
            {
                hyperlinks = new Hyperlinks();
                worksheet.InsertBefore(hyperlinks, worksheet.Descendants<PageMargins>().FirstOrDefault<PageMargins>());
            }
            else
            {
                hyperlinks = worksheet.Elements<Hyperlinks>().First<Hyperlinks>();
            }

            Hyperlink hyperlink = new Hyperlink { Reference = ColumnNameFromIndex(columnIndex) + rowIndex, Id = hyperlinkRelationship.Id };
            hyperlinks.Append(hyperlink);

            if (save)
            {
                worksheet.Save();
            }

            return true;
        }

        /// <summary>
        /// Sets a cell hyperlink relationship
        /// </summary>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="link">Link for the cell</param>
        /// <returns>Hyperlink relationship object</returns>
        private static HyperlinkRelationship SetHyperlinkRelationship(Worksheet worksheet, string link)
        {
            return worksheet.WorksheetPart.AddHyperlinkRelationship(new Uri(link, UriKind.Absolute), true);
        }

        /// <summary>
        /// The write xlsx values.
        /// </summary>
        /// <param name="fileName">
        /// The file Name.
        /// </param>
        /// <param name="values">
        /// The values.
        /// </param>
        /// <param name="separator">
        /// The separator.
        /// </param>
        public static void WriteValues(string fileName, string values, string separator)
        {
            SpreadsheetDocument spreadsheetDocument = CreateWorkbook(fileName);
            AddBasicStyles(spreadsheetDocument);
            AddWorksheet(spreadsheetDocument, "sheet1");

            Worksheet worksheet = FirstWorksheet(spreadsheetDocument);

            uint rowIndex = NextRowIndex(worksheet);
            uint columnIndex = 1;

            string[] splittedValueString = values.Split(new[] { separator }, StringSplitOptions.None);

            for (int i = 0; i < splittedValueString.Count(); i++)
            {
                string cellValue = splittedValueString[i];
                uint cellStyleIndex = 0;
                bool colorCell = false;

                if (columnIndex - 1 == KonfigExportWorkflow.Instance.eIdxColor)
                {
                    if (!cellValue.IndexOf(',').Equals(-1))
                    {
                        colorCell = true;

                        int[] rgbValue = new int[3];

                        int j = 0;
                        foreach (string value in cellValue.Split(','))
                        {
                            int.TryParse(value, out rgbValue[j]);

                            j++;
                        }

                        // Alpha Red Green Blue Color Value
                        HexBinaryValue rgbColor = HexBinaryValue.FromString(string.Format("FF{0:X2}{1:X2}{2:X2}", rgbValue[0], rgbValue[1], rgbValue[2]));

                        cellStyleIndex = (uint)AddCellStylesForegroundColor(spreadsheetDocument, rgbColor, true);
                    }
                }

                if (colorCell)
                {
                    SetCellValue(spreadsheetDocument, worksheet, columnIndex, rowIndex, string.Empty, true, cellStyleIndex, true);
                }
                else
                {
                    SetCellValue(spreadsheetDocument, worksheet, columnIndex, rowIndex, cellValue, true, cellStyleIndex, true);
                }

                if (KonfigExportCompact.Instance.eIdxImagePathDefault != -1
                    && KonfigExportCompact.Instance.eIdxImagePathDefault < splittedValueString.Count())
                {
                    if (splittedValueString[KonfigExportCompact.Instance.eIdxImagePathDefault].Equals(cellValue))
                    {
                        if (File.Exists(cellValue))
                        {
                            SetCellHyperlink(spreadsheetDocument, worksheet, columnIndex, rowIndex, cellValue, true);
                        }
                    }
                }

                columnIndex++;
            }

            worksheet.Save();
        }
    }
}

After the repair, the file is opened, and it's all there, just the links to the images were removed.

Have been a few hours at google searched but found nothing.

Does anyone know the Problem?

Answer 1

Hi FeSeBr,

Thanks for posting in the MSDN Forum.

For easier trouble shooting your issue. Would you please share your error spreadsheetdocument on skydrive or the code snippet which you used to generate spreadsheetdocument for further research?

Have a good day,

Tom

---

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

Answer 2

I have the same issue.  Has this been resolved?