ImportDataTable no longer uses existing row's formatting

fredrikg · August 31, 2015, 2:58pm

Hi,

We have just upgraded from Aspose v7.2.0.3 to v8.5.0.0 and noticed a change in the ImportDataTable behaviour that is causing problems for us.

In the previous version, the formatting and data validation settings would be copied for each new row inserted, when insertRows = true.

However, with 8.5.0.0, the copying of formatting does not occur. I realize that this better mimics the behaviour of Excel, and this is something that Aspose strives for. However, for us, this is a breaking change, and we have hundreds of exisitng Excel-templates that rely on it.

It is actually a very convenient way to import data into a sheet, and use the first row as a formatting template. The only problem has been that there is an extra row, one row below the imported data, that had to be deleted by code.

Can you recommend how to achieve the same result with the new Aspose version? What would be great would be a flag to ImportDataTable which would control if the existing formatting/data validation should be used for all inserted rows. This would ideally not leave an extra row with formatting below the imported range (please see attached result files to see what I mean by that extra row)

See the code and Excel files attached.

Description of files:

ImportDataTable.cs - the code

ImportDataTable.xlsx - the designer template

ImportDataTable_result_7.2.0.3.xlsx - result with Aspose 7.2.0.3

ImportDataTable_result_8.5.0.0.xlsx - result with Aspose 8.5.0.0

shakeel.faiz · August 31, 2015, 9:42pm

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and logged this issue in our database for improvement. We will look into it and see if we could introduce one additional parameter to fulfill your requirements and if it is feasible to do so. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

CELLSNET-43860 - Use existing row’s formatting while importing data table

shakeel.faiz · September 1, 2015, 10:28pm

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-43860 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

fredrikg · September 2, 2015, 3:45am

That is excellent news!

One more thing:
I think I discovered a bug in Aspose 8.5.0.0, related to this.

In my example attached in this thread, the data as imported in the first row, first column:
sourceCells.ImportDataTable(tbl, true, 0, 0, tbl.Rows.Count, tbl.Columns.Count, true);

In that case, no formatting was copied.

However, if I change to second row, second column:
sourceCells.ImportDataTable(tbl, true, 1, 1, tbl.Rows.Count, tbl.Columns.Count, true);
and change the Excel template accordingly, then the color formatting will be copied, but not the data validation.

/Fredrik

amjad.sahi · September 2, 2015, 4:26am

Hi,

Thanks for providing us further details.

As we already fixed your issue and we are conducting extensive testing for QA, so please spare us little more time (1-2 days hopefully). The new fix would resolve your issue completely. Your all test cases should be accommodated accordingly in the new fix.

Thank you.

amjad.sahi · September 2, 2015, 4:35am

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.5.2.2 (attached).

We have fixed your issue now.

Let us know your feedback.

Thank you.

fredrikg · September 2, 2015, 6:00am

Yes, the behaviour is now back to the previous version’s (v7.2.0.3): both color formatting and data validation is copied when insertRows = true.

Although I am delighted that you could address this so promptly, I am a little concerned about the change in the default behaviour (again) of ImportDataTable.

There could be a chance that some of your other customers will find this a breaking change? And then you might fix it back to the v8.5.0.0 behaviour, thus breaking it for us again…

What do you think? Maybe it would be better to stay with the current v8.5.0.0 behaviour (no formatting gets copied) and then introduce an optional flag to ImportDataTable? Maybe that flag could mimic the options you get when inserting a new row in Excel:
Format as above, Format as below (we would use this option), Clear formatting (default behaviour).

Of course, there is also that bug in v8.5.0.0 I mentioned (color formatting gets copied when the row>0)…

Thanks,
Fredrik

amjad.sahi · September 2, 2015, 6:23am

Hi,

Thanks for your feedback.

fredrikg:

Yes, the behaviour is now back to the previous version's (v7.2.0.3): both color formatting and data validation is copied when insertRows = true.
Although I am delighted that you could address this so promptly, I am a little concerned about the change in the default behaviour (again) of ImportDataTable.

There could be a chance that some of your other customers will find this a breaking change? And then you might fix it back to the v8.5.0.0 behaviour, thus breaking it for us again…

What do you think? Maybe it would be better to stay with the current v8.5.0.0 behaviour (no formatting gets copied) and then introduce an optional flag to ImportDataTable? Maybe that flag could mimic the options you get when inserting a new row in Excel:
Format as above, Format as below (we would use this option), Clear formatting (default behaviour).

Well, we follow Ms Excel standards and specifications. When you set insertRows Boolean parameter on, it will first insert blank row (at your specified indexed position) and then paste data into the cells accordingly. Actually this was as bug in the product’s recent versions, the product should behave like you use with older Aspose.Cells’ version. You may try the steps manually in MS Excel i.e., insert data for range of cells at specified location in the sheet which does produce the similar behavior, i.e., it does copy the formatting and data validations accordingly.

Furthermore, hopefully the current behavior would not break in the upcoming versions. We will also try to tighten the QA when incorporating new features or enhancing the existing features.

fredrikg:

Of course, there is also that bug in v8.5.0.0 I mentioned (color formatting gets copied when the row>0

I am not sure about it, do you mean it still produces the issue when the row index is greater than 0? Could you provide more details and attach the template file to reproduce the issue on our end, we will check it soon.

Thank you.

fredrikg · September 2, 2015, 6:41am

Hmm, when I try to manually perform the “ImportDataTable” in Excel I do the following:

Select the row where the data is to be inserted
Right-click the row and select “Insert”
Repeat for #2 for each row in the data table
Paste the data into the new area

In this case, formatting does not get copied. What are the manual steps you use?

About the bug, I mention it in earlier in this conversation, but it is fixed in v8.5.2.2:

"In my example attached in this thread, the data as imported in the first row, first column:

sourceCells.ImportDataTable(tbl, true, 0, 0, tbl.Rows.Count, tbl.Columns.Count, true);

In that case, no formatting was copied. However, if I change to second row, second column:

sourceCells.ImportDataTable(tbl, true, 1, 1, tbl.Rows.Count, tbl.Columns.Count, true);

and change the Excel template accordingly, then the color formatting will be copied, but not the data validation"

amjad.sahi · September 2, 2015, 8:44am

Hi,

Well, you have to use Insert Options while inserting new/blank row(s). My manual steps to insert blank rows are simple (e.g Excel 2007/2010):

1) Open your template Excel file.

2) Select A1:B1 cells only

3) Right-click on it and click “Insert” from the context menu,

4) Click OK button with default option selected from Insert dialog box.

5) Now click on “Insert Options” icon/drop down next to the A2:B2 cell and select “Format Same as Below” option from it.

6) Repeat the above step as per your number of records in the table etc.

Thank you.

fredrikg · September 3, 2015, 1:55am

As I see it then, is that the behaviour in v8.5.2.2 does not mimic the default functionality in Excel.
It is rather the behaviour you get if you change the formatting option to “Format Same as Below”.

But I am not going to argue against this anymore. It works fine for us, as long as Aspose doesn’t change it back in the future

Thanks!
/Fredrik

amjad.sahi · September 3, 2015, 2:30am

Hi Fredrik,

Good to know that your original issue is sorted out in the latest version/fix. Yes, Aspose.Cells won’t change this functionality now when you set the Boolean parameter insertRows to true. Also, we think if we add another flag to ImportDataTable() method for your custom needs (to apply or not to apply formattings etc.), it might create confusion for some of the users, so we decide not to add any further parameter to the method for now and get back to original functionality.

Thank you.