Apache Poi Example

back to OtherExamples


This is from the Jython mailing list and was posted September 2007

This is based on Java code at http://officewriter.softartisans.com/OfficeWriter-306.aspx and converted to Jython by Alfonso Reyes

This example requires Apache Poi installed and on the classpath.

   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)");

back to OtherExamples