Displaying Control Characters

Aspose.Cells Product Family
1

Using 1.9.2…

When using ImportDataTable() or PutValue() to output text to Excel, carriage return/line feeds appears as a ‘rectangle’ in place of an undisplayable control character such as a standard newline.

How do I get around this problem?

Cheers,

Haydn

2

Hi Haydn,

How do you set carriage return/line feeds using Aspose.Excel?

I use the following code and it works fine.

cell.PutValue(“abc\r\ndef”);

And could you email your file to me? Thanks.

3

Hi Haydn,

I got your file and view it in Excel 97, 2000, XP and 2003, English version and Chinese version. All displayed fine. So could you tell me which version of MS Excel are you using? And could you try to view it in another machine?

4

Hi Laurence,

I’m using Excel 2002. I’ve tried it on a number of different machines and no joy, same result.

Haydn

5

Hi Haydn,

I sent a file to you. It was created by following code:
Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;
cells[“A1”].PutValue(“This is a test \r\n”);
excel.Save(“d:\newline.xls”, FileFormatType.Default);

I guess if this problem may be caused by cell font. What happens if you change the font to “Arial”?

6

Hi Haydn,

Since your created file using MS Excel also has this problem, I think it’s caused by your Excel setting options.

Please check that:

Tools->Options->International->Show control characters

Have you select this option? Try to change it then see what will happen.

7

Hi Laurence,

I don’t have Show Control Characters enabled. I’ve enabled and disabled it on files you’ve sent and it makes no difference at all, the control characters still display.

The MS Excel file I created only has this problem if I turn off “Wrap Text” option in the “Format Cells->Alignment” property page for that cell. When I used Excel to generate the problem, as soon as I’d finished typing the string into the cell (with carriage returns), Excel automatically turned on “Wrap Text” for that cell and as a result, did not show the control character. Turning “Wrap Text” off makes Excel display the control character “\n”.

My only workaround is to check every string I’m about to put into a cell to see if it has “\r\n” in it, replace it with “\n” (this is what Excel puts in - not “\r\n”) and turn on “Wrap Text” for that cell.

Haydn

8

Hi Haydn,

I checked the Excel file and found your workaround is the only solution to solve your problem. If I want to solve in Aspose.Excel, I also can only do this.

I am very curious about this problem because I found it’s fine in most cases. Which edition of Excel 2002 are you using? I think it’s not an English version.

9

I’m using the English version! Excel 2002 (10.5815.4219) SP-2. SP3 has this problem also.

Haydn

10

I’m using the English version, Excel 2002 sp1. All worked fine.

Really a strange problem. I will modify my program in the same way as your solution.

11

I just had this problem with Cells.ImportArray. I’m importing an array of Objects (which are strings) and if an item contains CR & LF, I get the two boxes where the newline is.

If I replace the CR & LF with LF only, I still get one box which disappears after you enter the cell in Excel and move around in the formula editor (F2 didn’t do the trick).

Any ideas?

I’m testing on an English Microsoft Office Excel 2003 (11.6113.5703).

12

Hi Tommi,

Now you can try Haydn’s solution. Replace the CR & LF with LF first, then set the cell to wrap text.

13

Since I’m importing an array where some rows have CrLF and others don’t this will mean I would have to loop through the inserted data and check each one, or is there a better solution? What is the downside of setting the cell to wrap text even if it doesn’t need it?

14

Hi.

I have the same problem with control characters. When I try to put a carriage return or something similar in a string with PutValue appears a square in place of the control character in Excel.

I’ve tried all the solutions like put /r/n and set isTextWrapped to true, without any result.

I’m using Excel 2003 and Visual Studio .NET 2003.

Do you have any solution?

Thanks in advance.

15

Please try:

Excel excel1 = new Excel();
Cell cell = excel1.Worksheets[0].Cells["A1"];
cell.PutValue("Hello\nWorld");
cell.Style.IsTextWrapped = true;
excel1.Worksheets[0].Cells.SetRowHeight(0, 30);

excel1.Save("d:\\book1.xls");

16

Hi Laurence,

First of all, thanks for your fast reply. I’ve tested the solution that you suggested me but it doesn’t work. The square still appears in the excel when I introduce a control character.

Do you have any idea to solve my problem?
I’m using MS Excel 2003 and Visual Basic .NET 2003.

Thanks again.

17

The attached file is created with my sample code. And you can see it works fine from the screenshot.

Could you please send me the file created in your machine? And please attach a screenshot to show your problem. Thank you.

18

By the way, which version of Aspose.Excel are you using?

19

I’m using Aspose.Excel v3.4.1.0. The code that I use is very simillar to your code:

Dim cell As Cell = excel.Worksheets(0).Cells(“A1”)
Cell.PutValue(“Hello\nWorld”)
Cell.Style.IsTextWrapped = True
excel.Worksheets(0).Cells.SetRowHeight(0, 30)
excel.Save(“c:\book1.xls”)

Could it be problem of my MS Excel version?

Thanks.

20

Please upload your generated file here. Thus I can check it. I use English MS Excel.