private static bool MatchFont(XDocument sXDoc, XElement xf, CellDfn 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 CompareStyles(XDocument sXDoc, XElement xf, CellDfn 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 bool MatchFormat(XDocument sXDoc, XElement xf, CellDfn 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 (CellDfn.StandardFormats.ContainsKey(cell.FormatCode))
{
nfi = CellDfn.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 MatchAlignment(XDocument sXDoc, XElement xf, CellDfn 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 GetCellStyle(SpreadsheetDocument sDoc, CellDfn 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 static int GetFontId(XDocument sXDoc, CellDfn 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 List<SSW.RowDfn> GetRowCollection(string[][] result)
{
List<SSW.RowDfn> rowCollection = new List<SSW.RowDfn>();
for (int i = 0; i < result.Count(); i++)
{
string[] dataRow = result[i];
SSW.RowDfn row = new SSW.RowDfn();
List<SSW.CellDfn> cellCollection = new List<SSW.CellDfn>();
for (int j = 0; j < dataRow.Count(); j++)
{
SSW.CellDfn dataCell = new SSW.CellDfn();
dataCell.Value = dataRow[j];
dataCell.HorizontalCellAlignment = SSW.HorizontalCellAlignment.Left;
dataCell.CellDataType = CellDataType.String;
cellCollection.Add(dataCell);
}
row.Cells = cellCollection;
rowCollection.Add(row);
}
return rowCollection;
}
private static int GetCellStyle(SpreadsheetDocument sDoc, CellDfn 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 static bool MatchFormat(XDocument sXDoc, XElement xf, CellDfn 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 (CellDfn.StandardFormats.ContainsKey(cell.FormatCode))
nfi = CellDfn.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, CellDfn 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, CellDfn 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 bool CompareStyles(XDocument sXDoc, XElement xf, CellDfn 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 GetFontId(XDocument sXDoc, CellDfn 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 int CreateNewStyle(XDocument sXDoc, CellDfn 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 (CellDfn.StandardFormats.ContainsKey(cell.FormatCode))
{
applyNumberFormat = new XAttribute(SSNoNamespace.applyNumberFormat, 1);
numFmtId = new XAttribute(SSNoNamespace.numFmtId, CellDfn.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 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.CellDfn columnHeading = new SSW.CellDfn();
columnHeading.Value = processedObjects.First().BaseObject.GetType().FullName;
columnHeadings.Add(columnHeading);
break;
}
case Types.ScalarTypes:
{
SSW.CellDfn indexColumnHeading = new SSW.CellDfn();
indexColumnHeading.Value = "Index";
columnHeadings.Add(indexColumnHeading);
SSW.CellDfn valueColumnHeading = new SSW.CellDfn();
valueColumnHeading.Value = "Value";
columnHeadings.Add(valueColumnHeading);
SSW.CellDfn typeColumnHeading = new SSW.CellDfn();
typeColumnHeading.Value = "Type";
columnHeadings.Add(typeColumnHeading);
break;
}
default:
{
break;
}
}
}
private static int CreateNewStyle(XDocument sXDoc, CellDfn 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 (CellDfn.StandardFormats.ContainsKey(cell.FormatCode))
{
applyNumberFormat = new XAttribute(SSNoNamespace.applyNumberFormat, 1);
numFmtId = new XAttribute(SSNoNamespace.numFmtId, CellDfn.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 void CreateColumnHeadings(PSObject obj)
{
foreach (PSPropertyInfo property in obj.Properties)
{
if (columnHeadings.Count == 0)
{
SSW.CellDfn columnHeading = new SSW.CellDfn();
columnHeading.Value = property.Name;
columnHeadings.Add(columnHeading);
}
else if (!columnHeadings.Exists(e => e.Value.Equals(property.Name)))
{
SSW.CellDfn columnHeading = new SSW.CellDfn();
columnHeading.Value = property.Name;
columnHeadings.Add(columnHeading);
}
}
}
IEnumerable<RowDfn> GetRowsEnum(TestTable table)
{
var border = CellStyleBorder.CreateBoxBorder(CellStyleBorder.Thin);
var defaultCellStyle = new CellStyleDfn
{
Border = border,
};
var dateCellStyle = new CellStyleDfn
{
Border = border,
NumFmt = new CellStyleNumFmt { formatCode = "yyyy/mm/dd" },
};
int rowCount = table.RowCount;
var rows = new List<RowDfn>();
for (int r = 0; r < rowCount; r++)
{
var datarow = table.GetNextRow();
var cells = new CellDfn[]
{
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col1 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col2 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col3 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col4 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col5 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col6 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col7 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col8 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col9 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col10 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col11 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col12 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col13 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col14 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col15 },
};
var row = new RowDfn { Cells = cells };
//yield return row;
rows.Add(row);
}
return rows.ToArray();
}
