3200
Comment: Poi Example
|
← Revision 11 as of 2011-04-08 13:22:04 ⇥
4677
|
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 15: | Line 21: |
This example requires Apache Poi installed and on the classpath. |
|
Line 21: | Line 24: |
from java.io import FileOutputStream; from java.util import Date; from java.lang import System from java.lang import Math |
from java.io import FileOutputStream from java.util import Date from java.lang import System, Math |
Line 27: | Line 29: |
from org.apache.poi.hssf.util import HSSFColor; | from org.apache.poi.hssf.util import HSSFColor |
Line 29: | Line 31: |
startTime = System.currentTimeMillis(); | startTime = System.currentTimeMillis() wb = HSSFWorkbook() fileOut = FileOutputStream("POIOut2.xls") |
Line 32: | Line 37: |
wb = HSSFWorkbook(); fileOut = FileOutputStream("POIOut2.xls"); |
# Create 3 sheets sheet1 = wb.createSheet("Sheet1") sheet2 = wb.createSheet("Sheet2") sheet3 = wb.createSheet("Sheet3") sheet3 = wb.createSheet("Sheet4") # Create a header style styleHeader = wb.createCellStyle() fontHeader = wb.createFont() fontHeader.setBoldweight(2) fontHeader.setFontHeightInPoints(14) fontHeader.setFontName("Arial") styleHeader.setFont(fontHeader) # Create a style used for the first column style0 = wb.createCellStyle() font0 = wb.createFont() font0.setColor(HSSFColor.RED.index) style0.setFont(font0) |
Line 36: | Line 58: |
#// Create 3 sheets sheet1 = wb.createSheet("Sheet1"); sheet2 = wb.createSheet("Sheet2"); sheet3 = wb.createSheet("Sheet3"); sheet3 = wb.createSheet("Sheet4"); #// Create a header style styleHeader = wb.createCellStyle(); fontHeader = wb.createFont(); fontHeader.setBoldweight(2); fontHeader.setFontHeightInPoints(14); fontHeader.setFontName("Arial"); styleHeader.setFont(fontHeader); #/ Create a style used for the first column style0 = wb.createCellStyle(); font0 = wb.createFont(); font0.setColor(HSSFColor.RED.index); style0.setFont(font0); |
# Create the style used for dates. styleDates = wb.createCellStyle() styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")) |
Line 57: | Line 63: |
#// Create the style used for dates. styleDates = wb.createCellStyle(); styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); |
# create the headers rowHeader = sheet1.createRow(1) # String value cell0 = rowHeader.createCell(0) cell0.setCellStyle(styleHeader) cell0.setCellValue("Name") |
Line 62: | Line 71: |
#// create the headers rowHeader = sheet1.createRow(1); #// String value cell0 = rowHeader.createCell(0); cell0.setCellStyle(styleHeader); cell0.setCellValue("Name"); |
# numbers for i in range(0, 8, 1): cell = rowHeader.createCell((i + 1)) cell.setCellStyle(styleHeader) cell.setCellValue("Data " + str( (i + 1)) ) |
Line 70: | Line 78: |
#// numbers for i in range(0, 8, 1): cell = rowHeader.createCell((i + 1)); cell.setCellStyle(styleHeader); cell.setCellValue("Data " + str( (i + 1)) ); |
# Date cell10 = rowHeader.createCell(9) cell10.setCellValue("Date") cell10.setCellStyle(styleHeader) for i in range(0, 100, 1): # create a new row row = sheet1.createRow(i + 2) for j in range(0, 10, 1): # create each cell cell = row.createCell(j) # Fill the first column with strings if j == 0: cell.setCellValue("Product " + str(i)) cell.setCellStyle(style0) # Fill the next 8 columns with numbers. elif j < 9: cell.setCellValue( (Math.random() * 100)) # Fill the last column with dates. else: cell.setCellValue(Date()) cell.setCellStyle(styleDates) # Summary row 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 77: | Line 114: |
#// Date cell10 = rowHeader.createCell(9); cell10.setCellValue("Date"); cell10.setCellStyle(styleHeader); for i in range(0, 100, 1): #// create a new row row = sheet1.createRow(i + 2); for j in range(0, 10, 1): #// create each cell cell = row.createCell(j); #// Fill the first column with strings if (j == 0): cell.setCellValue("Product " + str(i)); cell.setCellStyle(style0); #// Fill the next 8 columns with numbers. elif (j < 9): cell.setCellValue( (Math.random() * 100)); #// Fill the last column with dates. else: cell.setCellValue(Date()); cell.setCellStyle(styleDates); #// Summary row 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); |
cellSum0 = rowSummary.createCell( 0) cellSum0.setCellValue("TOTALS:") cellSum0.setCellStyle(sumStyle) |
Line 113: | Line 119: |
cellSum0 = rowSummary.createCell( 0); cellSum0.setCellValue("TOTALS:"); cellSum0.setCellStyle(sumStyle); |
# numbers # B cellB = rowSummary.createCell( 1) cellB.setCellStyle(sumStyle) cellB.setCellFormula("SUM(B3:B102)") wb.write(fileOut) fileOut.close() stopTime = System.currentTimeMillis() print 'POI generation took %d ms' %(stopTime - startTime) }}} |
Line 118: | Line 134: |
#// numbers #// B cellB = rowSummary.createCell( 1); cellB.setCellStyle(sumStyle); 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 {{{#!python """ 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() |
Line 125: | Line 187: |
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 wb = HSSFWorkbook()
13 fileOut = FileOutputStream("POIOut2.xls")
14
15
16 # Create 3 sheets
17 sheet1 = wb.createSheet("Sheet1")
18 sheet2 = wb.createSheet("Sheet2")
19 sheet3 = wb.createSheet("Sheet3")
20 sheet3 = wb.createSheet("Sheet4")
21
22 # Create a header style
23 styleHeader = wb.createCellStyle()
24 fontHeader = wb.createFont()
25 fontHeader.setBoldweight(2)
26 fontHeader.setFontHeightInPoints(14)
27 fontHeader.setFontName("Arial")
28 styleHeader.setFont(fontHeader)
29
30 # Create a style used for the first column
31 style0 = wb.createCellStyle()
32 font0 = wb.createFont()
33 font0.setColor(HSSFColor.RED.index)
34 style0.setFont(font0)
35
36
37 # Create the style used for dates.
38 styleDates = wb.createCellStyle()
39 styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
40
41
42 # create the headers
43 rowHeader = sheet1.createRow(1)
44 # String value
45 cell0 = rowHeader.createCell(0)
46 cell0.setCellStyle(styleHeader)
47 cell0.setCellValue("Name")
48
49
50 # numbers
51 for i in range(0, 8, 1):
52 cell = rowHeader.createCell((i + 1))
53 cell.setCellStyle(styleHeader)
54 cell.setCellValue("Data " + str( (i + 1)) )
55
56
57 # Date
58 cell10 = rowHeader.createCell(9)
59 cell10.setCellValue("Date")
60 cell10.setCellStyle(styleHeader)
61
62 for i in range(0, 100, 1):
63 # create a new row
64 row = sheet1.createRow(i + 2)
65 for j in range(0, 10, 1):
66 # create each cell
67 cell = row.createCell(j)
68 # Fill the first column with strings
69 if j == 0:
70 cell.setCellValue("Product " + str(i))
71 cell.setCellStyle(style0)
72
73 # Fill the next 8 columns with numbers.
74 elif j < 9:
75 cell.setCellValue( (Math.random() * 100))
76
77 # Fill the last column with dates.
78 else:
79 cell.setCellValue(Date())
80 cell.setCellStyle(styleDates)
81
82 # Summary row
83 rowSummary = sheet1.createRow(102)
84 sumStyle = wb.createCellStyle()
85 sumFont = wb.createFont()
86 sumFont.setBoldweight( 5)
87 sumFont.setFontHeightInPoints(12)
88 sumStyle.setFont(sumFont)
89 sumStyle.setFillPattern(HSSFCellStyle.FINE_DOTS)
90 sumStyle.setFillForegroundColor(HSSFColor.GREEN.index)
91
92
93 cellSum0 = rowSummary.createCell( 0)
94 cellSum0.setCellValue("TOTALS:")
95 cellSum0.setCellStyle(sumStyle)
96
97
98 # numbers
99 # B
100 cellB = rowSummary.createCell( 1)
101 cellB.setCellStyle(sumStyle)
102 cellB.setCellFormula("SUM(B3:B102)")
103
104 wb.write(fileOut)
105 fileOut.close()
106
107 stopTime = System.currentTimeMillis()
108 print 'POI generation took %d ms' %(stopTime - startTime)
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