CNTNm3MX

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