private static bool MatchFormat(XDocument sXDoc, XElement xf, Cell cell) { if ((int?)xf.Attribute(SSNoNamespace.applyNumberFormat) != 1 && cell.FormatCode == null) return true; if (xf.Attribute(SSNoNamespace.applyNumberFormat) == null && cell.FormatCode != null) return false; int numFmtId = (int)xf.Attribute(SSNoNamespace.numFmtId); int? nfi = null; if (cell.FormatCode != null) { if (Cell.StandardFormats.ContainsKey(cell.FormatCode)) nfi = Cell.StandardFormats[cell.FormatCode]; if (nfi == numFmtId) return true; } XElement numFmts = sXDoc .Root .Element(S.numFmts); if (numFmts == null) return false; XElement numFmt = numFmts .Elements(S.numFmt) .FirstOrDefault(numFmtElement => (int)numFmtElement.Attribute(SSNoNamespace.numFmtId) == numFmtId); if (numFmt == null) return false; string styleFormatCode = (string)numFmt.Attribute(SSNoNamespace.formatCode); bool match = styleFormatCode == cell.FormatCode; return match; }
private static bool MatchFont(XDocument sXDoc, XElement xf, Cell cell) { if (((int?)xf.Attribute(SSNoNamespace.applyFont) == 0 || xf.Attribute(SSNoNamespace.applyFont) == null) && (cell.Bold == null || cell.Bold == false) && (cell.Italic == null || cell.Italic == false)) return true; if (((int?)xf.Attribute(SSNoNamespace.applyFont) == 0 || xf.Attribute(SSNoNamespace.applyFont) == null) && (cell.Bold == true || cell.Italic == true)) return false; int fontId = (int)xf.Attribute(SSNoNamespace.fontId); XElement font = sXDoc .Root .Element(S.fonts) .Elements(S.font) .ElementAt(fontId); XElement fabFont = new XElement(S.font, cell.Bold == true ? new XElement(S.b) : null, cell.Italic == true ? new XElement(S.i) : null); bool match = XNode.DeepEquals(font, fabFont); return match; }
private static bool MatchAlignment(XDocument sXDoc, XElement xf, Cell cell) { if ((int?)xf.Attribute(SSNoNamespace.applyAlignment) == 0 || (xf.Attribute(SSNoNamespace.applyAlignment) == null) && cell.HorizontalCellAlignment == null) return true; if (xf.Attribute(SSNoNamespace.applyAlignment) == null && cell.HorizontalCellAlignment != null) return false; string alignment = (string)xf.Element(S.alignment).Attribute(SSNoNamespace.horizontal); bool match = alignment == cell.HorizontalCellAlignment.ToString().ToLower(); return match; }
private static int GetFontId(XDocument sXDoc, Cell cell) { XElement fonts = sXDoc.Root.Element(S.fonts); if (fonts == null) { fonts = new XElement(S.fonts, new XAttribute(SSNoNamespace.count, 1), new XElement(S.font, cell.Bold == true ? new XElement(S.b) : null, cell.Italic == true ? new XElement(S.i) : null)); sXDoc.Root.Add(fonts); return 0; } XElement font = new XElement(S.font, cell.Bold == true ? new XElement(S.b) : null, cell.Italic == true ? new XElement(S.i) : null); fonts.Add(font); int count = (int)fonts.Attribute(SSNoNamespace.count); fonts.SetAttributeValue(SSNoNamespace.count, count + 1); return count; }
private static bool CompareStyles(XDocument sXDoc, XElement xf, Cell cell) { bool matchFont = MatchFont(sXDoc, xf, cell); bool matchAlignment = MatchAlignment(sXDoc, xf, cell); bool matchFormat = MatchFormat(sXDoc, xf, cell); return (matchFont && matchAlignment && matchFormat); }
private static int CreateNewStyle(XDocument sXDoc, Cell cell, SpreadsheetDocument sDoc) { XAttribute applyFont = null; XAttribute fontId = null; if (cell.Bold == true || cell.Italic == true) { applyFont = new XAttribute(SSNoNamespace.applyFont, 1); fontId = new XAttribute(SSNoNamespace.fontId, GetFontId(sXDoc, cell)); } XAttribute applyAlignment = null; XElement alignment = null; if (cell.HorizontalCellAlignment != null) { applyAlignment = new XAttribute(SSNoNamespace.applyAlignment, 1); alignment = new XElement(S.alignment, new XAttribute(SSNoNamespace.horizontal, cell.HorizontalCellAlignment.ToString().ToLower())); } XAttribute applyNumberFormat = null; XAttribute numFmtId = null; if (cell.FormatCode != null) { if (Cell.StandardFormats.ContainsKey(cell.FormatCode)) { applyNumberFormat = new XAttribute(SSNoNamespace.applyNumberFormat, 1); numFmtId = new XAttribute(SSNoNamespace.numFmtId, Cell.StandardFormats[cell.FormatCode]); } else { applyNumberFormat = new XAttribute(SSNoNamespace.applyNumberFormat, 1); numFmtId = new XAttribute(SSNoNamespace.numFmtId, GetNumFmtId(sXDoc, cell.FormatCode)); } } XElement newXf = new XElement(S.xf, applyFont, fontId, applyAlignment, alignment, applyNumberFormat, numFmtId); XElement cellXfs = sXDoc .Root .Element(S.cellXfs); if (cellXfs == null) { cellXfs = new XElement(S.cellXfs, new XAttribute(SSNoNamespace.count, 1), newXf); return 0; } else { int currentCount = (int)cellXfs.Attribute(SSNoNamespace.count); cellXfs.SetAttributeValue(SSNoNamespace.count, currentCount + 1); cellXfs.Add(newXf); return currentCount; } }
private static int GetCellStyle(SpreadsheetDocument sDoc, Cell cell) { XDocument sXDoc = sDoc.WorkbookPart.WorkbookStylesPart.GetXDocument(); var match = sXDoc .Root .Element(S.cellXfs) .Elements(S.xf) .Select((e, i) => new { Element = e, Index = i, }) .FirstOrDefault(xf => CompareStyles(sXDoc, xf.Element, cell)); if (match != null) return match.Index; // if no match, then create a style int newId = CreateNewStyle(sXDoc, cell, sDoc); return newId; }
private void CreateColumnHeadings(PSObject obj) { foreach (PSPropertyInfo property in obj.Properties) { if (columnHeadings.Count == 0) { SSW.Cell columnHeading = new SSW.Cell(); columnHeading.Value = property.Name; columnHeadings.Add(columnHeading); } else if (!columnHeadings.Exists(e => e.Value.Equals(property.Name))) { SSW.Cell columnHeading = new SSW.Cell(); columnHeading.Value = property.Name; columnHeadings.Add(columnHeading); } } }
private void CreateColumns() { switch (type) { case Types.ReferenceType: { foreach (PSObject obj in processedObjects) { if (!typeCollection.Contains(obj.BaseObject.GetType())) { typeCollection.Add(obj.BaseObject.GetType()); CreateColumnHeadings(obj); } } break; } case Types.ScalarType: { SSW.Cell columnHeading = new SSW.Cell(); columnHeading.Value = processedObjects.First().BaseObject.GetType().FullName; columnHeadings.Add(columnHeading); break; } case Types.ScalarTypes: { SSW.Cell indexColumnHeading = new SSW.Cell(); indexColumnHeading.Value = "Index"; columnHeadings.Add(indexColumnHeading); SSW.Cell valueColumnHeading = new SSW.Cell(); valueColumnHeading.Value = "Value"; columnHeadings.Add(valueColumnHeading); SSW.Cell typeColumnHeading = new SSW.Cell(); typeColumnHeading.Value = "Type"; columnHeadings.Add(typeColumnHeading); break; } default: { break; } } }
private List<SSW.Row> GetRowCollection(string[][] result) { List<SSW.Row> rowCollection = new List<SSW.Row>(); for (int i = 0; i < result.Count(); i++) { string[] dataRow = result[i]; SSW.Row row = new SSW.Row(); List<SSW.Cell> cellCollection = new List<SSW.Cell>(); for (int j = 0; j < dataRow.Count(); j++) { SSW.Cell dataCell = new SSW.Cell(); dataCell.Value = dataRow[j]; dataCell.HorizontalCellAlignment = SSW.HorizontalCellAlignment.Left; dataCell.CellDataType = CellDataType.String; cellCollection.Add(dataCell); } row.Cells = cellCollection; rowCollection.Add(row); } return rowCollection; }