ClosedXML New Workbook Styles All Sheets

Question

I just got started with ClosedXML. When I create a new workbook with the code below, it automatically applies "Blue, Table Style Light 9" to each worksheet. I don't want any style on the worksheets. How do I specify no style?

XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(dt, "sheet1");

I'm just basically filling the sheet with a SQL datatable.

score 11 · Accepted Answer · answered Jul 10 '17 at 09:42

11

By default, ClosedXML will create a new Excel table when you use the IXLWorksheets.Add(DataTable dt) method. Excel tables always have styles applied.

To populate your worksheet with the DataTable without any styles, use this code:

using (var wb = new XLWorkbook())
{
    var ws = wb.Worksheets.Add("sheet1");
    // The false parameter indicates that a table should not be created:
    ws.FirstCell().InsertTable(dt, false);
}

answered Jul 10 '17 at 09:42

Francois Botha

4,520
1
34
46

Glad to have answer from one of the authors! – Jeson Martajaya May 22 '21 at 20:39

score 3 · Answer 2 · answered Oct 15 '19 at 18:29

3

To add a dt without a Theme use the following code:

var ws = wb.Worksheets.Add("SheetName");
ws.FirstCell().InsertTable.InsertTable(dt).Theme = XLTableTheme.None;;

answered Oct 15 '19 at 18:29

Tony Batista

177
2
13

score 2 · Answer 3 · answered Jul 07 '17 at 18:45

2

XLWorkbook and XLWorksheet both have a Style property. You can modify the style like so:

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Style Worksheet");

ws.Style.Font.Bold = true;
ws.Style.Font.FontColor = XLColor.Red;
ws.Style.Fill.BackgroundColor = XLColor.Cyan;

See here for the documentation: https://github.com/ClosedXML/ClosedXML/wiki/Using-Default-Styles

answered Jul 07 '17 at 18:45

C. Helling

1,394
6
20
34

Yeah, I saw all of that. So you're saying the only way to get no format is to format the background to white, and the font color to black??? I'm not saying that's wrong...it just seems odd that it would start with an arbitrary style. Seems like it would have no style unless you explicitly chose one. – J. Wilson Jul 07 '17 at 19:01
I think the tables look nice with an auto-chosen style. If you don't like it, yes, you have to explicitly remove it. – C. Helling Jul 07 '17 at 19:03
I don't mind the look...but the recipient of the report seems to be fairly particular. Amazing how we must please others. Haha! Thanks for answering! – J. Wilson Jul 07 '17 at 19:22

ClosedXML New Workbook Styles All Sheets

3 Answers3