Apache Poi Examples
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, 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)")
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): 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()
back to OtherExamples