static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
#region fts (fonts)
Fonts fts = new Fonts();
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Verdana");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ft.Append(new Bold());
ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
#endregion
#region fills
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00efffd9");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00f8e8d6");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
#endregion
#region borders
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
#endregion
#region csfs (CellStyleFormats)
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
#endregion
uint iExcelIndex = 164;
var nfs = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
var nfDateTime = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
var nfDate = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDate.FormatCode = StringValue.FromString("dd/mm/yyyy");
nfs.Append(nfDate);
var nf4decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
var nf2decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
var nfForcedText = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// #,##0.00 is also Excel style index 4
var nf0decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf0decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf0decimal.FormatCode = StringValue.FromString("#,##0");
nfs.Append(nf0decimal);
// index 1
cf = new CellFormat();
cf.NumberFormatId = nfDate.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nf0decimal.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1; //Verdana 18
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 2; //Arial 11, Bold
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 3; //Light Orange
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 8
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 2; //Arial 11, Bold
cf.FillId = 2; //Light Green
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cf.Append(new Alignment()
{
WrapText = true
});
cfs.Append(cf);
// index 10
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 3; //Light Orange
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cf.Append(new Alignment()
{
WrapText = true
});
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
ss.Append(tss);
return(ss);
}
private static MSOpenXML.Stylesheet CreateStylesheet(int pQuantidadeDecimais = 2)
{
MSOpenXML.Stylesheet ss = new MSOpenXML.Stylesheet();
#region Fontes
MSOpenXML.Fonts fts = new MSOpenXML.Fonts();
MSOpenXML.Font ft = new MSOpenXML.Font();
MSOpenXML.FontName ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
MSOpenXML.FontSize ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new MSOpenXML.Font();
ft.Bold = new MSOpenXML.Bold();
ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
#endregion
#region Preenchimento
MSOpenXML.Fills fills = new MSOpenXML.Fills();
MSOpenXML.Fill fill;
MSOpenXML.PatternFill patternFill;
fill = new MSOpenXML.Fill();
patternFill = new MSOpenXML.PatternFill();
patternFill.PatternType = MSOpenXML.PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
/*fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Gray125;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*
* fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Solid;
* patternFill.ForegroundColor = new dos.ForegroundColor();
* patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
* patternFill.BackgroundColor = new dos.BackgroundColor();
* patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*/
fills.Count = (uint)fills.ChildElements.Count;
#endregion
#region Bordas
MSOpenXML.Borders borders = new MSOpenXML.Borders();
MSOpenXML.Border border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.TopBorder.Style = MSOpenXML.BorderStyleValues.Thin;
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
#endregion
MSOpenXML.CellStyleFormats csfs = new MSOpenXML.CellStyleFormats();
MSOpenXML.CellFormat cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
MSOpenXML.NumberingFormats nfs = new MSOpenXML.NumberingFormats();
MSOpenXML.CellFormats cfs = new MSOpenXML.CellFormats();
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
MSOpenXML.NumberingFormat nfDateTime = new MSOpenXML.NumberingFormat();
nfDateTime.NumberFormatId = iExcelIndex++;
nfDateTime.FormatCode = "dd/mm/yyyy";
nfs.Append(nfDateTime);
MSOpenXML.NumberingFormat nf4decimal = new MSOpenXML.NumberingFormat();
nf4decimal.NumberFormatId = iExcelIndex++;
nf4decimal.FormatCode = "#,##0";
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
MSOpenXML.NumberingFormat nf2decimal = new MSOpenXML.NumberingFormat();
nf2decimal.NumberFormatId = iExcelIndex++;
nf2decimal.FormatCode = FormatoDecimal(pQuantidadeDecimais); //"#,##0.00"
nfs.Append(nf2decimal);
// @ is also Excel style index 49
MSOpenXML.NumberingFormat nfForcedText = new MSOpenXML.NumberingFormat();
nfForcedText.NumberFormatId = iExcelIndex++;
nfForcedText.FormatCode = "@";
nfs.Append(nfForcedText);
// index 1
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 2
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 3
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 4
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 5
// Header text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cf.Alignment.Horizontal = MSOpenXML.HorizontalAlignmentValues.Center;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// index 6
// group text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 7
// Total text, ColumnHeader Text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
MSOpenXML.CellStyles css = new MSOpenXML.CellStyles();
MSOpenXML.CellStyle cs = new MSOpenXML.CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
MSOpenXML.DifferentialFormats dfs = new MSOpenXML.DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
MSOpenXML.TableStyles tss = new MSOpenXML.TableStyles();
tss.Count = 0;
//tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
//tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
ss.Append(tss);
return(ss);
}
