Free Support Forum - aspose.com

Use of Import and Export methods of Cells

I have a question about the proper use of the ExportDataTable method of Cells.

In my project, this line of code works:

cells.ImportFromDataReader(sqlDataReader, 1, 0, true);

At this point, I have a not-so-common use case that I need to handle that requires me to change some of the data in the cells that were just imported. (I do not want to save the Excel document yet!)

To satisfy the above, I thought that I could call the ExportDataTable method, edit the returned DataTable instance, and then call ImportDataTable. But the following code does not return any data:

DataTable table = cells.ExportDataTable(row, column, maxRow, maxColumn, false);

Should this approach work? Could someone post some sample code that will work for this use case? Thanks.

P.S. - Part of the problem may be due to the following. The API documentation for (Cells) ExportDataTable (and how other Aspose APIs work) leads me to believe it is okay to specifiy a "maxRows" or "maxColumns" whose value is larger than the number of populated rows or columns. But passing 65536 and 256, respectively, causes a failure; whereas passing 2 and 2 (below) works.

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

Aspose.Excel.Cells cells = excel.Worksheets[0].Cells;

cells[0, 0].PutValue("row0col0");

cells[0, 1].PutValue("row0col1");

cells[1, 0].PutValue("row1col0");

cells[1, 1].PutValue("row1col1");

//FAILS: DataTable table = cells.ExportDataTable(0, 0, 65536, 256, false);

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 2, 2, false);

Assert.IsTrue(table.Columns.Count == 2);

Assert.IsTrue(table.Rows.Count == 2);

When you call

DataTable table = cells.ExportDataTable(0, 0, 65536, 256, false);

Aspose.Excel creates a DataTable with 65536 rows and 256 columns and populates null if blank data in a cell.

Could you please help to understand the expected behavior in the following code?

After the following code executes, a call to table.Rows.Count returns 2. Should it be 3? Is the complication that I sent a value of 'false' as the exportColumnName argument to the Cells ExportDataTable method?

If I change the exportColumnName argument to 'true', a call to table.Rows.Count returns 1, which is not correct.

The Sample Code:

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

Aspose.Excel.Cells cells = excel.Worksheets[0].Cells;

cells[0, 0].PutValue("row0col0");

cells[0, 1].PutValue("row0col1");

cells[1, 0].PutValue("row1col0");

cells[1, 1].PutValue("row1col1");

cells[2, 0].PutValue("row2col0");

cells[2, 1].PutValue("row2col1");

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 2, 2, false);

Your sample code returns 2 rows and 2 columns. Maybe the API reference mislead you. I will shape it.

When you call ExportDataTable method, you should specify the number of rows and columns of data.

Thank you. But why does Rows.Count return 1 for the following, where the last argument is 'true' to export the column names? I specificed 2x2 as the number of rows and columns of data. I would think the starting row and starting column should be (0, 0) so that the 'true' argument has meaning?

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

Aspose.Excel.Cells cells = excel.Worksheets[0].Cells;

cells[0, 0].PutValue("header0");

cells[0, 1].PutValue("header1");

cells[1, 0].PutValue("row1col0");

cells[1, 1].PutValue("row1col1");

cells[2, 0].PutValue("row2col0");

cells[2, 1].PutValue("row2col1");

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 2, 2, true);

The first row of data will be recognized as the column header name when you set exportColumnName to true. Cell (0,0) and cell (0,1) contains the colunmn name.

So in your case, if you want to export 2X2 rows and columns of data with the column name as you specified, please call:

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 2, true);

Laurence, table.Rows.Count is still returning 1 when I make the change that you suggest:

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 2, true);

The first assertion fails in the following code:

[TestMethod]

public void ExportDataTableTest_2()

{

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

Aspose.Excel.Cells cells = excel.Worksheets[0].Cells;

cells[0, 0].PutValue("header0");

cells[0, 1].PutValue("header1");

cells[1, 0].PutValue("row1col0");

cells[1, 1].PutValue("row1col1");

cells[2, 0].PutValue("row2col0");

cells[2, 1].PutValue("row2col1");

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 2, true);

Assert.IsTrue(table.Rows.Count == 2, "Table populated 3x2 but row count is " + table.Rows.Count + " (expected 2)");

Assert.IsTrue(table.Columns.Count == 2, "Table populated 3x2 but column count is " + table.Columns.Count + " (expected 2)");

}

It works fine in my place. I used the following sample code:

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

Aspose.Excel.Cells cells = excel.Worksheets[0].Cells;

cells[0, 0].PutValue("row0col0");

cells[0, 1].PutValue("row0col1");

cells[1, 0].PutValue("row1col0");

cells[1, 1].PutValue("row1col1");

cells[2, 0].PutValue("row2col0");

cells[2, 1].PutValue("row2col1");

DataTable table = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 2, true);


Console.WriteLine(table.Columns.Count);
Console.WriteLine(table.Rows.Count);

Which version are you using? Please try this attached one.

I was using version 3.5.3.2, which shows the bug. The problem is fixed in the DLL that you attached (version 3.5.3.4). Thank you!