public void ExportExcelOptionsTest()
{
var option = new ExportExcelOptions
{
Columns = { "Col1", "Col2" },
Values = new List <List <object> > {
new List <object> {
0, 1
},
new List <object> {
2, 3
}
},
ColumnFormattings = { "Col1", "Col2" },
Title = "Info",
Author = "George Theofilis"
};
Assert.AreEqual("Info", option.Title);
Assert.AreEqual("George Theofilis", option.Author);
Assert.AreEqual(null, option.Path);
Assert.AreEqual(false, option.IsValid);
option.Path = "";
Assert.AreEqual(false, option.IsValid);
option.Path = "Book.xlsx";
Assert.AreEqual(true, option.IsValid);
ExportExcelFile(option);
}
public static void SetCellFormat(ExportExcelOptions options, ExcelRange range, object value, string columnFormatting)
{
if (!string.IsNullOrWhiteSpace(columnFormatting) && (value is double || value is DateTime))
{
var formatting = value is DateTime?Common.ConvertMomentFormat(columnFormatting) : columnFormatting.Replace("'", "");
range.Style.Numberformat.Format = formatting;
}
}
public static void ExportExcelFile(ExportExcelOptions options)
{
if (EnsureExcelOptionsAreValid(options))
{
throw new ExcelExportException();
}
using (var package = new ExcelPackage(options.FileInfo))
{
using (var worksheet = package.Workbook.Worksheets.Add("Sheet1"))
{
// Add headers
for (var columnIndex = 0; columnIndex < options.Columns.Count; columnIndex++)
{
worksheet.Cells[1, columnIndex + 1].Value = options.Columns[columnIndex];
}
worksheet.Cells[1, 1, 1, options.Columns.Count].Style.Font.Bold = true;
worksheet.Cells[1, 1, 1, options.Columns.Count].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, 1, 1, options.Columns.Count].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 221, 235, 247));
worksheet.Cells[1, 1, 1, options.Columns.Count].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[1, 1, 1, options.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// Add values
for (var rowIndex = 0; rowIndex < options.Values.Count; rowIndex++)
{
var row = options.Values[rowIndex];
for (var colIndex = 0; colIndex < row.Count; colIndex++)
{
var cellValue = GetCellValue(row[colIndex]);
var columnFormatting = options.ColumnFormattings != null && options.ColumnFormattings.Count > colIndex ? options.ColumnFormattings[colIndex] : null;
var cell = worksheet.Cells[rowIndex + 2, colIndex + 1];
SetCellFormat(options, cell, cellValue, columnFormatting);
cell.Value = cellValue;
}
}
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
// calculate
//worksheet.Calculate(); //not needed since no formulas are supported by our library
// set some document properties
package.Workbook.Properties.Title = options.Title;
package.Workbook.Properties.Author = options.Author;
package.Workbook.Properties.Comments = "This report was generated by zAppDev";
package.Save();
}
}
}
public static bool EnsureExcelOptionsAreValid(ExportExcelOptions options)
{
return(options == null || !(options.IsValid));
}
