It seems that the DataTableImport feature is ignoring columns with NULL or blank values, as well as any column attained from a sub-select or sub-query.
I normally always stay away from correlated sub-queries, but in this case I have no choice. My data table gets formed perfectly, and I’ve even added ‘N/A’ to replace null values. However, those columns are still not being imported into the sheet.
Any thoughts? Am I Missing something?
Thanks in advance!
Hi,
Could you explain your question in advance?
Yes, in DataTableImport feature, if one column of a row has NULL values, it is omitted. Does that cause any problem for you? What do you want to put in the excel cell when the column value is NULL?
If possible, could you send me your code? Thus I can understand your question more clearly.
Thanks.
I’m having a similar problem with ImportArray(objArray()) where dates are DBNull. I would need Aspose.Excel to clear the cell value when it finds a Null/Nothing/DBNull. I’ve tried changing the array object to Nothing and “” to no avail.
Would a property such as ClearIfEmpty As Boolean (IsNullCleared or whatever) be possible to implement? It would affect all Import methods (called after setting = True).
Thanks again.
Hi Tommi,
What value do you want to put to a cell if the data value is DBNull?
I would like it to be “empty”, in other words it would retain formatting, but the contents would be blank, like when you open a new Excel document. I guess Null/Nothing is the closest thing to it?
–Tomi
Hi Tomi,
I understand your need now. Previously, if input value is null, I leave the cell data without any touch.
I will changed it to meet your need. It will be available within this week.
Great, thank you!
Are you going to implement the property I mentioned earlier? I don’t need it, but that might avoid problems with other people who rely on the current way it works.
Good advice!
I will add a parameter in import method to allow users to overwrite current cell data if the input data is null.
Hi Tomi,
If users use import method, generally they want to overwrite data in current cell. So I think it’s better to directly overwrite the cell if input data is null. That can simplify the method.
Laurence, thanks for the fix. A new day and new ideas: I came across the need for the old way, too:
I would like the user to be able to specify that data will be filled in on every X rows, ie. skip a specified amount of rows. I’m still building the data marker system and it would allow designers/templates in the following format as an example:
Template:
Row1: %%=DataSource.ProductGroup(Skip:1)
Row2: %%=DataSource.VendorName(Skip:1)
Result:
Row1: Product Group Name 1
Row2: Vendor Name 1
Row3: Product Group Name 2
Row4: Vendor Name 2
I could easily achieve this by adding a Null row into the array/datasource as every other row. Of course, this won’t be very smart if the dataset is large, since it effectively doubles (or more) the data size.
You mentioned earlier that just switching to the new way (clear if DBNull) is easier to implement than having it as a parameter on the various Import-methods. What if you do it the way I suggested, ie. with a property? Then you could just check the boolean value of the property in the Import-methods to see if we should clear the cell or not.
Sorry for all the trouble and extraneous ideas…
Oh, one more thing came to mind: Of course you could add a new parameter to the Import-methods: Skip. The Import-method would then place the data on every X rows, skipping rows as requested.
This way I wouldn’t have to add dummy rows into the array or write the code to do it 
Hi Tomi,
It will be available at the start of next week.
Hi Tomi,
Now it’s availabe. Please download it at
and refer to http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ImportObjectArray.html.
Excellent, thank you! Works like a charm.
–TB