/// <summary>
/// <see cref="IMCItem"/> の色情報を使用して、<see cref="Fill"/> を生成します。
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private Fill _createFillFromMCItem(IMCItem item)
{
// "#RRGGBB" => "FFRRGGBB"
var backgroundColorCode = HexBinaryValue.FromString(this._mcItemToArgbCode(item));
var pFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
pFill.Append(new ForegroundColor()
{
Rgb = backgroundColorCode
});
pFill.Append(new BackgroundColor()
{
Rgb = backgroundColorCode
});
var fill = new Fill();
fill.Append(pFill);
return(fill);
}
/// <summary>
/// Create a new fill
/// </summary>
/// <param name="fillColorRgb">The foreground color of the fill</param>
/// <param name="pattern">The fill pattern</param>
/// <returns></returns>
public static Fill CreateFill(string fillColorRgb, PatternValues?pattern = null)
{
PatternFill patternFill = new PatternFill();
if (!string.IsNullOrEmpty(fillColorRgb))
{
patternFill.PatternType = pattern ?? PatternValues.Solid;
ForegroundColor foregroundColor = new ForegroundColor()
{
Rgb = fillColorRgb
};
BackgroundColor backgroundColor = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill.Append(foregroundColor);
patternFill.Append(backgroundColor);
}
else
{
patternFill.PatternType = pattern ?? PatternValues.None;
}
return(new Fill(patternFill));
}
private static UInt32Value CreateFill(Stylesheet templateSheetobj, string colorCode)
{
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor fgColor = new ForegroundColor()
{
Rgb = colorCode
};
BackgroundColor bgBolor = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill.Append(fgColor);
patternFill.Append(bgBolor);
Fill fill = new Fill(patternFill);
templateSheetobj.Fills.Append(fill);
UInt32Value result = templateSheetobj.Fills.Count;
templateSheetobj.Fills.Count++;
return(result);
}
public int SetConditionStyle(string fontName = DefaultFont, double fontSize = 10D, bool fontBold = false, string fontColor = null, string fill = null)
{
DifferentialFormat format = new DifferentialFormat();
Font font = new Font();
font.Append(new FontName()
{
Val = fontName
});
font.Append(new FontSize()
{
Val = fontSize
});
if (fontColor == null)
{
font.Append(new Color()
{
Theme = (UInt32Value)1U
});
}
else
{
font.Append(new Color()
{
Rgb = fontColor
});
}
if (fontBold)
{
font.Append(new Bold());
}
format.Append(font);
if (fill != null)
{
Fill f = new Fill();
PatternFill p = new PatternFill()
{
PatternType = PatternValues.Solid
};
p.Append(new ForegroundColor()
{
Rgb = fill
});
p.Append(new BackgroundColor()
{
Rgb = fill
});
f.Append(p);
format.Append(f);
}
return(this.SetStyle <DifferentialFormat>(format));
}
private UInt32Value SetCellStyle(System.Drawing.Color fillColor, bool locked)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = System.Drawing.Color.FromArgb(fillColor.A, fillColor.R, fillColor.G, fillColor.B).Name
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill.Append(foregroundColor1);
patternFill.Append(backgroundColor1);
fill.Append(patternFill);
ExcelStyleSheet.Fills.Count++;
ExcelStyleSheet.Fills.Append(fill);
CellFormat myCellFormat =
new CellFormat
{
FontId = (UInt32Value)0U,
FillId = (UInt32Value)ExcelStyleSheet.Fills.Count - 1,
ApplyFill = true,
NumberFormatId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyBorder = true,
ApplyProtection = true
};
Alignment alignment = new Alignment()
{
WrapText = true
};
myCellFormat.Append(alignment);
if (!locked)
{
Protection protection = new Protection()
{
Locked = false
};
myCellFormat.Append(protection);
}
ExcelStyleSheet.CellFormats.Count++;
ExcelStyleSheet.CellFormats.Append(myCellFormat);
return((UInt32Value)ExcelStyleSheet.CellFormats.Count - 1);
}
static Fills GenerateFills()
{
var fills = new Fills {
Count = 3U
};
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
var fill2 = new Fill();
var patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
var fill3 = new Fill();
var patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
var foregroundColor1 = new ForegroundColor()
{
Theme = (UInt32Value)0U, Tint = -0.14999847407452621D
};
var backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills.Append(fill1);
fills.Append(fill2);
fills.Append(fill3);
return(fills);
}
public void AddFill(string name, string color, string c)
{
var filepath = getOutputDir() + name;
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true)) {
var iter = spreadSheet.WorkbookPart.WorksheetParts.GetEnumerator();
iter.MoveNext();
var workSheetPart = iter.Current;
Cell cell = InsertCellInWorksheet(GetColumnName(c), GetRowIndex(c), workSheetPart);
Fills fs = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills;
var cf = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = color
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill1.Append(foregroundColor1);
patternFill1.Append(backgroundColor1);
fill1.Append(patternFill1);
fs.Append(fill1);
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = (UInt32)(fs.Elements <Fill>().Count() - 1), BorderId = 0, FormatId = 0, ApplyFill = true
};
cf.Append(cellFormat2);
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
cell.StyleIndex = (UInt32)(spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements <CellFormat>().Count() - 1);
workSheetPart.Worksheet.Save();
}
}
private void pushFillsToStylesheet(Stylesheet ss)
{
var fills = ss.Fills;
var xn = _doc.SelectSingleNode("stylesheet/fills");
var nlst = xn.SelectNodes("fill");
Console.WriteLine(nlst.Count);
foreach (XmlNode f in nlst)
{
var fill_id = f.Attributes.GetNamedItem("id").Value;
var fill_type = f.Attributes.GetNamedItem("type").Value;
Console.WriteLine(fill_type);
var fill_color = f.Attributes.GetNamedItem("color").Value;
Console.WriteLine(fill_color);
Fill fill = new Fill();
PatternFill pf = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor fc = new ForegroundColor()
{
Rgb = fill_color
};
BackgroundColor bc = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
pf.Append(fc);
pf.Append(bc);
fill.Append(pf);
fills.Append(fill);
var fill_idx = fills.Elements <Fill>().Count() - 1;
fill_map.Add(fill_id, fill_idx);
}
}
public int SetFill(string color)
{
Fill fill = new Fill();
PatternFill pattern = new PatternFill()
{
PatternType = PatternValues.Solid
};
pattern.Append(new ForegroundColor()
{
Rgb = color
});
pattern.Append(new BackgroundColor()
{
Rgb = color
});
fill.Append(pattern);
return(this.SetStyle <Fill>(fill));
}
private static Fill CreateFill(string color)
{
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
patternFill.Append(new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(color)
});
patternFill.Append(new BackgroundColor()
{
Indexed = (UInt32Value)64U
});
Fill fill = new Fill()
{
PatternFill = patternFill
};
return(fill);
}
private static Fill Fill3()
{
// ReSharper disable PossiblyMistakenUseOfParamsMethod
//
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.Solid
};
var foregroundColor1 = new ForegroundColor {
Rgb = "B8CCE4"
};
var backgroundColor1 = new BackgroundColor {
Indexed = 64U
};
patternFill1.Append(foregroundColor1);
patternFill1.Append(backgroundColor1);
fill1.Append(patternFill1);
return(fill1);
// ReSharper restore PossiblyMistakenUseOfParamsMethod
}
public EWFill(System.Drawing.Color color, EWPattern pattern = EWPattern.Solid)
{
_oxFill = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = pattern.As <PatternValues>()
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = color.ToHexString()
};
//BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
//patternFill3.Append(backgroundColor1);
_oxFill.Append(patternFill3);
}
static void Main(string[] args)
{
var workbookClosed = new XLWorkbook();
var sheet = workbookClosed.Worksheets.Add("Sheet1");
sheet.Cell(1, 1).Value = 1;
sheet.Cell(2, 1).Value = -1;
workbookClosed.SaveAs("closed.xlsx");
using (SpreadsheetDocument report = SpreadsheetDocument.Open("closed.xlsx", true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.PrependChild<ConditionalFormatting>(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
public static Stylesheet GenerateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)3U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 14D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold2 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 18D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold2);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold1 = new Bold();
FontSize fontSize3 = new FontSize()
{
Val = 30D
};
Color color3 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName3 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme3 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font3.Append(bold1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
// 0 no BG color just normal Excel
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
// 1 red or open CAT 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FFFF0000"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill2.Append(foregroundColor2);
patternFill2.Append(backgroundColor2);
fill2.Append(patternFill2);
// 2 silver or N/A
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor3 = new ForegroundColor()
{
Rgb = "FFCCCCCC"
};
BackgroundColor backgroundColor3 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor3);
patternFill3.Append(backgroundColor3);
fill3.Append(patternFill3);
// 3 green or NaF
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor4 = new ForegroundColor()
{
Rgb = "FF50CC83"
};
BackgroundColor backgroundColor4 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor4);
patternFill4.Append(backgroundColor4);
fill4.Append(patternFill4);
// 4 black&white or Not Reviewed
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor5 = new ForegroundColor()
{
Rgb = "FFFFFFFF"
};
BackgroundColor backgroundColor5 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill5.Append(foregroundColor5);
patternFill5.Append(backgroundColor5);
fill5.Append(patternFill5);
// 5 red or open CAT 1
Fill fill6 = new Fill();
PatternFill patternFill6 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor6 = new ForegroundColor()
{
Rgb = "FFE53935"
};
BackgroundColor backgroundColor6 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill6.Append(foregroundColor6);
patternFill6.Append(backgroundColor6);
fill6.Append(patternFill6);
// 6 orange open CAT 2
Fill fill7 = new Fill();
PatternFill patternFill7 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor7 = new ForegroundColor()
{
Rgb = "FFFFA500"
};
BackgroundColor backgroundColor7 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill7.Append(foregroundColor7);
patternFill7.Append(backgroundColor7);
fill7.Append(patternFill7);
// 7 orange open CAT 3
Fill fill8 = new Fill();
PatternFill patternFill8 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor8 = new ForegroundColor()
{
Rgb = "FFD8D80E"
};
BackgroundColor backgroundColor8 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill8.Append(foregroundColor8);
patternFill8.Append(backgroundColor8);
fill8.Append(patternFill8);
// 8 gray background for any general use
Fill fill9 = new Fill();
PatternFill patternFill9 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor9 = new ForegroundColor()
{
Rgb = "FFBBBBBB"
};
BackgroundColor backgroundColor9 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill9.Append(foregroundColor9);
patternFill9.Append(backgroundColor9);
fill9.Append(patternFill9);
fills1.Append(fill1); // these are 0 based when you include in the CellFormat statements below, so 1 off
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
fills1.Append(fill6);
fills1.Append(fill7);
fills1.Append(fill8);
fills1.Append(fill9);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)4U
};
// style index 0: normal font and wrapping of text for cell rows
CellFormat cellFormat2 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 1: normal font with numerical format
CellFormat cellFormat3 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
// style index 2: title font of 30 bold
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// style index 3: info row under title and header rows font bold size 18
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// fill colors based on the fillx variables above to match the 4 statuses of the checklist vulnerabilities
// style index 4: red or Open
CellFormat cellFormat6 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 5: silver or Not Applicable
CellFormat cellFormat7 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 6: green or Not a Finding
CellFormat cellFormat8 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 7: b/w or Not Reviewed
CellFormat cellFormat9 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// CENTER The content for listings versus the excel export of a checklist above
// style index 8: red or Open centered content
CellFormat cellFormat10 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 9: silver or Not Applicable
CellFormat cellFormat11 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 10: green or Not a Finding
CellFormat cellFormat12 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 11: b/w or Not Reviewed
CellFormat cellFormat13 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 12: orange Open CAT 2
CellFormat cellFormat14 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)6U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 13: yellow Open CAT 3
CellFormat cellFormat15 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)7U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 14: orange Open CAT 2 centered
CellFormat cellFormat16 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)6U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 15: yellow Open CAT 3 centered
CellFormat cellFormat17 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)7U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 16: yellow Open CAT 3 centered
CellFormat cellFormat18 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)8U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// add all these formats
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
cellFormats1.Append(cellFormat18);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0, BuiltinId = 0
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
// Generates content of workbookStylesPart1.
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet1 = new Stylesheet();
Fills fills1 = new Fills()
{
Count = (UInt32Value)3U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders = new Borders()
{
Count = (UInt32Value)6U
};
Border noBorder = new Border(); //id 0
borders.Append(noBorder);
Border leftBorder = new Border(); //id 1
Border rightBorder = new Border(); //id 2
Border topBorder = new Border(); //id 3
Border botBorder = new Border(); //id 4
Border allBorder = new Border(); //id 5
LeftBorder leftBorder1 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
leftBorder.Append(leftBorder1);
RightBorder rightBorder1 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
rightBorder.Append(rightBorder1);
TopBorder topBorder1 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
topBorder.Append(topBorder1);
BottomBorder bottomBorder1 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
botBorder.Append(bottomBorder1);
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
allBorder.Append(leftBorder2);
allBorder.Append(rightBorder2);
allBorder.Append(topBorder2);
allBorder.Append(bottomBorder2);
borders.Append(leftBorder);
borders.Append(rightBorder);
borders.Append(topBorder);
borders.Append(botBorder);
borders.Append(allBorder);
Fonts fonts = new Fonts();
Font defFont = new Font();
FontSize dFontSize = new FontSize()
{
Val = 11D
};
FontName dFontName = new FontName()
{
Val = "Calibri"
};
defFont.Append(dFontSize);
defFont.Append(dFontName);
fonts.Append(defFont);
foreach (FontStyleFormat f in fontStyleFormatList)
{
Font font = new Font();
if (f.Bold)
{
Bold bold = new Bold();
font.Append(bold);
}
if (f.Italic)
{
Italic italic = new Italic();
font.Append(italic);
}
if (f.Underline)
{
Underline underline = new Underline();
font.Append(underline);
}
FontSize fontSize = new FontSize()
{
Val = f.Size
};
font.Append(fontSize);
FontName fontName = new FontName()
{
Val = f.FontName
};
font.Append(fontName);
fonts.Append(font);
}
CellStyleFormats cellStyleFormats1 = new CellStyleFormats();
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats = new CellFormats();
CellFormat dCellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
cellFormats.Append(dCellFormat);
int rgbId = 1;
foreach (CellStyleFormat f in cellStyleFormatList)
{
rgbId++;
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "F" + f.RgbHtmlCode
};
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill3);
int treeadsw = 0;
if (f.Treead)
{
treeadsw = 4;
}
int lineStyle;
switch (f.LineStyle)
{
default: lineStyle = 0; break;
case 1: lineStyle = 1; break;
case 2: lineStyle = 2; break;
case 3: lineStyle = 3; break;
case 4: lineStyle = 4; break;
case 5: lineStyle = 5; break;
}
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)(UInt32)treeadsw, FontId = (UInt32Value)(UInt32)f.FontIndex, FillId = (UInt32Value)(UInt32)rgbId, BorderId = (UInt32Value)(UInt32)lineStyle, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyBorder = true, ApplyNumberFormat = f.Treead, ApplyFill = true
};
cellFormats.Append(cellFormat);
VerticalAlignmentValues vav;
HorizontalAlignmentValues hav;
switch (f.HorizontalAlignment)
{
default: hav = HorizontalAlignmentValues.Distributed; break;
case 1: hav = HorizontalAlignmentValues.Left; break;
case 2: hav = HorizontalAlignmentValues.Right; break;
case 3: hav = HorizontalAlignmentValues.Center; break;
}
switch (f.VerticalAlignment)
{
default: vav = VerticalAlignmentValues.Distributed; break;
case 1: vav = VerticalAlignmentValues.Bottom; break;
case 2: vav = VerticalAlignmentValues.Center; break;
case 3: vav = VerticalAlignmentValues.Top; break;
}
Alignment alignment = new Alignment()
{
Horizontal = hav, Vertical = vav, WrapText = f.WrapText
};
cellFormat.Append(alignment);
}
CellStyles cellStyles1 = new CellStyles();
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats();
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
stylesheet1.Append(fonts);
stylesheet1.Append(fills1);
stylesheet1.Append(borders);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart.Stylesheet = stylesheet1;
}
private static Stylesheet SetDefault()
{
//Fonts
var fonts = new Fonts();
var font0 = new Font();
var fontSize0 = new FontSize {
Val = 10
};
font0.AppendChild(fontSize0);
var font1 = new Font();
var fontSize1 = new FontSize {
Val = 12
};
var fontBold1 = new Bold();
var fontColor1 = new Color {
Rgb = "FFFFFF"
};
font1.AppendChild(fontSize1);
font1.AppendChild(fontBold1);
font1.AppendChild(fontColor1);
fonts.AppendChild(font0);
fonts.AppendChild(font1);
// Alignments
var alignmentCenter = new Alignment();
alignmentCenter.Horizontal = HorizontalAlignmentValues.Center;
//Fill
var fills = new Fills();
var fill0 = new Fill();
var patternFill0 = new PatternFill {
PatternType = PatternValues.None
};
fill0.Append(patternFill0);
var fillSkip = new Fill(); // Not valid needs to skip
var patternFillSkip = new PatternFill {
PatternType = PatternValues.None
};
fillSkip.Append(patternFillSkip);
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.Solid
};
var foregroundColor1 = new ForegroundColor {
Rgb = "00000000"
};
var backgroundColor1 = new BackgroundColor {
Indexed = 64U
};
patternFill1.Append(foregroundColor1);
patternFill1.Append(backgroundColor1);
fill1.Append(patternFill1);
fills.Append(fill0);
fills.Append(fillSkip);
fills.Append(fill1);
//Border
var borders = new Borders();
var border0 = new Border();
var border1 = new Border();
borders.AppendChild(border0);
borders.AppendChild(border1);
var cellFormats = new CellFormats(
new CellFormat {
FontId = 0, FillId = 0, BorderId = 0, ApplyFill = true
}, // Index 0
new CellFormat {
FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true, Alignment = alignmentCenter
} // Index 1
);
return(new Stylesheet(fonts, fills, borders, cellFormats));
}
private UInt32Value SetCellStyle(System.Drawing.Color fillColor, bool locked)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = System.Drawing.Color.FromArgb(fillColor.A, fillColor.R, fillColor.G, fillColor.B).Name };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill.Append(foregroundColor1);
patternFill.Append(backgroundColor1);
fill.Append(patternFill);
ExcelStyleSheet.Fills.Count++;
ExcelStyleSheet.Fills.Append(fill);
CellFormat myCellFormat =
new CellFormat
{
FontId = (UInt32Value)0U,
FillId = (UInt32Value)ExcelStyleSheet.Fills.Count - 1,
ApplyFill = true,
NumberFormatId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyBorder = true,
ApplyProtection = true
};
Alignment alignment = new Alignment() { WrapText = true };
myCellFormat.Append(alignment);
if (!locked)
{
Protection protection = new Protection() { Locked = false };
myCellFormat.Append(protection);
}
ExcelStyleSheet.CellFormats.Count++;
ExcelStyleSheet.CellFormats.Append(myCellFormat);
return (UInt32Value)ExcelStyleSheet.CellFormats.Count - 1;
}
private static Stylesheet createStylesheetCurrencies()
{
Stylesheet workbookstylesheet = new Stylesheet();
UInt32Value ID1 = 164U;
UInt32Value ID2 = 166U;
//<NumberingFormats>
NumberingFormats numberingFormats1 = new NumberingFormats();
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = ID1, FormatCode = "#,##0.0000\\ \"Kč\""
};
NumberingFormat numberingFormat2 = new NumberingFormat()
{
NumberFormatId = ID2, FormatCode = "#,##0.000"
};
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
//<Fonts>
Fonts fonts1 = new Fonts();
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
font1.Append(fontSize1);
font1.Append(fontName1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize()
{
Val = 10D
};
FontName fontName2 = new FontName()
{
Val = "Lucida Sans Unicode"
};
font2.Append(fontSize2);
font2.Append(fontName2);
fonts1.Append(font1);
fonts1.Append(font2);
// <Fills>
Fills fills1 = new Fills();
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFA9A9A9"
}; //nadpis
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)66U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
// <Borders>
Borders borders1 = new Borders();
Border border0 = new Border();
border0.TopBorder = new TopBorder();
border0.BottomBorder = new BottomBorder();
border0.LeftBorder = new LeftBorder();
border0.RightBorder = new RightBorder();
border0.TopBorder.Style = BorderStyleValues.Medium;
border0.BottomBorder.Style = BorderStyleValues.Medium;
border0.LeftBorder.Style = BorderStyleValues.Medium;
border0.RightBorder.Style = BorderStyleValues.Medium;
Border border1 = new Border();
border1.RightBorder = new RightBorder();
border1.LeftBorder = new LeftBorder();
border1.RightBorder.Style = BorderStyleValues.Thin;
border1.LeftBorder.Style = BorderStyleValues.Thin;
Border border2 = new Border();
border2.TopBorder = new TopBorder();
border2.BottomBorder = new BottomBorder();
border2.TopBorder.Style = BorderStyleValues.Medium;
border2.BottomBorder.Style = BorderStyleValues.Medium;
Border border3 = new Border();
border3.RightBorder = new RightBorder();
border3.RightBorder.Style = BorderStyleValues.Thin;
// <APENDING Borders>
borders1.Append(border0);
borders1.Append(border1);
borders1.Append(border2);
borders1.Append(border3);
//Formats
// <CellFormats>
CellFormats cellformats1 = new CellFormats();
//Formát prázdno
CellFormat cellformat0 = new CellFormat()
{
FontId = 0, FillId = 2, ApplyFont = true, ApplyFill = true
};
//Formát typu 2
CellFormat cellformat1 = new CellFormat()
{
FontId = 1, BorderId = 1, ApplyFont = true, ApplyBorder = true
};
//Formát typu 3 pro plain text
CellFormat cellformat2 = new CellFormat()
{
FontId = 1, BorderId = 2, ApplyFont = true, ApplyBorder = true
};
//Formát typu 3 pro množství
CellFormat cellformat3 = new CellFormat()
{
FontId = 1, BorderId = 3, ApplyFont = true, ApplyBorder = true
};
//Formát typu 3 pro měnu
CellFormat cellformat4 = new CellFormat()
{
FontId = 1, BorderId = 1, NumberFormatId = ID1, ApplyFont = true, ApplyBorder = true, ApplyNumberFormat = true
};
//Formát typu 3 pro MJ
CellFormat cellformat5 = new CellFormat()
{
FontId = 1, BorderId = 3, NumberFormatId = ID1, ApplyFont = true, ApplyBorder = true, ApplyNumberFormat = true
};
CellFormat cellformat6 = new CellFormat()
{
FontId = 1, BorderId = 0, NumberFormatId = ID1, ApplyFont = true, ApplyBorder = true, ApplyNumberFormat = true
};
CellFormat cellformat7 = new CellFormat()
{
FontId = 1, BorderId = 0, ApplyFont = true, ApplyBorder = true
};
// <APENDING CellFormats>
cellformats1.Append(cellformat0);
cellformats1.Append(cellformat1);
cellformats1.Append(cellformat2);
cellformats1.Append(cellformat3);
cellformats1.Append(cellformat4);
cellformats1.Append(cellformat5);
cellformats1.Append(cellformat6);
cellformats1.Append(cellformat7);
// Append NUMBERINGFORMATS, FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
workbookstylesheet.Append(numberingFormats1);
workbookstylesheet.Append(fonts1);
workbookstylesheet.Append(fills1);
workbookstylesheet.Append(borders1);
workbookstylesheet.Append(cellformats1);
/*stylesheet.Stylesheet = workbookstylesheet;
* stylesheet.Stylesheet.Save();*/
return(workbookstylesheet);
}
private static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)5U
};
// FillId = 0
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
// FillId = 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
// FillId = 2,RED
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFFF0000"
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
// FillId = 3,BLUE
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FF0070C0"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
// FillId = 4,YELLO
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor3 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
BackgroundColor backgroundColor3 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)4U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
// создает ситили форматы
private void GenWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts1 = new Fonts();
Font font1 = new Font();
fonts1.Append(font1);
Fills fills1 = new Fills();
Fill fill1 = new Fill();
Fill fill2 = new Fill();
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders();
Border border1 = new Border();
borders1.Append(border1);
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)2U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
};
NumberingFormat numberingFormat2 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)166U, FormatCode = "#,##0\"р.\""
};
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)5U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U
}; // Формат № 0 по умолчанию общий
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)164U
}; // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)166U
}; //формат № 2 для денег FormatCode = "#,##0.00\"р.\""
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U
}; //формат № 3 для дат * 14.03.2015
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)2U
}; //формат № 4 для чисел
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
stylesheet.Append(numberingFormats1);
stylesheet.Append(fonts1);
stylesheet.Append(fills1);
stylesheet.Append(borders1);
stylesheet.Append(cellFormats1);
workbookStylesPart.Stylesheet = stylesheet;
}
private void AddConditionalFormatting()
{
//use openxml directly to apply conditional formatting.
using (SpreadsheetDocument report = SpreadsheetDocument.Open(fileName, true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting() {
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.Append(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
internal static int AddCaptainFormat(Stylesheet stylesheet, uint borderId)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FF305496"
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = 64
};
patternFill.Append(foregroundColor1);
patternFill.Append(backgroundColor1);
fill.Append(patternFill);
if (stylesheet.Fills == null)
{
stylesheet.Fills = new Fills();
stylesheet.Fills.Count = 0U;
}
stylesheet.Fills.AppendChild <Fill>(fill);
uint fillId = (uint)stylesheet.Fills.Count++;
Font font = new Font()
{
Color = new Color()
{
Rgb = "FFFFFFFF"
}
};
stylesheet.Fonts.AppendChild(font);
uint fontId = stylesheet.Fonts.Count++;
var cellFormat = new CellFormat();
cellFormat.FontId = fontId;
cellFormat.FillId = fillId;
cellFormat.BorderId = borderId;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center
});
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
}
// Generates content of workbookStyles.
private void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStyles)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)43U, FormatCode = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"
};
numberingFormats1.Append(numberingFormat1);
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)4U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold2 = new Bold();
Italic italic1 = new Italic();
FontSize fontSize3 = new FontSize()
{
Val = 11D
};
Color color3 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName3 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme3 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
Font font4 = new Font();
Italic italic2 = new Italic();
FontSize fontSize4 = new FontSize()
{
Val = 11D
};
Color color4 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName4 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme4 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font4.Append(italic2);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontScheme4);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
Fills fills1 = new Fills()
{
Count = (UInt32Value)4U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Theme = (UInt32Value)0U, Tint = -4.9989318521683403E-2D
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FFFFFFCC"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders()
{
Count = (UInt32Value)2U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder();
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)16U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment1 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment2 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat5.Append(alignment2);
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment3 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat6.Append(alignment3);
CellFormat cellFormat7 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment4 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat7.Append(alignment4);
CellFormat cellFormat8 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment5 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right
};
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment6 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat9.Append(alignment6);
CellFormat cellFormat10 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment7 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat10.Append(alignment7);
CellFormat cellFormat11 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment8 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat11.Append(alignment8);
CellFormat cellFormat12 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true
};
CellFormat cellFormat13 = new CellFormat()
{
NumberFormatId = (UInt32Value)9U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment9 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat13.Append(alignment9);
CellFormat cellFormat14 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment10 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat14.Append(alignment10);
CellFormat cellFormat15 = new CellFormat()
{
NumberFormatId = (UInt32Value)37U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true
};
Alignment alignment11 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat15.Append(alignment11);
CellFormat cellFormat16 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
CellFormat cellFormat17 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
//StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
//stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
//stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStyles.Stylesheet = stylesheet1;
}
//описвывает форматы ячеек
protected void GenWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts1 = new Fonts();
Font font1 = new Font();
Font font2 = new Font();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color1 = new Color() { Rgb = "FF00B050" };
FontName fontName2 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontCharSet fontCharSet1 = new FontCharSet() { Val = 204 };
font2.Append(fontSize2);
font2.Append(color1);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering1);
font2.Append(fontCharSet1);
//fonts1.Append(font2);
fonts1.Append(font1);
Fills fills1 = new Fills();
Fill fill1 = new Fill();
Fill fill2 = new Fill();
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "0000FF" };
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders();
Border border1 = new Border();
borders1.Append(border1);
//Переопределяем номера формата 164U, 166U, 167U, 168U,
NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)3U };
NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)164U, FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy" };
NumberingFormat numberingFormat2 = new NumberingFormat() { NumberFormatId = (UInt32Value)166U, FormatCode = "#,##0.000\"р.\"" };
NumberingFormat numberingFormat3 = new NumberingFormat() { NumberFormatId = (UInt32Value)167U, FormatCode = "#,##0\"р.\"" }; //"#,##0.00\"р.\"" };
NumberingFormat numberingFormat4 = new NumberingFormat() { NumberFormatId = (UInt32Value)168U, FormatCode = "#,##0.00\"р.\"" };
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
numberingFormats1.Append(numberingFormat3);
numberingFormats1.Append(numberingFormat4);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)6U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U }; // Формат № 0 по умолчанию общий
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)164U }; // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)166U }; //формат № 2 для денег по тысяч. ###,### <-- (number/ 1000)
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)14U }; //формат № 3 для дат * 14.03.2015
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)2U }; //формат № 4 для чисел
CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)1U }; //более подходящий формат № 5U для чисел (5U)
CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)165U };//формат №6U для денег по умолчанию
CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)167U };// формат №7U для денег(переопределенный) по три разряда ### ### р.
CellFormat cellFormat10 = new CellFormat() { NumberFormatId = (UInt32Value)168U };// формат №8U для денег(переопределенный) ### ###,00 р.
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
stylesheet.Append(numberingFormats1);
stylesheet.Append(fonts1);
stylesheet.Append(fills1);
stylesheet.Append(borders1);
stylesheet.Append(cellFormats1);
workbookStylesPart.Stylesheet = stylesheet;
}
private static Stylesheet CreateStylesheet()
{
// Stylesheet declarion and namespace
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// List of fonts
Fonts fontsList = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
// FontId=0 - Regular Excel font
Font font0 = new Font();
FontSize fontSize0 = new FontSize()
{
Val = 11D
};
Color color0 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName0 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering0 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme0 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font0.Append(fontSize0);
font0.Append(color0);
font0.Append(fontName0);
font0.Append(fontFamilyNumbering0);
font0.Append(fontScheme0);
// FontId=1 - Bold font for header
Font font1 = new Font();
Bold bold = new Bold();
font1.Append(bold);
fontsList.Append(font0);
fontsList.Append(font1);
// List of fills
Fills fillList = new Fills()
{
Count = (UInt32Value)3U
};
// FillId = 0, Normal background
Fill fill0 = new Fill();
PatternFill patternFill0 = new PatternFill()
{
PatternType = PatternValues.None
};
fill0.Append(patternFill0);
// FillId = 00, Normal background
Fill fill00 = new Fill();
PatternFill patternFill00 = new PatternFill()
{
PatternType = PatternValues.None
};
fill00.Append(patternFill00);
// FillId = 1, Light Blue for alternating cells
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFDEEFF7"
};
patternFill1.Append(foregroundColor1);
fill1.Append(patternFill1);
fillList.Append(fill0);
fillList.Append(fill00);
fillList.Append(fill1);
// Borders style
Borders bordersList = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
bordersList.Append(border1);
// List of cell styles formats
CellStyleFormats cellStyleFormatsList = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormatsList.Append(cellFormat);
// Cells formats
CellFormats cellFormatsList = new CellFormats()
{
Count = (UInt32Value)5U
};
// StyleIndex = 0 - Regular font
CellFormat cellFormat0 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 1 - Bold font
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 2 - Date (Short Date)
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 3 - Light blue background (Text and Numbers)
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
// StyleIndex = 4 - Light blue background (Date)
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
cellFormatsList.Append(cellFormat0);
cellFormatsList.Append(cellFormat1);
cellFormatsList.Append(cellFormat2);
cellFormatsList.Append(cellFormat3);
cellFormatsList.Append(cellFormat4);
// Cells styles
CellStyles cellStyleList = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle0 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyleList.Append(cellStyle0);
DifferentialFormats differentialFormats0 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles0 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheet.Append(fontsList);
stylesheet.Append(fillList);
stylesheet.Append(bordersList);
stylesheet.Append(cellStyleFormatsList);
stylesheet.Append(cellFormatsList);
stylesheet.Append(cellStyleList);
stylesheet.Append(differentialFormats0);
stylesheet.Append(tableStyles0);
stylesheet.Append(stylesheetExtensionList);
return(stylesheet);
}
internal void Save()
{
var stylesPart = sd.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
CellFormats cellFormats = new CellFormats();
Fills fills = new Fills();
// There are two fills that seemingly need to be always in there
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill();
patternFill1.PatternType = PatternValues.None;
fill1.Append(patternFill1);
fills.Append(fill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill();
patternFill2.PatternType = PatternValues.Gray125;
fill2.Append(patternFill2);
fills.Append(fill2);
// And two XFs
CellFormat cellFormat1 = new CellFormat();
cellFormat1.FillId = (UInt32)0;
cellFormats.Append(cellFormat1);
CellFormat cellFormat2 = new CellFormat();
cellFormat2.FillId = (UInt32)1;
cellFormats.Append(cellFormat2);
foreach (var pal in palette)
{
CellFormat cellFormat = new CellFormat();
cellFormat.FillId = (UInt32)(pal.Value + 2);
cellFormats.Append(cellFormat);
Fill fill = new Fill();
PatternFill patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
ForegroundColor fgc = new ForegroundColor();
fgc.Rgb = "FF" + ColorTranslator.ToHtml(pal.Key).Replace("#", "");
patternFill.Append(fgc);
fill.Append(patternFill);
fills.Append(fill);
}
// Add dummy borders and fonts, which we need for the cellXfs to work
Borders borders = new Borders();
Border border = new Border();
borders.Append(border);
Fonts fonts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fonts.Append(font);
// Add the other things we made to the stylesheet
stylesPart.Stylesheet.Append(fonts);
stylesPart.Stylesheet.Append(fills);
stylesPart.Stylesheet.Append(borders);
stylesPart.Stylesheet.Append(cellFormats);
sd.Save();
sd.Close();
}
// создает ситили форматы
private void GenWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts1 = new Fonts();
Font font1 = new Font();
fonts1.Append(font1);
Fills fills1 = new Fills();
Fill fill1 = new Fill();
Fill fill2 = new Fill();
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders();
Border border1 = new Border();
borders1.Append(border1);
//Переопределяем номера формата 164U, 166U, 167U, 168U,
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)3U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
};
NumberingFormat numberingFormat2 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)166U, FormatCode = "#,##0.000\"р.\""
};
NumberingFormat numberingFormat3 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)167U, FormatCode = "#,##0\"р.\""
}; //"#,##0.00\"р.\"" };
NumberingFormat numberingFormat4 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)168U, FormatCode = "#,##0.00\"р.\""
};
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
numberingFormats1.Append(numberingFormat3);
numberingFormats1.Append(numberingFormat4);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)6U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U
}; // Формат № 0 по умолчанию общий
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)164U
}; // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)166U
}; //формат № 2 для денег по тысяч. ###,### <-- (number/ 1000)
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U
}; //формат № 3 для дат * 14.03.2015
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)2U
}; //формат № 4 для чисел
CellFormat cellFormat7 = new CellFormat()
{
NumberFormatId = (UInt32Value)1U
}; //более подходящий формат № 5U для чисел (5U)
CellFormat cellFormat8 = new CellFormat()
{
NumberFormatId = (UInt32Value)165U
}; //формат №6U для денег по умолчанию
CellFormat cellFormat9 = new CellFormat()
{
NumberFormatId = (UInt32Value)167U
}; // формат №7U для денег(переопределенный) по три разряда ### ### р.
CellFormat cellFormat10 = new CellFormat()
{
NumberFormatId = (UInt32Value)168U
}; // формат №8U для денег(переопределенный) ### ###,00 р.
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
stylesheet.Append(numberingFormats1);
stylesheet.Append(fonts1);
stylesheet.Append(fills1);
stylesheet.Append(borders1);
stylesheet.Append(cellFormats1);
workbookStylesPart.Stylesheet = stylesheet;
}
// Generates content of workbookStyles.
private void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStyles)
{
Stylesheet stylesheet1 = new Stylesheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats(){ Count = (UInt32Value)1U };
NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)43U, FormatCode = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)" };
numberingFormats1.Append(numberingFormat1);
Fonts fonts1 = new Fonts(){ Count = (UInt32Value)4U, KnownFonts = true };
Font font1 = new Font();
FontSize fontSize1 = new FontSize(){ Val = 11D };
Color color1 = new Color(){ Theme = (UInt32Value)1U };
FontName fontName1 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme1 = new FontScheme(){ Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize(){ Val = 11D };
Color color2 = new Color(){ Theme = (UInt32Value)1U };
FontName fontName2 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme2 = new FontScheme(){ Val = FontSchemeValues.Minor };
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold2 = new Bold();
Italic italic1 = new Italic();
FontSize fontSize3 = new FontSize(){ Val = 11D };
Color color3 = new Color(){ Rgb = "FFFF0000" };
FontName fontName3 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme3 = new FontScheme(){ Val = FontSchemeValues.Minor };
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
Font font4 = new Font();
Italic italic2 = new Italic();
FontSize fontSize4 = new FontSize(){ Val = 11D };
Color color4 = new Color(){ Rgb = "FFFF0000" };
FontName fontName4 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme4 = new FontScheme(){ Val = FontSchemeValues.Minor };
font4.Append(italic2);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontScheme4);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
Fills fills1 = new Fills(){ Count = (UInt32Value)4U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill(){ PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill(){ PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill(){ PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor(){ Theme = (UInt32Value)0U, Tint = -4.9989318521683403E-2D };
BackgroundColor backgroundColor1 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill(){ PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor(){ Rgb = "FFFFFFCC" };
BackgroundColor backgroundColor2 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders(){ Count = (UInt32Value)2U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder();
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color5 = new Color(){ Indexed = (UInt32Value)64U };
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats(){ Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)16U };
CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment1 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment2 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat5.Append(alignment2);
CellFormat cellFormat6 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment3 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat6.Append(alignment3);
CellFormat cellFormat7 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment4 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat7.Append(alignment4);
CellFormat cellFormat8 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment5 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Right };
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment6 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat9.Append(alignment6);
CellFormat cellFormat10 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment7 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat10.Append(alignment7);
CellFormat cellFormat11 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment8 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat11.Append(alignment8);
CellFormat cellFormat12 = new CellFormat(){ NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat13 = new CellFormat(){ NumberFormatId = (UInt32Value)9U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment9 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat13.Append(alignment9);
CellFormat cellFormat14 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment10 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat14.Append(alignment10);
CellFormat cellFormat15 = new CellFormat(){ NumberFormatId = (UInt32Value)37U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment11 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat15.Append(alignment11);
CellFormat cellFormat16 = new CellFormat(){ NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat17 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension(){ Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles(){ DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
//StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
//stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
//stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStyles.Stylesheet = stylesheet1;
}
