question

SivaGL-2369 avatar image
0 Votes"
SivaGL-2369 asked TimonYang-MSFT commented

File getting corrupted while adding comments to excel using openXml

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

dotnet-csharp
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please post the exact code you're using, not the recommended code in an archived post. Also provide an example of what the cell looks like that you're attaching the comment to.

0 Votes 0 ·

I have added the exact code i used

0 Votes 0 ·
SivaGL-2369 avatar image
0 Votes"
SivaGL-2369 answered cooldadtx commented

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;
         }
            
    
     }
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I took your code and ran it against a simple spreadsheet and it properly added the comments to the given cell. I had no issues opening the document. Are you sure this is all the changes you're making?

The only issue I really see with your code is that it is probably way more complicated than it needs to be. I wonder about your XML that you are auto-generating there. I don't see any reason to be doing that and I wonder if that is causing issues. IT isn't clear what you're trying to do there anyway. You might try simplifying that logic down or remove it to verify the XML generation is the actual issue.

It could also be a stream management issue as you have lots of using statements floating around. Some extension methods to clean all this up might make the problem easier to identify.

0 Votes 0 ·

Thanks Cooldadtx for your response,

The code works perfect with un-formatted excel. But in formatted excel the file gets corrupted like excel with conditional formatting with color and validated excel cells.

Please share me if you have any simple code to achieve the same with OpenXML.

Also help me to add conditional formatting to a cell(eg: C6) to fill cell with red color if cell is empty with Open XML.

Or else suggest me any open source from nuget package to add comments and add conditional formatting for empty cell.


Your response is much appreciable. Please do help on this, It is a critical requirement to be completed ASAP.

0 Votes 0 ·

Hmm. I changed the formatting of a cell to use more decimal places and added conditional formatting if value is greater than 2 and the comment was still added properly. Can you identify exactly which formatting rule is causing the issue?

0 Votes 0 ·

While testing existing comments in Excel I also got a warning that legacy comments will be removed when opened in newer Excel versions. Recently MS added support for threaded comments and it looks like they are incompatible with existing comments. You'll want to read up on that but to clarify you are using legacy comments right now so it works pre-Office 365?

0 Votes 0 ·
Show more comments
TimonYang-MSFT avatar image
1 Vote"
TimonYang-MSFT answered TimonYang-MSFT commented

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.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Interop need Microsoft Excel to be installed in server, which client will not allowed to do so. Or else can you please suggest any other package that support conditional formatting, adding sheet & comments, read & write data faster within .net.

0 Votes 0 ·

Thanks for you response.

please suggest any other package that support conditional formatting, adding sheet & comments, read & write data faster within .net

0 Votes 0 ·

@SivaGL-2369
I updated the answer and added some new content, please check it.

1 Vote 1 ·

Your time for me is much appreciated.
FreeSpire.XLS, needs license to work with more than 200 rows, it also costs.
I tried SpreadsheetLight, it is open source, but i the file got corrupted when i saved/ saveas. Dont know why?
Please suggest with a package the satisfy my requirement.



0 Votes 0 ·
Show more comments