private object getCellValueForAttrS(Cell cell, double cellValue) { object v = null; var styleIndex = cell.StyleIndex; // атрибут s if (styleIndex != null) { int si; if (int.TryParse(styleIndex.Value.ToString(), out si)) { CellFormats cfs = this.stylesheet.CellFormats; var en = cfs.Descendants <CellFormat>().GetEnumerator(); while (si-- >= 0) { en.MoveNext(); } CellFormat cf = en.Current; switch (cf.NumberFormatId.Value) { case 0: case 1: int i = (int)cellValue; v = i; // String.Format("{0:0}", i); break; case 4: case 167: v = cellValue; // String.Format("{0:#,##0.00}", cellValue); break; case 14: case 164: case 165: case 166: int days = (int)cellValue; DateTime date = (new DateTime(1900, 1, 1)).AddDays(days - 2); v = date; // String.Format("{0:yyyy-MM-dd}", date); break; default: v = cf.NumberFormatId.Value.ToString() + ": " + cell.CellValue.Text; break; } } else { v = cellValue; } } return(v); }
private static Dictionary<int, CellFormat> CreateCellFormatsList(CellFormats formats) { Dictionary<int, CellFormat> sharedFormatList = new Dictionary<int, CellFormat>(); int itemIndex = 0; foreach (CellFormat cell in formats.Descendants<CellFormat>()) { sharedFormatList.Add(itemIndex++, cell); } return sharedFormatList; }
public static void GetAndOrSetErrorStyleID(SpreadsheetDocument workdocument, Worksheet worksheet, Cell currentCell) { WorkbookStylesPart stylesPart = workdocument.WorkbookPart.WorkbookStylesPart; uint fillId = GetOrSetErrorFillID(workdocument); uint styleId = currentCell.StyleIndex ?? 0; CellFormats cellFormats = stylesPart.Stylesheet.CellFormats; CellFormat currentCellCellFormat = cellFormats.Descendants <CellFormat>().ElementAt((int)styleId); bool cellFormatComparation = true; uint checkedStyleIndex = 0; //iterate over all cellFormat of the page foreach (CellFormat cfItem in cellFormats) { checkedStyleIndex++; cellFormatComparation = true; //iterate over all attributes of the current cellFormat to check if all are the same foreach (var item in cfItem.GetAttributes()) { bool check = false; try { check = Equals(currentCellCellFormat.GetAttribute(item.LocalName, item.NamespaceUri), item); } catch (KeyNotFoundException) { check = false; break; } if (!check) // { cellFormatComparation = false; break; } } if (cellFormatComparation) { styleId = checkedStyleIndex; break; } } if (cellFormatComparation) { if (currentCellCellFormat.FillId != fillId) { CellFormat currentCellNewFormat = (CellFormat)currentCellCellFormat.CloneNode(true); currentCellNewFormat.FillId = fillId; cellFormats.AppendChild(currentCellNewFormat); cellFormats.Count++; } } else { cellFormats.AppendChild(new CellFormat() { BorderId = DefaultStyle, FillId = ErrorStyleFillId, FontId = DefaultStyle, NumberFormatId = DefaultStyle }); cellFormats.Count++; } styleId = (uint)cellFormats.Descendants <CellFormat>().Count(); currentCell.StyleIndex = styleId - 1; worksheet.Save(); }