static Dictionary <int, CellFormat> ReadStyleSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument)
{
Dictionary <int, CellFormat> cellFormats = new Dictionary <int, CellFormat>();
var importedStyleSheet = new Excel.Stylesheet();
if (importDocument.WorkbookPart.WorkbookStylesPart != null)
{
importedStyleSheet.Load(importDocument.WorkbookPart.WorkbookStylesPart);
}
int index = 0;
foreach (Excel.CellFormat item in importedStyleSheet.CellFormats)
{
if (item.ApplyFill != null && item.ApplyFill.Value && item.FillId != null)
{
int fillID = (int)(uint)item.FillId.Value;
var fill = (Excel.Fill)importedStyleSheet.Fills.ElementAt(fillID);
if (fill.PatternFill.ForegroundColor != null)
{
string fillColor = fill.PatternFill.ForegroundColor.Rgb;
cellFormats.Add(index, document.CellFormats().CellFormat(fillColor));
}
}
index++;
}
return(cellFormats);
}
static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedWorkbook = new Excel.Workbook();
exportedWorkbookPart.Workbook = exportedWorkbook;
Excel.Sheets exportedSheets = new Excel.Sheets();
exportedWorkbook.AppendChild(exportedSheets);
uint sheetId = 1;
foreach (var sheet in document.Sheets)
{
SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++);
}
}
static void SaveRow(Excel.SheetData exportedSheetData, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Row row)
{
Excel.Row exportedRow = new Excel.Row()
{
RowIndex = RowIndex(row), Hidden = row._hidden
};
if (row._hidden)
{
exportedRow.Hidden = true;
}
exportedSheetData.Append(exportedRow);
foreach (var cell in row._cells.OrderBy(r => r.Column._index))
{
SaveCell(exportedRow, styleSheet, cellFormatList, cell);
}
}
public static void Write(Spreadsheet document, string path)
{
var exportedDocument = Package.SpreadsheetDocument.Create(path, OpenXml.SpreadsheetDocumentType.Workbook);
var exportedWorkbookPart = exportedDocument.AddWorkbookPart();
Dictionary <CellFormat, uint> cellFormatList = null;
Excel.Stylesheet exportedStyleSheet = SaveStyleSheet(exportedWorkbookPart, ref cellFormatList, document);
SaveDocument(exportedWorkbookPart, exportedStyleSheet, cellFormatList, document);
/*
* var exportedSharedStringTablePart = exportedWorkbookPart.AddNewPart<Package.SharedStringTablePart>();
* exportedSharedStringTablePart.SharedStringTable = SaveSharedStringTable();
* exportedSharedStringTablePart.SharedStringTable.Save();
*/
exportedWorkbookPart.Workbook.Save();
exportedDocument.Close();
}
public void Write(Stylesheet element)
{
if (element == null)
{
throw new ArgumentNullException(nameof(element));
}
// create stylesheet
var stylesheet = new OpenXml.Stylesheet();
Write(stylesheet, element.Fonts);
Write(stylesheet, element.Fills);
Write(stylesheet, element.Borders);
Write(stylesheet, element.NumberFormats);
Write(stylesheet, element.CellFormats);
// write to part
workbookStylesPart.Stylesheet = stylesheet;
stylesheet.Save();
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
static void SaveCell(Excel.Row exportedRow, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Cell cell)
{
Excel.Cell exportedCell = new Excel.Cell()
{
CellReference = CellIndex(cell.Column, cell.Row)
};
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
switch (cell.Type)
{
case ExcelValueType.String:
exportedCell.CellValue = new Excel.CellValue(cell.StringValue);
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
break;
case ExcelValueType.Number:
exportedCell.CellValue = new Excel.CellValue(cell.NumberValue.ToString());
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Number);
break;
case ExcelValueType.Boolean:
exportedCell.CellValue = new Excel.CellValue((cell.BooleanValue ? 1 : 0).ToString());
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Boolean);
break;
case ExcelValueType.Null:
break;
default:
throw new Exception("Unsupported type");
}
if (cell.CellFormat != null)
{
exportedCell.StyleIndex = cellFormatList[cell.CellFormat];
}
exportedRow.Append(exportedCell);
}
private static Stylesheet InitializeDefaultStyles(Stylesheet stylesheet)
{
//!!!!!!!!!!!!!!!!!!!!!!!!!
//Просьба не переделывать этот метод, это связано с непонятным багом в библиотеке Office XML SDK
//!!!!!!!!!!!!!!!!!!!!!!!!!
stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
stylesheet.Borders = new DocumentFormat.OpenXml.Spreadsheet.Borders();
stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
stylesheet.Fills = new DocumentFormat.OpenXml.Spreadsheet.Fills();
stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts();
stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(new DocumentFormat.OpenXml.Spreadsheet.Font());
stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(new DocumentFormat.OpenXml.Spreadsheet.Font());
stylesheet.CellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(new DocumentFormat.OpenXml.Spreadsheet.CellFormat());
stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(new DocumentFormat.OpenXml.Spreadsheet.CellFormat());
return(stylesheet);
}
void Write(OpenXml.Stylesheet stylesheet, CountedCollection <NumberFormat> numberFormats)
{
IWriter <CountedCollection <NumberFormat> > numberFormatWriter = new NumberFormatWriter(stylesheet);
numberFormatWriter.Write(numberFormats);
}
void Write(OpenXml.Stylesheet stylesheet, CountedCollection <CellFormat> cellFormats)
{
IWriter <CountedCollection <CellFormat> > cellFormatWriter = new CellFormatWriter(stylesheet);
cellFormatWriter.Write(cellFormats);
}
void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Border> borders)
{
IWriter <CountedCollection <Border> > borderWriter = new BorderWriter(stylesheet);
borderWriter.Write(borders);
}
void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Fill> fills)
{
IWriter <CountedCollection <Fill> > fillWriter = new FillWriter(stylesheet);
fillWriter.Write(fills);
}
void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Font> fonts)
{
IWriter <CountedCollection <Font> > fontWriter = new FontWriter(stylesheet);
fontWriter.Write(fonts);
}
private static void GerarExcelOpXml(System.Data.DataTable dt, string pathExcelFile, string sheetName,
string ColunaDefault, int rowDefault, int larguraColuna)
{
var styleIndexDefault = 0;
var styleIndexDefaultLine = 2;
DocumentFormat.OpenXml.UInt32Value styleIndex;
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Cell cellDefault =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault));
styleIndex = cellDefault.StyleIndex;
styleIndexDefault = Convert.ToInt32(styleIndex.Value.ToString());
OpenXmlSpread.Cell cellDefaultline =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault + 1));
styleIndex = cellDefaultline.StyleIndex;
styleIndexDefaultLine = Convert.ToInt32(styleIndex.Value.ToString());
}
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Stylesheet stylesheet = myDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
OpenXmlSpread.CellFormat deafultFormat = new OpenXmlSpread.CellFormat()
{
Alignment = new OpenXmlSpread.Alignment()
{
Horizontal = OpenXmlSpread.HorizontalAlignmentValues.Left,
Vertical = OpenXmlSpread.VerticalAlignmentValues.Center
},
ApplyAlignment = true
};
stylesheet.CellFormats.AppendChild(deafultFormat);
DocumentFormat.OpenXml.OpenXmlWriter
writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart);
var indexColumn = ColumnIndex(ColunaDefault);
var startColunaIndex = indexColumn + 1;
writer.WriteStartElement(new OpenXmlSpread.Worksheet());
writer.WriteStartElement(new OpenXmlSpread.Columns());
AjustaLarguraColunas(writer, startColunaIndex, larguraColuna, dt.Columns.Count);
writer.WriteStartElement(new OpenXmlSpread.SheetData());
writer.WriteStartElement(new OpenXmlSpread.Row {
RowIndex = (UInt32)rowDefault
});
CriarColunas(writer, dt, indexColumn, rowDefault, styleIndexDefault);
CriarLinha(writer, dt, indexColumn, rowDefault, styleIndexDefaultLine);
writer.Dispose();
}
}
static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>();
cellFormatList = new Dictionary <CellFormat, uint>();
Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet();
exportedStyleSheetPart.Stylesheet = exportedStyleSheet;
exportedStyleSheet.CellFormats = new Excel.CellFormats();
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
});
exportedStyleSheet.Fills = new Excel.Fills();
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None)
}));
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125)
}));
exportedStyleSheet.Fonts = new Excel.Fonts();
exportedStyleSheet.Fonts.Append(new Excel.Font()
{
FontSize = new Excel.FontSize()
{
Val = 11
},
Color = new Excel.Color()
{
Theme = 1
},
FontName = new Excel.FontName()
{
Val = "Calibri"
},
FontFamilyNumbering = new Excel.FontFamilyNumbering()
{
Val = 2
},
FontScheme = new Excel.FontScheme()
{
Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor)
}
});
exportedStyleSheet.Borders = new Excel.Borders();
exportedStyleSheet.Borders.Append(new Excel.Border()
{
LeftBorder = new Excel.LeftBorder(),
RightBorder = new Excel.RightBorder(),
TopBorder = new Excel.TopBorder(),
BottomBorder = new Excel.BottomBorder(),
DiagonalBorder = new Excel.DiagonalBorder()
});
exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats();
exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
});
exportedStyleSheet.CellStyles = new Excel.CellStyles();
exportedStyleSheet.CellStyles.Append(new Excel.CellStyle()
{
Name = "Normal", FormatId = 0, BuiltinId = 0
});
exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats();
exportedStyleSheet.TableStyles = new Excel.TableStyles()
{
DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
};
foreach (var cellFormat in document.CellFormats())
{
if (cellFormat.Count() > 0)
{
cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count());
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
BorderId = 0,
FormatId = 0,
FillId = (uint)exportedStyleSheet.Fills.ChildElements.Count(),
ApplyFill = true
});
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid),
ForegroundColor = new Excel.ForegroundColor()
{
Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor)
}
}));
}
}
exportedStyleSheet.Save();
return(exportedStyleSheet);
}
/// <summary>
/// Adds the basic styles to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <returns>True if succesful</returns>
public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
{
DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// Numbering formats (x:numFmts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>(
new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat()
{
NumberFormatId = 164,
FormatCode = "#,##0.00"
+ "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
}, 0);
// Fonts (x:fonts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>(
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
{
Val = 11
},
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
{
Val = "Calibri"
}
}, 0);
// Fills (x:fills)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
new DocumentFormat.OpenXml.Spreadsheet.Fill()
{
PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
{
Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
}
}
}, 0);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
new DocumentFormat.OpenXml.Spreadsheet.Fill()
{
PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
{
Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Gray125
}
}
}, 1);
// Borders (x:borders)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>(
new DocumentFormat.OpenXml.Spreadsheet.Border()
{
LeftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
RightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
TopBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
BottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
}, 0);
// Cell style formats (x:CellStyleXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
}, 0);
// Cell formats (x:CellXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5);
// General text
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
FormatId = 0,
NumberFormatId = 0
}, 0);
// Date
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 22,
FontId = 0,
FillId = 0,
BorderId = 0
},
1);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 164,
FontId = 0,
FillId = 0,
BorderId = 0
},
2);
// Percentage
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 10,
FontId = 0,
FillId = 0,
BorderId = 0
},
3);
stylesheet.Save();
return(true);
}
public FillWriter(OpenXml.Stylesheet stylesheet)
{
this.stylesheet = stylesheet ?? throw new ArgumentNullException(nameof(stylesheet));
}
private static MSOpenXML.Stylesheet CreateStylesheet(int pQuantidadeDecimais = 2)
{
MSOpenXML.Stylesheet ss = new MSOpenXML.Stylesheet();
#region Fontes
MSOpenXML.Fonts fts = new MSOpenXML.Fonts();
MSOpenXML.Font ft = new MSOpenXML.Font();
MSOpenXML.FontName ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
MSOpenXML.FontSize ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new MSOpenXML.Font();
ft.Bold = new MSOpenXML.Bold();
ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
#endregion
#region Preenchimento
MSOpenXML.Fills fills = new MSOpenXML.Fills();
MSOpenXML.Fill fill;
MSOpenXML.PatternFill patternFill;
fill = new MSOpenXML.Fill();
patternFill = new MSOpenXML.PatternFill();
patternFill.PatternType = MSOpenXML.PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
/*fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Gray125;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*
* fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Solid;
* patternFill.ForegroundColor = new dos.ForegroundColor();
* patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
* patternFill.BackgroundColor = new dos.BackgroundColor();
* patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*/
fills.Count = (uint)fills.ChildElements.Count;
#endregion
#region Bordas
MSOpenXML.Borders borders = new MSOpenXML.Borders();
MSOpenXML.Border border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.TopBorder.Style = MSOpenXML.BorderStyleValues.Thin;
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
#endregion
MSOpenXML.CellStyleFormats csfs = new MSOpenXML.CellStyleFormats();
MSOpenXML.CellFormat cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
MSOpenXML.NumberingFormats nfs = new MSOpenXML.NumberingFormats();
MSOpenXML.CellFormats cfs = new MSOpenXML.CellFormats();
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
MSOpenXML.NumberingFormat nfDateTime = new MSOpenXML.NumberingFormat();
nfDateTime.NumberFormatId = iExcelIndex++;
nfDateTime.FormatCode = "dd/mm/yyyy";
nfs.Append(nfDateTime);
MSOpenXML.NumberingFormat nf4decimal = new MSOpenXML.NumberingFormat();
nf4decimal.NumberFormatId = iExcelIndex++;
nf4decimal.FormatCode = "#,##0";
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
MSOpenXML.NumberingFormat nf2decimal = new MSOpenXML.NumberingFormat();
nf2decimal.NumberFormatId = iExcelIndex++;
nf2decimal.FormatCode = FormatoDecimal(pQuantidadeDecimais); //"#,##0.00"
nfs.Append(nf2decimal);
// @ is also Excel style index 49
MSOpenXML.NumberingFormat nfForcedText = new MSOpenXML.NumberingFormat();
nfForcedText.NumberFormatId = iExcelIndex++;
nfForcedText.FormatCode = "@";
nfs.Append(nfForcedText);
// index 1
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 2
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 3
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 4
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 5
// Header text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cf.Alignment.Horizontal = MSOpenXML.HorizontalAlignmentValues.Center;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// index 6
// group text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 7
// Total text, ColumnHeader Text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
MSOpenXML.CellStyles css = new MSOpenXML.CellStyles();
MSOpenXML.CellStyle cs = new MSOpenXML.CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
MSOpenXML.DifferentialFormats dfs = new MSOpenXML.DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
MSOpenXML.TableStyles tss = new MSOpenXML.TableStyles();
tss.Count = 0;
//tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
//tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
ss.Append(tss);
return(ss);
}