forked from OfficeDev/open-xml-docs
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Program.cs
153 lines (130 loc) · 4.95 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
// <Snippet0>
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.Linq;
// <Snippet1>
// Given a document, a worksheet name, a column name, and a one-based row index,
// deletes the text from the cell at the specified column and row on the specified worksheet.
static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook?.GetFirstChild<Sheets>()?.Elements<Sheet>()?.Where(s => s.Name is not null && s.Name == sheetName);
if (sheets is null || sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
string? relationshipId = sheets.First()?.Id?.Value;
if (relationshipId is null)
{
// The worksheet does not have a relationship ID.
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);
// Get the cell at the specified column and row.
Cell? cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
if (cell is null)
{
// The specified cell does not exist.
return;
}
cell.Remove();
worksheetPart.Worksheet.Save();
}
}
// </Snippet1>
// <Snippet2>
// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
static Cell? GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
IEnumerable<Row>? rows = worksheet.GetFirstChild<SheetData>()?.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex);
if (rows is null || rows.Count() == 0)
{
// A cell does not exist at the specified row.
return null;
}
IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference?.Value, columnName + rowIndex, true) == 0);
if (cells.Count() == 0)
{
// A cell does not exist at the specified column, in the specified row.
return null;
}
return cells.FirstOrDefault();
}
// </Snippet2>
// <Snippet3>
// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer
// reference the specified SharedStringItem and removes the item.
static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
bool remove = true;
if (document.WorkbookPart is null)
{
return;
}
foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
{
var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();
if (cells is null)
{
continue;
}
foreach (var cell in cells)
{
// Verify if other cells in the document reference the item.
if (cell.DataType is not null &&
cell.DataType.Value == CellValues.SharedString &&
cell.CellValue?.Text == shareStringId.ToString())
{
// Other cells in the document still reference the item. Do not remove the item.
remove = false;
break;
}
}
if (!remove)
{
break;
}
}
// Other cells in the document do not reference the item. Remove the item.
if (remove)
{
SharedStringTablePart? shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
if (shareStringTablePart is null)
{
return;
}
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
if (item is not null)
{
item.Remove();
// Refresh all the shared string references.
foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
{
var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();
if (cells is null)
{
continue;
}
foreach (var cell in cells)
{
if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString && int.TryParse(cell.CellValue?.Text, out int itemIndex))
{
if (itemIndex > shareStringId)
{
cell.CellValue.Text = (itemIndex - 1).ToString();
}
}
}
part.Worksheet.Save();
}
document.WorkbookPart.SharedStringTablePart?.SharedStringTable.Save();
}
}
}
// </Snippet3>
// </Snippet0>
DeleteTextFromCell(args[0], args[1], args[2], uint.Parse(args[3]));