File getting corrupted while adding comments to excel using openXml

Question

Hello,

Below link provided me the working code to add comments to an excel using Open Xml. This worked perfect for the excel that I created. But not working to an excel that already have some formatting / validations, show 'File corrupted error' while opening the file that I added comments using this code.

Can anyone help on this ASAP.

https://social.msdn.microsoft.com/Forums/office/en-US/40a95862-9adc-492d-a046-97a5e6e20260/how-to-insert-comments-in-excel-using-openxml?forum=oxmlsdk

Thanks in advance

Answer 1

Can we use other packages?

I used Microsoft.Office.Interop.Excel to write a simple example, even if the cell has a format or formula, it can still work

           Application application = new Application();  
            Workbook workbook = application.Workbooks.Open(@"C:\...\1.xlsx");  
            try  
            {  
                Worksheet oSheet = (Worksheet)workbook.ActiveSheet;  
                Range range = (Range)oSheet.Cells[3, 4];  
                range.AddComment("testComment");  
            }  
            catch (Exception e)  
            {  
                Console.WriteLine(e);  
            }  
            finally   
            {  
                workbook.Save();  
                workbook.Close();  
                application.Quit();  
            }  

Update:
I have written some new codes using FreeSpire.XLS, please try if it works for you.

            Workbook workbook = new Workbook();  
            workbook.LoadFromFile(@"C:\...\1.xlsx", ExcelVersion.Version2016);  
            Worksheet sheet = workbook.ActiveSheet;  
            String str = "testComment";  
            ExcelFont font1 = workbook.CreateFont();  
            font1.FontName = "Calibri";  
            font1.IsBold = true;  
            sheet.Range["D3"].Comment.RichText.Text = str;  
            sheet.Range["D3"].Comment.Width = 200;  
            sheet.Range["D3"].Comment.Height = 50;  
            sheet.Range["D3"].Comment.RichText.SetFont(26, 45, font1);  
            workbook.Save();  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

PFB Console application code, here i am trying to add comments to C6. This work perfect with un-formatted excel. But in formatted excel the file gets corrupted.

class Program
    {
        static void Main(string[] args)
        {
                string TemplatePath = ConfigurationManager.AppSettings["TemplatePath"].ToString();
                var filePath = ConfigurationManager.AppSettings["TempPath"].ToString() + "_" + Guid.NewGuid() + ".xlsx";
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(TemplatePath, false))
                {
                    var gg = doc.SaveAs(filePath);
                    gg.Close();
                    doc.Close();
                }   
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
                {
                    var workbookPart = document.WorkbookPart;
                    workbookPart.Workbook.Save();
                    //string relationshipSheetId = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name.Equals("Required"))?.Id;
                    string relationshipSheetId = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name.Equals("MySheet"))?.Id;
                    var requiredWorksheet = ((WorksheetPart)workbookPart.GetPartById(relationshipSheetId));
                    Dictionary<string, string> myDic = new Dictionary<string, string>();
                    myDic.Add("C6", "Just Comments");
                    InsertComments(requiredWorksheet, myDic);
                }
        }
        public static void InsertComments(WorksheetPart worksheetPart, Dictionary<string, string> commentsToAddDict)
        {
            if (commentsToAddDict.Count > 0)
            {
                string commentsVmlXml = string.Empty;
                // Create all the comment VML Shape XML
                foreach (var commentToAdd in commentsToAddDict)
                {
                    commentsVmlXml += GetCommentVMLShapeXML(GetColumnName(commentToAdd.Key), GetRowIndex(commentToAdd.Key).ToString());
                }
                // The VMLDrawingPart should contain all the definitions for how to draw every comment shape for the worksheet
                VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
                using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.OpenOrCreate), 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>");
                }
                // Create the comment elements
                foreach (var commentToAdd in commentsToAddDict)
                {
                    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 = commentToAdd.Key, 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 };
                    DocumentFormat.OpenXml.Spreadsheet.Color color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = (UInt32Value)81U };
                    RunFont runFont = new RunFont() { Val = "Tahoma" };
                    RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet() { Val = 1 };

                    runProperties.Append(bold);
                    runProperties.Append(fontSize);
                    runProperties.Append(color);
                    runProperties.Append(runFont);
                    runProperties.Append(runPropertyCharSet);
                    Text text = new Text();
                    text.Text = commentToAdd.Value;

                    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;
                    }
                    worksheetCommentsPart.Comments.Save();
                    worksheetPart.Worksheet.Save();
                }
            }
        }
        private static string GetCommentVMLShapeXML(string columnName, string rowIndex)
        {
            string commentVmlXml = string.Empty;
            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-left:59.25pt;margin-top:1.5pt;width:96pt;height:55.5pt;z-index:1;\r\n  visibility:hidden\' 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=\'mso-fit-shape-to-text:true'>\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;
        }
        private static string GetAnchorCoordinatesForVMLCommentShape(string columnName, string rowIndex)
        {
            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;
        }
        private static string GetRowIndex(string cellReference)
        {
            string rowid = string.Empty;
            if (!string.IsNullOrEmpty(cellReference))
                rowid = cellReference.Substring(1);

            return rowid;
        }
        private static string GetColumnName(string cellReference)
        {
            string rowid = string.Empty;
            if (!string.IsNullOrEmpty(cellReference))
                rowid = cellReference.Substring(0, 1);

            return rowid;
        }
        public static int? GetColumnIndexFromName(string columnName)
        {
            int? columnIndex = null;

            string[] colLetters = Regex.Split(columnName, "([A-Z]+)");
            colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();
            List<char> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J' };
            if (colLetters.Count() <= 2)
            {
                int index = 0;
                foreach (string col in colLetters)
                {
                    List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();
                    int? indexValue = Letters.IndexOf(col1.ElementAt(index));

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


    }