Adding datatype to table columns with ClosedXML

Question

I'm using ClosedXML to create an xlsx-file with some data. In the Excel-sheet I have a table with data. In the columns of the table (not the column of the sheet) I want to specify datatypes.

            /* This datatable is created and populated somewhere else in my program*/
            Datatable dt = new Datatable();

            ws.Cell(t.Position.Row, t.Position.Column).InsertTable(dt);     

            IXLTables allTables = ws.Tables;

            var table = allTables.ElementAt(i);

            int j = 1;
            /* The options object hold the excel datatypes for each column*/
            foreach (var c in option.Tables.ElementAt(i).Columns)
            {
                table.Column(j).DataType = c.Type;
                j++;
            }

            i++;

The datatype is found and added in the foreach-loop from an options object, how this works in my program is probably not important.

The problem is that when I add a datatype to a column in the table it includes the header of the table. Since the header is "Text" and the value I might specified is Number I get an error. Anyone got an idea how to make it ignore the headerline of the table and simply add datatype to the columns below?

Thanks in advance.

Steve Steve · Accepted Answer · 2017-04-05T09:41:53

Since setting the data type can work on a per column basis in Excel it's not unreasonable to expect the same from ClosedXml. As you've found it doesn't work like that however. The way I do it is to define a range that selects the whole column except the header. I've not worked with tables the way you are but the following snippet should give you a direction.

IXLWorksheet sheet = wb.Worksheets.Add("Sheet1");
sheet.Cell(1, 1).InsertTable(dt);
foreach (var column in sheet.ColumnsUsed())
{
   string columnLetter = column.ColumnLetter();
   string rng = $"${columnLetter}2:columnLetter}sheet.RangeUsed().RowCount()}";
   sheet.Range(rng).DataType = some data type;
}

Adding datatype to table columns with ClosedXML

1 Answers