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