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