/
Program.cs
159 lines (129 loc) · 8.66 KB
/
Program.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
// Copyright (c) Microsoft. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.
#nullable enable
using Common;
using DocumentFormat.OpenXml.Office2019.Excel.ThreadedComments;
using DocumentFormat.OpenXml.Packaging;
// WORKBOOK
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;
namespace ThreadedCommentsExample
{
internal class Program
{
private static void Main(string[] args)
{
string sheetName = "commentSheet";
string column = "A";
uint row = 1;
string reference = string.Concat(column, row.ToString());
int nRequiredArgs = 1;
// Must have x arguments.
if (args.Length < nRequiredArgs)
{
ExampleUtilities.ShowHelp(new string[] { $"ThreadedCommentExample requires {nRequiredArgs} arguments." });
return;
}
string filePath = args[0];
// start fresh with each run
if (ExampleUtilities.FileCheck(filePath))
{
try
{
File.Delete(filePath);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}
// WORKBOOK
using SpreadsheetDocument? sd = ExampleUtilities.CreateSpreadsheetWorkbook(filePath, sheetName);
if (sd != null)
{
ExampleUtilities.InsertText(sd, sheetName, @"Please comment on this cell.", column, row);
}
if (!(sd is null))
{
CreateMiscellaneousParts(sd, sheetName);
Worksheet? worksheet = ExampleUtilities.GetSheet(sd, sheetName)?.Worksheet;
if (worksheet != null)
{
worksheet.AddChild(new LegacyDrawing() { Id = "rId1" });
// NOTE: UserId, DisplayName, Id and ProviderId should be generated by querying the identity server used by this org and account.
string displayNameUser = "Jose Contoso";
string idUser = string.Concat("{", System.Guid.NewGuid().ToString().ToUpper(), "}");
string tcId = string.Concat("{", System.Guid.NewGuid().ToString().ToUpper(), "}");
// For the format of these userId parameter, please refer to [MS-XLSX] 2.6.203 CT_Person
// This can be a SID from an Active Directory provider, an email address using "PeoplePicker", and O365 id or a simple name.
string userIdJose = "j.contoso@example.com";
string providerIdAzure = "PeoplePicker";
WorkbookPart workbookPart = sd.WorkbookPart ?? sd.AddWorkbookPart();
WorkbookPersonPart pp = workbookPart.AddNewPart<WorkbookPersonPart>();
pp.PersonList = new PersonList(new Person() { DisplayName = displayNameUser, Id = idUser, UserId = userIdJose, ProviderId = providerIdAzure });
WorksheetPart wsp = ExampleUtilities.GetSheet(sd, sheetName);
WorksheetCommentsPart wscp = wsp.AddNewPart<WorksheetCommentsPart>();
wscp.Comments = new Comments(
new Authors(
new Author("tc=" + tcId)), new CommentList(
new Comment(
new DocumentFormat.OpenXml.Spreadsheet.CommentText(
new DocumentFormat.OpenXml.Spreadsheet.Text("Comment: Ok, here's a comment!")))
// Comment attributes
{ Reference = reference, AuthorId = 0, ShapeId = 0, Guid = tcId }));
WorksheetThreadedCommentsPart wstcp = wsp.AddNewPart<WorksheetThreadedCommentsPart>();
wstcp.ThreadedComments = new ThreadedComments(
new ThreadedComment(
new ThreadedCommentText("Ok, here's a threaded comment!"))
// ThreadedComment attributes
{ Ref = reference, PersonId = idUser, Id = tcId, DT = System.DateTime.Now });
}
}
else
{
throw new Exception("SpreadsheetDocument is null");
}
}
private static void CreateMiscellaneousParts(SpreadsheetDocument? sd, string sheetName)
{
if (sd == null)
{
return;
}
CoreFilePropertiesPart cfp = sd.AddCoreFilePropertiesPart();
using (System.Xml.XmlTextWriter writer =
new System.Xml.XmlTextWriter(cfp.GetStream(FileMode.Create), System.Text.Encoding.UTF8))
{
writer.WriteRaw("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"> <cp:lastModifiedBy>Jose Contose</cp:lastModifiedBy> <dcterms:modified xsi:type=\"dcterms:W3CDTF\">2022-03-01T18:55:39Z</dcterms:modified> </cp:coreProperties>");
writer.Flush();
}
ExtendedFilePropertiesPart efp = sd.AddExtendedFilePropertiesPart();
using (System.Xml.XmlTextWriter writer =
new System.Xml.XmlTextWriter(efp.GetStream(FileMode.Create), System.Text.Encoding.UTF8))
{
writer.WriteRaw("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\"> <Application>Microsoft Excel</Application> <DocSecurity>0</DocSecurity> <ScaleCrop>false</ScaleCrop> <HeadingPairs> <vt:vector size=\"2\" baseType=\"variant\"> <vt:variant> <vt:lpstr>Worksheets</vt:lpstr> </vt:variant> <vt:variant> <vt:i4>1</vt:i4> </vt:variant> </vt:vector> </HeadingPairs> <TitlesOfParts> <vt:vector size=\"1\" baseType=\"lpstr\"> <vt:lpstr>commentSheet</vt:lpstr> </vt:vector> </TitlesOfParts> <LinksUpToDate>false</LinksUpToDate> <SharedDoc>false</SharedDoc> <HyperlinksChanged>false</HyperlinksChanged> <AppVersion>16.0300</AppVersion> </Properties>");
writer.Flush();
}
LabelInfoPart lip = sd.AddLabelInfoPart();
lip.ClassificationLabelList = new DocumentFormat.OpenXml.Office2021.MipLabelMetaData.ClassificationLabelList(
new DocumentFormat.OpenXml.Office2021.MipLabelMetaData.ClassificationLabel()
{
Id = "{f42aa342-8706-4288-bd11-ebb85995028c}", // Unique sensitivity label Id.
SiteId = "{72f988bf-86f1-41af-91ab-2d7cd011db47}", // Azure AD site Id.
Method = "Standard",
Enabled = true,
ContentBits = 0,
Removed = false,
});
VmlDrawingPart wsd = ExampleUtilities.GetSheet(sd, sheetName).AddNewPart<VmlDrawingPart>("rId1");
using (System.Xml.XmlTextWriter writer =
new System.Xml.XmlTextWriter(wsd.GetStream(FileMode.Create), System.Text.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:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1; visibility:hidden' fillcolor=\"infoBackground [80]\" strokecolor=\"none [81]\" o:insetmode=\"auto\"> <v:fill color2=\"infoBackground [80]\"/> <v:shadow color=\"none [81]\" 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, 3, 31, 4, 1</x:Anchor> <x:AutoFill>False</x:AutoFill> <x:Row>0</x:Row> <x:Column>0</x:Column> </x:ClientData> </v:shape></xml>");
writer.Flush();
}
}
}
}