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 """   
   5 from org.apache.poi.hssf.usermodel import *
   6 from java.io import FileInputStream;
   7 
   8 file = "H:Book1.xls"
   9 print file
  10 fis = FileInputStream(file)
  11 wb = HSSFWorkbook(fis);
  12 sheet = wb.getSheetAt(0);
  13 
  14 # get No. of rows
  15 rows = sheet.getPhysicalNumberOfRows();
  16 print wb, sheet, rows
  17 
  18 cols = 0; # No. of columns
  19 tmp = 0;
  20 
  21 # This trick ensures that we get the data properly even if it
  22 # doesn’t start from first few rows
  23 for i in range(0, 10,1):
  24     row = sheet.getRow(i);
  25     if(row != None):
  26         tmp = sheet.getRow(i).getPhysicalNumberOfCells();
  27         if(tmp > cols): 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();