RE: Poi lisking to other spreadsheets

["JK" <johnking@xxxxxxx>]

Darrell,

 Build the cell contents by cat'ing strings together like any other cell.
Set the cell type to "CELL_TYPE_FORMULA" (which is '2'). Omit the equals
sign from the front of the string. The following was largely swiped from
SKlement's excellent tutorial. Search the archives if you haven't already
read it from start to finish!

JK 

//============================================================
// Generate a column total formula in the cell.               
//============================================================
Begsr PutColumnFormula;                                       
                                                              
  // Column total (FORMULA) field (no '=' sign).              
  w_ColStr = %subst(w_Alpha:x_ColF+1:1);                      
  w_ColEnd = %subst(w_Alpha:x_ColF+1:1);                      
  valueChr = 'Sum(' +                                         
             %trim(w_ColStr) + %char(w_RowStrSheet+1) +       
             ':'+                                             
             %trim(w_ColEnd) + %char(w_RowEndSheet+1) +       
             ')';                                             
  hssf_formula( thisRow: x_ColF : %trim(valueChr) : StyleHdr);
                                                              
Endsr;                                                        

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
 *  hssf_formula(): Shortcut for inserting a new cell that contains 
 *        a formla into a given row of a sheet                      
 *                                                                  
 *     peRow = Row object that cell should be created in            
 *     peCol = column number of new cell                            
 * peFormula = formula to place in cell                             
 *   peStyle = cell style object to associate with cell             
 *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
P hssf_formula    B                   EXPORT                        
D hssf_formula    PI                                                
D   peRow                             like(HSSFRow)                 
D   peCol                        5I 0 value                         
D   peFormula                 1024A   varying const                 
D   peStyle                           like(HSSFCellStyle)           

D wwStr           s                   like(jString)                 
D wwCell          s                   like(HSSFCell)                
/free                                               
    wwCell = HSSFRow_createCell(peRow: peCol);       
    HSSFCell_setCellType(wwCell: CELL_TYPE_FORMULA); 
    wwStr = new_String(peFormula);                   
    HSSFCell_setCellFormula(wwCell: wwStr);          
    HSSFCell_setCellStyle(wwCell: peStyle);          
    hssf_freeLocalRef(wwStr);                        
    hssf_freeLocalRef(wwCell);                       
 /end-free                                           
P                 E                                  
                                                              

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of DLee@xxxxxxxx
> Sent: Wednesday, July 19, 2006 11:54 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Poi lisking to other spreadsheets
>
> Hi, I would like to create a formula which links to other spreadsheet
> cells in poi rpg, but I don't know how to get started.
>
> The formula in excel would look like this, but how to convert this to rpg
> is currently a mystery.  NAIC10, NAIC12, NAIC14, NAIC16 are the sheets I
> want to link to.
>
> =(NAIC10!O9+NAIC12!O9+NAIC14!O9+NAIC16!O9)
>
> appreciate any help.
>
> Darrell