Set wrap text for the cell and apply AutoFitRows function in Java

Aspose.Cells Product Family
1

We are working on a project on which we need to convert Excel introp based code to Apsose to generate Excel report. while converting to the Aspose we have noticed that if in data we have "\r\n" excel automatically set Wrap setting for that cell while aspose doews not.

Also if we explicitly set Wrap for the cell and then apply AutoFitRows and columns it is not working.

Attached is the sample report for the same.

Thanks

2

Hi,

Thanks for your posting and using Aspose.Cells.

I was able to see the problem using the latest version:
Aspose.Cells
for .NET v7.2.0.4

Please see the output file generated by your code in the attachment.

We have logged your issue in our database, we will look into it and resolve the problem.

Once, the issues is resolved or we have some other update for you relating to this issue, we will let you know asap.

This issue has been logged as CELLSNET-40628.

C#


Workbook wb = new Workbook();


Worksheet ws = wb.Worksheets[0];


Aspose.Cells.Cells cell = ws.Cells;


cell.SetColumnWidth(0, 35);


cell.SetRowHeight(0, 65);


cell[0, 0].PutValue(“I am using\nthe latest version of \nAspose.Cells to \ntest this functionality”);


//Manually set wrap for the cell.


//Style oStyle = cell[0, 0].GetStyle();

///oStyle.IsTextWrapped = true;

//cell[0, 0].SetStyle(oStyle);


ws.AutoFitColumns();

ws.AutoFitRows();


wb.Save(“SampleOutput.xls”);



3

Hi,

We do not automatically set Wrap and you have to call AutoFitRows to get the correct row height.

4

Hi,

In a sample project if you remove below lines for setting height and width.
cell.SetColumnWidth(0, 35);
cell.SetRowHeight(0, 65);

Then apply wrap setting anf then apply
ws.AutoFitColumns();
ws.AutoFitRows();

Still in generated excel file i can see autofile does not apply you have to manually select entire sheet in click on any column then it is correct autofit.

Thanks

5

Hi

Please try the following code.

C#


Workbook wb = new Workbook();


Worksheet ws = wb.Worksheets[0];


Aspose.Cells.Cells cell = ws.Cells;


cell.SetColumnWidth(0, 35);


// cell.SetRowHeight(0, 65);


cell[0, 0].PutValue(“I am using\nthe latest version of \nAspose.Cells to \ntest this functionality”);


//Manually set wrap for the cell.


Style oStyle = cell[0, 0].GetStyle();


oStyle.IsTextWrapped = true;


cell[0, 0].SetStyle(oStyle);


// ws.AutoFitColumns();


ws.AutoFitRows();


wb.Save(@“D:\Filetemp\dest.xls”);

6

Thanks for the response but unfortunately this solution does not solve my problem. By setting the column width and not applying AutofitColumns it will not be AutoFit in excel output excel always show column width as 35 even though we do not have that much data.

7

Hi,

We have fixed this issue.

Please download and try the latest fix: Aspose.Cells for .NET v7.2.0.8

Please use the following code.

C#


Workbook wb = new Workbook();


Worksheet ws = wb.Worksheets[0];


Aspose.Cells.Cells cell = ws.Cells;


cell.SetColumnWidth(0, 35);


cell.SetRowHeight(0, 65);


cell[0, 0].PutValue(“I am using\nthe latest version of \nAspose.Cells to \ntest this functionality”);


//Manually set wrap for the cell.

Style oStyle = cell[0, 0].GetStyle();

oStyle.IsTextWrapped = true;

cell[0, 0].SetStyle(oStyle);


ws.AutoFitRows();

ws.AutoFitColumns();

wb.Save(@“D:\FileTemp\SampleOutput.xls”);

8

Hi,

I am also facing the same issue, I am not able to increase the row height although warp text property of cell is set to true. My requirement is to fit the data in fixed column size, and to increase the row height if data increases. Currently, I am getting trimmed text as row height is not increasing on increasing data.

I have tried with the dll provided by you (Aspose.Cells for .NET v7.2.0.8), it is still not working for me. Could you please share your sample demo to achieve so.

Thanks in Advance,

Preety

9

Hi Preety,


We recommend you to kindly try our the latest version/fix: Aspose.Cells for .NET v7.4.0.3 if it works fine.

Here is the sample code that you may try:

Sample code:

//Create Workbook Object
Workbook wb = new Workbook();

//Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];

//Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;

//Increase the width of First Column Width
cell.SetColumnWidth(0, 35);

//Increase the height of first row
cell.SetRowHeight(0, 65);

//Add Text to the Firts Cell with Explicit Line Breaks
cell[0, 0].PutValue(“I am using\nthe latest version of \nAspose.Cells to \ntest this functionality”);

//Make Cell’s Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);
// Save Excel File
wb.Save(“e:\test2\outWrappingText.xls”);

If you still find the issue, kindly give us your sample runnable code with template file, we will check your issue soon.

Thank you.



10

Hi,

Thanks for your reply.

But this is not working for me. The dll provided by you is working fine with the code provided, as you are setting width and height of the cell in your code:

cell.SetColumnWidth(0, 35);

//Increase the height of first row
cell.SetRowHeight(0, 65);

If I am trying removing these two lines from your code, the cell height is remaining same and not getting increased as per the text entered in cell, though text is getting wrapped (but whoele text is not visible)

As per my requiremenr I want something without setting Height and width of the cell in code as these two are defined in my template itself. So I just want to increase the cell size (in terms of height) if text is increased in cell, so that the entered text will get autofit in cell with wrapped text in defined width and height in excel template.

For example: The text string assigning to cell as a value in the code given by you should get fit in to the cell by increasing height of the cell dynamically with in the given width.(same as "Wrap text" property does in cell format - it does not increase the width of the cell but increase the height of the cell as per the entered text)

Thanks in Advance

Preety

11

Hi,


Well, sure, if I remove these lines of code, still it works fine and the auto-fit operation is applied accordingly. See the updated sample code and please find attached the output file here.

Sample code:

//Create Workbook Object
Workbook wb = new Workbook();

//Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];

//Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;

//Add Text to the First Cell
cell[0, 0].PutValue(“I am using the latest version of Aspose.Cells to test this functionality”);

//Make Cell’s Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);

// Save Excel File
wb.Save(“e:\test2\MyoutWrappingText.xls”);

Note: if you are applying auto-fit operation on merged cells, I am afraid, it will not work as MS Excel too does not auto-fit rows on merged cells area. You may confirm it in MS Excel.

If you have different issue and MS Excel can work fine but Aspose.Cells does not work, please create a sample application to show the issue by attaching it here (zip the archive), we will check it soon.
Also attach your template file and output file. Moreover, attach your desired output file after auto-fit operation applied in Ms Excel.

Thank you.
12

Hi,

Thanks for your reply.

I am able to achieve my target for creation of excel with wrapped cells but now facing problem while converting excel to pdf.

The resultant pdf is not showing the properly wrapped data as in excel, it might not considering variable heights of cells at my end. Due to which data is getting trimmed or for some rows extra spae is coming (Refer attached image PdfWithEqualGaps.png). I am just taking the path of excel and converting in to pdf using :

ExcelDocumentPath.Workbook.Save(DestinationFilePath, SaveFormat.Pdf);

Please generate pdf with the same text and format from excel and let me know if you will be able to get the exact result.

I am attaching my sample excel (ConvertExcelToPdf.xls)

Thanks in advance

Preety

13

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

After initial investigation, we were able to find this issue. The text is not wrapped correctly.

Below is a sample test code. I have also attached the output pdf generated by it.

We have logged this issue in our database. We will look into it and fix it. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41423.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\ConvertExcelToPdf.xls”;


Workbook workbook = new Workbook(filePath);


workbook.Save(filePath + “.out.pdf”, SaveFormat.Pdf);

14

Hi,

Thanks for your posting and using Aspose.Cells.

Please use autofitrow and autofitcolumn. We do not find this issue. You can try to autofit in your end.

We have also attached the output pdf for your reference.

15

Hi

Thanks for your response.

I have tried converting excel to pdf with text wrapped cells in my sample application, but still I am not able to achieve the pdf in correct format (i.e. same as that in excel).

I am attaching my sample application and the resultant excel and pdf for your reference.

Please correct my sample if I have missed something and let me know as this requirement is very much critical in my project.

Please reply as soon as possible.

Thanks in advance,

Preety

16

Hi,

Thanks for your feedback and further input.

We have logged your sample project and requirements in our database against the issue id: CELLSNET-41423.

We will look into it and help you asap.

17

Hi,

Please add the following line, it will generate correct pdf. I have attached the output pdf for your reference.

C#
var finalPdfFilePath = new WorkbookDesigner {Workbook = new Workbook(tempExcelFilePath)};

finalPdfFilePath.Workbook.Worksheets[0].AutoFitRows();

finalPdfFilePath.Workbook.Save(tempExcelFilePath.Replace(".xls", “.pdf”), SaveFormat.Pdf);

Screenshot:

18

I followed all these examples and I still find this issue. In my scenario, I am not manually setting the column width. Instead, all I am doing is taking an excel spreadsheet and converting it to a PDF. When I do the exact same steps and export a JPG, the column widths and heights are honored. When I create a PDF, no column widths and heights from the excel spreadsheet make it into the PDF. As a result, I cannot maintain the formatting.


Is it possible to convert a formatted excel spreadsheet to PDF and have the correct column widths and heights automatically followed?

19

Hi Matthew,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v7.7.1.1 and see if it makes any difference.

If your problem persists, then please provide us your sample source xls/xlsx file which you are converting to pdf and the sample code replicating this issue with the latest version.

We will look into your issue and help you asap.

20

I am using the most recent version and have attached my code and files. I would point out that creating the JPG image works fine but creating the PDF does not work because the column widths and heights are not honors.


I appreciate your help on this.