如何使用Open XML SDK 2.0在Excel 2007中的单元格中添加注释? [英] How to add a comment to a cell in Excel 2007 using the Open XML SDK 2.0?
本文介绍了如何使用Open XML SDK 2.0在Excel 2007中的单元格中添加注释?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有没有人碰巧知道如何使用Open XML SDK 2.0向Excel添加注释?我找不到有关从何处着手此问题的任何文档.
Has anyone ever had any luck figuring out how to add a comment to Excel using the Open XML SDK 2.0? I couldn't find any documentation on where to get started on this issue.
推荐答案
下面的代码将获取您要向其添加注释的工作表，然后遍历commentsToAdd字典.字典键是单元格引用(即A1)，值是要添加的注释文本.
The below code will take the worksheet that you want to add comments to and then iterate over the commentsToAdd dictionary. The dictionary key is the cell reference (ie. A1) and the value is the comment text to be added.
/// <summary>
/// Adds all the comments defined in the commentsToAddDict dictionary to the worksheet
/// </summary>
/// <param name="worksheetPart">Worksheet Part</param>
/// <param name="commentsToAddDict">Dictionary of cell references as the key (ie. A1) and the comment text as the value</param>
public static void InsertComments(WorksheetPart worksheetPart, Dictionary<string, string> commentsToAddDict)
{
if (commentsToAddDict.Any())
{
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.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>");
}
// 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 };
Color color = new 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;
}
}
}
}
将为Shape创建VML XML的Helper方法:
Helper method that will create the VML XML for the Shape:
/// <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)
{
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=\'position:absolute;\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;
}
帮手找出列索引和注释Shape的坐标:
Helpers to figure out the Column Index and coordinates for the comment Shape:
/// <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)
{
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;
}
/// <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)
{
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> 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;
}
完成操作后，请不要忘记保存工作表和工作簿以查看更改.
Don't forget to save your worksheet and workbook once you are done in order to see the changes.
这篇关于如何使用Open XML SDK 2.0在Excel 2007中的单元格中添加注释?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
