@manhnguyenv

Welcome to my blog!

DataTables Export Excel JavaScript + EPPlus Excel Format

December 24, 2019 20:41

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

https://www.c-sharpcorner.com/blogs/how-to-format-excel-table-using-epplus-net-library-c-sharp-part-fifteen (HAY)

EPPlus Ignore Excel Warning

https://stackoverflow.com/questions/26483496/is-it-possible-to-ignore-excel-warnings-when-generating-spreadsheets-using-epplu/

https://stackoverflow.com/questions/26483496/is-it-possible-to-ignore-excel-warnings-when-generating-spreadsheets-using-epplu/26484880#26484880

https://support.office.com/en-gb/article/block-or-unblock-external-content-in-office-documents-10204ae0-0621-411f-b0d6-575b0847a795

EPPlus Excel Format

https://github.com/JanKallman/EPPlus/wiki/Formatting-and-styling

https://www.c-sharpcorner.com/blogs/how-to-apply-cell-text-background-color-in-excel-sheet-using-epplus

EPPlus Excel Format

https://www.codeproject.com/Articles/1194712/Advanced-Excels-With-EPPlus

https://www.c-sharpcorner.com/blogs/how-to-format-excel-table-using-epplus-net-library-c-sharp-part-fifteen

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

https://www.c-sharpcorner.com/blogs/how-to-apply-cell-border-style-on-excel-sheet-using-epplus-net-application-c-sharp-part3

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

Categories

Recent posts