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

 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()