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)
});
}
}
}
}
