Recommended approach to using ImportDataTable and template with listobject

Aspose.Cells Product Family
1

Good morning, afternoon and evening,

Is there a recommended approach to using ImportDataTable to add rows to template spreadsheet that has a table (or ListObject) that is defined in it?

I currently call ImportDataTable with insertRows = false

I am wondering if there is some way to resize the ListObject so that the number of rows matches what was imported or ???

Thanks,

Alan

2

Hi Alan,

The insertRows parameter in the ImportDataTable refers whether to retain the existing formatting of the cells or not, it will insert extra rows in the sheet (based on the data in the source data table). If you set it to false, it will retain the existing formatting of the cells (where you imports the data table) and does not insert extra rows. If you set this parameter to true, it will always insert extra rows and the existing formatting would be lost or shift to downwards or right.

I have tested your scenario a bit and could not find any issue, I have created a list object in an excel template file manually in MS Excel 2003, I have import data table to some range of cells in it with insert rows parameter set to false, it works fine.

I am not very clear about your issue or requirement, could you explain it more. You may also perform your steps in MS Excel manually, Aspose.Cells for .NET would work fine same as MS Excel. If you still have some issue, kindly create a sample console demo application (with all the input and output files), zip the project and post it here to reproduce the issue, we will look into it soon.

Thank you.


3

Hi Alan,

After analyzing your scenario further, I think we can understand your issue a bit. You want to update the ListObject (Table) in the template file upon importing data from the source. Actually, we do not support to update the ListObject (Table) when inserting /deleting rows. We will support it later on. Anyways, we have logged your issue into our issue tracking system with an issue id: CELLSNET-14524.

Thank you.

4

Hi,

We have supported to update the ListObject in inserting
rows. Please try the attached version.

Thank you.

5

I have two problems:

1) When I call cells.ImportDataTable(dataTable, true, 0, 0, true) it inserts the rows and the table (listobject) size is adjusted but there is an extra row at the bottom with the pre-existing column names. If I insert the rows anywhere other than 0,0 the table size is not adjusted.

2) Also, if the template has a conditional format on one of the columns, I am getting an nullreference exception when I call cells.ImportDataTable(dataTable, true, 0, 0, true);

System.NullReferenceException: Object reference not set to an instance of an obj
ect.
at Aspose.Cells.ConditionalFormattings.xc62b07e82af56ce0(Int32 x78a7603cacf2a
e22, Int32 xa21e294c3adee41c)
at Aspose.Cells.Cells.InsertRows(Int32 rowIndex, Int32 rowNumber)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNam
eShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber,
Boolean insertRows, String dateFormatString, Boolean convertStringToNumber)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNam
eShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber,
Boolean insertRows, String dateFormatString)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNam
eShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber,
Boolean insertRows)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNam
eShown, Int32 firstRow, Int32 firstColumn, Boolean insertRows)

6

Hi,

Kindly provide a sample application, zip it and post it here to show both your mentioned issues, we will check it soon.

Thank you.

7

please see attached

template1.xlsx reproduces the problem where column names are repeated at bottom of table

template2.xlsx reproduces the exception when a column has conditional formatting

I had trouble including the aspose dll in the zip file so you'll need to add it and license to test

thanks!

8

Hi,

Thanks for providing us sample project with template files.

After an initial test, I can reproduce both the issues you have mentioned. We will look into it soon.

Your issue CELLSNET-14524 is set active again.

Thank you.

9

Hi,

a):<o:p></o:p>

i)If insertRow is true when importing a DataTable, we will insert rows at the row after firstRow and copy the style of the first row to the inserted rows, then copy the first row to firstRow + insertedNumber and clear the first row.

For your issue, please insert row before importing DataTable .

ii)If you update the cell and the cell is in the header row of ListObject(Table) in MS Excel, MS will change the name of the ListColumn.

We do not auto-change the name of the ListColumn, so if you want to replace the first row with the field name, please update the name of ListColumn

With the method ListObject.UpdateColumnNames

Please check the following code:

internal void Test()

{

Workbook workbook = new Workbook();

workbook.Open(@"F:\FileTemp\template1.xlsx");

//workbook.IsTemp2007 = true;

Cells cells = workbook.Worksheets[0].Cells;

//cells["A1"].PutValue("sdfsdfsdf");

DataTable dataTable = new DataTable();

dataTable.Columns.Add();

dataTable.Rows.Add("sdf");

dataTable.Rows.Add("sdf");

dataTable.Rows.Add("sdf");

dataTable.Rows.Add("sdf");

dataTable.Rows.Add("sdf");

//// dataTable.Load(sqlDataReader);

cells.InsertRows(1, dataTable.Rows.Count);

bool exportFieldName = true;

if (exportFieldName)

{

cells.ImportDataTable(dataTable, true, 0, 0, false);

workbook.Worksheets[0].ListObjects[0].UpdateColumnNames();

}

else

{

cells.ImportDataTable(dataTable, false, 1, 0, false);

}

// workbook.Worksheets[0].ListObjects[0].ListColumns[2].Name = "sdfsdfsDF";

workbook.Save(@"F:\FileTemp\dest.xlsx");

}

b):

We have fixed the issue of Conditional formatting.

Thank you.

10

First, thank you again for the excellent support.

On the first issue, I am confused. In the sample code, you set "insertRow" to false when calling ImportDataTable so the table won't get resized? That is the problem I am running into...

Thanks!

Alan

11

Hi Alan,

1) When the “insertRow” parameter is set to true for your scenario, while importing the DataTable, we will
insert rows at an index (position) next to firstRow parameter (you provide in the method) and copy
the style of the first row to the inserted rows, then copy the first row to the firstRow

  • insertedNumber and clear the first row. This will not work well when you are putting values from a data table into an existing list/table, you have already seen the issue doing this.

    So, for your situation, it is better to set “insertRow” to false, so doing this the table won’t get re-sized. That’s why we first insert rows manually based on your source data table using Cells.InsertRows()
    method.

    Thank you.
12

Greetings,

Unfortunately it does not appear that the conditional formatting problem is fixed. I still get a nullreferenceexception when inserting rows...

Please assist.

Alan

13

Also, when I manually insert rows the table size (list object size) is not adjusted

Moreover, I don't even seem to be able to find the method "UpdateColumnNames"

Please help:)

Alan

14

Hi,

Well, as we recommended, please don’t set insertRows parameter to true.


"Moreover, I don’t even seem to be able to find the method "UpdateColumnNames"

Well, the method is “UpdateColumnName” and not “UpdateColumnNames” under ListObject class, sorry for the mistake. And, please make sure that you are using v4.8.2.8 (that I have attached in my previous post) or newer version.

Thank you.

15

thank you. I somehow had wrong version.

16

The issues you have found earlier (filed as 14524) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.