Import to Excel (2007)

Aspose.Cells Product Family
1

Hi there,

I have a routine which imports a datatable to excel. All works fine, except when my values are exported with a newline construct in the field.

When the data contains an 'Environment.NewLine' my collegues using Windows XP see little squares on the end of the lines, two of them. One presumably for the carriage return and the other for the newline.

When they click in the cell, the newline appears to render, but the carriage return still stays as a 'little square'.

For me, on Windows 7, I don't see the issue. We are all using excel 2007.

I can confirm that the Environment.Newline is the issue, by manually inserting a value to a cell like this:

opCell = opInstructionsSheet.Cells["A1"];
opStyle = opCell.GetStyle();
opStyle.IsTextWrapped = true;
opCell.SetStyle(opStyle);

string zpInstructions = String.Format("Here are some instructions.{0}This is line 2.{0}",Environment.NewLine);

opCell.PutValue(zpInstructions);

If in the above example, I replace 'Environment.NewLine' with "\n" then the sheet renders without the boxes for all of us. This is fine for anything I'm manually creating, but for the values in the database which may have newlines in them, I can't parse every possible item and replace any newlines.

Am I missing something here? any suggestions on how to circumvent this behaviour?

Cheers,

Doug

2

Hi,

I have checked your code with the latest version and I was not able to reproduce this problem. I think, you should download the latest version and give it a try.

Please download: Aspose.Cells for .NET v6.0.0.2

Please see the code below and see the output file.

C#


Workbook workbook = new Workbook();


Worksheet opInstructionsSheet = workbook.Worksheets[0];


Cell opCell = opInstructionsSheet.Cells[“A1”];

Style opStyle = opCell.GetStyle();

opStyle.IsTextWrapped = true;

opCell.SetStyle(opStyle);


string zpInstructions = String.Format(“Here are some instructions.{0}This is line 2.{0}”, Environment.NewLine);


opCell.PutValue(zpInstructions);


opInstructionsSheet.Cells.SetRowHeight(0, 100);

opInstructionsSheet.Cells.SetColumnWidth(0, 100);


workbook.Save(OUTDIR + “output.xlsx”, SaveFormat.Xlsx);


3

Hi mshakeel.faiz,

I will certainly download and try the latest version to see if that helps.

The only thing I'm doing differently in my code is that I'm saving the file as a .xls file, as our customer does not have office 2007 available. If you output your file as .xls and open it with excel 2007 on a windows XP machine, do you see the problem?

As I say, in .xlsx format, or on windows 7 I don't see the error either. Can you confirm what you're running the above code with please?

Many thanks,

Doug

4

Hi,

I can now see the problem and have attached the output.xls file generated by the same code but in xls format.

We have logged this issue in our database and fix it asap.

This issue has been logged as CELLSNET-28778.

5

Thanks very much for the feedback, I will monitor the thread. Let me know if you want any more info.

Many thanks,

Doug

6

Hi,

I do not think, we need any other information. Whenever you post your thread, you get subscribed to it and you get email notification. We will also post in this thread when the fix is available.

7

Hi,

Please change your code as the following:

C#


Style opStyle = opCell.GetStyle();

opStyle.IsTextWrapped = true;

opStyle.VerticalAlignment = TextAlignmentType.Bottom;

opCell.SetStyle(opStyle);


There are different default vertical alignment in xls and xlsx file.

8

Thanks for the reply, I've now implemented the

opStyle.VerticalAlignment = TextAlignmentType.Bottom;

Suggestion and this does give me a slight improvement, in that the string is now rendered with a single 'box' after each newline, rather than the two from before. This is the same behaviour I see, if I export the cell without 'TextAlignmentType.Bottom' and then double click in the cell once it's exported (one of the boxes dissappear as this point)

Unfortunately this is not ideal, and using a mechanism where I have to explicitely defined a cell property seems a bit unwieldy as in my example I may have 10 worksheets being exported with 10,000 rows on each. If I had to loop through each cell and apply the formatting I would imagine this might slow the export down quite a bit (although this is just a guess, it might be fine!)

A couple of things I did wonder about:

1. Does is make any difference if I have also called 'AutoFitRows()' on the sheet? I'd assume not, but I will remove any other calls to the sheet which might cause odd behaviours and retry a few times.

2. Is there anything that can be applied at a sheet level, or even a workbook level to perhaps replace or force the rendering of these values? I wonder about putting a VBA function in there to fiddle with the formatting, but again this feels a bit overkill for something which feels like it should be native to excel.

Any further suggestions? I will keep experimenting this end too,

Many thanks,

Doug

9

Hi,

I found the problem is still occurring and alignment is not taking an effect, will it be a problem, if you remove carriage return character with this simple code. If you just change your line of putting value like mentioned below, you will get a quick fix.


opCell.PutValue(zpInstructions.Replace("\r", “”));


The complete code will look same as above. I have pasted the code and the output file. Also, I will reopen this issue so that carriage return problem could be fixed.

1 - AutoFitRows() does not make any difference, it just creates more space so that all the text could be visible. Similarly, AutoFitColums() does the same job.

2 - I am not sure if such a solution is available.

C#
Workbook workbook = new Workbook();

Worksheet opInstructionsSheet = workbook.Worksheets[0];

Cell opCell = opInstructionsSheet.Cells["A1"];
Style opStyle = opCell.GetStyle();
opStyle.IsTextWrapped = true;
opCell.SetStyle(opStyle);

string zpInstructions = String.Format("Here are some instructions.{0}This is line 2.{0}", Environment.NewLine);

opCell.PutValue(zpInstructions.Replace("\r", "")); //changed code

opInstructionsSheet.Cells.SetRowHeight(0, 100);
opInstructionsSheet.Cells.SetColumnWidth(0, 100);

workbook.Save(DIR + "output.xls", SaveFormat.Excel97To2003);


10

Thanks again for the reply. I'm of the same opinion as yourself, that it's the carriage return potentially causing the issue rather than the newline.

Whilst it's possible that I can do a string.Replace() on certain values, namely those that are manually created like the instructions string I gave as an example; the majority of the worksheets being exported are exported using ImportCustomObjects() and with this method I would have to parse all my data lists, establish whether the property was a string type and then perform the replace.

I'd be concerned about a) the performance impact of needing this step and b) the affect it would have on the data once I export it from Excel back into the backend. In this particular application the user can edit the data in the excel workbook and then re-import the same file to the database (post validation of course). We wouldn't want to lose all our Environment.Newlines and have them replaced purely with "\n" as the front end would then render it with simply a newline and no carriage return and the user would effectively lose data (or the formatting of the data) through the export/import which is not desired.

I will continue to monitor the thread and hope that at some point a carriage return fix can be implemented. Thanks again for all the help,

Doug

11

Hi,

I think, it is a bug in xls format, carriage return should be discarded while inserting values in cells.

I have reopened the ticket associated with this issue. Hopefully, it will be fixed soon.