/// <summary>
/// Append TimelineStyle element.
/// </summary>
/// <param name="filePath">Target file path</param>
/// <param name="log">Logger</param>
public void AddTimelineStyle(string filePath, VerifiableLog log)
{
//Add TimelineStyle
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
try
{
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = this.StylesheetExtensionUri
};
stylesheetExtension.AppendChild <X15.TimelineStyles>(new X15.TimelineStyles()
{
DefaultTimelineStyle = this.timelineStyleName3
});
package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.AppendChild <StylesheetExtension>(stylesheetExtension);
log.Pass("Append TimelineStyle element. DefaultTimelineStyle attribute value=[{0}]", this.timelineStyleName3);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Add Fail:[" + e.Message + "]");
}
}
}
/// <summary>
/// Append TimelineStyle element.
/// </summary>
public void AddTimelineStyle(Stream stream)
{
// Add TimelineStyle
using (var package = SpreadsheetDocument.Open(stream, true))
{
var stylesheetExtension = new StylesheetExtension()
{
Uri = _stylesheetExtensionUri
};
stylesheetExtension.AppendChild(new TimelineStyles()
{
DefaultTimelineStyle = TimelineStyleName3
});
package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.AppendChild <StylesheetExtension>(stylesheetExtension);
}
}
/// <summary>
/// Append TimelineStyle element.
/// </summary>
/// <param name="filePath">Target file path</param>
/// <param name="log">Logger</param>
public void AddTimelineStyle(string filePath, VerifiableLog log)
{
//Add TimelineStyle
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = this.StylesheetExtensionUri
};
stylesheetExtension.AppendChild <X15.TimelineStyles>(new X15.TimelineStyles()
{
DefaultTimelineStyle = this.timelineStyleName3
});
package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.AppendChild <StylesheetExtension>(stylesheetExtension);
log.Pass("Append TimelineStyle element. DefaultTimelineStyle attribute value=[{0}]", this.timelineStyleName3);
}
}
/// <summary>
/// Append TimelineStyle element.
/// </summary>
/// <param name="filePath">Target file path</param>
/// <param name="log">Logger</param>
public void AddTimelineStyle(string filePath, VerifiableLog log)
{
//Add TimelineStyle
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
try
{
StylesheetExtension stylesheetExtension = new StylesheetExtension() { Uri = this.StylesheetExtensionUri };
stylesheetExtension.AppendChild<X15.TimelineStyles>(new X15.TimelineStyles() { DefaultTimelineStyle = this.timelineStyleName3 });
package.WorkbookPart.WorkbookStylesPart.Stylesheet.StylesheetExtensionList.AppendChild<StylesheetExtension>(stylesheetExtension);
log.Pass("Append TimelineStyle element. DefaultTimelineStyle attribute value=[{0}]", this.timelineStyleName3);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Add Fail:[" + e.Message + "]");
}
}
}
public WorkbookStylesPart Do(WorkbookPart workbookPart, params XlsxCellStyle[] styles)
{
var fonts = styles.Select(x => new Font(
Map(x.FontStyle),
new FontSize {
Val = x.FontSize
},
new Color {
Rgb = x.FontCollorRgb
}, //{Theme = 1U}
new FontName {
Val = x.FontFamily
},
new FontFamilyNumbering {
Val = 2
},
new FontCharSet {
Val = 204
},
new FontScheme {
Val = FontSchemeValues.Minor
}
)).ToArray();
var fills = new[] {
new Fill(new PatternFill {
PatternType = PatternValues.None
}),
new Fill(new PatternFill {
PatternType = PatternValues.None
})
}.Concat(styles.Select(x => x.Fill == null
? new Fill(new PatternFill {
PatternType = PatternValues.None
})
: new Fill(new PatternFill(
new ForegroundColor {
Rgb = x.Fill.FillForegroundColorArgb
}, //{Theme = 1U}
new BackgroundColor {
Rgb = x.Fill.FillBackgroundColorArgb
} //{Theme = 1U}
)
{
PatternType = PatternValues.Solid
})
)).ToArray();
var borders = new OpenXmlElement[] {
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()
),
new Border(
new LeftBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
}
),
new Border(
new LeftBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thick
},
new RightBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
}
)
}; // Color color4 = new Color(){ Indexed = (UInt32Value)64U };
var cellStyleFormats = new OpenXmlElement[] {
new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
}
};
var cellFormats = styles.Select((x, index) => new CellFormat(
new Alignment {
Horizontal = x.HorizontalAlignment,
Vertical = x.VerticalAlignment,
WrapText = x.WrapText
})
{
NumberFormatId = 0U,
FontId = (uint)index,
FillId = (uint)index + 2,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyAlignment = true
}
).ToArray();
var cellStyles = new OpenXmlElement[] {
new CellStyle {
Name = "ќбычный", FormatId = 0U, BuiltinId = 0U
}
};
var differentialFormats = new OpenXmlElement[] { };
var tableStyles = new OpenXmlElement[] { };
var slicerStyleExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
slicerStyleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
slicerStyleExtension.AppendChild(new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles {
DefaultSlicerStyle = "SlicerStyleLight1"
});
//var timelineStyleExtension = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//timelineStyleExtension.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//timelineStyleExtension.AppendChild(new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" });
var 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");
stylesheet.Append(
new Fonts(fonts)
{
Count = (uint)fonts.Count(), KnownFonts = true
},
new Fills(fills)
{
Count = (uint)fills.Count()
},
new Borders(borders)
{
Count = (uint)borders.Count()
},
new CellStyleFormats(cellStyleFormats)
{
Count = (uint)cellStyleFormats.Count()
},
new CellFormats(cellFormats)
{
Count = (uint)cellFormats.Count()
},
new CellStyles(cellStyles)
{
Count = (uint)cellStyles.Count()
},
new DifferentialFormats(differentialFormats)
{
Count = (uint)differentialFormats.Count()
},
new TableStyles(tableStyles)
{
Count = (uint)tableStyles.Count(),
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleMedium9"
},
new StylesheetExtensionList(
slicerStyleExtension //,
//timelineStyleExtension
)
);
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = stylesheet;
return(workbookStylesPart);
}
public 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");
var fonts1 = AddFonts();
var fills1 = AddFills();
var borders1 = AddBorders();
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.AppendChild(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 4U
};
// Black text on White background
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
// White text on Orange background
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 2U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// White text on Blue background
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 3U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// Black text on Yellow background
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 4U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
cellFormats1.AppendChild(cellFormat2);
cellFormats1.AppendChild(cellFormat3);
cellFormats1.AppendChild(cellFormat4);
cellFormats1.AppendChild(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.AppendChild(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet1.AppendChild(fonts1);
stylesheet1.AppendChild(fills1);
stylesheet1.AppendChild(borders1);
stylesheet1.AppendChild(cellStyleFormats1);
stylesheet1.AppendChild(cellFormats1);
stylesheet1.AppendChild(cellStyles1);
stylesheet1.AppendChild(differentialFormats1);
stylesheet1.AppendChild(tableStyles1);
stylesheet1.AppendChild(stylesheetExtensionList);
return(stylesheet1);
}
private void CreateStylesheet(WorkbookPart workbookPart)
{
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId101");
var stylesheet = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
stylesheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var numberingFormats = new NumberingFormats(new NumberingFormat
{
NumberFormatId = 44U,
FormatCode = FORMAT_CODE
})
{
Count = 1U
};
var fonts = new Fonts {
Count = 3U, KnownFonts = true
};
var font1 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font2 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font3 = new Font(new Bold(), new FontSize {
Val = 11D
}, new Color {
Theme = 0U
},
new FontName {
Val = "Calibri"
}, new FontFamilyNumbering {
Val = 2
},
new FontScheme {
Val = FontSchemeValues.Minor
});
fonts.Append(font1, font2, font3);
var fills = new Fills {
Count = 3U
};
var fill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
});
var fill2 = new Fill(new PatternFill {
PatternType = PatternValues.Gray125
});
var fill3 = new Fill(new PatternFill(
new ForegroundColor {
Rgb = "FF0070C0"
},
new BackgroundColor {
Indexed = 64U
})
{
PatternType = PatternValues.Solid
});
fills.Append(fill1, fill2, fill3);
var borders = new Borders(new Border(), new LeftBorder(), new RightBorder(), new TopBorder(),
new BottomBorder(), new DiagonalBorder())
{
Count = 1U
};
var cellStyleFormats = new CellStyleFormats {
Count = 2U
};
var cellFormat1 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
var cellFormat2 = new CellFormat
{
NumberFormatId = 44U,
FontId = 1U,
FillId = 0U,
BorderId = 0U,
ApplyFont = false,
ApplyFill = false,
ApplyBorder = false,
ApplyAlignment = false,
ApplyProtection = false
};
cellStyleFormats.Append(cellFormat1, cellFormat2);
var cellFormats = new CellFormats {
Count = 6U
};
var cellFormat3 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
var cellFormat4 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
var protection1 = new Protection {
Locked = false
};
cellFormat4.AppendChild(protection1);
var cellFormat5 = new CellFormat
{
NumberFormatId = 44U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 1U,
ApplyFont = true,
ApplyProtection = true
};
var protection2 = new Protection {
Locked = false
};
cellFormat5.AppendChild(protection2);
var cellFormat6 = new CellFormat
{
NumberFormatId = 14U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyNumberFormat = true,
ApplyProtection = true
};
var protection3 = new Protection {
Locked = false
};
cellFormat6.AppendChild(protection3);
var cellFormat7 = new CellFormat
{
NumberFormatId = 0U,
FontId = 2U,
FillId = 2U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyFill = true,
ApplyProtection = true
};
var cellFormat8 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
cellFormats.Append(cellFormat3, cellFormat4, cellFormat5, cellFormat6, cellFormat7, cellFormat8);
var cellStyles = new CellStyles {
Count = 2U
};
var cellStyle1 = new CellStyle {
Name = "Currency", FormatId = 1U, BuiltinId = 4U
};
var cellStyle2 = new CellStyle {
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles.Append(cellStyle1, cellStyle2);
var differentialFormats = new DifferentialFormats {
Count = 0U
};
var tableStyles = new TableStyles
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
var stylesheetExtensionList = new StylesheetExtensionList();
var stylesheetExtension = new StylesheetExtension {
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14",
X14_SCHEMA);
var slicerStyles = new X14.SlicerStyles {
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet.Append(numberingFormats, fonts, fills, borders, cellStyleFormats, cellFormats, cellStyles,
differentialFormats, tableStyles, stylesheetExtensionList);
workbookStylesPart.Stylesheet = stylesheet;
}
