Q: I've been using your HSSF tips to create Excel files. Now I have a problem. I'm trying to create a text cell that is approximately 800 characters long. I've turned text wrapping on, but I don't like where it wraps the text. Is there something I can do to tell it where the text should wrap?

For example, if I have the following text:

  "On Monday, we eat beans. On Tuesday we eat cauliflower."

I'd like Excel to format it as follows:

   On Monday, we eat beans.
   On Tuesday, we eat cauliflower.

A: You can do this in Excel by inserting a linefeed character wherever you'd like the word wrapping to occur. This does require that text wrapping be enabled by calling the setWrapText() method of the HSSFCellStyle class.

I have the following prototype defined for the setWrapText() method:

     D HSSFCellStyle_setWrapText...
     D                 PR                  EXTPROC(*JAVA
     D                                     :'org.apache.poi.hssf.-
     D                                     usermodel.HSSFCellStyle'
     D                                     :'setWrapText')
     D   wrapped                       N   value

In the RPG code where you set up your cell style, enable text wrapping for that style. For example, the following code creates a cell style where the text is left-aligned and text wrapping is enabled:

         Text = HSSFWorkbook_createCellStyle(book);
         HSSFCellStyle_setAlignment(Text: ALIGN_LEFT);
         HSSFCellStyle_setWrapText(Text: *ON);

Now that this has been done, you can insert linefeed characters into your string to tell HSSF where the data should be wrapped. In EBCDIC, the x'25' character represents a line feed, so the following code should do the job:

            hssf_text( row: 1 : 'On Monday, we eat beans.' + x'25' 
                              + 'On Tuesday, we eat cauliflower.' + x'25'
                              : Text);

This question references articles that were published in previous issues of Club Tech iSeries Programming tips. You can read those articles at the following links:
http://www.iseriesnetwork.com/article.cfm?id=19278
http://www.iseriesnetwork.com/article.cfm?id=19337
http://www.iseriesnetwork.com/article.cfm?id=19592
http://www.iseriesnetwork.com/article.cfm?id=19610