Sizing cell height dynamically w.r.t data text

Aspose.Cells Product Family
1

Hi,

I am using aspose cells in my application. I have merged a few cells together to form a text area. I want the cells height to be dynamically adjusted w.r.t text.

The functions IsTextWrap and AutoFitRow does not work as the cells are merged.

Can anyone kindly suggest me a solution?

Thanks,

Kannan

2
Hi Kanan,

Thanks for considering Aspose.

Yes you may do it... following is sample code:

.
.
ArrayList al = new ArrayList();
al = cells.MergedCells;
CellArea ca;
int frow,fcol,erow,ecol;//,trows,tcols;
ca = new CellArea();
//Get the first cell area of the merged cells in a template file sheet.
ca = (CellArea)al[0];
frow = ca.StartRow;
fcol = ca.StartColumn;
erow = ca.EndRow;
ecol = ca.EndColumn;

int ht=0, wid=0;
for (int r = frow; r<=erow; r++)
{
ht = ht + sheet.Cells.GetRowHeightPixel(r);

}
.
.

And IsTextWrapped works fine. Following is my testing code which works fine:

//Instantiate a workbook.
Workbook workbook = new Workbook();
//Get the first (default) worksheet.
Worksheet sheet = workbook.Worksheets[0];
//Get the cells in the sheet.
Cells cells = sheet.Cells;
//Merging two cells (B5:D6)into a single cell(B5).
cells.Merge(4,1,2,3);
//Put some value into the merged cell.
cells["B5"].PutValue("Hello World lajdfljds ajflk ladjfadsflend!");
//Align the text as Center aligned.
cells["B5"].Style.HorizontalAlignment = TextAlignmentType.Center;
//Set wrapping text.
cells["B5"].Style.IsTextWrapped = true;
//Save the file.
workbook.Save("d:\\test\\test2_mergcells.xls");
Thank you.
3

Hi Amjad,

Thanks for te reply. However I am still facing the problem.

I am using a pre-defined template in which the cells are already merged. I have pasted the code below.

For Each objCompAssmntRow In objdtCompAssmnt.Rows

collCells(CurrentCa_start_cell_row, CurrentCa_start_cell_col + 6).PutValue(objCompAssmntRow("SkillText"))

collCells(CurrentCa_start_cell_row, CurrentCa_start_cell_col + 6).Style.HorizontalAlignment = TextAlignmentType.Center

collCells(CurrentCa_start_cell_row, CurrentCa_start_cell_col + 6).Style.IsTextWrapped = True

Next

Let me know if anything needs to be added for this code. The number of rows are generated dynamically as per data and hence I am planning to set the Style thru code.

Thanks,

Kannan

4
Hi,

Please set the row height and column width accordingly to fit the data into the cells before implement text wrapping.

I tested the following code and it works fine. Attached is the output file.

Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets["Sheet1"];
Cells cell = ws.Cells;
cell.SetColumnWidth(0, 35);
cell.SetRowHeight(0,36);
cell[0, 0].PutValue("Voor meer informatie\nga naar de Informatiepagina op\nThis is the third line");
cell[0, 0].Style.IsTextWrapped = true;
ws.AutoFitRow(0);
wb.Save("d:\\test\\tmultilinsvalue.xls", FileFormatType.Excel2003);

If you still find the issue please post your template file with complete sample code here. We will check it soon.

Thank you.

5

Hi,

I have attached the zip file which contains the template and function that fills the data.

PS: I am trying to load the Skills section in the template and the problem for dynamic height is the "SkillText" column.

Please let me know if you found a solution for this.

Thanks,

Kannan

6

Hi Kannan,

Well, Aspose.Cells works in the same way as MS Excel . One thing I am not very clear checking your code: you write in the loop: collCells.SetRowHeight(0, 36) collCells.SetColumnWidth(0, 35) two three times. If you want to wrap text in the Skill section (starts from 20th row) say A20, N20 and P20 onwards cells or the Comments column, you should set the row heights and column widths starting from 20th row (19 index) and their respective column(s) and not the first row/column heights/widths.

Could you throughly check your code to amend it accordingly. If you still could not solve it, pleae post your test code with input and output template excel file. We will check it soon.

Thank you.

7

Hi,

I have amendment the code as instructed. However the problem still exists. I have attached the input template, output template and the sample code. Kindly let me know if anything needs to be added in the code.

Thanks,

Kannan

8

Hi,

After checking your template input and output file, I think you need to set row heights accordingly for row 21 and column 0 (A21) onwords.... (i.e., Skill section) plus comments (T20 merged cell(s) onwords.). So kindly set the related row heights accordingly before applying Cell.Style.IsTextWrapped to true in your code.

e.g.,

'in the loop you can try to set the rowheights to around 40 of row21 to onwards as I checked in ms excel it works fine when you apply text wrapping.

collCells.SetRowHeight(SKillName_start_cell_row, 42)

collCells(SKillName_start_cell_row, SKillName_start_cell_col).Style.IsTextWrapped = True

.

.

SKillName_start_cell_row = SKillName_start_cell_row + 1

.

.

Thank you.