Create Excel File and Apply Table Style Using Open Xml in C# .Net

Create Excel File and Apply Table Style Using Open Xml  in C# and Angular

In the Open XML SDK used for creating Excel file in visual studio, in here we are talk about how to create excel file in using Open Xml SDK and apply Table style for excel table

Available Table Styles

Example: Create Excel File and Apply Table Style Using Open Xml in C# .Net

.net Namespaces

Add EPPlus Nuget Package 

using OfficeOpenXml;

using System.Reflection;

using OfficeOpenXml.Table;

using OfficeOpenXml.Style;

Create Excel File

using (ExcelPackage exPack = newExcelPackage())

{

       ExcelWorksheet ws = exPack.Workbook.Worksheets.Add(“SheetName”);

       intsRow = 1;

       int sCol = 1;

       int eRow = sRow;

       int eCol = sCol + tblTable.Columns.Count + 3;

ws.Cells[sRow, eCol + 2].LoadFromDataTable(tblTable, true, TableStyles.Light17);

ws.Cells[sRow, eCol + 2, sRow, eCol + 2 + tblTable.Columns.Count –

       1].Style.Font.Bold = true;

       ws.Cells[sRow, eCol + 4, sRow + tblTable.Rows.Count + 2, eCol +

              4].Style.Numberformat.Format = "0.00";

       ws.Cells[sRow, eCol + 4, sRow + tblTable.Rows.Count + 2, eCol +

              4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

       ws.Cells[sRow + tblTable.Rows.Count + 2, eCol + 3].Value = "TOTAL";

       ws.Cells[sRow + tblTable.Rows.Count + 2, eCol + 4].Formula = "=SUM(" +

              ws.Cells[sRow + 1, eCol + 4].Address + ":"+ ws.Cells[sRow +

              tblTable.Rows.Count, eCol + 4].Address + ")";

       ws.Cells[sRow + tblTable.Rows.Count + 2, eCol + 4].Style.Numberformat.Format =

              "0.00";

       

       Open Excel File

       using (var memoryStream = new MemoryStream())

       {

   string fileName = "FileName.xls";

   byte[] bytes = exPack.GetAsByteArray();

   try

   {

using(MemoryStream ms = newMemoryStream(bytes))

{

  HttpResponseMessage httpResponseMessage = newHttpResponseMessage();

  httpResponseMessage.Content = newByteArrayContent(bytes);

  httpResponseMessage.Content.Headers.Add("x-filename", fileName);

  httpResponseMessage.Content.Headers.ContentType = new   

   MediaTypeHeaderValue("application/octet-stream");

         httpResponseMessage.Content.Headers.ContentDisposition = new

           ContentDispositionHeaderValue("attachment");

  httpResponseMessage.Content.Headers.ContentDisposition.FileName =

     fileName;

  httpResponseMessage.StatusCode = HttpStatusCode.OK;

  return httpResponseMessage;

              }

   }

   catch

   {

returnnewHttpResponseMessage(HttpStatusCode.InternalServerError);

   }

      }

}

Angular File

ExportToExcel(){

    this.reportService.getReportExcel(this.quoHed.numQuoID).subscribe(data=>{

      var filename = ‘filename.xls';

      var contentType = 'application/ms-excel';

      var linkElement = document.createElement('a');   

      var blob = new Blob([data], { type: contentType });

      var url =  window.URL.createObjectURL(blob);

      linkElement.setAttribute('href', url);

      linkElement.setAttribute("download", filename);

      var clickEvent = new MouseEvent("click", {

          "view": window,

          "bubbles": true,

          "cancelable": false

      });

      linkElement.dispatchEvent(clickEvent

    },()=>{

      this.toastr.error("An error occured while generation report!");

    });

  }