ExportDdateTable

Why am I getting the ‘Specified argument was out of renge of valid values.’ error message when calling

DataTable datatable = cells.ExportDdateTable(1, 1, 35535, 255);

Row and column indexes are zero based. So please try:

DataTable datatable = cells.ExportDdateTable(1, 1, 35535, 254);

or

DataTable datatable = cells.ExportDdateTable(1, 0, 35535, 255);

I have exported a data table and bound it to a data grid. It works fine except that the data grid displays (null) in cells that were empty in the original xls file.

Could you email me your code and designer file?

If the cell is empty in the designer file, it will not show in the data grid. What do you want to show if the cell is empty?

The original excel file has empty cells, they might be of a different type, general, number, text… Regardless what time it is want the datagrid to display these cell empty, not a null as it currently displays.

I still don’t understand your need. What do you mean “display these cell empty”? Now if a cell is empty, it will not be displayed in the datagrid. Do you want the datagrid show a string of “empty” in those empty cells?

My application doesn’t control format of the excel files it processes. Cells, in the original files, that appear to be empty (visually empty, meaning that when I look at the original file I don’t see anything in them), are displayed by the data grid with the (null) value. For example, there is a cell of type ‘General’ in the original file. Since, the creator of the file has not put anything in this cell it visually appears to be empty. Now, if I use ‘Aspose’ to read in the file, export the data table and bind it to the grid this same cell will be displayed with a (null) in it. I don’t want the null to show up.

Please, let me know if the explanation is any clearer.

Which version are you using? Please try the latest hotfix. It’s v2.1.2.3.

If a cell appears to empty, the value in the exported DataTable will be DBNull. Please debug and watch your exported DataTable.

In my machine, the data grid shows empty but not the “null”. If you still have the problem, please zip your project and send it to me. I will investigate to see what caused your problem.

Laurence,

I am using v2.1.2.7. Let me show you the steps I am taking and outcome I am getting;

I have a DataSet, with one table of one row, represented by an xml document;




I use this documet to create a DataSet object and import the table into your excel object.

// create a data set object
DataSet dataSet = new DataSet();

// initialize the data set with the above xm document
dataSet.ReadXml(new StringReader(this.Model));

// create an excelTable object which is used
// to model the table
Excel excelTable = new Excel();

// add a table worksheet to the excel object
int tableWorksheetIndex = excelTable.Worksheets.Add();

// get the worksheet’s cells
excelTableCells = excelTable.Worksheets[tableWorksheetIndex].Cells;

// import the table from the model
excelTableCells.ImportDataTable(dataSet.Tables[EdmStringConstants.Table], false, 0, 0);

Finally, I export a table from your excel document and bind it with the data grid (normally, I would minipulate the excel data prior to exporting the table);

// set the current binding
this.dataGrid.SetDataBinding(excelTableCells.ExportDataTable(0, 0, 3, 3), string.Empty);


As a result I am getting (null) in the empty cells;



It’s not a problem of Aspose.Excel but a problem of windows DataGrid control. It always display a “(null)” when meeting a DBNull value. You can try the following code:

DataTable dt = new DataTable(“Products”);
dt.Columns.Add(“Product_ID”,typeof(Int32));
dt.Columns.Add(“Product_Name”,typeof(string));
dt.Columns.Add(“Units_In_Stock”,typeof(Int32));
DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = “Aniseed Syrup”;
dr[2] = 15;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = 2;
dr[1] = “Boston Crab Meat”;
dt.Rows.Add(dr);


this.dataGrid.SetDataBinding(dt, string.Empty);

However, in a web DataGrid control, the DBNull value is not shown as “(null)”.

It is not DataGrid problem. It is a problem with your API. Aspse package builds the DataTable when calling the ‘ExportDataTable’. The default value (DataCalumn.DefaultValue) of the DataCalumn, you use to build the DataTable, is DBNull and there is no way for me to set the default value to string.Empty.

Now I really understand your need. I will provide a new overload ExportDataTable method:

public void ExportDataTable(DataTable dt, int firstRow, byte firstColumn, int maxRows, int maxColumns)

You can set default value for the column when you initialize the DataTable.

How do you think of it?

It would be great if could do it. Although, I am not sure how the table initialization would work. The DataTable API doesn’t allow for setting the default values, it is the DataColumn.DefaultValue method that does is. So, in order to initialize the default value I would have to create the data table object, create the data column objects and set their default value, add the columns to the table and pass the table to your API. It doesn’t sound like a workable solution. How about

public DataTable ExportDataTable(int firstRow, int firstColumn, int maxRows, int maxColumns, object defaultValue,…)

… - Other initialization values (to be determined)

P.S. Why do you use byte for the firstColumn argument?

Thanks for your suggestion. But I think that the variable parameters of default value tend to cause error. For example, if you want to export 10 columns, you may have to call this method with 14 parameters!

So I change the method to:

public DataTable ExportDataTable(int firstRow, byte firstColumn, int maxRows, int maxColumns, object[] defaultValues)

Column index is type of byte. In MS Excel, the total column number is 256. So we set column index as type of byte: from 0 to 255. Then it can be checked at compile time, not run time.


Please download V2.2. Now this method is available, please refer to http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ExportDataTable2.html.

Thanks Laurence,

That was exactly what I needed.

Roman