Loading ...
Sorry, an error occurred while loading the content.

RE: [Easy400Group] Re: Excel Workbook with tabs - code example

Expand Messages
  • Augusto Rios
    Paul, I appreciate a lot your response...... I will try this and let you know. Thanks much, Augusto Rios To: Easy400Group@yahoogroups.comFrom:
    Message 1 of 2 , Apr 1, 2008
    • 0 Attachment

      Paul,
      I appreciate a lot your response......
      I will try this and let you know.
      Thanks much,
      Augusto Rios




      To: Easy400Group@yahoogroups.com
      From: paul.decourcyellis@...
      Date: Tue, 1 Apr 2008 15:28:22 +1100
      Subject: [Easy400Group] Re: Excel Workbook with tabs - code example

      Hi Augusto,
      There is a bit of code attached here. But it's all very easy to read(I think).
      Remember i didn't invent this - quite  few people have been doing this for some time.
      Save your excel spreadhseet as XML
      Take the save as option and choose "XML SpreadSheet". This is important because this generates the xml source.
      Use excel as a design tool to get all the formatting and colours correct and place dummy data as place holders in your excel cells.
       
      Edit xml source
      Using editor of choice put your section delimiters in as you would for CGIDEV2 HTML and replace the hardcoded data with variable substitutes.
      I have highlighted the delimiters in red I hope this formatting comes out in the post. Either way just look for <as400> amd /% %/.
       
      example : header using section delimiter "topofdoc"

      <as400>topofdoc
      <?xml version="1.0"?>
      <?mso-application progid="Excel.Sheet"?>
      <!--  ************ ********* ********* ********* ********* ********* ********* ********* * -->
      <!--  *  Used by Program : GLEF02I                                               * -->
      <!--  *  This is an xml template.  The above program reads in this data          * -->
      <!--  *  substitutes the data base data into the variables  and then writes      * -->
      <!--  *  the ouput to the ifs.                                                   * -->
      <!--  *  A web page link to that IFS file will cause it to be loaded to the      * -->
      <!--  *  browser as an excel spreadsheet.                                        * -->
      <!--  *  Neat, Huh?                                                              * -->
      <!--  ************ ********* ********* ********* ********* ********* ********* ********* * -->
      <Workbook xmlns="urn:schemas- microsoft- com:office: spreadsheet"
       xmlns:o="urn:schemas- microsoft- com:office: office"
       xmlns:x="urn:schemas- microsoft- com:office: excel"
       xmlns:ss="urn:schemas- microsoft- com:office: spreadsheet"
       xmlns:html="http://www.w3. org/TR/REC- html40">
       <DocumentProperties xmlns="urn:schemas- microsoft- com:office: office">
        <Author>ferrero</Author>
        <LastAuthor>ferrero</LastAuthor>
        <Created>2008-03-05T05: 49:34Z</Created>
        <LastSaved>2008-03-05T05: 59:59Z</LastSaved>
        <Company>FERRERO Australia</Company>
        <Version>11.9999</Version>
       </DocumentProperties>
       <ExcelWorkbook xmlns="urn:schemas- microsoft- com:office: excel">
        <WindowHeight>10875</WindowHeight>
        <WindowWidth>14220</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
       </ExcelWorkbook>
      Variable substitution
      then use section delimiters for your headers and rows and variable dlimiters "/%"  for your actual data.
      in the example below we are putting out totals for credits and debits before we write out the actual detail data.
      <as400>header
         <Row ss:Index="4" ss:AutoFitHeight="0" ss:Height="15">
          <Cell ss:StyleID="s25"><Data ss:Type="String">Data Created /%rundate%/</Data></Cell>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s27"/>
          <Cell ss:StyleID="s26"><Data ss:Type="String">For period /%period%/,Year /%year%/</Data></Cell>
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="15">
          <Cell ss:StyleID="s25"/>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s27"/>
          <Cell ss:StyleID="s26"/>
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="15">
          <Cell ss:StyleID="s25"/>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s27"/>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s58"><Data ss:Type="String">Debit Total</Data></Cell>
          <Cell ss:StyleID="s59"><Data ss:Type="Number">/%totaldr%/</Data></Cell>
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="15">
          <Cell ss:StyleID="s25"/>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s27"/>
          <Cell ss:StyleID="s26"/>
          <Cell ss:StyleID="s58"><Data ss:Type="String">Credit Total</Data></Cell>
          <Cell ss:StyleID="s59"><Data ss:Type="Number">/%totalcr%/</Data></Cell>
         </Row> 
      Detail data
      Now here is where we write out the detail data row by row , just as we would for a HTML table report. Note we do some slightly different cell formatting depending on whether
      this is a credit or a debit. (Don;t worry too much about ss:type and ss:index - these are CSS-Like styles which are  created for you when you save the file as xml).
      The only rule you have to follow is keeping the correct number of cells in each row - a lot like HTML tables.

      <as400>datarow
         <Row>
          <Cell><Data ss:Type="Number">/%gblseq%/</Data></Cell>
          <Cell><Data ss:Type="String">/%gblbrand%/</Data></Cell>
          <Cell><Data ss:Type="String">/%gblclass%/</Data></Cell>
          <Cell><Data ss:Type="Number">/%gblsku%/</Data></Cell>
          <Cell><Data ss:Type="Number">/%gblaccount% /</Data></Cell>
      <as400>rowdr
          <Cell><Data ss:Type="Number">/%gbldramt%/</Data></Cell>
          <Cell ss:Index="8"><Data ss:Type="String">/%gbldesc%/</Data></Cell>
         </Row>
      <as400>rowcr
          <Cell ss:Index="7"><Data ss:Type="Number">/%gblcramt%/</Data></Cell>
          <Cell><Data ss:Type="String">/%gbldesc%/</Data></Cell>
         </Row>
      When finished save your new xml source to wherever you keep your HTMl templates.


      RPG source
       

      • load up your new saved xml source as if it was a HTML template
      • write out sections and variables
      • write out to ifs using wrthtmltostmf
      • create a link on your web page to that file(or email it , )



      // Ask the service program to load into core
                     // html member  GLB_LIST.XML
                      CLRHTMLBuffer( );  // clear the buffer
                      loadhtml();  
      //  issue start of XMl doc
                        wrtsection(' topofdoc' ); 
           some file IO here........ .
      // do each data row
                 updhtmlvar(' gblseq' : %char(gbl.bllnum) );
                 updhtmlvar(' gblbrand' : gbl.blus08);
                 updhtmlvar(' gblclass' : getGlBrand(gbl. blus08));
                 updhtmlvar(' gblsku' : gbl.blldes);
                 updhtmlvar(' gblaccount' : gbl.blus07);
                 wrtsection(' datarow') ;                                        
           etc, etc, etc
       //  writeTotals( );
                 wrtsection(' finish');
                 closeCursor( );
       //Send the response html buffer and exit
                 rc = WrtHtmlToStmf( %trimr(ifsExclpa th ):CodePage) ;
                *inlr = *on;
                 return;  
       
      Notes:
      The CodePage I use in WrthtmlToStmf is (819).
      Once again, all the xml stuff is created for you by excel - all you have to do is break it up into sections and put in your variables.
      Powered by CGIDEV2 :-)
       
                                       
      Paul deCourcy Ellis
      Application Support
      Ferrero Australia Pty Limited
      * Locked Bag 902, North Sydney, NSW, 2060
      ( (02) 9409 8451
      6 (02) 9409 8861
      Mob 0423196525

       
             Before you print this message, be sure that it is necessary.  The environment is in our hands!
       
      Notice: The information contained in this e-mail and/or documents which accompany it is confidential, may be privileged, proprietary and exempt from disclosure, and is intended for the exclusive use of the person or persons to whom it is addressed. If you are not the intended recipient or if you have received this message in error for any reason, please advise us immediately by reply e-mail and delete both the message and the copy thereof contained in reply. Thank you.



      Get in touch in an instant. Get Windows Live Messenger now.
    Your message has been successfully submitted and would be delivered to recipients shortly.