ImportTableOptions documentation


#1

There seems to not be any good explanation on the usage of ImportTableOptions.
2 properties of this class provide no examples that are difficult to figure out.

  1. string[] NumberFormats - I have tried so many different variations and I cannot get anything formatting properly. How is this used? if there an order string must be in the array?
  2. DateFormat - I have tried using “MM/dd/yyyy” and my dates in the import are consistantly shown as a number. if I have “ConvertNumericData = false” then the dates come in properly, however all of my numbers are now displayed as text.

please help.


#2

@rileyja,

For your queries,

  1. See the example with comments for your reference. You may execute to generate the output file for results and understanding.
    e.g
    Sample code:

     DataTable dt = new DataTable();
                 DataRow row;
                 dt.Columns.Add("NAME", typeof(String));
                 dt.Columns.Add("AMOUNT", typeof(decimal));
                 dt.Columns.Add("DATE", typeof(DateTime));
    
                 row = dt.NewRow();
                 row["NAME"] = "Smith";
                 row["AMOUNT"] = 100.55;
                 row["DATE"] = DateTime.Now;
                 dt.Rows.Add(row);
                 row = dt.NewRow();
                 row["NAME"] = "Jones";
                 row["DATE"] = DateTime.Now;
                 dt.Rows.Add(row);
                 
                 
                 row = dt.NewRow();
                 row["NAME"] = null;
                 row["AMOUNT"] = 200.00;
                 row["DATE"] = DateTime.Now;
                 dt.Rows.Add(row);
    
    
                 var workBook = new Workbook();
                 workBook.Worksheets.Clear();
                 int i = workBook.Worksheets.Add();
                 Worksheet worksheet = workBook.Worksheets[i];
                 ImportTableOptions options = new ImportTableOptions();
                 //first field in the data table will not be set to any formatting, second field will be set to "#.00" 
                 //while the third field will be set to the sepcified DateTime formattings.
                 options.NumberFormats = new string[] { null, "#.00", "MM/dd/yyyy" };
                 //all three fields won't be set as formulas
                 options.IsFormulas = new bool[]{false, false, false};
                 //First field will have "default" as it its default value, while second field would be set to "0" accordingly.
                 options.DefaultValues = new object[] { "default", 0, null };
      
                 worksheet.Cells.ImportData(dt, 0, 0, options);
                 Cells cells = worksheet.Cells;
                 workBook.Save("e:\\test2\\out1.xlsx");
    

options.NumberFormats = new string[] { null, “#.00”, “MM/dd/yyyy” };

Well, you may try to use only NumberFormats attribute (as above) to set your desired DateTime and other formattings to your desired columns using this single properly.
But if you still want to use it separately and has some issue with it, kindly provide sample code (runnable) with template file to reproduce the issue, we will check it soon.

PS. Please try using our latest version/fix v19.8.x if you are not already using it.


#3

Yes, but then I would need to make a column format for every single report in our system and that is just not feasible. I have designed my own ColumnFormatBuilder class that handles this for me.


#4

@rileyja,
It seems that your issue is resolved. Please feel free to write us back if you have any other query in this regard.