public Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
Font ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(8)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(18)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(9)
},
Bold = new Bold()
};//new Bold() { Val = new BooleanValue(true) }
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(14)
}
};
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.None
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Gray125
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
},
BackgroundColor = new BackgroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
}
}
};
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thick
},
RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thick
},
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thick
},
BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thick
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Double
},
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
};
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("@")
};
nfs.Append(nfForcedText);
// index 1
cf = new CellFormat()
{
NumberFormatId = nfDateTime.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 2
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 3
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 4
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 5 Header text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 6 column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center)
};
cfs.Append(cf);
// index 7 coloured 2 decimal text
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 8 coloured column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 9
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 10
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center
};
cfs.Append(cf);
// index 11
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 3, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 12
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 13
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 3, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(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);
var css = new CellStyles();
DocumentFormat.OpenXml.Spreadsheet.CellStyle cs = new DocumentFormat.OpenXml.Spreadsheet.CellStyle()
{
Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
TableStyles tss = new TableStyles()
{
Count = 0, DefaultTableStyle = StringValue.FromString("TableStyleMedium9"), 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);
}