none
Can not open Excel file after inserting comments programmatically RRS feed

  • Question

  • Hi,

    After inserting comments programmatically to an Excel file,I tried to open it.But it asks   "Excel found unreadable content in MyTemplate.xlsx.Do you want to recover the contents of this workbook?".If I select Yes,the excel file will get repaired but all the comments that I have inserted will be gone.It says that "Removed Part: Drawing shape."Does anyone know the reason for this?

    Elizabeth Abraham

    Friday, May 2, 2014 9:25 AM

Answers

  • Hi Elizabeth Abraham,

    Please try to use OpenXmlPart.FeedData Method to write the xml stream back instead of writing to the stream directly.

    You could write the xml to a memory stream, and then call OpenXmlPart.FeedData Method to write it back by using the code below:

    vmlDrawingPart.FeedData(memoryStrem);

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 8, 2014 3:28 AM
    Moderator

All replies

  • Hi,

    According to your description, I think there may be something wrong with the code which you used to insert comments into Excel spreadsheet programmatically.

    As far as I know, inserting comments into Excel is somehow complex, Cell comments also have a graphics object which is in a VML drawing part. VML is a cryptic legacy specification and is not in the approved ECMA standard. You can find documentation on it in Microsoft's Open XML documents, but it is not pretty.

    Since we are not know your spreadsheet, would you mind sharing your sample code and Excel workbook for us to reproduce and troubleshoot it?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 5, 2014 3:12 AM
    Moderator
  • Which Excel version are you using? Have you tried to repair your Office application? And I have searched some posts on similar Excel opening issue, maybe you can have a check on this post.

    http://openxmldeveloper.org/discussions/formats/f/14/p/2700/9312.aspx

    If you find a better solution, please share it with us. Thanks.

    Monday, May 5, 2014 3:36 AM
  • Hi matcooper,

    I'm using Microsoft Excel 2010.I'm looking for solutions.And I will share if i got one.

    Thanks,

    Elizabeth Abraham

    Monday, May 5, 2014 3:41 AM
  • Hi Luna Zhang,


    Here is the code i'm using to insert comments.

            public static void InsertComments(string filePath,string DirectoryPathfp, List<string> ColumnName, List<string> CellIndex, List<string> NewCommentList)

            {

                //WorksheetPart worksheetPart

                try

                {

                    DirectoryInfo fd = new DirectoryInfo(DirectoryPathfp);

                    FileInfo[] Templates = fd.GetFiles("*.xlsx"); //Getting Excel files

                    foreach (FileInfo file in Templates)

                    {                                           

                        file.Attributes = file.Attributes & ~FileAttributes.ReadOnly;

     

                    }

     

                  using (SpreadsheetDocument NewspreadsheetDocument =

                           SpreadsheetDocument.Open(filePath, true))

                    {

                        WorkbookPart workBookPart = NewspreadsheetDocument.WorkbookPart;

                       foreach (Sheet s in workBookPart.Workbook.Descendants<Sheet>())

                        {

                            WorksheetPart worksheetPart = workBookPart.GetPartById(s.Id) as WorksheetPart;

                            VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();

                    if ((ColumnName.Count > 0 && CellIndex.Count > 0 && NewCommentList.Count > 0))

                    {

                        string commentsVmlXml = string.Empty;

     

                        // Create all the comment VML Shape XML

     

                        for (var i = 0; i < ColumnName.Count; i++)

                        {

                            commentsVmlXml += GetCommentVMLShapeXML(ColumnName[i], CellIndex[i]);

                        }

     

                       

                        using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8))

                        {

     

                            writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n  <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n" +

                            "</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"\r\n  path=\"m,l,21600r21600,l21600,xe\">\r\n  <v:stroke joinstyle=\"miter\"/>\r\n  <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n </v:shapetype>"

                            + commentsVmlXml + "</xml>");

                        }

     

            

    Monday, May 5, 2014 3:55 AM

  •            // Create the comment elements

     

     

     

                        for (var j = 0; j < NewCommentList.Count; j++)

                        {

                            WorksheetCommentsPart worksheetCommentsPart = worksheetPart.WorksheetCommentsPart ?? worksheetPart.AddNewPart<WorksheetCommentsPart>();

     

                            // We only want one legacy drawing element per worksheet for comments

                            if (worksheetPart.Worksheet.Descendants<LegacyDrawing>().SingleOrDefault() == null)

                            {

                                string vmlPartId = worksheetPart.GetIdOfPart(vmlDrawingPart);

                                LegacyDrawing legacyDrawing = new LegacyDrawing() { Id = vmlPartId };

                                worksheetPart.Worksheet.Append(legacyDrawing);

                            }

     

                            Comments comments;

                            bool appendComments = false;

                            if (worksheetPart.WorksheetCommentsPart.Comments != null)

                            {

                                comments = worksheetPart.WorksheetCommentsPart.Comments;

                            }

                            else

                            {

                                comments = new Comments();

                                appendComments = true;

                            }

     

                            // We only want one Author element per Comments element

                            if (worksheetPart.WorksheetCommentsPart.Comments == null)

                            {

                                Authors authors = new Authors();

                                Author author = new Author();

                                author.Text = "Author Name";

                                authors.Append(author);

                                comments.Append(authors);

                            }

     

                            CommentList commentList;

                            bool appendCommentList = false;

                            if (worksheetPart.WorksheetCommentsPart.Comments != null &&

                                worksheetPart.WorksheetCommentsPart.Comments.Descendants<CommentList>().SingleOrDefault() != null)

                            {

                                commentList = worksheetPart.WorksheetCommentsPart.Comments.Descendants<CommentList>().Single();

                            }

                            else

                            {

                                commentList = new CommentList();

                                appendCommentList = true;

                            }

     

                            Comment comment = new Comment() { Reference = ColumnName[j] + CellIndex[j], AuthorId = (UInt32Value)0U };

     

                            CommentText commentTextElement = new CommentText();

     

                            Run run = new Run();

     

                            RunProperties runProperties = new RunProperties();

                            Bold bold = new Bold();

                            FontSize fontSize = new FontSize() { Val = 8D };

                            Color color = new Color() { Indexed = (UInt32Value)81U };

                            RunFont runFont = new RunFont() { Val = "Tahoma" };

                            RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet() { Val = 1 };

     

                       

    Monday, May 5, 2014 3:56 AM

  •     runProperties.Append(bold);

                            runProperties.Append(fontSize);

                            runProperties.Append(color);

                            runProperties.Append(runFont);

                            runProperties.Append(runPropertyCharSet);

                            Text text = new Text();

                            text.Text = NewCommentList[j];

     

                            run.Append(runProperties);

                            run.Append(text);

     

                            commentTextElement.Append(run);

                            comment.Append(commentTextElement);

                            commentList.Append(comment);

     

                            // Only append the Comment List if this is the first time adding a comment

                            if (appendCommentList)

                            {

                                comments.Append(commentList);

                            }

     

                            // Only append the Comments if this is the first time adding Comments

                            if (appendComments)

                            {

                                worksheetCommentsPart.Comments = comments;

                            }

                        }

     

                    }

                    break;

                  }

                }

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

     

     

     

     

     

     

     

     

     

         

     

            /// <summary>

            /// Creates the VML Shape XML for a comment. It determines the positioning of the

            /// comment in the excel document based on the column name and row index.

            /// </summary>

            /// <param name="columnName">Column name containing the comment</param>

            /// <param name="rowIndex">Row index containing the comment</param>

            /// <returns>VML Shape XML for a comment</returns>

            private static string GetCommentVMLShapeXML(string columnName, string rowIndex)

            {

                try

                {

                    string commentVmlXml = string.Empty;

     

                    // Parse the row index into an int so we can subtract one

                    int commentRowIndex;

                    if (int.TryParse(rowIndex, out commentRowIndex))

                    {

                        commentRowIndex -= 1;

     

                        commentVmlXml = "<v:shape id=\"" + Guid.NewGuid().ToString().Replace("-", "") + "\" type=\"#_x0000_t202\" style=\'\r\n  margin-margin-width:96pt;height:55.5pt;\r\n  fillcolor=\"#ffffe1\" o:insetmode=\"auto\">\r\n  <v:fill color2=\"#ffffe1\"/>\r\n" +

                        "<v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>\r\n  <v:path o:connecttype=\"none\"/>\r\n  <v:textbox style=\'\r\n   <div style=\'text-align:left\'></div>\r\n  </v:textbox>\r\n  <x:ClientData ObjectType=\"Note\">\r\n   <x:MoveWithCells/>\r\n" +

                        "<x:SizeWithCells/>\r\n   <x:Anchor>\r\n" + GetAnchorCoordinatesForVMLCommentShape(columnName, rowIndex) + "</x:Anchor>\r\n   <x:AutoFill>False</x:AutoFill>\r\n   <x:Row>" + commentRowIndex + "</x:Row>\r\n   <x:Column>" + GetColumnIndexFromName(columnName) + "</x:Column>\r\n  </x:ClientData>\r\n </v:shape>";

                    }

     

                    return commentVmlXml;

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

     

     

     

          

     

     

    Monday, May 5, 2014 3:57 AM
  •         /// <summary>

            /// Gets the coordinates for where on the excel spreadsheet to display the VML comment shape

            /// </summary>

            /// <param name="columnName">Column name of where the comment is located (ie. B)</param>

            /// <param name="rowIndex">Row index of where the comment is located (ie. 2)</param>

            /// <returns><see cref="<x:Anchor>"/> coordinates in the form of a comma separated list</returns>

            private static string GetAnchorCoordinatesForVMLCommentShape(string columnName, string rowIndex)

            {

                try

                {

                    string coordinates = string.Empty;

                    int startingRow = 0;

                    int startingColumn = GetColumnIndexFromName(columnName).Value;

     

                    // From (upper right coordinate of a rectangle)

                    // [0] Left column

                    // [1] Left column offset

                    // [2] Left row

                    // [3] Left row offset

                    // To (bottom right coordinate of a rectangle)

                    // [4] Right column

                    // [5] Right column offset

                    // [6] Right row

                    // [7] Right row offset

                    List<int> coordList = new List<int>(8) { 0, 0, 0, 0, 0, 0, 0, 0 };

     

                    if (int.TryParse(rowIndex, out startingRow))

                    {

                        // Make the row be a zero based index

                        startingRow -= 1;

     

                        coordList[0] = startingColumn + 1; // If starting column is A, display shape in column B

                        coordList[1] = 15;

                        coordList[2] = startingRow;

                        coordList[4] = startingColumn + 3; // If starting column is A, display shape till column D

                        coordList[5] = 15;

                        coordList[6] = startingRow + 3; // If starting row is 0, display 3 rows down to row 3

     

                        // The row offsets change if the shape is defined in the first row

                        if (startingRow == 0)

                        {

                            coordList[3] = 2;

                            coordList[7] = 16;

                        }

                        else

                        {

                            coordList[3] = 10;

                            coordList[7] = 4;

                        }

     

                        coordinates = string.Join(",", coordList.ConvertAll<string>(x => x.ToString()).ToArray());

                    }

     

                    return coordinates;

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

     

            /// <summary>

            /// Given just the column name (no row index), it will return the zero based column index.

            /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ).

            /// A length of three can be implemented when needed.

            /// </summary>

            /// <param name="columnName">Column Name (ie. A or AB)</param>

            /// <returns>Zero based index if the conversion was successful; otherwise null</returns>

            public static int? GetColumnIndexFromName(string columnName)

            {

                try

                {

                    int? columnIndex = null;

     

                    string[] colLetters = Regex.Split(columnName, "([A-Z]+)");

                    colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();

     

                    if (colLetters.Count() <= 2)

                    {

                        int index = 0;

                        foreach (string col in colLetters)

                        {

                            List<char> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };

                            List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();

                            int? indexValue = Letters.IndexOf(col1.ElementAt(index));

                            //  int? indexValue = 1;

     

                            if (indexValue != -1)

                            {

                                // The first letter of a two digit column needs some extra calculations

                                if (index == 0 && colLetters.Count() == 2)

                                {

                                    columnIndex = columnIndex == null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26);

                                }

                                else

                                {

                                    columnIndex = columnIndex == null ? indexValue : columnIndex + indexValue;

                                }

                            }

     

                            index++;

                        }

                    }

     

                    return columnIndex;

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

    Elizabeth Abraham



    Monday, May 5, 2014 3:57 AM
  • Hi,

    Thank you for posting in the MSDN Forum.

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Sorry for any inconvenience and have a nice day!


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 7, 2014 10:10 AM
    Moderator
  • Hi Elizabeth Abraham,

    Please try to use OpenXmlPart.FeedData Method to write the xml stream back instead of writing to the stream directly.

    You could write the xml to a memory stream, and then call OpenXmlPart.FeedData Method to write it back by using the code below:

    vmlDrawingPart.FeedData(memoryStrem);

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 8, 2014 3:28 AM
    Moderator