void grid_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)
{
if (e.Element == ExportElement.HeaderCell || e.Element == ExportElement.CommonColumnHeader)
{
PatternFill fill = new PatternFill(PatternType.Solid, Colors.Blue, Colors.Transparent);
(e.VisualParameters as GridViewDocumentVisualExportParameters).Style = new CellSelectionStyle() { Fill = fill };
}
}
public static uint ajoutFill(Stylesheet ss, string color)
{
Fills fills = ss.Fills;
uint indexFill = fills.Count;
Fill fill;
PatternFill patternFill;
if (color == "")
{
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
}
else
{
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString(color);
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
}
fills.Count = (uint)fills.ChildElements.Count;
return(fills.Count - 1);
}
public static uint GetOrSetErrorFillID(SpreadsheetDocument workdocument)
{
WorkbookStylesPart stylesPart = workdocument.WorkbookPart.WorkbookStylesPart;
Fills fills = stylesPart.Stylesheet.Fills;
Fill cellFill = null;
uint fillId = 0;
if (!ExistsRedFillPattern(fills, ref cellFill, ref fillId))
{
Fill newErrorFill = new Fill();
PatternFill newErrorPattern = new PatternFill()
{
PatternType = PatternValues.Solid
};
newErrorPattern.AppendChild(new ForegroundColor()
{
Rgb = "FFFF0000"
});
newErrorPattern.AppendChild(new BackgroundColor()
{
Indexed = 64
});
newErrorFill.AppendChild(newErrorPattern);
fills.AppendChild(newErrorFill);
fills.Count++;
}
workdocument.Save();
ErrorStyleFillId = fillId;
return(fillId);
}
public static Fills GenerateFills()
{
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);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor() /*Theme = (UInt32Value)0U, Tint = -0.14999847407452621D*/ Rgb
{
public void CreateFillColorFilter()
{
#region radspreadprocessing-features-filtering_5
IFill fill = new PatternFill(PatternType.Solid, Colors.Red, Colors.Red);
FillColorFilter filter = new FillColorFilter(0, fill);
#endregion
}
private void AddCustomContextMenuItem()
{
DelegateCommand highlightCommand = null;
Action <object> highlightCommandAction = new Action <object>((parameter) =>
{
this.radSpreadsheet.ActiveWorksheetEditor.Selection.Cells.SetFill(highlightFill);
highlightCommand.InvalidateCanExecute();
});
Predicate <object> highlightCommandPredicate = new Predicate <object>((obj) =>
{
RadWorksheetEditor editor = this.radSpreadsheet.ActiveWorksheetEditor;
if (editor != null)
{
PatternFill patternFill = editor.Worksheet.Cells[editor.Selection.ActiveRange.SelectedCellRange].GetFill().Value as PatternFill;
if (patternFill != null)
{
return(!patternFill.Equals(highlightFill));
}
}
return(false);
});
highlightCommand = new SelectionDependentCommand(this.radSpreadsheet, highlightCommandAction, highlightCommandPredicate);
RadMenuItem newItem = new RadMenuItem();
newItem.Header = ContextMenuItemHeader;
newItem.Command = highlightCommand;
this.radSpreadsheet.WorksheetEditorContextMenu.Items.Add(newItem);
}
/// <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);
}
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 void CreateCellStyle()
{
#region radspreadprocessing-features-styling-cell-styles_1
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
CellStyle cellStyle = workbook.Styles.Add("My style", CellStyleCategory.Custom);
cellStyle.BeginUpdate();
CellBorder border = new CellBorder(CellBorderStyle.DashDotDot, new ThemableColor(Colors.Red));
cellStyle.LeftBorder = border;
cellStyle.TopBorder = border;
cellStyle.RightBorder = border;
cellStyle.BottomBorder = border;
ThemableColor patternColor = new ThemableColor(ThemeColorType.Accent1);
ThemableColor backgroundColor = new ThemableColor(ThemeColorType.Accent5, ColorShadeType.Shade2);
IFill fill = new PatternFill(PatternType.Gray75Percent, patternColor, backgroundColor);
cellStyle.Fill = fill;
cellStyle.HorizontalAlignment = RadHorizontalAlignment.Left;
cellStyle.VerticalAlignment = RadVerticalAlignment.Center;
cellStyle.EndUpdate();
workbook.ActiveWorksheet.Cells[0, 0].SetStyleName("My style");
#endregion
}
/// <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));
}
/// <summary>
/// Create Fill styles
/// </summary>
private static void CreateFillStyles(Fills fills)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 1
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
}
private Fill CreateFill(string fillColor, PatternValues patternValue)
{
try
{
Fill fill = new Fill();
PatternFill patternfill = new PatternFill();
patternfill.PatternType = patternValue;
if (!string.IsNullOrWhiteSpace(fillColor))
{
patternfill.ForegroundColor = new ForegroundColor()
{
Rgb = new HexBinaryValue {
Value = fillColor
}
};
}
fill.PatternFill = patternfill;
return(fill);
}
catch (Exception exception)
{
LogWriter.LogError("Unable to create Fill.");
throw exception;
}
}
private static UInt32Value CreateFill(
Stylesheet styleSheet,
System.Drawing.Color fillColor)
{
PatternFill patternFill =
new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
});
patternFill.PatternType = fillColor ==
System.Drawing.Color.White ? PatternValues.None : PatternValues.LightDown;
Fill fill = new Fill(patternFill);
styleSheet.Fills.Append(fill);
UInt32Value result = styleSheet.Fills.Count;
styleSheet.Fills.Count++;
return(result);
}
public MakeStyleIntent WithFill(PatternFill patternFill)
{
return(WithFill(new Fill()
{
PatternFill = patternFill
}));
}
public static Fills GenerateFills()
{
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
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);
return(fills1);
}
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 static Fill CreateGrayFill()
{
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
return fill;
}
///<summary>
///Returns a new Font object from the style information provided
///</summary>
public Fill ToFill()
{
Fill fill = new Fill();
fill.AppendChild <PatternFill>(PatternFill.CloneElement <PatternFill>());
return(fill);
}
private void BuildFills(WorkbookStylesPart stylesPart)
{
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill {
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// create green
var green = createColor("FF006836");
// create yellow
var yellow = createColor("FFffff00");
// create orange
var orange = createColor("FFff7d00");
// create blue
var blue = createColor("ff002fff");
// create white
var white = createColor("ffffffff");
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = solidRed
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = green
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = yellow
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = orange
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = blue
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = white
});
stylesPart.Stylesheet.Fills.Count = 7;
}
private uint InsertFill(PatternFill fill)
{
StyleSheet.Fills.Append(new Fill {
PatternFill = fill
});
StyleSheet.Fills.Count++;
uint insertedIndex = StyleSheet.Fills.Count - 1;
return(insertedIndex);
}
private static Fill CreateColoredFill()
{
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.BackgroundColor = new BackgroundColor();
fill.PatternFill = patternFill;
return fill;
}
public MainPage()
{
this.highlightFill = new PatternFill(PatternType.Solid, Colors.Yellow, Colors.Transparent);
InitializeComponent();
WorkbookFormatProvidersManager.RegisterFormatProvider(new XlsxFormatProvider());
this.AddCustomContextMenuItem();
}
public MainWindow()
{
this.highlightFill = new PatternFill(PatternType.Solid, Colors.Yellow, Colors.Transparent);
InitializeComponent();
WorkbookFormatProvidersManager.RegisterFormatProvider(new XlsxFormatProvider());
this.AddCustomContextMenuItem();
}
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);
}
/// <summary>
/// Генерировать наполнение
/// </summary>
/// <returns></returns>
public Fill GenerateFill()
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.None
};
fill.Append(patternFill);
return(fill);
}
void grid_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)
{
if (e.Element == ExportElement.HeaderCell || e.Element == ExportElement.CommonColumnHeader)
{
PatternFill fill = new PatternFill(PatternType.Solid, Colors.Blue, Colors.Transparent);
(e.VisualParameters as GridViewDocumentVisualExportParameters).Style = new CellSelectionStyle()
{
Fill = fill
};
}
}
static public PatternFill GetFill(this CellFillAttribute cellFillAttribute)
{
PatternFill retFill = new PatternFill() { PatternType = PatternValues.Solid };
if(!string.IsNullOrEmpty(cellFillAttribute.HexColor))
{
retFill.ForegroundColor = new ForegroundColor() { Rgb = HexBinaryValue.FromString(cellFillAttribute.HexColor) };
}
return retFill;
}
private static Fills CreateFills()
{
// fill 0
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 1 (in-built fill)
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 2
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
var fillColor = Color.LightSkyBlue;
patternFill.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
patternFill.BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 3
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
fillColor = Color.Orange;
patternFill.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
patternFill.BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
public CustomStylesheet()
{
// blank font list
var fonts = new Fonts();
fonts.AppendChild(new Font());
fonts.Count = 1;
Append(fonts);
// create fills
var fills = new Fills();
// create a solid blue fill
var solidBlue = new PatternFill() { PatternType = PatternValues.Solid };
solidBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("397FDB") }; // blue fill
solidBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };
fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
fills.AppendChild(new Fill { PatternFill = solidBlue });
fills.Count = 3;
Append(fills);
// blank border list
var borders = new Borders();
borders.AppendChild(new Border());
borders.AppendChild(new Border()
{
TopBorder = new TopBorder() { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder() { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin },
LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin }
});
borders.Count = 2;
Append(borders);
// blank cell format list
var cellStyleFormats = new CellStyleFormats();
cellStyleFormats.AppendChild(new CellFormat());
cellStyleFormats.Count = 1;
Append(cellStyleFormats);
// cell format list
var cellFormats = new CellFormats();
// empty one for index 0, seems to be required
cellFormats.AppendChild(new CellFormat());
// cell format default with border
cellFormats.AppendChild(new CellFormat() { FormatId = 0, FontId = 0, BorderId = 1, FillId = 0 }).AppendChild(new Alignment() { WrapText = true });
// cell format for header (blue with border)
cellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
cellFormats.Count = 2;
Append(cellFormats);
}
public void SetPatternFill()
{
#region radspreadprocessing-working-with-cells-get-set-clear-properties_4
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
PatternFill diagonalStripePatternFill = new PatternFill(PatternType.DiagonalStripe, Color.FromRgb(231, 76, 60), Color.FromRgb(241, 196, 15));
worksheet.Cells[0, 0, 0, 5].SetFill(diagonalStripePatternFill);
PatternFill solidPatternFill = new PatternFill(PatternType.Solid, Color.FromRgb(46, 204, 113), Colors.Transparent);
worksheet.Cells[1, 0, 5, 5].SetFill(solidPatternFill);
#endregion
}
public void SetPatternFill()
{
#region radspreadsheet-features-formatting-cells_4
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
PatternFill diagonalStripePatternFill = new PatternFill(PatternType.DiagonalStripe, Color.FromRgb(231, 76, 60), Color.FromRgb(241, 196, 15));
worksheet.Cells[0, 0, 0, 5].SetFill(diagonalStripePatternFill);
PatternFill solidPatternFill = new PatternFill(PatternType.Solid, Color.FromRgb(46, 204, 113), Colors.Transparent);
worksheet.Cells[1, 0, 5, 5].SetFill(solidPatternFill);
#endregion
}
private static IFill GenerateFill(Brush brush)
{
if (brush != null)
{
SolidColorBrush solidBrush = brush as SolidColorBrush;
if (solidBrush != null)
{
return(PatternFill.CreateSolidFill(solidBrush.Color));
}
}
return(null);
}
public MakeStyleIntent WithFill(string rgbColor, PatternValues patternType = PatternValues.Solid)
{
var _rgb = rgbColor.TrimStart('#');
var patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
patternFill.ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(_rgb)
};
return(WithFill(patternFill));
}
private static Fill Fill1()
{
// ReSharper disable PossiblyMistakenUseOfParamsMethod
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.Gray125
};
fill1.Append(patternFill1);
// ReSharper restore PossiblyMistakenUseOfParamsMethod
return(fill1);
}
private PatternFill createColor(string colorhex)
{
var color = new PatternFill {
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString(colorhex)
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
return(color);
}
internal override void BeforeSave()
{
if (_patternFill != null)
{
PatternFill.UpdateXml();
}
else if (_blipFill != null)
{
BlipFill.UpdateXml();
}
else
{
base.BeforeSave();
}
}
// Creates an Stylesheet instance and adds its children.
public 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)1U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
FontName fontName1 = new FontName() { Val = "Calibri" };
font1.Append(fontSize1);
font1.Append(fontName1);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
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 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);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color1 = new Color() { Indexed = (UInt32Value)64U };
leftBorder2.Append(color1);
RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color2 = new Color() { Indexed = (UInt32Value)64U };
rightBorder2.Append(color2);
TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };
topBorder2.Append(color3);
BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder2.Append(color4);
DiagonalBorder diagonalBorder2 = new DiagonalBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };
diagonalBorder2.Append(color5);
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)1U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
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);
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 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 void AddHeaderRow(Worksheet worksheet, int numberOfGroupDescriptors, IList<GridViewBoundColumnBase> columns)
{
int headerColumnStartIndex = numberOfGroupDescriptors;
for (int i = 0; i < columns.Count; i++)
{
worksheet.Cells[0, headerColumnStartIndex + i].SetValue(columns[i].Header.ToString());
}
IFill fill = new PatternFill(PatternType.Solid, this.HeaderRowColor, Colors.Transparent);
worksheet.Cells[0, 0, 0, numberOfGroupDescriptors + columns.Count - 1].SetFill(fill);
}
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();
}
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
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)2U };
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 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)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, PivotButton = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = 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 };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public CustomStylesheet()
{
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName { Val = StringValue.FromString("Arial") };
var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName { Val = StringValue.FromString("Arial") };
fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.DodgerBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
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);
//Top and Bottom Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count =
UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
var numberingFormats = new NumberingFormats();
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
numberingFormats.Count =
UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
private int AddGroupRow(Worksheet worksheet, int rowIndex, int numberOfIndentCells, QueryableCollectionViewGroup group, IList<GridViewBoundColumnBase> columns)
{
int startColumnIndex = this.GetGroupLevel(group);
CellSelection groupHeaderRow = worksheet.Cells[rowIndex, startColumnIndex, rowIndex, numberOfIndentCells + columns.Count - 1];
groupHeaderRow.Merge();
IFill fill = new PatternFill(PatternType.Solid, this.GroupHeaderRowColor, Colors.Transparent);
groupHeaderRow.SetFill(fill);
string cellValue = group.Key != null ? group.Key.ToString() : string.Empty;
groupHeaderRow.SetValue(cellValue);
groupHeaderRow.SetHorizontalAlignment(RadHorizontalAlignment.Left);
rowIndex++;
startColumnIndex++;
if (group.HasSubgroups)
{
foreach (var subGroup in group.Subgroups)
{
int newRowIndex = this.AddGroupRow(worksheet, rowIndex, numberOfIndentCells, subGroup as QueryableCollectionViewGroup, columns);
rowIndex = newRowIndex;
}
}
else
{
this.AddDataRows(worksheet, rowIndex, startColumnIndex, group.Items, columns);
rowIndex += group.Items.Count;
}
return rowIndex;
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
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("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
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);
//Boarder Index 1
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);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
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);
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);
uint iExcelIndex = 164;
NumberingFormats nfs = new 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);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
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 = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.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);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
}
public static void Method()
{
Console.WriteLine("Creating document");
using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook))
{
Console.WriteLine("Creating workbook");
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new Workbook();
Console.WriteLine("Creating worksheet");
var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
wsPart.Worksheet = new Worksheet();
var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
Console.WriteLine("Creating styles");
// blank font list
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
// create fills
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF0000") }; // red fill
solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 };
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });
stylesPart.Stylesheet.Fills.Count = 3;
// blank border list
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
// blank cell format list
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
// cell format list
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
stylesPart.Stylesheet.CellFormats.Count = 2;
stylesPart.Stylesheet.Save();
Console.WriteLine("Creating sheet data");
var sheetData = wsPart.Worksheet.AppendChild(new SheetData());
Console.WriteLine("Adding rows / cells...");
var row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("This"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("is"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("a"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });
sheetData.AppendChild(new Row());
row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("Value:"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("123"), DataType = CellValues.Number });
row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });
// style index = 1, i.e. point at our fill format
row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"), DataType = CellValues.Number, StyleIndex = 1 });
Console.WriteLine("Saving worksheet");
wsPart.Worksheet.Save();
Console.WriteLine("Creating sheet list");
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });
Console.WriteLine("Saving workbook");
spreadsheet.WorkbookPart.Workbook.Save();
Console.WriteLine("Done.");
}
}
// Creates an Stylesheet instance and adds its children.
public static Stylesheet GenerateStylesheet()
{
var 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 = new Fonts {Count = 7U, KnownFonts = true};
var font1 = new Font();
var fontSize1 = new FontSize {Val = 11D};
var color1 = new Color {Theme = 1U};
var fontName1 = new FontName {Val = "Calibri"};
var fontFamilyNumbering1 = new FontFamilyNumbering {Val = 2};
var fontCharSet1 = new FontCharSet {Val = 204};
var fontScheme1 = new FontScheme {Val = FontSchemeValues.Minor};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontCharSet1);
font1.Append(fontScheme1);
var font2 = new Font();
var bold1 = new Bold();
var fontSize2 = new FontSize {Val = 11D};
var color2 = new Color {Theme = 1U};
var fontName2 = new FontName {Val = "Calibri"};
var fontFamilyNumbering2 = new FontFamilyNumbering {Val = 2};
var fontCharSet2 = new FontCharSet {Val = 204};
var fontScheme2 = new FontScheme {Val = FontSchemeValues.Minor};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontCharSet2);
font2.Append(fontScheme2);
var font3 = new Font();
var bold2 = new Bold();
var italic1 = new Italic();
var fontSize3 = new FontSize {Val = 11D};
var color3 = new Color {Theme = 1U};
var fontName3 = new FontName {Val = "Calibri"};
var fontFamilyNumbering3 = new FontFamilyNumbering {Val = 2};
var fontCharSet3 = new FontCharSet {Val = 204};
var 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(fontCharSet3);
font3.Append(fontScheme3);
var font4 = new Font();
var bold3 = new Bold();
var fontSize4 = new FontSize {Val = 18D};
var color4 = new Color {Theme = 1U};
var fontName4 = new FontName {Val = "Calibri"};
var fontFamilyNumbering4 = new FontFamilyNumbering {Val = 2};
var fontCharSet4 = new FontCharSet {Val = 204};
var fontScheme4 = new FontScheme {Val = FontSchemeValues.Minor};
font4.Append(bold3);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontCharSet4);
font4.Append(fontScheme4);
var font5 = new Font();
var bold4 = new Bold();
var fontSize5 = new FontSize {Val = 11D};
var fontName5 = new FontName {Val = "Calibri"};
var fontFamilyNumbering5 = new FontFamilyNumbering {Val = 2};
var fontCharSet5 = new FontCharSet {Val = 204};
var fontScheme5 = new FontScheme {Val = FontSchemeValues.Minor};
font5.Append(bold4);
font5.Append(fontSize5);
font5.Append(fontName5);
font5.Append(fontFamilyNumbering5);
font5.Append(fontCharSet5);
font5.Append(fontScheme5);
var font6 = new Font();
var fontSize6 = new FontSize {Val = 11D};
var fontName6 = new FontName {Val = "Calibri"};
var fontFamilyNumbering6 = new FontFamilyNumbering {Val = 2};
var fontCharSet6 = new FontCharSet {Val = 204};
var fontScheme6 = new FontScheme {Val = FontSchemeValues.Minor};
font6.Append(fontSize6);
font6.Append(fontName6);
font6.Append(fontFamilyNumbering6);
font6.Append(fontCharSet6);
font6.Append(fontScheme6);
var font7 = new Font();
var bold5 = new Bold();
var fontSize7 = new FontSize {Val = 12D};
var color5 = new Color {Theme = 1U};
var fontName7 = new FontName {Val = "Calibri"};
var fontFamilyNumbering7 = new FontFamilyNumbering {Val = 2};
var fontCharSet7 = new FontCharSet {Val = 204};
var fontScheme7 = new FontScheme {Val = FontSchemeValues.Minor};
font7.Append(bold5);
font7.Append(fontSize7);
font7.Append(color5);
font7.Append(fontName7);
font7.Append(fontFamilyNumbering7);
font7.Append(fontCharSet7);
font7.Append(fontScheme7);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fonts1.Append(font6);
fonts1.Append(font7);
var fills1 = new Fills {Count = 2U};
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);
fills1.Append(fill1);
fills1.Append(fill2);
var borders1 = new Borders {Count = 2U};
var border1 = new Border();
var leftBorder1 = new LeftBorder();
var rightBorder1 = new RightBorder();
var topBorder1 = new TopBorder();
var bottomBorder1 = new BottomBorder();
var diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
var border2 = new Border();
var leftBorder2 = new LeftBorder {Style = BorderStyleValues.Thin};
var color6 = new Color {Indexed = 64U};
leftBorder2.Append(color6);
var rightBorder2 = new RightBorder {Style = BorderStyleValues.Thin};
var color7 = new Color {Indexed = 64U};
rightBorder2.Append(color7);
var topBorder2 = new TopBorder {Style = BorderStyleValues.Thin};
var color8 = new Color {Indexed = 64U};
topBorder2.Append(color8);
var bottomBorder2 = new BottomBorder {Style = BorderStyleValues.Thin};
var color9 = new Color {Indexed = 64U};
bottomBorder2.Append(color9);
var diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
var cellStyleFormats1 = new CellStyleFormats {Count = 4U};
var cellFormat1 = new CellFormat {NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U};
var cellFormat2 = new CellFormat
{
NumberFormatId = 0U,
FontId = 5U,
FillId = 0U,
BorderId = 1U,
ApplyNumberFormat = false,
ApplyAlignment = false,
ApplyProtection = false
};
var cellFormat3 = new CellFormat
{
NumberFormatId = 0U,
FontId = 4U,
FillId = 0U,
BorderId = 1U,
ApplyNumberFormat = false,
ApplyAlignment = false,
ApplyProtection = false
};
var cellFormat4 = new CellFormat {NumberFormatId = 0U, FontId = 6U, FillId = 0U, BorderId = 1U};
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
cellStyleFormats1.Append(cellFormat3);
cellStyleFormats1.Append(cellFormat4);
var cellFormats1 = new CellFormats {Count = 5U};
var cellFormat5 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
var cellFormat6 = new CellFormat
{
NumberFormatId = 0U,
FontId = 1U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true
};
var cellFormat7 = new CellFormat
{
NumberFormatId = 0U,
FontId = 2U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyBorder = true
};
var cellFormat8 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyAlignment = true
};
var cellFormat9 = new CellFormat
{
NumberFormatId = 0U,
FontId = 3U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyAlignment = true
};
var alignment1 = new Alignment {Horizontal = HorizontalAlignmentValues.Center};
cellFormat9.Append(alignment1);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
var cellStyles1 = new CellStyles {Count = 4U};
var cellStyle1 = new CellStyle {Name = "Custom", FormatId = 3U};
var cellStyle2 = new CellStyle {Name = "Нейтральный", FormatId = 2U, BuiltinId = 28U, CustomBuiltin = true};
var cellStyle3 = new CellStyle {Name = "Обычный", FormatId = 0U, BuiltinId = 0U};
var cellStyle4 = new CellStyle {Name = "Плохой", FormatId = 1U, BuiltinId = 27U, CustomBuiltin = true};
cellStyles1.Append(cellStyle1);
cellStyles1.Append(cellStyle2);
cellStyles1.Append(cellStyle3);
cellStyles1.Append(cellStyle4);
var differentialFormats1 = new DifferentialFormats {Count = 0U};
var tableStyles1 = new TableStyles
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
var stylesheetExtensionList1 = new StylesheetExtensionList();
var stylesheetExtension1 = new StylesheetExtension {Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"};
stylesheetExtension1.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
var slicerStyles1 = new X14.SlicerStyles {DefaultSlicerStyle = "SlicerStyleLight1"};
stylesheetExtension1.Append(slicerStyles1);
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 uint InsertFill(PatternFill fill)
{
StyleSheet.Fills.Append(new Fill { PatternFill = fill });
StyleSheet.Fills.Count++;
uint insertedIndex = StyleSheet.Fills.Count - 1;
return insertedIndex;
}
private void AddDataRows(Worksheet worksheet, int startRowIndex, int startColumnIndex, IList items, IList<GridViewBoundColumnBase> columns)
{
for (int rowIndex = 0; rowIndex < items.Count; rowIndex++)
{
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
{
object value = columns[columnIndex].GetValueForItem(items[rowIndex]);
int currentRowIndex = startRowIndex + rowIndex;
int cuurentColumnIndex = startColumnIndex + columnIndex;
worksheet.Cells[currentRowIndex, cuurentColumnIndex].SetValue(value.ToString());
}
}
IFill fill = new PatternFill(PatternType.Solid, this.DataRowColor, Colors.Transparent);
worksheet.Cells[startRowIndex, 0, startRowIndex + items.Count - 1, startColumnIndex + columns.Count - 1].SetFill(fill);
}
public static int GetFillIndex(SpreadsheetDocument document, PatternFill patternFill)
{
return GetFillIndex(document, patternFill.GetXElement());
}
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();
}
}
//описвывает форматы ячеек
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;
}
internal void FromHash(string Hash)
{
PatternFill pf = new PatternFill();
string[] saElementAttribute = Hash.Split(new string[] { SLConstants.XmlPatternFillElementAttributeSeparator }, StringSplitOptions.None);
if (saElementAttribute.Length >= 2)
{
pf.InnerXml = saElementAttribute[0];
string[] sa = saElementAttribute[1].Split(new string[] { SLConstants.XmlPatternFillAttributeSeparator }, StringSplitOptions.None);
if (sa.Length >= 1)
{
if (!sa[0].Equals("null")) pf.PatternType = (PatternValues)Enum.Parse(typeof(PatternValues), sa[0]);
}
}
this.FromPatternFill(pf);
}
/*private static DocumentFormat.OpenXml.Spreadsheet.Borders CreateBorders()
{
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
// All Boarder Index 1
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);
// Top and Bottom Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
// Left Right
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
// Left Top Right
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
// Left Bottom Right
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
// Bottom
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
return borders;
}*/
private static DocumentFormat.OpenXml.Spreadsheet.Fills CreateFills()
{
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Windows.Media.Colors.LightBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Windows.Media.Colors.DodgerBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return fills;
}
/// <summary>
/// Form SLPatternFill from DocumentFormat.OpenXml.Spreadsheet.PatternFill class.
/// </summary>
/// <param name="patternFill">The source PatternFill class.</param>
public void FromPatternFill(PatternFill patternFill)
{
this.SetAllNull();
if (patternFill.ForegroundColor != null)
{
this.clrForegroundColor.FromForegroundColor(patternFill.ForegroundColor);
this.HasForegroundColor = !this.clrForegroundColor.IsEmpty();
}
if (patternFill.BackgroundColor != null)
{
this.clrBackgroundColor.FromBackgroundColor(patternFill.BackgroundColor);
this.HasBackgroundColor = !this.clrBackgroundColor.IsEmpty();
}
if (patternFill.PatternType != null)
{
this.PatternType = patternFill.PatternType;
}
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.PatternFill class from SLPatternFill.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.PatternFill class with the properties of this SLPatternFill class.</returns>
public PatternFill ToPatternFill()
{
PatternFill pf = new PatternFill();
if (HasForegroundColor) pf.ForegroundColor = this.clrForegroundColor.ToForegroundColor();
if (HasBackgroundColor) pf.BackgroundColor = this.clrBackgroundColor.ToBackgroundColor();
if (HasPatternType) pf.PatternType = this.PatternType;
return pf;
}
public virtual void SaveCustomStylesheet(WorkbookPart workbookPart)
{
//get a copy of the default excel style sheet then add additional styles to it
var stylesheet = CreateDefaultStylesheet();
// ***************************** Fills *********************************
var fills = stylesheet.Fills;
//header fills background color
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
//patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("C8EEFF") };
patternFill.BackgroundColor = new BackgroundColor() { Indexed = 64 };
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
// *************************** numbering formats ***********************
var nfs = stylesheet.NumberingFormats;
//number less than 164 is reserved by excel for default formats
uint iExcelIndex = 165;
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
nfs.Append(nf);
nfs.Count = (uint)nfs.ChildElements.Count;
//************************** cell formats ***********************************
var cfs = stylesheet.CellFormats;//this should already contain a default StyleIndex of 0
var cf = new CellFormat();// Date time format is defined as StyleIndex = 1
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
cf = new CellFormat();// Header format is defined as StyleINdex = 2
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 2;
cf.ApplyFill = true;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
cfs.Count = (uint)cfs.ChildElements.Count;
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
var style = workbookStylesPart.Stylesheet = stylesheet;
style.Save();
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
public Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
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);
borders.Count = (uint)borders.ChildElements.Count;
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 = (uint)csfs.ChildElements.Count;
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 nfs = new NumberingFormats();
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);
CellStyles css = new CellStyles(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (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 = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return ss;
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName { Val = "Arial" };
var ftsz = new FontSize { Val = 11 };
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
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);
var css = new CellStyles();
var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return ss;
}
public static UInt32Value CreateFill(Stylesheet styleSheet, System.Drawing.Color fillColor)
{
PatternFill patternFill =
new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
});
patternFill.PatternType = fillColor ==
System.Drawing.Color.White ? PatternValues.None : PatternValues.LightDown;
Fill fill = new Fill(patternFill);
styleSheet.Fills.Append(fill);
UInt32Value result = styleSheet.Fills.Count;
styleSheet.Fills.Count++;
return result;
}
private static Fill CreateEmptyFill()
{
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
return fill;
}
