DataTables and DataTypes

Jan 12, 2012 at 8:36 PM
Edited Jan 12, 2012 at 8:36 PM

Hello,

While working with ClosedXML, I am able to control the datatypes while manually looping through a recordset:

worksheet.Column("A").SetDataType(XLCellValues.Text);

or

worksheet.Column("A").DataType = XLCellValues.Text;

and setting the value using

Cell.Value = "Hi";

or

Cell.SetValue("Hi");

Using a DataTable to populate the spreadsheet, I am unable to change the resulting DataType assignments.  Every column appears to be "General".  I tried several ways and in many places to change the DataType:

DataTable dtMainTable = new DataTable();
// dtMainTable populated from database recordset
IXLRange rngRow = worksheet.Range("A8:U8");
IXLTable mainTable = rngRow.Cell(1, 1).InsertTable(dtMainTable.AsEnumerable(), false);

I tried all of these without success:

mainTable.Column("A").Cells().DataType = XLCellValues.Text;
mainTable.Column("A").DataType = XLCellValues.Text;
mainTable.Column("A").Cells().SetDataType(XLCellValues.Text);
mainTable.Column("A").SetDataType(XLCellValues.Text);
mainTable.DataType = XLCellValues.Text;

Using ClosedXML this way, can I change the DataType of the columns?

Thanks in advance!

Jan 12, 2012 at 10:48 PM
Edited Jan 12, 2012 at 10:51 PM

Update:

It turns out that this does work:

mainTable.Column("A").Cells().DataType = XLCellValues.Text;

I happened to have a file that had a numeric string with ONE letter like "123456789E00005" - there was no automatic conversion to scientific notation when I opened the file.  This was an "oops" on my part - I should have check more files.  The cells, however, in the excel document are still of type "General" and if I were to change a value in a "General" cell to match something like the above value, the contents would be changed to scientific notation.

I think this is a non-issue, but I wanted to share anyway - just in case anyone will be processing values that excel might think is scientific notation.  Be sure to specify the DataType to avoid conversion that might be change the way data is displayed.

Please disregard this issue - it is not one!

Coordinator
Jan 13, 2012 at 12:19 AM

The following should work too...

mainTable.Column("A").DataType = XLCellValues.Text;