ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

create XML from RPG read as EXCEL

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #31
    Re: create XML from RPG read as EXCEL

    that code is just the heading to the XML

    to get the fields in a file you need this--or-- something like it
    ( i made this a procedure so it only needs to be written once)
    I would recommend you do the same.

    Code:
          *--------------------------------------------------------
          * PROGRAM - MSC52
          * PURPOSE - Generic send email attachement program
          * WRITTEN - january 17th 2008
          * AUTHOR  - jamie flanary
    
          * PROGRAM DESCRIPTION
          *   Generic send email attachment program
          *
          * INDICATOR USAGE
          *--------------------------------------------------------
          *
          * To Compile:
          *    CRTSQLRPGI OBJ(JAMIELIB/MSC52) SRCFILE(JAMIELIB/SOURCE)
          *    SRCMBR(MSC52) OBJTYPE(*MODULE)
          *
          *    CRTPGM PGM(lbiobj/MSC52)
          *           MODULE(lbiobj/MSC52)
          *           BNDSRVPGM(QTCP/QTMMSNDM)
          *
          *
         H BNDDIR('QC2LE':'BASE64') OPTION(*SRCSTMT:*NOSHOWCPY)
    
         fqaokl02a  if   e           k disk    prefix(q_)
         fqatmsmtpa if   e           k disk    prefix(q_)
    
           //
           //  *entry plist
           //
    
         d MSC52           pr
         d  intype                        3
         d  intoname                     35
         d  intoaddress                  35
         d  insubject                    50
         d  inlibFile                    21
         d  inattachname                 35
         d  inbody                     3000    varying
         d  inspoolf                     10
    
         d MSC52           pi
         d  intype                        3
         d  intoname                     35
         d  intoaddress                  35
         d  insubject                    50
         d  inlibFile                    21
         d  inattachname                 35
         d  inbody                     3000    varying
         d  inspoolf                     10
    
         D AR              S              1    DIM(9999)
         D AKEY            S             10    DIM(9999)
         D AK#             S              2  0 DIM(9999)
         D J               S              2  0
         D JJ              S              2  0
         D I               S              7  0
         D S               S              7  0
         D RelRecNbr       S              4  0
         D XX              S              7  0
         D I1              S              1
         D I2              S              2
         D pFileLib        S             20
         D FileLib         s             20    inz(*blanks)
         d headerstring    s           3000    varying
         D InputLib        s             10    inz(*blanks)
         D InputName       s             10    inz(*blanks)
         D InFormat        S             10
         D EntryFmt        S             10    inz('*FIRST')
         D NbrofField      S             04  0
         D OutField        S             10
         D OutSize         S             15  5
         D OutDecimal      S             01
         D OutType2        S             01
          *
         D GENDS           DS
         D  OffsetHdr            117    120B 0
         D  SizeHeader           121    124B 0
         D  OffsetList           125    128B 0
         D  NbrInList            133    136B 0
         D  SizeEntry            137    140B 0
         D HeaderDs        DS
         D  OutFileNam             1     10
         D  OutLibName            11     20
         D  OutType               21     25
         D  OutFormat             31     40
         D  RecordLen             41     44B 0
         D InputDs         DS
         D  UserSpace              1     20
         D  SpaceName              1     10
         D  SpaceLib              11     20
         D  InpFileLib            29     48
         D  InpFFilNam            29     38
         D  InpFFilLib            39     48
         D  InpRcdFmt             49     58
         D ListDs          DS
         D  FieldName              1     10
         D  FieldType             11     11
         D  BufferOut             13     16B 0
         D  FieldLen              21     24B 0
         D  Digits                25     28B 0
         D  Decimals              29     32B 0
         D  FieldDesc             33     82
         D ErrorDs         DS                  INZ
         D  BytesPrv               1      4B 0
         D  BytesAvl               5      8B 0
         D  MessageId              9     15
         D  ERR###                16     16
         D  MessageDta            17    116
         D ReceiveVr2      S            100
         D ReceiveVar      DS          4096
         D  NbrOfFmts             62     63B 0
         D  DBFileOff            317    320B 0
         D FindSelDs       DS           150
         D  NbrOfKeys            117    118B 0
         D  KeyOffset            136    139B 0
         D KeyDataDs       DS
         D  DependKey              1     10
         D                 DS
         D  StartPosit             1      4B 0
         D  StartLen               5      8B 0
         D  SpaceLen               9     12B 0
         D  ReceiveLen            13     16B 0
         D  MessageKey            17     20B 0
         D  MsgDtaLen             21     24B 0
         D  MsgQueNbr             25     28B 0
         DGenSpcPtr                        *
         DLstSpcPtr                        *
         DHdrPtr                           *
          //
          // Program Info
          //
    
         d                SDS
         d  @PGM                 001    010
         d  @PARMS               037    039  0
         d  @MSGDTA               91    170
         d  @MSGID               171    174
         d  @JOB                 244    253
         d  @USER                254    263
         d  @JOB#                264    269  0
    
          /copy qprcsrc,attach_cp
    
          // start of sendmail copybook
         d QtmmSendMail    PR                  ExtProc('QtmmSendMail')
         d   FileName                   255A   const options(*varsize)
         d   FileNameLen                 10I 0 const
         d   MsgFrom                    256A   const options(*varsize)
         d   MsgFromLen                  10I 0 const
         d   RecipBuf                          likeds(ADDTO0100)
         d                                     dim(32767)
         d                                     options(*varsize)
         d   NumRecips                   10I 0 const
         d   ErrorCode                 8000A   options(*varsize)
    
         d ADDTO0100       ds                  qualified
         d                                     based(Template)
         d   NextOffset                  10I 0
         d   AddrLen                     10I 0
         d   AddrFormat                   8A
         d   DistType                    10I 0
         d   Reserved                    10I 0
         d   SmtpAddr                   256A
    
         d ADDR_NORMAL     C                   CONST(0)
         d ADDR_CC         C                   CONST(1)
         d ADDR_BCC        C                   CONST(2)
    
          // end of sendmail copybook
    
          /copy qpgmsrc,base64_h
    
         d QCMDEXC         PR                  ExtPgm('QCMDEXC')
         d   cmd                      32702A   const options(*varsize)
         d   len                         15P 5 const
    
         d tmpnam          PR              *   extproc('_C_IFS_tmpnam')
         d   string                      39A   options(*omit)
    
         d MailDate        PR            31A
    
         d CR              c                   x'0d'
         d CRLF            c                   x'0d25'
    
         d count           s              4  0
         d fromName        s            100A   varying
         d fromAddr        s            300A   varying
         d length          s              4  0
         d lines           s              4  0
         d toName          s            100A   varying
         d toAddr          s            300A   varying
         d subject         s             80A   varying
         d str             s              4  0
         d attFile         s             21A   varying
         d attName         s             50A   varying
    
         d filename        s             50A   varying
         d tempAttach      s             50A   varying
         d att             s             10I 0
         d fd              s             10I 0
         d SQ              s              1    inz('''')
         d header          s           2000A   varying
         d body            s            500A   varying
         d cmd             s            500A   varying
         d boundary        s             78A   varying
         d x               s             10I 0
         d data            s             54A
         d encData         s             74A
         d encLen          s             10i 0
         d len             s             10i 0
    
         d recip           ds                  likeds(ADDTO0100)
         d                                     dim(1)
         d NullError       ds
         d   BytesProv                   10I 0 inz(0)
         d   BytesAvail                  10I 0 inz(0)
         d wait            s              1A
    
          /free
    
              //
              // intype
              // intoname
              // intoaddress
              // insubject
              // inlibFile
              // inattachname
              //
    
              fromName = %trim(@user);
    
              // use the from userid to get the email address
              clear FromAddr;
                   chain (@user) QAOKL02A;
                   if %found(QAOKL02A);
                    chain (q_WOS1DDEN) qtmsmpr;
                    if %found;
                     fromAddr = %trim(Q_smtpuid) + '@';
                     if %subst(Q_DOMROUTE:1:5) = 'MAIL.';
                      fromAddr = %trim(fromAddr) + %trim(%subst(Q_DOMROUTE:6:100));
                     else;
                      fromAddr = %trim(fromAddr) + %trim(Q_DOMROUTE);
                     endif;
                    endif;
                   endif;
    
              toName   = %trim(intoname);
              toAddr   = %trim(intoaddress);
              subject  = %trim(insubject);
              attFile  = %trim(inlibfile);
    
    
              // ------------------------------------------
              //  Convert the PF to a format that PCs like
              //  a Comma Separated Values (CSV) file
              // ------------------------------------------
    
    
              select;
               when intype = 'CSV';
               attname  = %trim(inattachname) + '.csv';
    
    
                exsr $getfields;
    
    
                tempAttach = %str(tmpnam(*omit));
                unlink(tempAttach);
                // headings first
                cmd = 'CPYTOIMPF FROMFILE(QTEMP/temphead)  '
                    +           'TOSTMF(''' + tempAttach +''') '
                    +           'MBROPT(*REPLACE)  DTAFMT(*FIXED) '
                    +           'STMFCODPAG(*PCASCII) '
                    +           'RCDDLM(*CRLF)';
                QCMDEXC( cmd : %len(cmd));
    
                cmd = 'CPYTOIMPF FROMFILE(' + attFile + ') '
                    +           'TOSTMF(''' + tempAttach +''') '
                    +           'MBROPT(*ADD) '
                    +           'STMFCODPAG(*PCASCII) '
                    +           'RCDDLM(*CRLF)';
    
              when intype = 'PDF';
                attname  = %trim(inattachname) + '.pdf';
                tempAttach = '/home/';
    
                    //
                    //  CVTSPLF FROMFILE(@NORTHAP) TOSTMF(CRACK.PDF)
                    //  TODIR('/home/jamie') SPLNBR(*LAST) TOFMT(*PDF)
                    //  STMFOPT(*REPLACE)
                    //
                    //
    
               cmd = 'CVTSPLF FROMFILE('   +
                     %trim(inspoolf ) + ')' + ' TOSTMF(' +
                     %trim(inattachname) + '.pdf) ' +
                     'TODIR(' + SQ + %trim(tempAttach) + SQ + ') ' +
                     'SPLNBR( *LAST) '     +
                     'TOFMT(*PDF) STMFOPT(*REPLACE)';
                tempAttach = '/home/' + %trim(inattachname) + '.pdf';
    
    
             endsl;
    
             QCMDEXC( cmd : %len(cmd));
    
    
              // ------------------------------------------
              // create a temporary file in the IFS.
              // mark that file as ccsid 819 (ISO 8859-1 ASCII)
              // ------------------------------------------
    
              filename = %str(tmpnam(*omit));
              unlink(filename);
    
              fd = open( filename
                       : O_CREAT+O_EXCL+O_WRONLY+O_CCSID
                       : M_RDWR
                       : 819 );
              if (fd = -1);
              endif;
    
              // ------------------------------------------
              // close file & reopen in text mode so that
              // data will be automatically translated
              // ------------------------------------------
    
              callp close(fd);
              fd = open( filename : O_WRONLY + O_TEXTDATA );
              if (fd = -1);
              endif;
    
              // ------------------------------------------
              //  build an e-mail header in a variable
              // ------------------------------------------
              Boundary = '-----------------------';
    
              header =
               'From: ' + fromName + ' <' + fromAddr + '>' + CRLF
              +'To: '   + toName   + ' <' + toAddr   + '>' + CRLF
              +'Date: ' + maildate() + CRLF
              +'Subject: ' + subject + CRLF
              +'MIME-Version: 1.0' + CRLF
              +'Content-Type: multipart/mixed;'
              +   ' boundary="' + Boundary + '"' + CRLF
              + CRLF
              + 'Your mail reader doesn''t support MIME!' + CRLF
              + CRLF;
    
              callp write(fd: %addr(header)+2: %len(header));
    
              // ------------------------------------------
              //  Insert the headers for the text part
              // ------------------------------------------
    
              body =
               '--' + boundary + CRLF
              +'Content-type: text/plain' + CRLF
              + CRLF;
              callp write(fd: %addr(body)+2: %len(body));
    
              // ------------------------------------------
              //  Set up the body from passed in data
              // ------------------------------------------
    
              lines = %len(%trim(inbody))/100;
              if %rem(%len(%trim(inbody)):100) > *zeros;
               lines +=1;
              endif;
    
              str = 1;
              for count = 1 to lines;
               length = ( %len(%trim(inbody)) - str)+1;
               if length > 100;
                length = 100;
               endif;
               body = %subst(inbody:str:length) + CRLF;
               callp write(fd: %addr(body)+2: %len(body));
               str += length;
              endfor;
    
              // ------------------------------------------
              //  Insert the headers for the CSV file
              // ------------------------------------------
    
              body =
               '--' + boundary + CRLF
              +'Content-Type: text/csv; name="' + attname + '"' + CRLF
              +'Content-Transfer-Encoding: base64' + CRLF
              +'Content-Disposition: attachment;'
              +    ' filename="' + attname + '"' + CRLF
              + CRLF;
              callp write(fd: %addr(body)+2: %len(body));
    
    
              // ------------------------------------------
              //  Read the attachment file, and base64
              //  encode it.  Write the results to the
              //  e-mail message.
              // ------------------------------------------
    
              att = open( tempAttach: O_RDONLY );
              if (att = -1);
              endif;
              unlink(tempAttach);
    
              dow '1';
                 len = read(att: %addr(data): %size(data));
                 if (len < 1);
                    leave;
                 endif;
    
                 enclen = base64_encode( %addr(data)
                                       : len
                                       : %addr(encdata)
                                       : %size(encdata)-2 );
    
                 %subst(encdata:enclen+1) = CRLF;
                 callp write(fd: %addr(encdata): enclen+2);
              enddo;
    
              callp close(att);
    
              // ------------------------------------------
              //  Finish the message & close the file
              // ------------------------------------------
              body = CRLF + '--' + boundary + '--' + CRLF;
              callp write(fd: %addr(body)+2: %len(body));
              callp close(fd);
    
              // ------------------------------------------
              //  Use the QtmmSendMail() API to send the
              //  IFS file via SMTP
              // ------------------------------------------
    
              recip(1).NextOffset = %size(ADDTO0100);
              recip(1).AddrFormat = 'ADDR0100';
              recip(1).DistType   = ADDR_NORMAL;
              recip(1).Reserved   = 0;
              recip(1).SmtpAddr   = toAddr;
              recip(1).AddrLen    = %len(toAddr);
    
              QtmmSendMail( FileName
                          : %len(FileName)
                          : fromAddr
                          : %len(fromAddr)
                          : recip
                          : %elem(recip)
                          : NullError );
    
              *inlr = *on;
    
          /end-free
          *---------------------------------------------------
          * $getfields - get all the field names
          *---------------------------------------------------
         c     $getfields    begsr
          *
         c                   eval      SpaceName  = 'FFDSPC'
         c                   eval      SpaceLib   = 'QTEMP'
          *
          *  QTEMP/PCH49EXP
          *
         c                   eval      InpFFilNam = %subst(inlibFile :
         c                             %scan('/': inlibfile)+1)
         c                   eval      InpFFilLib = %subst(inlibFile : 1:
         c                             %scan('/': inlibfile)-1)
    
         c                   eval      filelib = InpFFilNam + InpFFilLib
          *
          * Create the user space
          *
         c                   call      'QUSCRTUS'
         c                   parm                    UserSpace
         c                   parm      *BLANKS       SpaceAttr        10
         c                   parm      4096          SpaceLen
         c                   parm      *BLANKS       Spaceval          1
         c                   parm      '*CHANGE'     SpaceAuth        10
         c                   parm      *BLANKS       SpaceText        50
         c                   parm      '*YES'        SpaceRepl        10
         c                   parm                    ErrorDs
          *
          * Attemp to retrieve object description
          *
         c                   call(e)   'QUSROBJD'
         c                   parm                    ReceiveVr2
         c                   parm      100           ReceiveLen
         c                   parm      'OBJD0100'    FileFormat        8
         c                   parm                    FileLib
         c                   parm      '*FILE'       ObjectType       10
         c                   parm                    ErrorDs
          *
         c                   if        not%error
          *
          *  List fields to user space
          *
         c                   call      'QUSLFLD'
         c                   parm                    UserSpace
         c                   parm      'FLDL0100'    ListFormat        8
         c                   parm                    InpFileLib
         c                   parm      EntryFmt      InpRcdFmt
         c                   parm      '1'           OverRide          1
         c                   eval      StartPosit = 1
         c                   eval      StartLen = 140
          *
         c                   call      'QUSRTVUS'
         c                   parm                    UserSpace
         c                   parm                    StartPosit
         c                   parm                    StartLen
         c                   parm                    GENDS
         c                   eval      StartPosit = OffsetHdr + 1
         c                   eval      StartLen = SizeHeader
          *
         c                   call      'QUSRTVUS'
         c                   parm                    UserSpace
         c                   parm                    StartPosit
         c                   parm                    StartLen
         c                   parm                    HeaderDs
          *
         c                   eval      StartPosit = OffsetList + 1
         c                   eval      StartLen = SizeEntry
         c                   eval      SpaceName  = 'FFDSPC'
         c                   eval      SpaceLib   = 'QTEMP'
          *
          * read thru fields to build header
          *
         c                   clear                   headerstring
         c                   do        NbrInList     NbrOfField
         c                   call      'QUSRTVUS'
         c                   parm                    UserSpace
         c                   parm                    StartPosit
         c                   parm                    StartLen
         c                   parm                    ListDs
    
         c                   if        headerstring = *blanks
         c                   eval      headerstring += '"' + %trim(fielddesc) + '"'
         c                   else
         c                   eval      headerstring += ',' + '"' + %trim(fielddesc)
         c                             + '"'
         c                   endif
         c                   eval      StartPosit = StartPosit + SizeEntry
         c                   enddo
          *
          *  create the file with headers first......
          *
         c                   eval      headerstring += ',""'
          *
    
         c/exec sql
         c+ drop table qtemp/temphead
         c/End-Exec
         c/exec sql
         c+ create table qtemp/temphead
         c+ (data  char(512) )
         c/End-Exec
         c/exec sql
         c+ insert into qtemp/temphead VALUES(
         c+                                   : headerstring
         c+                                                   )
         c/end-exec
    
         c                   endif
         c                   endsr
          *---------------------------------------------------
    
          *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          *  MailDate():  Returns the current date, formatted for use
          *               in an e-mail message.
          *
          *     For example:  'Sat, 23 Oct 2004 14:42:06 -0500'
          *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
         P MailDate        B
         D MailDate        PI            31A
    
         D CEELOCT         PR                  opdesc
         D   Lilian                      10I 0
         D   Seconds                      8F
         D   Gregorian                   23A
         D   fc                          12A   options(*omit)
    
         D CEEUTCO         PR                  opdesc
         D   Hours                       10I 0
         D   Minutes                     10I 0
         D   Seconds                      8F
         D   fc                          12A   options(*omit)
    
         D CEEDATM         PR                  opdesc
         D   input_secs                   8F   const
         D   date_format                 80A   const options(*varsize)
         D   char_date                   80A   options(*varsize)
         D   feedback                    12A   options(*omit)
    
         D rfc2822         c                   'Www, DD Mmm YYYY HH:MI:SS'
         D junk1           s              8F
         D junk2           s             10I 0
         D junk3           s             23A
         D hours           s             10I 0
         D mins            s             10I 0
         D tz_hours        s              2P 0
         D tz_mins         s              2P 0
         D tz              s              5A   varying
         D CurTime         s              8F
         D Temp            s             25A
    
          /free
    
             //
             //  Calculate the Timezone in format '+0000', for example
             //    CST should show up as '-0600'
             //
    
             CEEUTCO(hours: mins: junk1: *omit);
             tz_hours = %abs(hours);
             tz_mins = mins;
    
             if (hours < 0);
                tz = '-';
             else;
                tz = '+';
             endif;
    
             tz += %editc(tz_hours:'X') + %editc(tz_mins:'X');
    
             //
             //  Get the current time and convert it to the format
             //    specified for e-mail in RFC 2822
             //
    
             CEELOCT(junk2: CurTime: junk3: *omit);
             CEEDATM(CurTime: rfc2822: Temp: *omit);
    
             return Temp + ' ' + tz;
    
          /end-free
         P                 E
    Attached Files
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #32
      Re: create XML from RPG read as EXCEL

      Hi emily:

      I realize what I'm about to suggest is considered "old school"
      but if you are able to do a snddst from your system....try the following:
      Code:
       /* move xml file from ifs to a folder                  */
      CPY  OBJ('/home/myxml.xml') +                        
         TOOBJ('/qdls/myflr/myxml.xml') REPLACE(*YES)    
      /* delete ifs object                                         */
      RMVLNK OBJLNK('/home/myxml.xml')                      
      SNDDST TYPE(*DOC) +                                    
             TOINTNET((myuserid@mydomain.com)) +        
             DSTD('customer service weekly analysis') +      
             MSG('Customer Service Weekly Analysis Reports') 
             DOC(myxml.XML) FLR(myflr)
      By using the old snddst you can avoid converting the xml file to base64 and inserting in a mime format.

      If you do not have smtp there is a cheat sheet located here:


      Best of luck
      GLS
      The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

      Comment


      • #33
        Re: create XML from RPG read as EXCEL

        Thanks guys I'll give them both a try!!! I greatly appreciate it.
        Stand up for what you beleive in ...... even if you are standing alone

        Comment


        • #34
          Re: create XML from RPG read as EXCEL

          Here's another brand new open source solution that targets this issue. It promises to deliver formatted excel spreadsheets without excessive programming time and messing around with the XML code.





          XLSCGI is the utility I worked out to overcome problems with generating Excel spreadsheets from CGI programs.

          This utility:
          - Allows you to define the XML skeleton spreadsheet you would like to have without using Excel, to define your output variables, to define your CGIDEV2 sections, and to save the skeleton into an IFS directory of your choice.
          Provides ILE-RPG subprocedures to provide your output variables with the format expected by Excel
          - Provides an automated procedure to convert the output buffer to a temporary .xls stream file and to send that file to the browser.
          As a result, you may have in a few minutes what you would have obtained (perhaps) after hours or days of frustrating attempts.

          Comment


          • #35
            Re: create XML from RPG read as EXCEL

            If what you're really after is a proper spreadsheet output file that requires no transformations after the fact, then check this out: http://www.brainblox.co.za/spreadsheet.php

            Comment


            • #36
              Re: create XML from RPG read as EXCEL

              Ok, here is a question for you. I want to move some of this functionality into a service program with calls like "AddWorksheet", "AddCell" etc etc etc...


              But I would like to add formatting options to each of these calls, which means I would like to tie the formatting to a particular cell in the spreadsheet instead of to a style sheet.

              What is the format for specifying the font, color, background, border, etc. etc. to a particular cell? (If it's possible.)

              Comment


              • #37
                Re: create XML from RPG read as EXCEL

                Hi Soup_Dog:

                If you generate one of these/change the color in a cell/ saveit/ then open it in wordpad you will see that the style sheet for that cell only is different than all the others in that column.

                Excel may actually change the names of all the style sheets when you save the change.

                Hope that helps
                GLS

                By the way.....If you get this going I'd really like to see it.
                The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

                Comment


                • #38
                  Re: create XML from RPG read as EXCEL

                  Well, according to this Microsoft article, there is at least a way to specify the font and bold, underline, etc. (but not borders).

                  Unfortunately it doesn't seem to work in actual practice. LOL

                  http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

                  Take a look at the <ss: Data> tag. Supposedly you can specify the <B>,<I>,<U>,<Font> tags within the data tag. No luck here though.

                  I guess I will just need to figure out an elegant solution for the styles.

                  Comment


                  • #39
                    Re: create XML from RPG read as EXCEL

                    could you make the style sheet a copy book in the service program?
                    All my answers were extracted from the "Big Dummy's Guide to the As400"
                    and I take no responsibility for any of them.

                    www.code400.com

                    Comment


                    • #40
                      Re: create XML from RPG read as EXCEL

                      yeah, I think I'm going to need to do something like that. the only problem is I need to come up with any possible combination of bold, underline, italics, colors, fonts, etc that the user could possibly want.


                      Which could be big! LOL

                      Comment


                      • #41
                        Re: create XML from RPG read as EXCEL

                        Originally posted by soup_dog View Post
                        Ok, here is a question for you. I want to move some of this functionality into a service program with calls like "AddWorksheet", "AddCell" etc etc etc...


                        But I would like to add formatting options to each of these calls, which means I would like to tie the formatting to a particular cell in the spreadsheet instead of to a style sheet.

                        What is the format for specifying the font, color, background, border, etc. etc. to a particular cell? (If it's possible.)
                        Excel Spreadsheets from RPG with POI
                        "Time passes, but sometimes it beats the <crap> out of you as it goes."

                        Comment


                        • #42
                          Re: create XML from RPG read as EXCEL

                          Indeed, for the fancy bits it would probably be easier to work through POI. I purposely excluded formatting from my rpg to excel thingy 'cos that overhead is what I hate about using POI.

                          Must confess I've not yet personally matured to the point where it is necessary to format cells and all and all. If the data is there then the users are free do what they will after the fact. I find it difficult to sell this idea though...

                          In my estimation it would take a user less time to create stylesheets than to continuously have developers farting about in the engine room because they've specified exactly the wrong shade of red on a negative number in the range 1.23 to 6.990004.

                          I guess I'm just not user-centric enough.

                          Comment


                          • #43
                            Re: create XML from RPG read as EXCEL

                            Originally posted by vernond View Post
                            Indeed, for the fancy bits it would probably be easier to work through POI. I purposely excluded formatting from my rpg to excel thingy 'cos that overhead is what I hate about using POI.

                            Must confess I've not yet personally matured to the point where it is necessary to format cells and all and all. If the data is there then the users are free do what they will after the fact. I find it difficult to sell this idea though...

                            In my estimation it would take a user less time to create stylesheets than to continuously have developers farting about in the engine room because they've specified exactly the wrong shade of red on a negative number in the range 1.23 to 6.990004.

                            I guess I'm just not user-centric enough.

                            LOL! I understand! Really I do! The only reason I am headed this way is because our largest customer (whose name rhymes with MalWart) wants us to send our quotes to them using an Excel spreadsheet that they have specifically designed and contains special formatting.

                            I figured if I need to do it for them, I might as well provide the tools for other oppourtunities down the road.

                            Comment


                            • #44
                              Re: create XML from RPG read as EXCEL

                              for a RPG native (non-Java) approach...

                              Comment


                              • #45
                                Re: create XML from RPG read as EXCEL

                                Originally posted by vernond
                                In my estimation it would take a user less time to create stylesheets than to continuously have developers farting about in the engine room because they've specified exactly the wrong shade of red on a negative number in the range 1.23 to 6.990004.
                                I agree with vernond... we let our users mostly do what the want with the spreadsheet.
                                Originally posted by soup_dog
                                The only reason I am headed this way is because XXXX wants us to send our quotes to them using an Excel spreadsheet that they have specifically designed and contains special formatting.
                                like Group as well. For this, we said "sod this for a laugh" and bought some software with which one can set up templates, workbooks, coloured cells, fonted cells, whatever the heart desires, and emails it as well. Best of all it doesn't cost an arm and a leg.
                                Regards

                                Kit
                                http://www.ecofitonline.com
                                DeskfIT - ChangefIT - XrefIT
                                ___________________________________
                                There are only 3 kinds of people -
                                Those that can count and those that can't.

                                Comment

                                Working...
                                X