Strange Worksheet.Copy problem (4.0.0.1)

Aspose.Cells Product Family
1

Laurence,

I have just found another strange problem. When you look at the attached sample, you will see that the code

- Opens a target workbook with a couple empty / placeholder sheets
- Opens individual workbooks and copies over sheets for which it finds target sheets in the aforementioned “container” workbook (search is done by name)

Now, when you switch to the fourth (Liabilities) sheet of the resulting Test.xls, you will see an orange box near the stop with just a single word in it. However, when you open the corresponding source file (FK1 [1].xls) you will notice that on the source sheet there is a bit more text within the box. Even worse the single word on the target sheet is never mentioned in the original text.

It gets even more strange when you limit the copy process to the single source sheet, i.e. do not copy all the other sheets of the other workbooks, just the “Liabilities” sheet. In this case it will copy the text, but font settings are corrupted.

Again, this hasn’t happened to use before we switched to 3.9.1.5 or 4.0.0.1. Before all that we were using 3.8.0.2

Thanks for your help

Kai

2

In 3.8.x, when copying among worksheets, rich text formatting in cells are lost. Since v3.9.x, I added this feature to also copy rich text formatting. However, there is a bug related to this feature. I fixed it. Please try attached v4.0.0.2.

3

Thanks for the update, this looks better. However I do get an error message when opening the resulting Envelope.xls now. It tells me that I may not use the same name as for an internal name. In my case Excel tells me that an attempt was made to name a range the same as the print area (Druckbereich) in German, i.e. I get a name conflict I did not get before.

Kai

4

Sorry, here’s the sample

5

This may be a problem of language setting. Are you using a German MS Excel?

Please try to add following piece of code:

Workbook excel1 = new Workbook();
excel1.Open(@"d:\temp\sample\Envelope.xls");

excel1.Language = CountryCode.Germany;

excel1.Region = CountryCode.Germany;

6

Laurence,

that didn’t fix it, sorry. However how would I ever know the target Excel’s languag users are using?

With our last “working” version 3.8.0.2 this never was an issue

Kai

7

Could you please post your output envelop.xls with v3.8.0.2? I will check it ASAP.

8

Laurence,

I will do this later today. However it seems that the printable area gets completely distorted as the attached sample shows. The TPL_HSBC.xls Template is fine. The Test.xls was generated from that and not the first sheets’ print areas.

Kai

9

I used your sample file and sample code to generate an output file. It works fine when opening in MS Excel. Could you give me an email address? I can send it to you to check if it still prompt error message in your machine.

10

I will check your new sample. Yes, the print area setting is changed. How do you create this test.xls? Just using Aspose.Cells to open and save it, or copying template worksheet to a new workbook object?

11

Sorry, I didn’t make myself clear. The last sample simply showed that 4.0.0.2 seems to trash the print area. Simply open the TPL_HSBC.xls (it won’t give any error message) and check the print areas. Next, open Test.xls (again, no Error) and check the print areas. I thought that this sample could help you further digging into it.

12

I open the template, populate some data and save it back using Save()
No copying is performed

Kai

13

Hi Kai,

I understand your problem now. However I cannot reproduce it in my machine. Following is my sample code:

Workbook workbook = new Workbook();

workbook.Open("TPL_HSBC.xls");

workbook.Save("book1.xls");

The print area shows fine. Please run it in your machine to see if the problem still occurs.

If it works fine in your machine, could you post your sample code here? Thank you very much.

14

Laurence, I forgot to mention that InsertRows also take place when populating the data. When you add a single call like workbook.Worksheets[0].Cells.InsertRow(5) you will notice the distorted print area.

BTW: Sorry, I am not able to provide you with a pre 3.9 version of the envelope as I would have to revert back all the changes I made for 3.9 (class name changes etc.).

Kai

15

Thank you for this information. It helps me to figure out the problem. Please try this attached fix.

16

Laurence, this does it. All issues I had seem to have gone, namely

- Formula reference updates when inserting / deleting
- Copying of Worksheets
- Distortion of Print Area
- Name conflict when opening workbook

Kai