midrange.com code scratchpad
Name:
cpytoxlsf.py
Scriptlanguage:
Python
Tabwidth:
4
Date:
05/25/2012 05:45:40 pm
IP:
Logged
Description:
iSeries Python program to convert any arbitrary DDS physical file to Excel binary file. Requires third-party xlwt package.
Code:
  1. '''Copy data from a physical file to an Excel binary file in the IFS.
  2.  
  3. Written by John Yeung.  Last modified 2012-05-21.
  4.  
  5. Usage (from CL):
  6.     python233/python '/util/cpytoxlsf.py' parm(&pf &xls [&A1text &A2text ...])
  7.  
  8. (The above assumes this program is located in '/util', and that iSeries
  9. Python 2.3.3 is installed.  If you are at V5R3 or later, you should be using
  10. iSeries Python 2.7 instead.)
  11.  
  12. Some features/caveats:
  13.  
  14. -  Column headings come from the COLHDG values in the DDS.  Multiple
  15.     values for a single field are joined by spaces, not newlines.  For
  16.     any fields without a COLHDG, or with only blanks in the COLHDG (these
  17.     two situations are indistinguishable), the field name is used as the
  18.     heading (the TEXT keyword is not checked).  To specify a blank column
  19.     heading rather than the field name, use COLHDG('*BLANK').
  20. -  Column headings wrap and are displayed in bold.
  21. -  Each column is sized approximately according to its longest data,
  22.     assuming that the default font is Arial 10, unless a width is
  23.     specified in the field text (using 'width=<number>').  [For this
  24.     purpose, the length of numeric data is assumed to always include
  25.     commas and fixed decimal places.]
  26. -  Each column may be formatted using an Excel format string in the
  27.     field text (using 'format="<string>"').
  28. -  Character fields with no format string are set to Excel text format.
  29. -  Columns with a supported EDTCDE value but no format string are
  30.     formatted according to the edit code.
  31. -  Columns may specify 'zero=blank' anywhere in the field text to leave
  32.     a cell empty when its value is zero.  (This is different than using
  33.     a format string or edit code to hide zero values.  See the ISBLANK
  34.     and ISNUMBER functions in Excel.)
  35. -  Columns may be skipped entirely by specifying COLHDG('*SKIP')
  36. -  Numeric fields that are 8 digits long with no decimal places are
  37.     automatically converted to dates if they have a suitable edit word.
  38. -  Numeric fields that are 6 digits long with no decimal places are
  39.     automatically converted to times if they have a suitable edit word.
  40. -  Free-form data may be inserted at the top using additional parameters,
  41.     one parameter for each row.  The data will be in bold.  Any number of
  42.     parameters may be specified, up to the limits of the operating system.
  43.  
  44. The motivation for this program is to provide a tool for easy generation
  45. of formatted spreadsheets.
  46.  
  47. Nice-to-have features not yet implemented include general edit word
  48. support (not just for date detection), more available edit codes, more
  49. comprehensive date support, the ability to choose fonts, and automatic
  50. population of multiple sheets given multiple file members.
  51.  
  52. Also, it would be nice to wrap this in a command for even greater ease of
  53. use, including meaningful promptability.
  54. '''
  55.  
  56. import sys
  57. import re
  58. from os import system
  59. from datetime import date, time
  60.  
  61. # Third-party package available at <http://pypi.python.org/pypi/xlwt>
  62. import xlwt
  63. # If running iSeries Python 2.3.3, xlwt needs to be modified to work with
  64. # EBCDIC.  This is not required for iSeries Python 2.5 or 2.7.
  66. # Table of empirically determined character widths in Arial 10, the default
  67. # font used by xlwt.  Note that font rendering is somewhat dependent on the
  68. # configuration of the PC that is used to open the resulting file, so these
  69. # widths are not necessarily exact for other people's PCs.  Also note that
  70. # only characters which are different in width than '0' are needed here.
  71. charwidths = {
  72.     '0': 262.637,
  73.     'f': 146.015,
  74.     'i': 117.096,
  75.     'j': 88.178,
  76.     'k': 233.244,
  77.     'l': 88.178,
  78.     'm': 379.259,
  79.     'r': 175.407,
  80.     's': 233.244,
  81.     't': 117.096,
  82.     'v': 203.852,
  83.     'w': 321.422,
  84.     'x': 203.852,
  85.     'z': 233.244,
  86.     'A': 321.422,
  87.     'B': 321.422,
  88.     'C': 350.341,
  89.     'D': 350.341,
  90.     'E': 321.422,
  91.     'F': 291.556,
  92.     'G': 350.341,
  93.     'H': 321.422,
  94.     'I': 146.015,
  95.     'K': 321.422,
  96.     'M': 379.259,
  97.     'N': 321.422,
  98.     'O': 350.341,
  99.     'P': 321.422,
  100.     'Q': 350.341,
  101.     'R': 321.422,
  102.     'S': 321.422,
  103.     'U': 321.422,
  104.     'V': 321.422,
  105.     'W': 496.356,
  106.     'X': 321.422,
  107.     'Y': 321.422,
  108.     ' ': 146.015,
  109.     '!': 146.015,
  110.     '"': 175.407,
  111.     '%': 438.044,
  112.     '&': 321.422,
  113.     '\'': 88.178,
  114.     '(': 175.407,
  115.     ')': 175.407,
  116.     '*': 203.852,
  117.     '+': 291.556,
  118.     ',': 146.015,
  119.     '-': 175.407,
  120.     '.': 146.015,
  121.     '/': 146.015,
  122.     ':': 146.015,
  123.     ';': 146.015,
  124.     '<': 291.556,
  125.     '=': 291.556,
  126.     '>': 291.556,
  127.     '@': 496.356,
  128.     '[': 146.015,
  129.     '\\': 146.015,
  130.     ']': 146.015,
  131.     '^': 203.852,
  132.     '`': 175.407,
  133.     '{': 175.407,
  134.     '|': 146.015,
  135.     '}': 175.407,
  136.     '~': 291.556}
  137.  
  138. ezxf = xlwt.easyxf
  139.  
  140. # I have a custom SNDMSG wrapper that I use to receive immediate messages
  141. # from iSeries Python, but for basic use, simply printing the message works.
  142. # iSeries Python also comes with os400.sndmsg, but I have not been able to
  143. # get that to work.
  144. def sndmsg(msg):
  145.     print msg
  146.  
  147. def _integer_digits(n):
  148.     '''Return the number of digits in a positive integer'''
  149.     if n == 0:
  150.         return 1
  151.     digits = 0
  152.     while n:
  153.         digits += 1
  154.         n //= 10
  155.     return digits
  156.  
  157. def number_analysis(n, dp=0):
  158.     '''Return a 4-tuple of (digits, thousands, points, signs)'''
  159.     digits, thousands, points, signs = 0, 0, 0, 0
  160.     if n < 0:
  161.         signs = 1
  162.         n = -n
  163.     if dp > 0:
  164.         points = 1
  165.     if isinstance(n, float):
  166.         idigits = _integer_digits(int(n) + 1)
  167.     elif isinstance(n, (int, long)):
  168.         idigits = _integer_digits(n)
  169.     else:
  170.         return None
  171.     digits = idigits + dp
  172.     thousands = (idigits - 1) // 3
  173.     return digits, thousands, points, signs
  174.  
  175. def colwidth(n):
  176.     '''Translate human-readable units to BIFF column width units'''
  177.     if n <= 0:
  178.         return 0
  179.     if n <= 1:
  180.         return n * 456
  181.     return 200 + n * 256
  182.  
  183. def fitwidth(data, bold=False):
  184.     '''Try to autofit Arial 10'''
  185.     units = 220
  186.     for char in str(data):
  187.         if char in charwidths:
  188.             units += charwidths[char]
  189.         else:
  190.             units += charwidths['0']
  191.     if bold:
  192.         units *= 1.1
  193.     return max(units, 700) # Don't go smaller than a reported width of 2
  195. def numwidth(data, dp, use_commas=False):
  196.     '''Try to autofit a number in Arial 10'''
  197.     units = 220
  198.     digits, commas, points, signs = number_analysis(data, dp)
  199.     units += digits * charwidths['0']
  200.     if use_commas:
  201.         units += commas * charwidths[',']
  202.     units += points * charwidths['.']
  203.     units += signs * charwidths['-']
  204.     return max(units, 700) # Don't go smaller than a reported width of 2
  206. def datewidth():
  207.     return 220 + 8 * charwidths['0'] + 2 * charwidths['/']
  208.  
  209. def timewidth():
  210.     digits_width = 6 * charwidths['0']
  211.     separators_width = 2 * charwidths[':']
  212.     space_width = charwidths[' ']
  213.     am_pm_width = max(charwidths['A'], charwidths['P']) + charwidths['M']
  214.     return 220 + digits_width + separators_width + space_width + am_pm_width
  215.  
  216. def default_numformat(dp=0, use_commas=False):
  217.     '''Generate a style object for Excel fixed number format'''
  218.     integers, decimals = '0', ''
  219.     if use_commas:
  220.         integers = '#,##0'
  221.     if dp > 0:
  222.         decimals = '.' + '0' * dp
  223.     combined = integers + decimals
  224.     return ezxf(num_format_str=combined)
  225.  
  226. def editcode(code, dp=0):
  227.     '''Generate a style object corresponding to an edit code'''
  228.     code = code.lower()
  229.     if len(code) != 1 or code not in ('1234nopq'):
  230.         return default_numformat(dp)
  231.     sign, integers, decimals, zero = '', '#', '', ''
  232.     if code in 'nopq':
  233.         sign = '-'
  234.     if code in '12no':
  235.         integers = '#,###'
  236.     if dp > 0:
  237.         decimals = '.' + '0' * dp
  238.     positive = integers + decimals
  239.     negative = sign + positive
  240.     if code in '13np':
  241.         zero = positive[:-1] + '0'
  242.     return ezxf(num_format_str=';'.join((positive, negative, zero)))
  243.  
  244. def is_numeric_date(size, editword):
  245.     return size == (8, 0) and editword in ("'    -  -  '", "'    /  /  '")
  246.  
  247. def is_numeric_time(size, editword):
  248.     return size == (6, 0) and editword in ("'  .  .  '", "'  :  :  '")
  249.  
  250. # Check parameters
  251. parameters = len(sys.argv) - 1
  252. if parameters < 2:
  253.     sndmsg('Program needs at least 2 parameters; received %d.' % parameters)
  254.     sys.exit(2)
  255. pf = sys.argv[1].split('/')
  256. if len(pf) == 1:
  257.     libname = '*LIBL'
  258.     filename = pf[0].upper()
  259. elif len(pf) == 2:
  260.     libname = pf[0].upper()
  261.     filename = pf[1].upper()
  262. else:
  263.     sndmsg('Could not parse file name.')
  264.     sys.exit(2)
  265. sndmsg('Parameters checked.')
  266.  
  267. infile = File400(filename, 'r', lib=libname)
  268. if libname.startswith('*'):
  269.     libname = infile.libName()
  270. sndmsg('Opened ' + libname + '/' + filename + ' for reading.')
  271.  
  272. # Get column headings and formatting information from the DDS
  273. fieldlist = []
  274. headings = {}
  275. numformats = {}
  276. dateflags = {}
  277. timeflags = {}
  278. commaflags = {}
  279. decplaces = {}
  280. colwidths = {}
  281. blankzeros = {}
  282. template = "dspffd %s/%s output(*outfile) outfile(qtemp/dspffdpf)"
  283. system(template % (libname, filename))
  284. ddsfile = File400('DSPFFDPF', 'r', lib='QTEMP')
  285.  
  286. ddsfile.posf()
  287. while not ddsfile.readn():
  288.     fieldname = ddsfile['WHFLDE']
  289.     fieldtext = ddsfile['WHFTXT']
  290.  
  291.     # Set heading
  292.     headertuple = (ddsfile['WHCHD1'], ddsfile['WHCHD2'], ddsfile['WHCHD3'])
  293.     text = ' '.join(headertuple).strip()
  294.     if not text:
  295.         text = fieldname
  296.     elif text.upper() in ('*BLANK', '*BLANKS'):
  297.         text = ''
  298.     elif text.upper() == '*SKIP':
  299.         continue
  300.     fieldlist.append(fieldname)
  301.     headings[fieldname] = text
  302.  
  303.     # Get field size and type
  304.     if ddsfile['WHFLDD']:
  305.         fieldsize = (ddsfile['WHFLDD'], ddsfile['WHFLDP'])
  306.         decplaces[fieldname] = fieldsize[1]
  307.         numeric = True
  308.     else:
  309.         fieldsize = ddsfile['WHFLDB']
  310.         numeric = False
  311.  
  312.     # Look for number format string
  313.     match = re.search(r'format="(.*)"', fieldtext, re.IGNORECASE)
  314.     if match:
  315.         numformat = ezxf(num_format_str=match.group(1))
  316.     elif numeric:
  317.         numformat = editcode(ddsfile['WHECDE'], ddsfile['WHFLDP'])
  318.     else:
  319.         numformat = None
  320.     if numformat:
  321.         numformats[fieldname] = numformat
  322.         commaflags[fieldname] = ',' in numformat.num_format_str
  323.  
  324.     # Check whether it looks like a numeric date or time
  325.     dateflags[fieldname] = is_numeric_date(fieldsize, ddsfile['WHEWRD'])
  326.     timeflags[fieldname] = is_numeric_time(fieldsize, ddsfile['WHEWRD'])
  327.  
  328.     # Look for fixed column width
  329.     match = re.search(r'width=([1-9][0-9]*)', fieldtext, re.IGNORECASE)
  330.     if match:
  331.         colwidths[fieldname] = colwidth(int(match.group(1)))
  332.  
  333.     # Look for zero-suppression flag
  334.     match = re.search(r'zero(s|es)?=blanks?', fieldtext, re.IGNORECASE)
  335.     if match:
  336.         blankzeros[fieldname] = True
  337.  
  338. ddsfile.close()
  339.  
  340. # Create a workbook with one sheet
  341. wb = xlwt.Workbook()
  342. ws = wb.add_sheet(infile.fileName())
  343. row = 0
  344.  
  345. title_style = ezxf('font: bold on')
  346. header_style = ezxf('font: bold on; align: wrap on')
  347. date_style = ezxf(num_format_str='m/d/yyyy')
  348. time_style = ezxf(num_format_str='h:mm:ss AM/PM')
  349. text_style = ezxf(num_format_str='@')
  350.  
  351. # Populate first few rows using additional parameters, if provided.
  352. # Typically, these rows would be used for report ID, date, and title.
  353. for arg in sys.argv[3:]:
  354.     ws.write(row, 0, arg, title_style)
  355.     row += 1
  356.  
  357. # Keep track of the widest data in each column
  358. maxwidths = [0] * len(fieldlist)
  359.  
  360. # If there were top-row parameters, skip a row before starting the
  361. # column headings.
  362. if parameters > 2:
  363.     row += 1
  364.  
  365. # Create a row for column headings
  366. for col, name in enumerate(fieldlist):
  367.     desc = headings[name]
  368.     ws.write(row, col, desc, header_style)
  369.     if name not in colwidths:
  370.         maxwidths[col] = fitwidth(desc, bold=True)
  371.  
  372. infile.posf()
  373. while not infile.readn():
  374.     row += 1
  375.     for col, data in enumerate(infile.get(fieldlist)):
  376.         fieldname = fieldlist[col]
  377.         nativedate = False
  378.         nativetime = False
  379.         if infile.fieldType(fieldname) == 'DATE':
  380.             year, month, day = [int(x) for x in data.split('-')]
  381.             if year > 1904:
  382.                 ws.write(row, col, date(year, month, day), date_style)
  383.             nativedate = True
  384.         elif infile.fieldType(fieldname) == 'TIME':
  385.             hour, minute, second = [int(x) for x in data.split('.')]
  386.             ws.write(row, col, time(hour, minute, second), time_style)
  387.             nativetime = True
  388.         elif dateflags[fieldname]:
  389.             if data:
  390.                 year, md = divmod(data, 10000)
  391.                 month, day = divmod(md, 100)
  392.                 ws.write(row, col, date(year, month, day), date_style)
  393.         elif timeflags[fieldname]:
  394.             if data:
  395.                 hour, minsec = divmod(data, 10000)
  396.                 minute, second = divmod(minsec, 100)
  397.                 ws.write(row, col, time(hour, minute, second), time_style)
  398.         elif data == 0 and fieldname in blankzeros:
  399.             pass
  400.         elif fieldname in numformats:
  401.             ws.write(row, col, data, numformats[fieldname])
  402.         elif infile.fieldType(fieldname) == 'CHAR':
  403.             ws.write(row, col, data, text_style)
  404.         else:
  405.             ws.write(row, col, data)
  406.         if fieldname not in colwidths:
  407.             if nativedate or dateflags[fieldname]:
  408.                 maxwidths[col] = datewidth()
  409.             elif nativetime or timeflags[fieldname]:
  410.                 maxwidths[col] = timewidth()
  411.             if fieldname in decplaces:
  412.                 dp = decplaces[fieldname]
  413.                 cf = commaflags[fieldname]
  414.                 maxwidths[col] = max(maxwidths[col], numwidth(data, dp, cf))
  415.             else:
  416.                 maxwidths[col] = max(maxwidths[col], fitwidth(data))
  417. infile.close()
  418.  
  419. # Set column widths
  420. for col in range(len(fieldlist)):
  421.     if fieldlist[col] in colwidths:
  422.         ws.col(col).width = colwidths[fieldlist[col]]
  423.     else:
  424.         ws.col(col).width = maxwidths[col]
  425.  
  426. wb.save(sys.argv[2])
  427. sndmsg('File copied to ' + sys.argv[2] + '.')
  428.  
© 2004-2019 by midrange.com generated in 0.097s valid xhtml & css