none
How to add comments for cell RRS feed

  • Question

  • hi:

    我现在向一个单元格添加批注,重要的两部分vml和comments都添加进去了,但是批注仍旧未显示

     public void CreateSpreadsheetWorkbook(string filepath)
            {
                ExcelOperate eo = new ExcelOperate();
                List<ActivityExamResult> list = eo.GetActivityExamResult();
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                //工作簿
                workbookpart.Workbook = new Workbook();
                //工作表
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                //样式
                WorkbookStylesPart workbookStylesPart1 = workbookpart.AddNewPart<WorkbookStylesPart>();
                Stylesheet stylesheet = GenerateWorkbookStylesPart1Content(workbookStylesPart1);
                for (int i = 0; i < list.Count; i++)
                {
                    var item = list[i];
                    //sheet部分
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                   
                    //批注
                    WorksheetCommentsPart commentsPart = worksheetPart.AddNewPart<WorksheetCommentsPart>();
                    //sheet页
                    Worksheet worksheet1 = new Worksheet();
                    SheetData sheetData1 = new SheetData();
                    Columns columns1 = new Columns();
                    Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 8.2D, CustomWidth = true };
                    columns1.Append(column1);
                    worksheet1.Append(columns1);
                    worksheet1.Append(sheetData1);
                    worksheetPart.Worksheet = worksheet1;
                    UInt32Value index = UInt32Value.FromUInt32(Convert.ToUInt32(i + 1));
                    //添加一个sheet
                    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = index, Name = item.ActivityName };
                    sheets.Append(sheet);
                    //填充数据
                    SheetData sData = worksheetPart.Worksheet.GetFirstChild<SheetData>();  //往sheet添加数据   
                    ExportData(sData, worksheet1, stylesheet, worksheetPart, commentsPart, item.ExcelUserExamResultList);//填充数据
                    //添加vml
                    VmlDrawingPart vpart = worksheetPart.AddNewPart<VmlDrawingPart>("Rid13");
                    vpart.FeedData((System.IO.Stream)(new System.IO.FileStream("C:\\vmltest.txt", System.IO.FileMode.Open)));
                    LegacyDrawing lDrawing = new LegacyDrawing();
                    lDrawing.Id = "Rid13";
                    worksheetPart.Worksheet.Append(lDrawing);
                    workbookpart.Workbook.Save();
                }
                spreadsheetDocument.Close(); // Close the document.
            }

    Monday, July 9, 2012 10:12 AM

Answers

  • Hi lanseqiangwei,

    Please take a look at my snippet, it works fine on my side. I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using log4net;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Xml;
    using System.IO;
    
    namespace SpreadsheetDocumentComments
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                ILog log = log4net.LogManager.GetLogger(typeof(Program));
                string strPath = null;
                SaveFileDialog objSaveFileDialog = new SaveFileDialog();
                objSaveFileDialog.Filter = "Excel Document (*.xlsx)|*.xlsx";
                objSaveFileDialog.ShowDialog();
                strPath = objSaveFileDialog.FileName;
                if (strPath.Length > 0)
                {
                    using (SpreadsheetDocument objDocument =
                        SpreadsheetDocument.Create(strPath,
    SpreadsheetDocumentType.Workbook))
                    {
                        WorkbookPart objWorkbookPart =
                            objDocument.AddWorkbookPart();
                        log.Info("Add WorkbookPart");
                        WorksheetPart objWorksheetPart =
                             objWorkbookPart.AddNewPart<WorksheetPart>();
                        log.Info("Add WorksheetPart");
                        string strWorkSheetPartrId =
                             objWorkbookPart.GetIdOfPart(objWorksheetPart);
                        log.Info("WorksheetPart's rId : " + strWorkSheetPartrId);
                        objWorksheetPart.Worksheet = new Worksheet();
                        SheetData objSheetData = new SheetData();
                        objWorksheetPart.Worksheet.Append(objSheetData);
                        Sheets objSheets = new Sheets();
                        Sheet objSheet = new Sheet()
                        {
                            Name = "MySheet",
                            SheetId = (UInt32Value)1U,
                            Id = strWorkSheetPartrId
    
                        };
                        objSheets.Append(objSheet);
                        objWorkbookPart.Workbook = new Workbook();
                        objWorkbookPart.Workbook.Append(objSheets);
                        WorksheetCommentsPart wcp = objWorksheetPart
                            .AddNewPart<WorksheetCommentsPart>();
                        log.Info("Add WorksheetCommentsPart");
                        wcp.Comments = new Comments();
                        Authors objAuthors = new Authors();
                        Author objAuthor = new Author();
                        objAuthor.Text = "Tx_OfficeDev";
                        objAuthors.Append(objAuthor);
                        CommentList cl = new CommentList();
                        Comment c = new Comment()
                        {
                            Reference = "A1",
                            AuthorId = (UInt32Value)0U
                        };
                        CommentText ct = new CommentText();
                        Run r1 = new Run();
                        Text t1 = new Text() { Text = "Tx_OfficeDev:" };
                        r1.Append(t1);
                        Run r2 = new Run();
                        Text t2 = new Text()
                        {
                            Text = "\nThis is a test!",
                            Space = SpaceProcessingModeValues.Preserve
                        };
                        r2.Append(t2);
                        ct.Append(r1);
                        ct.Append(r2);
                        c.Append(ct);
                        cl.Append(c);
                        wcp.Comments.Append(objAuthors);
                        wcp.Comments.Append(cl);
                        log.Info("WorksheetCommentsPart finished!");
                        VmlDrawingPart vdp = objWorksheetPart.AddNewPart<VmlDrawingPart>();
                        XmlTextWriter writer =
                            new XmlTextWriter(vdp.GetStream(FileMode.Create),
                                Encoding.UTF8);
                        writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\""
                            +"xmlns:o=\"urn:schemas-microsoft-com:office:office\""
                            +"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"
                            +"<o:shapelayout v:ext=\"edit\">"
                            +"<o:idmap v:ext=\"edit\" data=\"1\"/>"
                            +"</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\""
                            +"path=\"m,l,21600r21600,l21600,xe\">"
                            +"<v:stroke joinstyle=\"miter\"/>"
                            +"<v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>"
                            +"</v:shapetype><v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style=\'position:absolute;"
                            +"margin-left:65.25pt;margin-top:1.5pt;width:270pt;height:59.25pt;z-index:1;"
                            +"visibility:hidden;mso-wrap-style:tight\' fillcolor=\"#ffffe1\" o:insetmode=\"auto\">"
                            +"<v:fill color2=\"#ffffe1\"/>"
                            +"<v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>"
                            +"<v:path o:connecttype=\"none\"/>"
                            +"<v:textbox style=\'mso-direction-alt:auto\'>"
                            +"<div style=\'text-align:left\'></div>"
                            +"</v:textbox>"
                            +"<x:ClientData ObjectType=\"Note\">"
                            +"<x:MoveWithCells/>"
                            +"<x:SizeWithCells/>"
                            +"<x:Anchor>"
                            +"1, 15, 0, 2, 6, 15, 4, 9</x:Anchor>"
                            +"<x:AutoFill>False</x:AutoFill>"
                            +"<x:Row>0</x:Row>"
                            +"<x:Column>0</x:Column>"
                            +"</x:ClientData>"
                            +"</v:shape></xml>");
                        writer.Flush();
                        writer.Close();
                        log.Info("VmlPart finished!");
                        Row row = new Row()
                        {
                            RowIndex = (UInt32Value)1U,
                            Spans = new ListValue<StringValue>() 
                            {
                                InnerText = "1:1" 
                            }
                        };
                        objSheetData.Append(row);
                        string vmlrid = objWorksheetPart.GetIdOfPart(vdp);
                        log.Info("vdp rid : " + vmlrid);
                        LegacyDrawing ld = new LegacyDrawing() { Id = vmlrid };
                        objWorksheetPart.Worksheet.Append(ld);
                    }
                }
                Console.ReadKey();
            }
        }
    }

    Best Regards,

    T.X.

     

    征诛志异,三让两家王朝
    功同开辟,一桮万古江南


    Monday, July 9, 2012 12:50 PM

All replies

  • Hi lanseqiangwei,

    Please take a look at my snippet, it works fine on my side. I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using log4net;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Xml;
    using System.IO;
    
    namespace SpreadsheetDocumentComments
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                ILog log = log4net.LogManager.GetLogger(typeof(Program));
                string strPath = null;
                SaveFileDialog objSaveFileDialog = new SaveFileDialog();
                objSaveFileDialog.Filter = "Excel Document (*.xlsx)|*.xlsx";
                objSaveFileDialog.ShowDialog();
                strPath = objSaveFileDialog.FileName;
                if (strPath.Length > 0)
                {
                    using (SpreadsheetDocument objDocument =
                        SpreadsheetDocument.Create(strPath,
    SpreadsheetDocumentType.Workbook))
                    {
                        WorkbookPart objWorkbookPart =
                            objDocument.AddWorkbookPart();
                        log.Info("Add WorkbookPart");
                        WorksheetPart objWorksheetPart =
                             objWorkbookPart.AddNewPart<WorksheetPart>();
                        log.Info("Add WorksheetPart");
                        string strWorkSheetPartrId =
                             objWorkbookPart.GetIdOfPart(objWorksheetPart);
                        log.Info("WorksheetPart's rId : " + strWorkSheetPartrId);
                        objWorksheetPart.Worksheet = new Worksheet();
                        SheetData objSheetData = new SheetData();
                        objWorksheetPart.Worksheet.Append(objSheetData);
                        Sheets objSheets = new Sheets();
                        Sheet objSheet = new Sheet()
                        {
                            Name = "MySheet",
                            SheetId = (UInt32Value)1U,
                            Id = strWorkSheetPartrId
    
                        };
                        objSheets.Append(objSheet);
                        objWorkbookPart.Workbook = new Workbook();
                        objWorkbookPart.Workbook.Append(objSheets);
                        WorksheetCommentsPart wcp = objWorksheetPart
                            .AddNewPart<WorksheetCommentsPart>();
                        log.Info("Add WorksheetCommentsPart");
                        wcp.Comments = new Comments();
                        Authors objAuthors = new Authors();
                        Author objAuthor = new Author();
                        objAuthor.Text = "Tx_OfficeDev";
                        objAuthors.Append(objAuthor);
                        CommentList cl = new CommentList();
                        Comment c = new Comment()
                        {
                            Reference = "A1",
                            AuthorId = (UInt32Value)0U
                        };
                        CommentText ct = new CommentText();
                        Run r1 = new Run();
                        Text t1 = new Text() { Text = "Tx_OfficeDev:" };
                        r1.Append(t1);
                        Run r2 = new Run();
                        Text t2 = new Text()
                        {
                            Text = "\nThis is a test!",
                            Space = SpaceProcessingModeValues.Preserve
                        };
                        r2.Append(t2);
                        ct.Append(r1);
                        ct.Append(r2);
                        c.Append(ct);
                        cl.Append(c);
                        wcp.Comments.Append(objAuthors);
                        wcp.Comments.Append(cl);
                        log.Info("WorksheetCommentsPart finished!");
                        VmlDrawingPart vdp = objWorksheetPart.AddNewPart<VmlDrawingPart>();
                        XmlTextWriter writer =
                            new XmlTextWriter(vdp.GetStream(FileMode.Create),
                                Encoding.UTF8);
                        writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\""
                            +"xmlns:o=\"urn:schemas-microsoft-com:office:office\""
                            +"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"
                            +"<o:shapelayout v:ext=\"edit\">"
                            +"<o:idmap v:ext=\"edit\" data=\"1\"/>"
                            +"</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\""
                            +"path=\"m,l,21600r21600,l21600,xe\">"
                            +"<v:stroke joinstyle=\"miter\"/>"
                            +"<v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>"
                            +"</v:shapetype><v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style=\'position:absolute;"
                            +"margin-left:65.25pt;margin-top:1.5pt;width:270pt;height:59.25pt;z-index:1;"
                            +"visibility:hidden;mso-wrap-style:tight\' fillcolor=\"#ffffe1\" o:insetmode=\"auto\">"
                            +"<v:fill color2=\"#ffffe1\"/>"
                            +"<v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>"
                            +"<v:path o:connecttype=\"none\"/>"
                            +"<v:textbox style=\'mso-direction-alt:auto\'>"
                            +"<div style=\'text-align:left\'></div>"
                            +"</v:textbox>"
                            +"<x:ClientData ObjectType=\"Note\">"
                            +"<x:MoveWithCells/>"
                            +"<x:SizeWithCells/>"
                            +"<x:Anchor>"
                            +"1, 15, 0, 2, 6, 15, 4, 9</x:Anchor>"
                            +"<x:AutoFill>False</x:AutoFill>"
                            +"<x:Row>0</x:Row>"
                            +"<x:Column>0</x:Column>"
                            +"</x:ClientData>"
                            +"</v:shape></xml>");
                        writer.Flush();
                        writer.Close();
                        log.Info("VmlPart finished!");
                        Row row = new Row()
                        {
                            RowIndex = (UInt32Value)1U,
                            Spans = new ListValue<StringValue>() 
                            {
                                InnerText = "1:1" 
                            }
                        };
                        objSheetData.Append(row);
                        string vmlrid = objWorksheetPart.GetIdOfPart(vdp);
                        log.Info("vdp rid : " + vmlrid);
                        LegacyDrawing ld = new LegacyDrawing() { Id = vmlrid };
                        objWorksheetPart.Worksheet.Append(ld);
                    }
                }
                Console.ReadKey();
            }
        }
    }

    Best Regards,

    T.X.

     

    征诛志异,三让两家王朝
    功同开辟,一桮万古江南


    Monday, July 9, 2012 12:50 PM
  • Hi lanseqiangwei,

    Thanks for posting in the MSDN Forum.

    I would recommend you translate your post to English in order to receive more better support form community members.

    Have a good day,

    Tom


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

    Tuesday, July 10, 2012 7:06 AM
    Moderator