Format Table while Export the datatable To Excel using c#

Question

I am confusing to format the datatable while exporting it to excel

My Code

var workBook = new ExpertXls.ExcelLib.ExcelWorkbook(ExpertXls.ExcelLib.ExcelWorkbookFormat.Xlsx_2007);
workBook.Worksheets.AddWorksheet();
var workSheet = workBook.Worksheets[0];
workSheet.LoadDataTable(ds.Tables[0], 1, 1, true);
workSheet.AutofitColumns();
context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "";
context.Response.ContentType = 
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
using (var MyMemoryStream = new MemoryStream())
{
    workBook.Save(MyMemoryStream);
    MyMemoryStream.WriteTo(context.Response.OutputStream);
    context.Response.Flush();
    context.Response.End();
}

The Above code works fine to export the datatable to excel without formating.

And i can able to set the Cell Font Color as,

workSheet["A1:N1"].Style.Font.Color = System.Drawing.Color.Green;

Then, how to get the worksheet cells and set its back color?

i tried but not able to get the cells

workSheet.

here can get the cells not style

worksheet["A1:N1"].Cells.

Keyur PATEL Keyur PATEL · Accepted Answer · 2018-01-03T03:24:50

I downloaded the Nuget Package of the library you're using, since their documentation is non-existent online.

Could you try the following to achieve what you want:

workSheet["A1:N1"].Style.Fill.FillType = ExpertXls.ExcelLib.ExcelCellFillType.SolidFill;
workSheet["A1:N1"].Style.Fill.SolidFillOptions.BackColor = System.Drawing.Color.Green;

Change Green to the color of your choice, and let me know if it works.

Format Table while Export the datatable To Excel using c#

1 Answers