Differences between revisions 5 and 7 (spanning 2 versions)
Revision 5 as of 2007-11-06 03:49:16
Size: 4623
Editor: AlfonsoReyes
Comment:
Revision 7 as of 2008-01-16 11:41:13
Size: 4563
Editor: 91
Comment:
Deletions are marked like this. Additions are marked like this.
Line 24: Line 24:
from java.io import FileOutputStream;
from java.util import Date;
from java.io import FileOutputStream
from java.util import Date
Line 30: Line 30:
from org.apache.poi.hssf.util import HSSFColor; from org.apache.poi.hssf.util import HSSFColor
Line 32: Line 32:
startTime = System.currentTimeMillis(); startTime = System.currentTimeMillis()
Line 35: Line 35:
wb = HSSFWorkbook();
fileOut = FileOutputStream("POIOut2.xls");
wb = HSSFWorkbook()
fileOut = FileOutputStream("POIOut2.xls")
Line 40: Line 40:
sheet1 = wb.createSheet("Sheet1");
sheet2 = wb.createSheet("Sheet2");
sheet3 = wb.createSheet("Sheet3");
sheet3 = wb.createSheet("Sheet4");
sheet1 = wb.createSheet("Sheet1")
sheet2 = wb.createSheet("Sheet2")
sheet3 = wb.createSheet("Sheet3")
sheet3 = wb.createSheet("Sheet4")
Line 46: Line 46:
styleHeader = wb.createCellStyle();
fontHeader = wb.createFont();
fontHeader.setBoldweight(2);
fontHeader.setFontHeightInPoints(14);
fontHeader.setFontName("Arial");
styleHeader.setFont(fontHeader);
styleHeader = wb.createCellStyle()
fontHeader = wb.createFont()
fontHeader.setBoldweight(2)
fontHeader.setFontHeightInPoints(14)
fontHeader.setFontName("Arial")
styleHeader.setFont(fontHeader)
Line 54: Line 54:
style0 = wb.createCellStyle();
font0 = wb.createFont();
font0.setColor(HSSFColor.RED.index);
style0.setFont(font0);
style0 = wb.createCellStyle()
font0 = wb.createFont()
font0.setColor(HSSFColor.RED.index)
style0.setFont(font0)
Line 61: Line 61:
styleDates = wb.createCellStyle();
styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
styleDates = wb.createCellStyle()
styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
Line 66: Line 66:
rowHeader = sheet1.createRow(1); rowHeader = sheet1.createRow(1)
Line 68: Line 68:
cell0 = rowHeader.createCell(0);
cell0.setCellStyle(styleHeader);
cell0.setCellValue("Name");
cell0 = rowHeader.createCell(0)
cell0.setCellStyle(styleHeader)
cell0.setCellValue("Name")
Line 75: Line 75:
    cell = rowHeader.createCell((i + 1));
    cell.setCellStyle(styleHeader);
    cell.setCellValue("Data " + str( (i + 1)) );
    cell = rowHeader.createCell((i + 1))
    cell.setCellStyle(styleHeader)
    cell.setCellValue("Data " + str( (i + 1)) )
Line 81: Line 81:
cell10 = rowHeader.createCell(9);
cell10.setCellValue("Date");
cell10.setCellStyle(styleHeader);
cell10 = rowHeader.createCell(9)
cell10.setCellValue("Date")
cell10.setCellStyle(styleHeader)
Line 87: Line 87:
    row = sheet1.createRow(i + 2);     row = sheet1.createRow(i + 2)
Line 90: Line 90:
        cell = row.createCell(j);         cell = row.createCell(j)
Line 92: Line 92:
        if (j == 0):
            cell.setCellValue("Product " + str(i));
            cell.setCellStyle(style0);
        if j == 0:
            cell.setCellValue("Product " + str(i))
            cell.setCellStyle(style0)
Line 97: Line 97:
        elif (j < 9):
            cell.setCellValue( (Math.random() * 100));
        elif j < 9:
            cell.setCellValue( (Math.random() * 100))
Line 102: Line 102:
            cell.setCellValue(Date());
            cell.setCellStyle(styleDates);
            cell.setCellValue(Date())
            cell.setCellStyle(styleDates)
Line 106: Line 106:
rowSummary = sheet1.createRow(102);
sumStyle = wb.createCellStyle();
sumFont = wb.createFont();
sumFont.setBoldweight( 5);
sumFont.setFontHeightInPoints(12);
sumStyle.setFont(sumFont);

sumStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
sumStyle.setFillForegroundColor(HSSFColor.GREEN.index);
rowSummary = sheet1.createRow(102)
sumStyle = wb.createCellStyle()
sumFont = wb.createFont()
sumFont.setBoldweight( 5)
sumFont.setFontHeightInPoints(12)
sumStyle.setFont(sumFont)

sumStyle.setFillPattern(HSSFCellStyle.FINE_DOTS)
sumStyle.setFillForegroundColor(HSSFColor.GREEN.index)
Line 116: Line 116:
cellSum0 = rowSummary.createCell( 0);
cellSum0.setCellValue("TOTALS:");
cellSum0.setCellStyle(sumStyle);
cellSum0 = rowSummary.createCell( 0)
cellSum0.setCellValue("TOTALS:")
cellSum0.setCellStyle(sumStyle)
Line 123: Line 123:
cellB = rowSummary.createCell( 1);
cellB.setCellStyle(sumStyle);
cellB.setCellFormula("SUM(B3:B102)");
cellB = rowSummary.createCell( 1)
cellB.setCellStyle(sumStyle)
cellB.setCellFormula("SUM(B3:B102)")
Line 142: Line 142:
Line 145: Line 144:
from java.io import FileInputStream; from java.io import FileInputStream
Line 150: Line 149:
wb = HSSFWorkbook(fis);
sheet = wb.getSheetAt(0);
wb = HSSFWorkbook(fis)
sheet = wb.getSheetAt(0)
Line 154: Line 153:
rows = sheet.getPhysicalNumberOfRows(); rows = sheet.getPhysicalNumberOfRows()
Line 157: Line 156:
cols = 0; # No. of columns
tmp = 0;
cols = 0 # No. of columns
tmp = 0
Line 163: Line 162:
    row = sheet.getRow(i);     row = sheet.getRow(i)
Line 165: Line 164:
        tmp = sheet.getRow(i).getPhysicalNumberOfCells();
        if(tmp > cols): cols = tmp;
        tmp = sheet.getRow(i).getPhysicalNumberOfCells()
        if tmp > cols:
           
cols = tmp
Line 170: Line 170:
    row = sheet.getRow(r);     row = sheet.getRow(r)
Line 174: Line 174:
            cell = row.getCell(c);
            if(cell != None):
            cell = row.getCell(c)
            if cell != None:
Line 179: Line 179:
fis.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

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

back to OtherExamples

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