JXLS auto fit row height according to the content

Question

I'm using JXLS 2.3.0 with apache poi implementation.
And I use following code to create excel :

try{
   InputStream is = ObjectCollectionDemo.class.getResourceAsStream("/template.xls")
   OutputStream os = new FileOutputStream("target/output.xls")  
   Context context = new Context();
   context.putVar("employees", employees);
   JxlsHelper.getInstance().processTemplate(is, os, context);

   }

my generated excel file looks like next :

As above screenshot shows, the first 'Name' value only display partial.

But what I want is :

That is the content in excel cell can be wrapped and the row height can auto fit the cell content.

How can I do that? Thanks in advance.

-------------- Updated -----------------

the solution is:

1. did as @Andy said
2. format the corresponding cell as wrap text in your template file
3. (optional) after step 1 and 2, the 99% info of cell content can be shown, but still miss some. Then I open the template file and found it looks like next:

we can found that the } of ${a.name} is on the new line, change it to:

that is make ${a.name} is on one line, then all content can be shown.

Abduct

Answer 1

I know this post is quite old, but i stumbled upon it as one of the first Google hits, and want to share my solution.

I implemented the auto-row-height feature for MS Excel 2010 in 3 steps.

Create a Command:

public class AutoRowHeightCommand extends AbstractCommand {

    // ... left out boilerplate

    @Override
    public Size applyAt(CellRef cellRef, Context context) {
        Size size = this.area.applyAt(cellRef, context);

        PoiTransformer transformer = (PoiTransformer) area.getTransformer();
        Row row = transformer.getWorkbook().getSheet(cellRef.getSheetName()).getRow(cellRef.getRow());
        row.setHeight((short) -1);

        return size;
    }
}

Configure the command to be used

// static method call:
XlsCommentAreaBuilder.addCommandMapping("autoRowHeight", AutoRowHeightCommand.class);

JxlsHelper.getInstance().processTemplate(is, os, context);

Make use of the command

In the template.xlsx file edit the cell-comment that already contains the loop-command, and add the autoRowHeight command as a new line, e.g.:

jx:each(items="myitems", var="i", lastCell="B4")
jx:autoRowHeight(lastCell="B4")

Thanks to Leonid Vysochyn and Franz Frühwirth, who lead me to this solution.

Michelinemichell

it's appreciated that you post your answer. I did as you said, but I failed, the cell content still cannot be wrap. What I missed? @Michelinemichell – Abduct

@Abduct hard to tell without any further information. First: are you using MS Excel? Which version? I've tested with MS Excel 2010, but the Solution by F. Frühwirth is inteded to work for 2013. Next: is the command executed at all? Put a breakpoint in AutoRowHeightCommand or add some log-output to see what happens. – Michelinemichell

Please see my updated part at my bottom of question. First, this command works, because when I change row.setHeight((short)-1) to row.setHeigh(3000), then the row height is increase much. Second, I am using MS Excel 2016. – Abduct

@Abduct hm... have you tried to put spaces within the values? As far as I understand it, the cell height is calculated by Excel, and I can imagine it has problems calculating it for one long value. – Michelinemichell

Hello @Andy, your soution works for me on Jxls ver. 2.4.5. The affected cells in template need to be setted like this: 1) Select cells to be formattted, than Cells -> Format -> Fit height of rows, 2) Alignment -> Wrapping text. Notice: Very important is to use original Excel. For example, Libre Office Calc doesn't display cells corectly. – Bevus

@chiahao glad to hear. Please click 'upvote' then. Thanks. – Michelinemichell

Hi @Andy, now I found that custom autoRowHeight command not work for merged cell, do you find the solution yet? – Abduct

@Abduct sorry, can't help. – Michelinemichell

not work for streaming workbook, or just need additional if for SXSSFRow in removeDyDescentAttr method – Lifeboat

Answer 2

I have the same issue with Excel 2016. I created a xlsx-template and used the same solution row.setHeight((short) -1) , but it doesn't work because Excel 2016 adds dyDescent attribute to the row definition in the template. I couldn't find a way how to force Excel not to set this attribute.

The dyDescent attribute has a side effect; it sets the customHeight attribute to true even if the customHeight attribute is explicitly set to false.

I'am not sure, that you can implement this for xls-template, but... Here is my workaround:

    @Override
    public Size applyAt(CellRef cellRef, Context context) {
       // code from @Andy example 
        Row row = .....
        removeDyDescentAttr(row);
        return size;
    }

    private void removeDyDescentAttr(Row row) {
        XSSFRow xssfRow = (XSSFRow) row;
        CTRowImpl ctRow = (CTRowImpl) xssfRow.getCTRow();
        QName dyDescent = new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
        if (ctRow.get_store().find_attribute_user(dyDescent) != null) {
           ctRow.get_store().remove_attribute(dyDescent);
        }
    }

Neckwear

@frank, I've updated my answer. Please look at it. And if you still need a complete code, just let me know and I prepare a project and push it to the github. – Neckwear

I tried but there is ClassCastException for XSSFRow. If it is ok then please share your complete project in github, thanks! – Abduct

I suppose, that in your case Row has HSSFRow type, because you use xls-template. But my workaround is applicable only for xlsx-templates. Can you save your template in xlsx? – Neckwear

I changed to xlsx-template, now it throws indexoutofboundsexception for ctRow.get_store().remove_attribute(new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac", "dyDescent")) – Abduct

@Abduct please look at github.com/UdalovSergey/jxls-auto-fit-row-example. Here is a full example. Your last exception rise because your template doesn't contain dyDescent attribute. – Neckwear

It works base on your new and complete removeDyDescentAttr() – Abduct

I tried your code with your xlsx template, it works! However, it failed if I create my xlsx template file(also with your code). test.xslx of github.com/takeAction/jxls-auto-fit-row-example is my template file, and I can't tell where there is difference between my template file with yours. It's appreciated if you could check, Thanks! – Abduct