DataTables Export Excel JavaScript (edit)
http://live.datatables.net/yifojova/2/edit
https://www.datatables.net/download/nightly
EPPlus Report
http://zeeshanumardotnet.blogspot.com/2011/06/creating-reports-in-excel-2007-using.html
EPPlus Samples Code
https://github.com/JanKallman/EPPlus/tree/master/SampleApp
EPPlus Ignore Excel Warning
EPPlus Excel Format
https://github.com/JanKallman/EPPlus/wiki/Formatting-and-styling
EPPlus Excel Format
https://www.codeproject.com/Articles/1194712/Advanced-Excels-With-EPPlus
https://riptutorial.com/epplus/topic/8219/styling-the-excel-document
EPPlus Example
using (var xlsx = File.Create("Text.xlsx")) using (var pkg = new ExcelPackage()) { var ws = pkg.Workbook.Worksheets.Add("Sheet1"); var r = 0; ws.Cells[++r, 1].Value = "Values"; ws.Cells[++r, 1].Value = 1171.2; ws.Cells[++r, 1].Value = 1.1; ws.Cells[++r, 1].Value = 1.2; ws.Cells[++r, 1].Value = 1.3; ws.Column(1).Style.Numberformat.Format = "General"; // Default //ws.Column(1).Style.Numberformat.Format = "0.00"; // Numeric with fixed decimals //ws.Column(1).Style.Numberformat.Format = "@"; // Text pkg.SaveAs(xlsx); }
Text Format
https://riptutorial.com/epplus/example/27222/text-format
Number Format
https://riptutorial.com/epplus/example/26056/number-formatting
DateTime Format
https://riptutorial.com/epplus/example/26058/date-formatting
DataTable to Excel using EPPlus
var ws = MainExcel.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); for (var rowNum = 1; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; var array = wsRow.Value as object[,]; var row = tbl.NewRow(); int hhh =0; foreach (var cell in wsRow) { cell.Style.Numberformat.Format = "@"; row[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(row); }
Range
using (var range = worksheet.Cells[1, 1, 1, 5]) //Address "A1:A5" { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); range.Style.Font.Color.SetColor(Color.White); }
Number format
worksheet.Cells["A1:B3,D1:E57"].Style.NumberFormat.Format = "#,##0"; //Sets the numberformat for a range containing two addresses.
worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
Font
worksheet.Cells["A:B"].Style.Font.Bold = true; //Sets font-bold to true for column A & B worksheet.Cells["1:1,A:A,C3"].Style.Font.Bold = true; //Sets font-bold to true for row 1,column A and cell C3
worksheet.Cells["A:XFD"].Style.Font.Name = "Arial"; //Sets font to Arial for all cells in a worksheet.
DateTime format
ws.Column(1).Style.Numberformat.Format = "yyyy-mm-dd";
//OR "yyyy-mm-dd h:mm" if you want to include the time!
Border thin
Background Color
//Sets the background color for the selected range (default is A1). //A range is selected using the by using the worksheet.Select method worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
