How to use new line char with in a cell?

Aspose.Cells Product Family
1

Hi,

I anm trying to display data in a cell with new line, like -

Header -1

Sub header - 1

These two headers must come in the same cell. I am using this code for it -

worksheet.Cells[0, 0].PutValue("Header -1" + (char)10 + "Sub header -1");

I get the headers , but with a small square between them. If i click on the header once and click on the formula bar, then it comes to correct position. HOw can i correct this? Basically i need to know how i can get to use "Alt + Enter" feature of a cell in aspose. Also, how can I put fonts for these headers? I tried using this code but dint work-

cells[startRow + 2, 7].PutValue("Hello World! &\"Courier New\"&14 IS this ok???"); any other way for this?

Rgds

Soumya


2
  1. For new line character, please try:

worksheet.Cells[0, 0].PutValue(“Header -1\nSub header -1”);

worksheet.Cells[0, 0].Style.IsTextWrapped = true;

worksheet.AutoFitRow(0);

  1. If you want to set difference font in a cell, please http://www.aspose.com/wiki/default.aspx/Aspose.Cells/RichText.html.
3

I am able to put in the style. But i see one problem that when i merge the cells the headers do not appear. Any problems with merging or is it that we have to apply text wrapp on all merged cells too?

4

Could you post your sample code to show this merge problem?

If you merge a range of cells, only data in the left-top cells will be preserved. That's same as MS Excel.

5

ok, this is the code

worksheet.Cells.InsertRow(0);

worksheet.Cells.InsertRow(0);

//set the font styles for Report Name and view name.

string[] rptName = r.Name.Split((char)10);

worksheet.Cells[0, 0].PutValue(r.Name); // Report name

worksheet.Cells[0, 0].Characters(0, rptName[0].Length).Font.Size = 16;

worksheet.Cells[0, 0].Characters(0, rptName[0].Length).Font.IsBold = true;

worksheet.Cells[0, 0].Characters(rptName[0].Length + 1, rptName[1].Length).Font.Size = 12;

worksheet.Cells[0, 0].Characters(rptName[0].Length + 1, rptName[1].Length).Font.IsBold = false;

worksheet.Cells[0, 0].Style.IsTextWrapped = true;

//worksheet.Cells.SetRowHeightPixel(0, 45);

worksheet.Cells[0, 0].Style.Font.Color = System.Drawing.Color.White;

worksheet.Cells[0, 0].Style.HorizontalAlignment = TextAlignmentType.Left;

int intFreezCol = 0;

if (intcol != 0)

{

//for table reports

intFreezCol = intcol;

}

else

{

//for matrix reports

intFreezCol = curCol;

}

worksheet.Cells[0, intFreezCol].Style.HorizontalAlignment = TextAlignmentType.Right;

worksheet.Cells.Merge(0, 0, 1, intFreezCol);

// indicates that 2 rows are added, this must be taken care of while freezing panes.

intFreezRow = intFreezRow + 2;

for (int i = 0; i <= intFreezCol; i++)

{

worksheet.Cells[0, 0].Style.IsTextWrapped = true;

worksheet.Cells[0, i].Style.ForegroundColor = workbook.GetMatchingColor(System.Drawing.Color.Black);

worksheet.Cells[0, i].Style.Pattern = BackgroundType.Solid;

}

worksheet.AutoFitRow(0);

6

1. Please call AutoFitRow method before merging cells. AutoFit won't take effort on merged cells. That's same as MS Excel.

2. Black color is normally in the color palette, so you don't need to call GetMatchingColor method for it(just a recommendation).

Following is my workable sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
string rName = "hello\nworld";

//set the font styles for Report Name and view name.

string[] rptName = rName.Split((char)10);

worksheet.Cells[0, 0].PutValue(rName); // Report name

worksheet.Cells[0, 0].Characters(0, rptName[0].Length).Font.Size = 16;

worksheet.Cells[0, 0].Characters(0, rptName[0].Length).Font.IsBold = true;

worksheet.Cells[0, 0].Characters(rptName[0].Length + 1, rptName[1].Length).Font.Size = 12;

worksheet.Cells[0, 0].Characters(rptName[0].Length + 1, rptName[1].Length).Font.IsBold = false;

worksheet.Cells[0, 0].Style.IsTextWrapped = true;

//worksheet.Cells.SetRowHeightPixel(0, 45);

worksheet.Cells[0, 0].Style.Font.Color = System.Drawing.Color.White;

worksheet.Cells[0, 0].Style.HorizontalAlignment = TextAlignmentType.Left;


int intFreezCol = 0;

int intcol = 2;
int intFreezeRow = 2;

if (intcol != 0)

{

//for table reports

intFreezCol = intcol;

}

else

{

//for matrix reports

intFreezCol = 2;

}

worksheet.Cells[0, intFreezCol].Style.HorizontalAlignment = TextAlignmentType.Right;

worksheet.AutoFitRow(0);

worksheet.Cells.Merge(0, 0, 1, intFreezCol);



// indicates that 2 rows are added, this must be taken care of while freezing panes.

intFreezeRow = intFreezeRow + 2;

for (int i = 0; i <= intFreezCol; i++)

{

worksheet.Cells[0, 0].Style.IsTextWrapped = true;

worksheet.Cells[0, i].Style.ForegroundColor = System.Drawing.Color.Black;

worksheet.Cells[0, i].Style.Pattern = BackgroundType.Solid;

}

workbook.Save("d:\\test\\abc.xls");

7

Ok i understand this, I put in your code . I still get the same problem. I have attached my excel ..pls take a look.....

8

It seems a bug in old version. Please try this attached fix.

9
Laurence:

worksheet.Cells[0, 0].PutValue("Header -1\nSub header -1");

I have the same problem with the little square using vbCrLF or vbCr in Visual Basic. The escaped n (\n) does not work in VB. Could you help with vb code, please?

Thanks

Vlado

10

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, you have to specify the Cell.Style.IsTextWrapped = true to perform this operation. In MS Excel if you don’t wrap the cell, it will also show you the “square” in place of line break character.

Please see the following sample code,

'Instantiate a new Workbook.

Dim excelbook As Workbook = New Workbook()

excelbook.Worksheets(0).Cells("C2").PutValue("Header -1" & vbLf & "Sub header -1")

excelbook.Worksheets(0).Cells("C2").Style.IsTextWrapped = True

'Save the excel file.

excelbook.Save("d:\tstradiobuttons.xls")

Thank You & Best Regards,

11

Thanks. Using vbLf instead of vbCrLf helped.