Guid in Excel Using SpreadSheetGear

Question

I just need a confirmation/quick info. I feel it's an obvious question but just want to make sure and fully understand what's happening. Couldn't find much detail on the web.

I'm using SpreadSheetGear to dump data in spreadsheets from a datatable. There are some Guid in those tables. Now when I try to copy from my data table, I get an error saying wrong data type, unless I'm importing using the flags AllText or by removing the columns containing Guids in my datable.

Seems like excel cannot support Guids as variable/data type. Is this normal? I don't necessarly need the data anyway can convert easily in text format, but I just want to fully understand this issue.

Here's a sample code with the following error: Invalid cell value type.

public void Test()
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(Guid));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    table.Rows.Add(Guid.NewGuid(), "Indocin", "David", DateTime.Now);
    table.Rows.Add(Guid.NewGuid(), "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(Guid.NewGuid(), "Hydralazine", "Christoff", DateTime.Now);

    IWorkbook wrk = Factory.GetWorkbook();
    IWorksheet wsht = wrk.Worksheets["Sheet1"];
    IRange rng = wsht.Cells["A1"];
    rng.CopyFromDataTable(table, SpreadsheetGear.Data.SetDataFlags.None);
    wrk.SaveAs("C:\\MyData.xls",FileFormat.OpenXMLWorkbook);
    wrk.Close();
}

Answer 1

Using IRange.CopyFromDataTable(...) without the SetDataFlags.AllText flag is basically like using a loop to set IRange.Value for each "cell" of data in your DataTable, which means the data types used in this incoming DataTable data are subject to the setter requirements of IRange.Value, which states:

...sets the value of the specified cell as a System.String, System.Double, System.Int32, System.Int64, System.Int16, System.Char, System.Boolean, System.DateTime, SpreadsheetGear.ValueError, System.Decimal, System.DBNull, object[,] or null

Since Guid is not a valid type to use with IRange.Value, using this data type in a DataTable won't work either.

When you specify the SetDataFlags.AllText flag, SpreadsheetGear first calls ToString() on each "cell" of data in your DataTable, so all incoming data types will be accepted, regardless of whether it is in the above list or not.