1 #jython poi example. from Jython mailing list
   2 
   3 from java.io import FileOutputStream
   4 from java.util import Date
   5 from java.lang import System
   6 from java.lang import Math
   7 
   8 from org.apache.poi.hssf.usermodel import *
   9 from org.apache.poi.hssf.util import HSSFColor
  10 
  11 startTime = System.currentTimeMillis()
  12 
  13 
  14 wb = HSSFWorkbook()
  15 fileOut = FileOutputStream("POIOut2.xls")
  16 
  17 
  18 # Create 3 sheets
  19 sheet1 = wb.createSheet("Sheet1")
  20 sheet2 = wb.createSheet("Sheet2")
  21 sheet3 = wb.createSheet("Sheet3")
  22 sheet3 = wb.createSheet("Sheet4")
  23 
  24 # Create a header style
  25 styleHeader = wb.createCellStyle()
  26 fontHeader = wb.createFont()
  27 fontHeader.setBoldweight(2)
  28 fontHeader.setFontHeightInPoints(14)
  29 fontHeader.setFontName("Arial")
  30 styleHeader.setFont(fontHeader)
  31 
  32 # Create a style used for the first column
  33 style0 = wb.createCellStyle()
  34 font0 = wb.createFont()
  35 font0.setColor(HSSFColor.RED.index)
  36 style0.setFont(font0)
  37 
  38 
  39 # Create the style used for dates.
  40 styleDates = wb.createCellStyle()
  41 styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
  42 
  43 
  44 # create the headers
  45 rowHeader = sheet1.createRow(1)
  46 # String value
  47 cell0 = rowHeader.createCell(0)
  48 cell0.setCellStyle(styleHeader)
  49 cell0.setCellValue("Name")
  50 
  51 
  52 # numbers
  53 for i in range(0, 8, 1):
  54     cell = rowHeader.createCell((i + 1))
  55     cell.setCellStyle(styleHeader)
  56     cell.setCellValue("Data " + str( (i + 1)) )
  57 
  58 
  59 # Date
  60 cell10 = rowHeader.createCell(9)
  61 cell10.setCellValue("Date")
  62 cell10.setCellStyle(styleHeader)
  63 
  64 for i in range(0, 100, 1):
  65     # create a new row
  66     row = sheet1.createRow(i + 2)
  67     for j in range(0, 10, 1):
  68         # create each cell
  69         cell = row.createCell(j)
  70         # Fill the first column with strings
  71         if j == 0:
  72             cell.setCellValue("Product " + str(i))
  73             cell.setCellStyle(style0)
  74 
  75         # Fill the next 8 columns with numbers.
  76         elif j < 9:
  77             cell.setCellValue( (Math.random() * 100))
  78 
  79             # Fill the last column with dates.
  80         else:
  81             cell.setCellValue(Date())
  82             cell.setCellStyle(styleDates)
  83 
  84 # Summary row
  85 rowSummary = sheet1.createRow(102)
  86 sumStyle = wb.createCellStyle()
  87 sumFont = wb.createFont()
  88 sumFont.setBoldweight( 5)
  89 sumFont.setFontHeightInPoints(12)
  90 sumStyle.setFont(sumFont)
  91 sumStyle.setFillPattern(HSSFCellStyle.FINE_DOTS)
  92 sumStyle.setFillForegroundColor(HSSFColor.GREEN.index)
  93 
  94 
  95 cellSum0 = rowSummary.createCell( 0)
  96 cellSum0.setCellValue("TOTALS:")
  97 cellSum0.setCellStyle(sumStyle)
  98 
  99 
 100 # numbers
 101 # B
 102 cellB = rowSummary.createCell( 1)
 103 cellB.setCellStyle(sumStyle)
 104 cellB.setCellFormula("SUM(B3:B102)")

   1 """    read.py
   2 Read an existant Excel file (Book1.xls) and show it on the screen
   3 """   
   4 from org.apache.poi.hssf.usermodel import *
   5 from java.io import FileInputStream
   6 
   7 file = "H:Book1.xls"
   8 print file
   9 fis = FileInputStream(file)
  10 wb = HSSFWorkbook(fis)
  11 sheet = wb.getSheetAt(0)
  12 
  13 # get No. of rows
  14 rows = sheet.getPhysicalNumberOfRows()
  15 print wb, sheet, rows
  16 
  17 cols = 0 # No. of columns
  18 tmp = 0
  19 
  20 # This trick ensures that we get the data properly even if it
  21 # doesn’t start from first few rows
  22 for i in range(0, 10,1):
  23     row = sheet.getRow(i)
  24     if(row != None):
  25         tmp = sheet.getRow(i).getPhysicalNumberOfCells()
  26         if tmp > cols:
  27             cols = tmp
  28 print cols
  29 
  30 for r in range(0, rows, 1):
  31     row = sheet.getRow(r)
  32     print r
  33     if(row != None):
  34         for c in range(0, cols, 1):
  35             cell = row.getCell(c)
  36             if cell != None:
  37                 print cell
  38 
  39 #wb.close()
  40 fis.close()