12 Replies Latest reply on Oct 18, 2012 9:23 AM by cknaap

    excel formatting

    ashishbyte
      Hi,
      I am pushing data from bean class to an excel spreadsheet.

      i am not able to set the width size of the column and the height of the cells.
      <e:column value=""  style="" width="200"> has no much effect and not customizable

      xls-column-autosize: true is
      not required.
      the requiremnt is the fixed size of the column which i can give accordingly.
      please let me know the solution.
      or any help ..
        • 1. Re: excel formatting
          nickarls

          Does it react to the column level style="xls-column-width:500" e.g.?

          • 2. Re: excel formatting
            ashishbyte

            style="xls-column-width:500", it works thanks.

            another issue i am facing about increasing the row height ;

            I have a table to display where the heading is put in the 5th row whereas this row should have a height greater that the rest of the rows below.

            kindly, update on this.
            • 3. Re: excel formatting
              nickarls

              Unfortunately, it doesn't appear to be supported (I didn't find any calls to the jexcelapi method that does it).


              You could file a JIRA request for it. Apparently it hasn't been added since there isn't really a concept of a row in the xhtml as there is for column so there is no natural place to stick a xls-row-height style. Perhaps it could use them as they appear in the cell styles...

              • 4. Re: excel formatting
                ashishbyte
                Okay, I have used the code below to achieve my requirement.

                thanks anyway.

                <e:column value=""  style="xls-alignment: centre;xls-column-width: 4400;xls-wrap: true">
                            <f:facet name="header" >
                               <e:cell value="Contract Volumn Booked" style="xls-wrap: true;
                               xls-font: black bold 11 Arial;
                               xls-background-color: red;
                               xls-border: thick black;
                               xls-vertical-alignment: centre;" />
                            </f:facet>
                         <e:cell value="#{sum.contractSumAll}"  style="xls-alignment: centre;xls-font: black normal 11 Arial;"/>
                         </e:column>


                I fixed the column width and set wrap as true.
                works for me.
                • 5. Re: excel formatting
                  ashishbyte
                  I found different issue with excel formatting.

                  I want to do print-tile for the excel sheet.

                  while using like this .....

                  <e:worksheet value="#"
                            var="summary"     name="Summary" startRow="4" orientation="landscape" fitToPages="true"
                       verticalFreeze="4">
                  <e:printTitles firstRow="2" firstColumn="0"
                                       lastRow="2" lastColumn="3"/>
                  <e:printTitles firstRow="4" firstColumn="0"
                                       lastRow="4" lastColumn="6"/>

                  <e:printArea firstRow="0" firstColumn="0"                    lastRow="50" lastColumn="6"/>
                                      
                  <e:mergeCells startColumn="0" startRow="1" endColumn="1" endRow="1"/>
                  <e:mergeCells startColumn="2" startRow="1" endColumn="3" endRow="1"/>
                  <e:mergeCells startColumn="0" startRow="3" endColumn="2" endRow="3"/>
                  <e:mergeCells startColumn="3" startRow="3" endColumn="6" endRow="3"/>

                  I am not getting fitToPages respond neither for the verticalFreeze.
                  Nor for the PrintTitles.
                  pleae help on this if there is any special condition for formating required.

                  I would be really thankful.
                  • 6. Re: excel formatting
                    ashishbyte
                    just to add ..for printTitles i am getting null pointer exception

                    <blockquote>
                    _ashish singh wrote on Dec 08, 2009 12:25:_<br/>

                    I found different issue with excel formatting.

                    I want to do print-tile for the excel sheet.

                    while using like this .....

                    <e:worksheet value="#"
                              var="summary"     name="Summary" startRow="4" orientation="landscape" fitToPages="true"
                         verticalFreeze="4">
                    <e:printTitles firstRow="2" firstColumn="0"
                                         lastRow="2" lastColumn="3"/>
                    <e:printTitles firstRow="4" firstColumn="0"
                                         lastRow="4" lastColumn="6"/>

                    <e:printArea firstRow="0" firstColumn="0"                    lastRow="50" lastColumn="6"/>
                                        
                    <e:mergeCells startColumn="0" startRow="1" endColumn="1" endRow="1"/>
                    <e:mergeCells startColumn="2" startRow="1" endColumn="3" endRow="1"/>
                    <e:mergeCells startColumn="0" startRow="3" endColumn="2" endRow="3"/>
                    <e:mergeCells startColumn="3" startRow="3" endColumn="6" endRow="3"/>

                    I am not getting fitToPages respond neither for the verticalFreeze.
                    Nor for the PrintTitles.
                    pleae help on this if there is any special condition for formating required.

                    I would be really thankful.
                    </blockquote>

                    Click HELP for text formatting instructions. Then edit this text and check the preview.
                    • 7. Re: excel formatting
                      nickarls

                      the value="#" in worksheet seems wrong to me. You should bind to the data there

                      • 8. Re: excel formatting
                        rancidvess
                        You should specify the default width property of the column in the parent worksheet.
                        You can reffer to the manual for more details but as I remember it was something like
                        [num_characters]*256. This worked in my case.
                        • 9. Re: excel formatting
                          nickarls

                          Yes JExcelAPI uses the Excel units of measurement that is a bit obscure, if I recall correctly even that formula was an approximation so you have to trial-and-error to get there.

                          • 10. Re: excel formatting
                            ashishbyte
                            Hi Karlson,

                            i appreciate for your reply.
                            but i wanted to know about the issues which i am facing with such as

                            1)fitToPages="true"  (not working for me)
                            2)verticalFreeze="4"  (not working for me)
                            3)printTitles (getting NULL pointer exception)


                            printArea working fine for me.

                            i have also refered to the doc.
                            If you have something to add on this please do so or any other approach which i can see ,please let me know.
                            • 11. Re: excel formatting
                              nickarls

                              Post the full xhtml page and a backing bean that provides dummy data for it demonstrating the issues and the full stack trace, please.

                              • 12. Re: excel formatting
                                cknaap

                                Although this is an old post, there aren't many about the same problem (printTitles NullPointerException), so I'd like to provide an answer.

                                 

                                The reference manual is wrong, you should use 'firstCol' and 'lastCol' on printTitles, instead of the full nouns 'firstColumn' and 'lastColumn'.

                                 

                                So you get this:

                                 

                                <e:worksheet name="mytestsheet" value="#{listOfTestObjects}" var="testObject" startRow="0" startColumn="0">

                                     <e:printTitles firstRow="0" lastRow="1" firstCol="0" lastCol="10"/>

                                     <e:cell .... />

                                </e:worksheet>