· 8 years ago · Apr 02, 2018, 12:48 PM
1using System;
2using System.Collections;
3using System.Collections.Generic;
4using System.IO;
5using System.Linq;
6using DevExpress.SpreadsheetSource.Xlsx.Import;
7using Report_Sender.Settings;
8using DocumentFormat.OpenXml;
9using DocumentFormat.OpenXml.Packaging;
10using DocumentFormat.OpenXml.Spreadsheet;
11using DocumentFormat.OpenXml.Validation;
12
13
14namespace Report_Sender
15{
16 public class ExcelResultDocument2: ResultDocument
17 {
18 private readonly string startColumn;
19 private readonly SharedStringTablePart sharedStringTablePart;
20 private readonly WorksheetPart worksheetPart;
21 private readonly SpreadsheetDocument document;
22 private readonly bool templateAppend;
23 private Hashtable sharedStrings;
24 private readonly string workFileName;
25 private readonly string templateFileName;
26 private uint currentRow;
27 private List<ColumnInfo> table;
28 public ExcelResultDocument2(string fileNamePattern, string fileSeparationValue, IEnumerable<ColumnInfo> table, IEnumerable<ColumnFormat> formats, string ColumnName, uint RowNum, string sheetName, string fileName, bool append = false)
29 : base(fileNamePattern, fileSeparationValue, table, formats, fileName)
30 {
31 sharedStrings = new Hashtable();
32 templateAppend = append;
33 startColumn = ColumnName;
34 currentRow = RowNum;
35 templateFileName = fileName;
36 workFileName = fileNamePattern;
37 document = SpreadsheetDocument.Open(fileNamePattern, true);
38 this.table = table.ToList();
39 worksheetPart = GetWorksheetPartByName(document, sheetName);
40 sharedStringTablePart = document.WorkbookPart.SharedStringTablePart;
41 if (worksheetPart != null && append)
42 {
43
44 bool notfree;
45 do
46 {
47 notfree = false;
48 for (int i = 0; i < table.Count(); i++)
49 {
50 try
51 {
52 Cell cell = GetCell(worksheetPart.Worksheet, AddColumn(startColumn, i), currentRow);
53 if ( cell != null && !(cell.CellValue == null || cell.CellValue.InnerText == ""))
54 {
55 notfree = true;
56 currentRow++;
57 break;
58 }
59 }
60 catch
61 {
62 notfree = false;
63 }
64
65 }
66
67 } while (notfree);
68 }
69 FillSharedStrings(sharedStringTablePart);
70 }
71
72 public override void AddRow(IEnumerable<object> values)
73 {
74 string currentColumn = startColumn;
75 List<object> vals = values.ToList();
76 for (int i = 0; i < values.Count(); i++)
77 {
78 currentColumn = AddColumn(startColumn, i);
79 Cell cell = GetCell(worksheetPart.Worksheet, currentColumn, currentRow);
80
81 bool isString = false;
82
83 if (table[i].Type == typeof(Int32) || table[i].Type == typeof(Int64) ||
84 table[i].Type == typeof(Double) || table[i].Type == typeof(Decimal)
85 || table[i].Type == typeof(Byte) || table[i].Type == typeof(SByte) ||
86 table[i].Type == typeof(UInt16) || table[i].Type == typeof(UInt32)
87 || table[i].Type == typeof(UInt64) || table[i].Type == typeof(Int16) ||
88 table[i].Type == typeof(Single))
89 {
90 cell.DataType = CellValues.Number;
91 }
92 else if (table[i].Type == typeof(DateTime))
93 cell.DataType = new EnumValue<CellValues>(CellValues.Date);
94 else
95 {
96 cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
97 int strId;
98 if (sharedStrings.ContainsKey(vals[i].ToString()))
99 {
100 strId = (int)sharedStrings[vals[i].ToString()];
101 }
102 else
103 {
104 strId = InsertSharedStringItem(vals[i].ToString(), sharedStringTablePart);
105 }
106 cell.CellValue = new CellValue(strId.ToString());
107 isString = true;
108 }
109
110 if (!isString)
111 {
112
113 cell.CellValue = new CellValue(vals[i].ToString().Replace(',','.'));
114 }
115 }
116 currentRow++;
117 saveResult.AddRowCount();
118 }
119 // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
120 // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
121 private int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
122 {
123 // If the part does not contain a SharedStringTable, create one.
124 if (shareStringPart.SharedStringTable == null)
125 {
126 shareStringPart.SharedStringTable = new SharedStringTable();
127 }
128
129 int i = 0;
130
131 // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
132 foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
133 {
134 if (item.InnerText == text)
135 {
136 return i;
137 }
138
139 i++;
140 }
141
142 // The text does not exist in the part. Create the SharedStringItem and return its index.
143 shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
144 shareStringPart.SharedStringTable.Save();
145 sharedStrings.Add(text, i);
146 return i;
147 }
148
149 private void FillSharedStrings(SharedStringTablePart shareStringPart)
150 {
151 // If the part does not contain a SharedStringTable, create one.
152 if (shareStringPart.SharedStringTable == null)
153 {
154 shareStringPart.SharedStringTable = new SharedStringTable();
155 }
156
157 int i = 0;
158
159 // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
160 foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
161 {
162 sharedStrings.Add(item.InnerText, i);
163 i++;
164 }
165
166 }
167
168 public override void Dispose()
169 {
170 var uriPartDictionary = BuildUriPartDictionary();
171 try
172 {
173 for (int countTables = 0; countTables < 10; countTables++)
174 {
175 PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 =
176 (PivotTableCacheDefinitionPart)uriPartDictionary["/xl/pivotCache/pivotCacheDefinition" + countTables + ".xml"];
177 PivotCacheDefinition pivotCacheDefinition1 = pivotTableCacheDefinitionPart1.PivotCacheDefinition;
178 pivotCacheDefinition1.RefreshOnLoad = true;
179 }
180 }
181 catch
182 {
183 //таблицы больше не найдены, можно продолжать.
184 }
185
186 if (worksheetPart != null)
187 {
188 worksheetPart.Worksheet.Save();
189 }
190 if (stream != null)
191 stream.Dispose();
192 document.Dispose();
193
194 if (templateAppend)
195 File.Copy(workFileName, templateFileName, true);
196 }
197 private Dictionary<String, OpenXmlPart> BuildUriPartDictionary()
198 {
199 var uriPartDictionary = new Dictionary<String, OpenXmlPart>();
200 var queue = new Queue<OpenXmlPartContainer>();
201 queue.Enqueue(document);
202 while (queue.Count > 0)
203 {
204 foreach (var part in queue.Dequeue().Parts.Where(part => !uriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString())))
205 {
206 uriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
207 queue.Enqueue(part.OpenXmlPart);
208 }
209 }
210 return uriPartDictionary;
211 }
212
213 private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
214 {
215 IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
216 Elements<Sheet>().Where(s => s.Name == sheetName);
217
218 if (sheets.Count() == 0)
219 {
220 // The specified worksheet does not exist.
221 // todo: create a new sheet
222
223 return null;
224 }
225
226 string relationshipId = sheets.First().Id.Value;
227 WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
228 return worksheetPart;
229
230 }
231
232 // Given a worksheet, a column name, and a row index,
233 // gets the cell at the specified column and
234 private static Cell GetCell(Worksheet worksheet,
235 string columnName, uint rowIndex)
236 {
237 Row row = GetRow(worksheet, rowIndex);
238 string cellReference = columnName + rowIndex;
239 if (row == null)
240 return null;
241
242 Cell ce = row.Elements<Cell>().FirstOrDefault(c => String.Compare(c.CellReference.Value, columnName + rowIndex, StringComparison.OrdinalIgnoreCase) == 0);
243 if (ce == null)
244 {
245 if (row.Elements<Cell>().Count(c => c.CellReference.Value == columnName + rowIndex) > 0)
246 {
247 return row.Elements<Cell>().First(c => c.CellReference.Value == cellReference);
248 }
249 else
250 {
251 // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
252 Cell refCell = null;
253 foreach (Cell cell in row.Elements<Cell>())
254 {
255 if (cell.CellReference.Value.Length == cellReference.Length)
256 {
257 if (String.Compare(cell.CellReference.Value, cellReference, StringComparison.OrdinalIgnoreCase) > 0)
258 {
259 refCell = cell;
260 break;
261 }
262 }
263 }
264
265 Cell newCell = new Cell() {CellReference = cellReference};
266 row.InsertBefore(newCell, refCell);
267
268 worksheet.Save();
269 return newCell;
270 }
271 }
272 return ce;
273 }
274
275
276 // Given a worksheet and a row index, return the row.
277 private static Row GetRow(Worksheet sheetData, uint rowIndex)
278 {
279 Row row;
280 if (sheetData.Elements<Row>().Count(r => r.RowIndex == rowIndex) != 0)
281 {
282 row = sheetData.Elements<Row>().First(r => r.RowIndex == rowIndex);
283 }
284 else
285 {
286 if (sheetData.GetFirstChild<SheetData>().Elements<Row>().Count(r => r.RowIndex == rowIndex) > 0)
287 {
288 row = sheetData.GetFirstChild<SheetData>().Elements<Row>().First(r => r.RowIndex == rowIndex);
289 }
290 else
291 {
292 //return null;
293 row = new Row() {RowIndex = rowIndex};
294 sheetData.GetFirstChild<SheetData>().Append(row);
295 }
296 }
297 return row;
298 }
299
300 private static string AddColumn(string input, int num)
301 {
302 for (int i = 0; i < num; i++)
303 {
304 input = AddOneColumn(input);
305 }
306
307 return input;
308 }
309 private static string AddOneColumn(string input)
310 {
311 string output = "";
312 input = input.ToUpper();
313 char tmp = input[input.Length - 1];
314 if (tmp != 'Z')
315 {
316 tmp = (char) (((int) tmp) + 1);
317 output = input.Substring(0, input.Length - 1) + tmp;
318 }
319 else
320 {
321 tmp = 'A';
322 if (input.Length > 1)
323 {
324 output = AddOneColumn(input.Substring(0, input.Length - 1)) + tmp;
325 }
326 else output = "AA";
327
328 }
329 //output = input.Substring(0, input.Length - 1) + tmp;
330
331 return output;
332 }
333 }
334}