public void AddCellFormat(XlsxCellFormat format) { var fillNumber = 0U; if (format.ForegroundColor != null) { var fill = new Fill() { PatternFill = new PatternFill() { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() { Rgb = HexBinaryValue.FromString(format.ForegroundColor.HexaCode) } } }; Stylesheet.Fills.Append(fill); Stylesheet.Fills.Count = (uint)Stylesheet.Fills.ChildElements.Count; fillNumber = Stylesheet.Fills.Count - 1; } var numberFormatId = 0U; if (!string.IsNullOrEmpty(format.NumberFormat)) { numberFormatId = AddDateFormat(format.NumberFormat); } if (format.Border != null) { var leftColor = format.Border.Color != null ? new Color() { Rgb = HexBinaryValue.FromString(format.Border.Color.HexaCode) } : new Color() { Auto = true }; var rightColor = format.Border.Color != null ? new Color() { Rgb = HexBinaryValue.FromString(format.Border.Color.HexaCode) } : new Color() { Auto = true }; var topColor = format.Border.Color != null ? new Color() { Rgb = HexBinaryValue.FromString(format.Border.Color.HexaCode) } : new Color() { Auto = true }; var bottomColor = format.Border.Color != null ? new Color() { Rgb = HexBinaryValue.FromString(format.Border.Color.HexaCode) } : new Color() { Auto = true }; var diagonalColor = format.Border.Color != null ? new Color() { Rgb = HexBinaryValue.FromString(format.Border.Color.HexaCode) } : new Color() { Auto = true }; Border border = new Border(); border.LeftBorder = new LeftBorder(leftColor) { Style = format.Border.LeftBorder }; border.RightBorder = new RightBorder(rightColor) { Style = format.Border.LeftBorder }; border.TopBorder = new TopBorder(topColor) { Style = format.Border.LeftBorder }; border.BottomBorder = new BottomBorder(bottomColor) { Style = format.Border.LeftBorder }; border.DiagonalBorder = new DiagonalBorder(diagonalColor) { Style = format.Border.LeftBorder }; Stylesheet.Borders.Append(border); Stylesheet.Borders.Count = (uint)Stylesheet.Borders.ChildElements.Count; } var cf = new CellFormat(); cf.NumberFormatId = numberFormatId; cf.FontId = 0; cf.FillId = fillNumber; cf.ApplyFill = true; cf.BorderId = format.Border != null ? Stylesheet.Borders.Count - 1 : 0; cf.FormatId = 0; Stylesheet.CellFormats.Append(cf); if (CellFormats.ContainsKey(format.Name)) { throw new Exception($"Formato duplicado {format.Name}"); } Stylesheet.CellFormats.Count = (uint)Stylesheet.CellFormats.ChildElements.Count; CellFormats.Add(format.Name, $"{CellFormats.Count}"); //Tengo un estilo por default provisto por excel }
static void Main(string[] args) { string path = @"C:\Temp\"; var fileName = Path.Combine(path, @"test.xlsx"); if (File.Exists(fileName)) { File.Delete(fileName); } var headerList = new string[] { "Header 1", "Header 2", "Header 3", "Header 4" }; //sheet1 var boolList = new bool[] { true, false, true, false }; var intList = new int[] { 1, 2, 3, -4 }; var dateList = new DateTime[] { DateTime.Now, DateTime.Today, DateTime.Parse("1/1/2014"), DateTime.Parse("2/2/2014") }; var sharedStringList = new string[] { "shared string", "shared string", "cell 3", "cell 4" }; var inlineStringList = new string[] { "inline string", "inline string", "3>", "<4" }; using (var spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // create the workbook var workbookPart = spreadSheet.AddWorkbookPart(); var openXmlExportHelper = new OpenXmlWriterHelper(); //openXmlExportHelper.SaveCustomStylesheet(workbookPart); var style = new XlsxStyleSheet(workbookPart); var dataStyle = new XlsxCellFormat() { Name = "Data", ForegroundColor = new XlsxColor("bb4055") }; style.AddCellFormat(dataStyle); var headerStyle = new XlsxCellFormat() { Name = "Header", ForegroundColor = new XlsxColor("C8EEFF"), Border = XlsxBorder.CreateBox(BorderStyleValues.Medium, new XlsxColor("bb4055")) }; style.AddCellFormat(headerStyle); style.AddCellFormat(new XlsxCellFormat("DataDate", dataStyle) { NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@" }); style.AddCellFormat(new XlsxCellFormat() { Name = "DataDateWhite", NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@" }); style.Save(); var workbook = workbookPart.Workbook = new Workbook(); var sheets = workbook.AppendChild <Sheets>(new Sheets()); // create worksheet 1 var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); using (var worksheet = new XlsxWorksSheet(style, workbookPart, worksheetPart, new List <XlsxColumn>() { new XlsxColumn() { ColumnNumber = 1, Width = 25 }, new XlsxColumn() { ColumnNumber = 2, Width = 25 } })) { worksheet.WriteRow(new List <XlsxCell>() { new XlsxSharedStringCell("Id", "Header"), new XlsxSharedStringCell("Name", "Header"), new XlsxSharedStringCell("Lastname", "Header"), new XlsxSharedStringCell("DocumentNumber", "Header"), new XlsxSharedStringCell("Birthdate", "Header") }); var odd = true; foreach (var data in GenerateData()) { var format = ""; var formatDate = "DataDateWhite"; if (!odd) { format = "Data"; formatDate = "DataDate"; odd = true; } else { odd = false; } worksheet.WriteRow(new List <XlsxCell>() { new XlsxNumberCell(data.Id, format), new XlsxSharedStringCell(data.Name, format), new XlsxSharedStringCell(data.Lastname, format), new XlsxSharedStringCell(data.DocumentNumber, format), new XlsxDateCell(data.Birthdate.ToOADate().ToString(CultureInfo.InvariantCulture), formatDate) }); } } } }