private static Fonts CreateFonts()
{
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(10);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts = new Fonts();
ft = new Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
//fonts count
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
return(fts);
}
internal static int AddDateFormat(Stylesheet stylesheet, uint borderStyleId)
{
if (stylesheet.NumberingFormats == null)
{
stylesheet.NumberingFormats = new NumberingFormats();
}
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3454);
nf2decimal.FormatCode = StringValue.FromString(@"[$-404]e""-""m""-""d""-""h"":""mm"":""ss;@");
stylesheet.NumberingFormats.AppendChild <NumberingFormat>(nf2decimal);
var cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = borderStyleId;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Left
});
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
}
private static bool Render(TextElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart)
{
uint styleId;
var row = shData.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Row>(new DocumentFormat.OpenXml.Spreadsheet.Row());
styleId = GetStyleId(element.Style, styles);
var value = element.Value;
if (element.Value == InternalContants.NewLine)
{
value = String.Empty;
}
if (element.Value == InternalContants.NewSection)
{
value = String.Empty;
}
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild(cell);
workbookpart.Workbook.Save();
return(false);
}
private static Borders CreateBorders()
{
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
//border.DiagonalBorder.Style = BorderStyleValues.Thin;
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
return(borders);
}
/// <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 static NumberingFormats CreateNumberingFormats()
{
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
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);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
return(nfs);
}
public void WriteExcelTitle(int nLineIndex,
int nCols,
string strTitle,
int nStyleIndex = 0)
{
string strStartCellName = "A" + (nLineIndex + 1).ToString();
string strEndCellName = GetColumnName(nCols - 1) + (nLineIndex + 1).ToString();
ExcelUtil.UpdateValue(
workbookpart,
worksheetPart.Worksheet,
strStartCellName,
strTitle,
UInt32Value.FromUInt32((uint)nStyleIndex),
true);
if (nCols > 1)
{
ExcelUtil.UpdateValue(
workbookpart,
worksheetPart.Worksheet,
strEndCellName,
"",
0,
false);
}
else
{
return;
}
ExcelUtil.InsertMergeCell(worksheetPart.Worksheet,
strStartCellName,
strEndCellName);
}
/// <summary>
/// Creates fonts
/// </summary>
/// <param name="styleSheet">The style sheet.</param>
static void CreateFonts(Stylesheet styleSheet)
{
Fonts fonts = new Fonts();
fonts.Append(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
fonts.Append(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
},
Bold = new Bold {
Val = BooleanValue.FromBoolean(true)
}
});
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
styleSheet.Append(fonts);
}
/// <summary>
/// http://stackoverflow.com/questions/8405025/set-print-area-openxml-with-excel
/// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname(v=office.14).aspx
/// </summary>
/// <param name="spreadsheetDoc"></param>
/// <param name="sheetName"></param>
/// <param name="columnStart">Ex: "$A"</param>
/// <param name="columnEnd">EX: "$B"</param>
/// null if just not set print areas column or row
public static void SetPrintArea(WorkbookPart workbookPart, string sheetName, string columnStart, string columnEnd, string rowStart, string rowEnd)
{
String definedName = sheetName;
var definedNames = workbookPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
DefinedName name = null;
UInt32Value locSheetId;
if (definedNames == null)
{
definedNames = new DefinedNames();
workbookPart.Workbook.Append(definedNames);
workbookPart.Workbook.Save();
locSheetId = UInt32Value.FromUInt32(0);
}
else
{
int defineNameCount = definedNames.Descendants <DefinedName>().Count();
locSheetId = UInt32Value.FromUInt32((UInt32)defineNameCount);
}
//_xlnm.Print_Area la tham so de set up cho khoang khong in
if (rowStart != null && rowEnd != null && columnEnd != null && columnStart != null)
{
name = new DefinedName()
{
Name = "_xlnm.Print_Area",
LocalSheetId = locSheetId
,
Text = String.Format("{0}!${1}${2}:${3}${4}", sheetName, columnStart.Replace("$", ""), rowStart.Replace("$", ""), columnEnd.Replace("$", ""), rowEnd.Replace("$", ""))
};
}
definedNames.Append(name);
workbookPart.Workbook.Save();
}
private void SetCellFormat(uint numberFormatId, bool applyNumberFormat, bool applyFont, bool applyFill, uint fontId, uint fillId, uint borderId)
{
CellFormat cellFormat = new CellFormat();
cellFormat.NumberFormatId = UInt32Value.FromUInt32(numberFormatId);
if (applyNumberFormat)
{
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(applyNumberFormat);
}
if (applyFont)
{
cellFormat.ApplyFont = BooleanValue.FromBoolean(applyFont);
}
if (applyFill)
{
cellFormat.ApplyFill = BooleanValue.FromBoolean(applyFill);
}
cellFormat.FontId = UInt32Value.FromUInt32(fontId);
cellFormat.FillId = UInt32Value.FromUInt32(fillId);
cellFormat.BorderId = UInt32Value.FromUInt32(borderId);
//cellFormat.FormatId = UInt32Value.FromUInt32(cellStyleFormatId);
ActualCellFormats.Append(cellFormat);
SetCellFormatsCount();
}
private static Fills GetFills()
{
Fills fills = new Fills();
var defaultFill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
}); // Index 0 - default
fills.Append(defaultFill1);
var defaultFill2 = new Fill(new PatternFill {
PatternType = PatternValues.Gray125
}); // Index 1 - default
fills.Append(defaultFill2);
Fill fillColumnHeader = new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FF005EB8")
}
}
};
fills.Append(fillColumnHeader);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
private static NumberingFormats CreateNumberingFormats(out NumberingFormat nfDateTime,
out NumberingFormat nf5Decimal, out NumberingFormat nfDuration, out NumberingFormat nfTotalDuration)
{
// built-in formats go up to 164
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.AppendChild(nfDateTime);
nf5Decimal = new NumberingFormat();
nf5Decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf5Decimal.FormatCode = StringValue.FromString("#,##0.00000");
nfs.AppendChild(nf5Decimal);
nfDuration = new NumberingFormat();
nfDuration.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDuration.FormatCode = StringValue.FromString("[h]:mm");
nfs.AppendChild(nfDuration);
nfTotalDuration = new NumberingFormat();
nfTotalDuration.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfTotalDuration.FormatCode = StringValue.FromString("d:h:mm");
nfs.AppendChild(nfTotalDuration);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
return(nfs);
}
public static UInt32Value GetStyleIndex(this WorkbookPart book, HexBinaryValue bgColor)
{
try { return(_styleIndexes[bgColor.Value]); }
catch (KeyNotFoundException ex)
{
var stylesheet = book.GetStyleSheet();
var fill = stylesheet.GetFill(bgColor);
var formats = stylesheet.CellFormats.Elements <CellFormat>();
for (int x = 0; x < formats.Count(); x++)
{
if (formats.ElementAt(x).FillId.Value == fill.Value)
{
return(new UInt32Value((uint)x));
}
}
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = fill,
BorderId = 0,
FormatId = 0,
ApplyFill = BooleanValue.FromBoolean(true)
};
stylesheet.CellFormats.Append(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
var value = new UInt32Value((uint)stylesheet.CellFormats.Count() - 1);
_styleIndexes.Add(bgColor.Value, value);
return(value);
}
}
//letterIDsAndValues: <letterID, value>
private static void InsertRow(ref SpreadsheetDocument spreadsheetDocument, ref SheetData sheetData, Dictionary <string, object> letterIDsAndValues)
{
//Create new row after the last low
//We use this instead of .Count() in case of rows where deleted
uint rowIndex = 1;
if (sheetData.Elements <Row>()?.Any() ?? false)
{
rowIndex = sheetData.Elements <Row>().Max(x => x.RowIndex.Value) + 1;
}
Row row = new() { RowIndex = UInt32Value.FromUInt32((rowIndex)) };
sheetData.Append(row);
foreach (var letterIDAndValue in letterIDsAndValues)
{
//Format XX00
string cellReference = letterIDAndValue.Key + rowIndex;
//<cellReference, value>
CreateCell(ref spreadsheetDocument, ref row, new KeyValuePair <string, object>(cellReference, letterIDAndValue.Value));
}
}
#endregion
#endregion
}
private void IntialStyleSheet()
{
this.style_part = this.document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
this.style_part.Stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
this.style_part.Stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
this.style_part.Stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
InttialFormatStyleSheet();
InitialFontStyleSheet();
InitialFillStyleSheet();
InitialBorderStyleSheet();
#region Cell Style Formats
CellStyleFormats csfs = this.style_part.Stylesheet.GetFirstChild <CellStyleFormats>();
if (csfs == null)
{
csfs = new CellStyleFormats();
this.style_part.Stylesheet.Append(csfs);
}
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
#endregion
InitialCellStyleSheet();
#region Cell Style
CellStyles css = this.style_part.Stylesheet.GetFirstChild <CellStyles>();
if (css == null)
{
css = new CellStyles();
this.style_part.Stylesheet.Append(css);
}
CellStyle cs = new CellStyle()
{
Name = "Normal",
FormatId = (UInt32Value)0U,
BuiltinId = (UInt32Value)0U
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
#endregion
}
internal static Cell GenerateStringCell(this object obj, int?stileindex = null)
{
return(new Cell()
{
CellValue = new CellValue(obj as string),
DataType = CellValues.String,
StyleIndex = UInt32Value.FromUInt32((uint)stileindex.GetValueOrDefault(0))
});
}
private static Fonts GetFonts()
{
Fonts fonts = new Fonts();
// 0
Font fontNormal = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
};
fonts.Append(fontNormal);
// 1
Font fontTitle = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(25)
},
Color = new Color {
Rgb = HexBinaryValue.FromString("ff584642")
}
};
fonts.Append(fontTitle);
// 2
Font fontColumnHeaders = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(13.5)
},
Color = new Color()
{
Rgb = HexBinaryValue.FromString("ffffffff")
}, //white
Bold = new Bold()
{
Val = true
}
};
fonts.Append(fontColumnHeaders);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
return(fonts);
}
internal static Cell GenerateNumberCell(this object obj, int?stileindex = null)
{
return(new Cell
{
DataType = new EnumValue <CellValues>(CellValues.Number),
CellValue = new CellValue(Convert.ToString(obj).Replace(",", ".")),
StyleIndex = UInt32Value.FromUInt32((uint)stileindex.GetValueOrDefault(0))
});
}
public static TableRow Height(this TableRow Row, int Height)
{
Row.Content()
.GetOrPrepend <TableRowProperties>().Content()
.GetOrAppend <TableRowHeight>()
.Val = UInt32Value.FromUInt32((uint)Height);
return(Row);
}
/// <summary>
/// Create Numbering format
/// </summary>
private static NumberingFormat CreateNumberFormat(ref uint iExcelIndex, NumberingFormats nfs, string formatString)
{
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString(formatString);
nfs.Append(nfDateTime);
return(nfDateTime);
}
public CustomStylesheet()
{
// built-in formats go up to 164
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var nfDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
nfs.AppendChild(nfDateTime);
var nf5Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00000")
};
nfs.AppendChild(nf5Decimal);
var nfDuration = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("[h]:mm")
};
nfs.AppendChild(nfDuration);
var nfTotalDuration = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("d:h:mm")
};
nfs.AppendChild(nfTotalDuration);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
Append(nfs);
Append(CreateFonts());
Append(CreateFills());
Append(CreateBorders());
Append(CreateCellStyleFormats());
Append(CreateCellFormats(nfDateTime, nf5Decimal, nfDuration, nfTotalDuration));
Append(CreateCellStyles());
Append(new DifferentialFormats {
Count = 0
});
Append(new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
});
}
private NumberingFormat CreateTwoDecimalFormat()
{
NumberingFormat decimalFormat2 = new NumberingFormat();
decimalFormat2.NumberFormatId = UInt32Value.FromUInt32(HomeIndex++);
decimalFormat2.FormatCode = StringValue.FromString("#,##0.00");
this.numberingFormats.Append(decimalFormat2);
return(decimalFormat2);
}
private NumberingFormat CreateFormatPercent()
{
NumberingFormat percentageFormat = new NumberingFormat();
percentageFormat.NumberFormatId = UInt32Value.FromUInt32(HomeIndex++);
percentageFormat.FormatCode = StringValue.FromString("#,##0.00%");
this.numberingFormats.Append(percentageFormat);
return(percentageFormat);
}
public MultipleSheetEntity([NotNull] ResourceEntity resourceEntity, int index, [NotNull][ItemNotNull] ISet <string> uniqueNames)
{
ResourceEntity = resourceEntity;
var sheetName = GetSheetName(resourceEntity, uniqueNames);
SheetName = sheetName;
Id = "Id" + index + 1;
_sheetId = UInt32Value.FromUInt32((uint)index + 1);
}
internal static void Set(this BorderType Border,
int Size,
string Color = "auto",
BorderValues Value = BorderValues.Single,
int Space = 0)
{
Border.Val = Value;
Border.Color = Color;
Border.Size = UInt32Value.FromUInt32((uint)Size);
Border.Space = UInt32Value.FromUInt32((uint)Space);
}
public static SectionProperties PageSize(this SectionProperties properties,
int Width = 11906,
int Height = 16838)
{
var size = properties.Content().GetOrAppend <PageSize>();
size.Width = UInt32Value.FromUInt32((uint)Width);
size.Height = UInt32Value.FromUInt32((uint)Height);
return(properties);
}
/// <summary>
/// RenderToExcel
/// </summary>
public Stream?RenderToExcel <T>(IEnumerable <T> source)
{
var fileInfo = new FileInfo("test1.xlsx");
var spreadsheetDocument = SpreadsheetDocument.Create("test1.xlsx", SpreadsheetDocumentType.Workbook);
var workbookpart = spreadsheetDocument.AddWorkbookPart();
var workbook = new Workbook();
var sheets = new Sheets();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var worksheet = new Worksheet();
var sheetData = new SheetData();
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = UInt32Value.FromUInt32(1u),
Name = "SS"
};
sheets.Append(sheet);
var row = new Row()
{
RowIndex = UInt32Value.FromUInt32(1u)
};
sheetData.Append(row);
var lists = new List <Cell>();
for (var i = 0; i < 100; i++)
{
lists.Add(new Cell
{
CellValue = new CellValue("12321323"),
DataType = new EnumValue <CellValues>(CellValues.String)
});
}
foreach (var item in lists)
{
row.Append(item);
}
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
workbook.Append(sheets);
workbookpart.Workbook = workbook;
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
return(null);
}
/// <summary>
/// Append custom format.
/// </summary>
/// <param name="stylesheet">Constructing stylesheet.</param>
/// <param name="numberFormatId">Excel NumberFormatId.</param>
/// <param name="formatCode">Excel FormatCode.</param>
protected void AppendFormat(Stylesheet stylesheet, uint numberFormatId, StringValue formatCode)
{
NumberingFormat format = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(numberFormatId),
FormatCode = formatCode
};
stylesheet.NumberingFormats.Append(format);
stylesheet.NumberingFormats.Count++;
}
internal static Cell GenerateFormulaCell(this object obj, int?stileindex = null)
{
return(new Cell()
{
CellValue = new CellValue("0"),
CellFormula = new CellFormula()
{
Text = obj as string
},
StyleIndex = UInt32Value.FromUInt32((uint)stileindex.GetValueOrDefault(0))
});
}
private static Fills CreateFills()
{
// fill 0
var fills = new Fills();
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
});
// fill 1 (in-built fill)
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Gray125
}
});
// fill 2
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(Color.LightSkyBlue)
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(Color.LightSkyBlue)
}
}
});
// fill 3
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(Color.Orange)
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(Color.Orange)
}
}
});
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
