Hi,
I ran the example. Wrote to and read from disc. Still studying and
playing around.
I can't find the openxml4j in the jar though, still seems to work fine
with one exception.
(no pun intended) NullPointerFound on the following line:
numSheets = workbook.getNumberOfSheets();
Thanks,
Jeff.
MSB wrote:
Hello again Jeff,
Firstly, I must admit that I am not too sure about exactly where the
XSSF..... classes are. Having said that you do not need to to worry about it
if you write code to the interfaces defined in the ss.usermodel package. The
other benefit of this is that it also does not matter if, at runtime, the
application finds itself having to deal with files in both the binary (HSSF)
and OpenXML (XSSF) formats.
Yesterday, I wrote a little bit of code that follows this pattern. In order
to compile and run it however, you will have to set your classpath
appropriately. I compiled the code against 3.5 beta 1 and so added the
following archives into my class path;
poi-3.5-beta1-20080718.jar
poi-ooxml-3.5-beta1-20080718.jar
openxml4j-bin-alpha-080407.jar
xmlbeans-2.3.0.jar
log4j-1.2.13.jar
dom4j-1.6.1.jar
ooxml-schemas.jar
It is highly likely that the exact names of the archives you have downloaded
will differ, do not be too concerned, just make sure that most of the name
is similar - i.e. the openxml4j part of the name for the
openxml4j-bin-alpha-080407.jar archive.
This is the code;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
public class Main {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
File inputFile = null;
FileInputStream fileIStream = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
Iterator<Row> rowIterator = null;
Iterator<Cell> cellIterator = null;
int numSheets = 0;
try {
inputFile = new File("C:/temp/sample.xlsx");
fileIStream = new FileInputStream(inputFile);
// Use the WorkbookFactory create() factory method to get
// either an HSSFWorkbook or XSSFWorkbook depending on
// the file type wrapped in the FileInputStream
workbook = WorkbookFactory.create(fileIStream);
// Lets see what we got, an XSSF or HSSFWorkbook
if(workbook instanceof
org.apache.poi.hssf.usermodel.HSSFWorkbook) {
System.out.println("You got an HSSFWorkbook");
}
else {
System.out.println("You got an XSSFWorkbook");
}
// Get the number of sheets
numSheets = workbook.getNumberOfSheets();
// Iterate through each sheet
for(int i = 0; i < numSheets; i++) {
// Get the sheet
sheet = workbook.getSheetAt(i);
// Get an iterator to work through the rows on the sheet
rowIterator = sheet.iterator();
// Iterate through the rows
while(rowIterator.hasNext()) {
row = rowIterator.next();
System.out.println("Processing row number: " +
row.getRowNum());
// Get an iterator to work through the cells on the row
cellIterator = row.iterator();
// Iterate through the cells
while(cellIterator.hasNext()) {
cell = cellIterator.next();
// Just print out the num,ber of the cell
// and the String representatin of the instance.
System.out.println("Cell: " +
cell.toString());
}
}
}
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows.........");
ex.printStackTrace(System.out);
}
finally {
if(fileIStream != null) {
try {
fileIStream.close();
}
catch(Exception ex) {
// I G N O R E //
}
}
}
}
}
which does not do anything exciting - iterates through all of the sheets in
a workbook, the rows on a sheet and the cells on a row then merely calls the
toString() method once it has a cell reference in hand. You should be able
to call it with both binary and OpenXML Excel files by simply changing this
line to point to your file;
inputFile = new File("C:/temp/sample.xlsx");
//inputFile = new File("C:/temp/sample.xls");
That only deals with reading existing files however. If you want to see how
a similar technique can be used to create a file, then take a look here;
http://poi.apache.org/spreadsheet/converting.html
and in particular, the section titled 'New, generic SS Usermodel Code'.
I accept that does not answer your original question, which I think has more
to do with dependencies and your classpath settings, but it will get you
working whilst I have a dig around to see where the XSSF......... classes
are.
PS do not be surprised when you run the code to see two warnings from the
logging system.
log4j:WARN No appenders could be found for logger (org.openxml4j.opc).
log4j:WARN Please initialize the log4j system properly.
They can safely be ignored for now.
Jeff Spence wrote:
I tried the following with success:
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("C:\\workbook.xls");
wb.write(fileOut);
fileOut.close();
But the following without success (narrowed down to this line I think).
XSSFWorkbook wb = new XSSFWorkbook();
...this error message.
java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
...tried these imports.
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
I don't think it's the path because, as stated above, they both
derive from ss.usermodel.
Anyone have a suggestion?
Thanks,
Jeff.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
