Differences between revisions 1 and 4 (spanning 3 versions)
Revision 1 as of 2007-09-17 04:23:16
Size: 3200
Editor: GregMoore
Comment: Poi Example
Revision 4 as of 2007-10-19 17:55:46
Size: 4615
Editor: GregMoore
Comment:
Deletions are marked like this. Additions are marked like this.
Line 5: Line 5:
= Apache Poi Example = = Apache Poi Examples =

[[TableOfContents]]
Line 9: Line 11:
Line 10: Line 13:
Below are a few Poi examples. These examples requires Apache Poi installed and on the classpath.
Line 11: Line 15:

=== Create Spreadsheet ===
Line 14: Line 20:


This example requires Apache Poi installed and on the classpath.
Line 36: Line 39:
#// Create 3 sheets # Create 3 sheets
Line 42: Line 45:
#// Create a header style # Create a header style
Line 50: Line 53:
#/ Create a style used for the first column # Create a style used for the first column
Line 57: Line 60:
#// Create the style used for dates. # Create the style used for dates.
Line 62: Line 65:
#// create the headers # create the headers
Line 64: Line 67:
#// String value # String value
Line 70: Line 73:
#// numbers # numbers
Line 77: Line 80:
#// Date # Date
Line 83: Line 86:
    #// create a new row     # create a new row
Line 86: Line 89:
        #// create each cell         # create each cell
Line 88: Line 91:
        #// Fill the first column with strings         # Fill the first column with strings
Line 93: Line 96:
        #// Fill the next 8 columns with numbers.         # Fill the next 8 columns with numbers.
Line 97: Line 100:
            #// Fill the last column with dates.             # Fill the last column with dates.
Line 102: Line 105:
#// Summary row # Summary row
Line 118: Line 121:
#// numbers
#// B
# numbers
# B
Line 125: Line 128:

=== Read an Excel file ===

Posted to the Jython-users mailing list by Alfonso Reyes on October 14, 2007
This Jython code will open and read an existant Excel file you can download the file at http://www.nabble.com/file/p13199712/Book1.xls

To do:
- create the excel file if it doesn't exist
- a nicer printing method
- a method to print values or formulas in the cells

{{{
""" read.py
Read an existant Excel file (Book1.xls) and show it on the screen

"""
from org.apache.poi.hssf.usermodel import *
from java.io import FileInputStream;

file = "H:Book1.xls"
print file
fis = FileInputStream(file)
wb = HSSFWorkbook(fis);
sheet = wb.getSheetAt(0);

# get No. of rows
rows = sheet.getPhysicalNumberOfRows();
print wb, sheet, rows

cols = 0; # No. of columns
tmp = 0;

# This trick ensures that we get the data properly even if it
# doesn’t start from first few rows
for i in range(0, 10,1):
    row = sheet.getRow(i);
    if(row != None):
        tmp = sheet.getRow(i).getPhysicalNumberOfCells();
        if(tmp > cols): cols = tmp;
print cols

for r in range(0, rows, 1):
    row = sheet.getRow(r);
    print r
    if(row != None):
        for c in range(0, cols, 1):
            cell = row.getCell(c);
            if(cell != None):
                print cell

#wb.close()
fis.close();
}}}

Apache Poi Examples

TableOfContents

back to OtherExamples


Below are a few Poi examples. These examples requires Apache Poi installed and on the classpath.

Create Spreadsheet

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

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

Read an Excel file

Posted to the Jython-users mailing list by Alfonso Reyes on October 14, 2007 This Jython code will open and read an existant Excel file you can download the file at http://www.nabble.com/file/p13199712/Book1.xls

To do: - create the excel file if it doesn't exist - a nicer printing method - a method to print values or formulas in the cells

"""    read.py
Read an existant Excel file (Book1.xls) and show it on the screen

"""   
from org.apache.poi.hssf.usermodel import *
from java.io import FileInputStream;

file = "H:Book1.xls"
print file
fis = FileInputStream(file)
wb = HSSFWorkbook(fis);
sheet = wb.getSheetAt(0);

# get No. of rows
rows = sheet.getPhysicalNumberOfRows();
print wb, sheet, rows

cols = 0; # No. of columns
tmp = 0;

# This trick ensures that we get the data properly even if it
# doesn’t start from first few rows
for i in range(0, 10,1):
    row = sheet.getRow(i);
    if(row != None):
        tmp = sheet.getRow(i).getPhysicalNumberOfCells();
        if(tmp > cols): cols = tmp;
print cols

for r in range(0, rows, 1):
    row = sheet.getRow(r);
    print r
    if(row != None):
        for c in range(0, cols, 1):
            cell = row.getCell(c);
            if(cell != None):
                print cell

#wb.close()
fis.close();

back to OtherExamples

PoiExample (last edited 2011-04-08 13:22:04 by c-76-24-221-65)