Poi3 excel
Hello,
I am trying to read and write to an Excel file via my Java applet. I have done so successfully on several simple Excel files that simply had data cells without many complicated equations or any other Excel extras. However, now is the time to get the complicated ones to work. I didn't think that there would be a difference, but there is. At first, I was beginning to think that it was a file size problem, but now I am unsure. Does anyone know if there are any restrictions on POI? How about some good reference info, asside from the java doc?
Here is some more info. Currently, the excel file is 6 sheets with many complicated formulas as well as a few add ins including a fairly complex solver. There will be more added to it later, which worries me even more. It will also interact with some VB in the future.
However, I can not load the file at all. When I run the following lines:
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs, false);
I get the following exception on the second line:
[WARNING] Unknown Ptg 3c (60)
[WARNING] Unknown Ptg 3c (60)
[WARNING] Unknown Ptg 3c (60)
[WARNING] Unknown Ptg 3c (60)
java.lang.reflect.InvocationTargetException: java.lang.ArrayIndexOutOfBoundsException
at org.apache.poi.util.LittleEndian.getNumber(LittleEndian.java:557)
at org.apache.poi.util.LittleEndian.getShort(LittleEndian.java:90)
at org.apache.poi.hssf.record.formula.NamePtg.<init>(NamePtg.java:100)
at org.apache.poi.hssf.record.formula.Ptg.createPtg(Ptg.java:259)
at org.apache.poi.hssf.record.FormulaRecord.getParsedExpressionTokens(FormulaRecord.java:171)
at org.apache.poi.hssf.record.FormulaRecord.fillFields(FormulaRecord.java:147)
at org.apache.poi.hssf.record.Record.fillFields(Record.java:143)
at org.apache.poi.hssf.record.Record.<init>(Record.java:105)
at org.apache.poi.hssf.record.FormulaRecord.<init>(FormulaRecord.java:118)
at java.lang.reflect.Constructor.newInstance(Native Method)
at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:254)
at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:192)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:191)
at scheduling.util.ExcelSheet.loadExcel(ExcelSheet.java:174)
I found the sheet that causes the problem, however I have no idea why. When I delete all the cells in the sheet, it passes through this part, but crashes further down the line. I tried deleting cells in groups, trying to determine which cell or cells are causing the problem, but it doesn't work because, in my opinion, the equations rely on cells that I deleted.
Is there a way to run a debugger to determine where it is having a problem? What about in the future, how will I be able to figure out the next hurdle? There must be some good resources on this subject, I just can't find any ... anyone know of any?
If you wish, please feel free to contact me via email at zgolo@planitusa.com. I have figured out how to use POI in a relatively simple manner, but now I am at a loss. Please Help.
[3270 byte] By [
myhoneya] at [2007-11-27 9:57:25]
# 1
in this you need to provide the workable source code. else it will not be possible to understand. and usage of POI / limitations is given on their web-sitesregardsAniruddha
# 2
The code is working fine in poi 2.5, but it will give warnings in poi 3.0 alpha version, my requirement is to read the data from ppt files,so i changed the poi 2.5 to poi 3 alpha version, now its reading ppt files ascii characters, and its not reading japanese and korean characters, and even its displaying the reflection error also, please help me..
/**
* Extract the content from the given Excel file. As a side effect the type is set too.
*
*/
public final static Reader getXLContent(final File f) {
Reader reader = null;
try {
CharArrayWriter writer = new CharArrayWriter();
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
String num = Double.toString(cell.getNumericCellValue()).trim();
if (num.length() > 0) {
writer.write(num + " ");
}
break;
case HSSFCell.CELL_TYPE_STRING:
String text = cell.getStringCellValue().trim();
if (text.length() > 0) {
writer.write(text + " ");
}
break;
default: // skip
}
}
}
}
return new CharArrayReader(writer.toCharArray());
}
catch (Exception e) {
System.out.println("Exception in xls " + e.getClass());
}
return reader;
}
Regards
Madhu
# 3
hi!this code is running just fine for xls. what is your problem in ppt? if it can't read .ppt that is a separate issue. just define your problem properly. and please do not double postregards Aniruddha
# 4
public void processPOIFSReaderEvent(POIFSReaderEvent event) {
try {
if (!event.getName().equalsIgnoreCase("PowerPoint Document")) {
return;
}
DocumentInputStream input = event.getStream();
System.out.println("event.toString()" + event.toString());
byte[] buffer = new byte[input.available()];
System.out.println("input.available" + input.available());
//OutputStream os;
//os.
//DocumentOutputStream docOut = new DocumentOutputStream(os, ()4098);
input.read(buffer, 0, input.available());
System.out.println("Buffer" + buffer.toString());
for (int i = 0; i < buffer.length - 20; i++) {
long type = LittleEndian.getUShort(buffer, i + 2);
long size = LittleEndian.getUInt(buffer, i + 4);
if (type == 4008) {
System.out.println("type" + type);
writer.write(' ');
writer.write(buffer, i + 4 + 4, (int) size);
i = i + 4 + 4 + (int) size - 1;
}
}
}
catch (Exception e) {
log.warn("Error parsing powerpoint", e);
}
}
Above is the code which i am using to read from ppt files, but its not reading japnese character, its ony reading ascii values, i need to read even japanese character also,
I need to read even japanese character for lucene indexing, so
please guide me.
# 5
hiyou are too careless, your code is not compilable. you have not provided compilable code, what is writer, log in your code. why do not you use code formatting TAG ? i do not know in this manner any one will be willing to help you or not. this is really bad habit.
# 6
/** A utility for making Lucene Documents from a File. */
public class FileDocument implements POIFSReaderListener {
private static final int SUMMARY_SIZE = 200;
private ByteArrayOutputStream writer = new ByteArrayOutputStream();
public static Document Document(File f)
throws Exception {
Document doc = null;
doc = new Document();
if (f.getName().endsWith(".pdf") || f.getName().endsWith(".PDF")){
doc = LucenePDFDocument.getDocument(f);
}
try {
doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.UN_TOKENIZED));
doc.add(new Field("modified",
DateTools.timeToString(f.lastModified(), DateTools.Resolution.MINUTE),
Field.Store.YES, Field.Index.UN_TOKENIZED));
if (f.getName().endsWith(".xls") || f.getName().endsWith(".XLS")) {
doc.add(new Field("contents", getXLContent(f)));
} else if (f.getName().endsWith(".doc") || f.getName().endsWith(".DOC")) {
doc.add(new Field("contents", getDOCContent(f)));
} else if (f.getName().endsWith(".ppt") || f.getName().endsWith(".PPT")) {
FileDocument ss = new FileDocument();
doc.add(new Field("contents", ss.getContent(f)));
}
else{
doc.add(new Field("contents", new FileReader(f)));
}
} catch (NullPointerException e){
}
// return the document
return doc;
}
/**
* Extract the content from the given Excel file. As a side effect the type is set too.
*
*/
public final static Reader getXLContent(final File f) {
Reader reader = null;
try {
CharArrayWriter writer = new CharArrayWriter();
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
String num = Double.toString(cell.getNumericCellValue()).trim();
if (num.length() > 0) {
writer.write(num + " ");
}
break;
case HSSFCell.CELL_TYPE_STRING:
String text = cell.getStringCellValue().trim();
if (text.length() > 0) {
writer.write(text + " ");
}
break;
default: // skip
}
}
}
}
return new CharArrayReader(writer.toCharArray());
}
catch (Exception e) {
System.out.println("Exception in xls " + e.getClass());
}
return reader;
}
/**
* Extract the content from the given Powerpoint file. As a side effect the type is set too.
*
*/
public final Reader getContent(final File f) {
try {
POIFSReader reader = new POIFSReader();
reader.registerListener(this);
reader.read(new FileInputStream(f));
return new InputStreamReader(new ByteArrayInputStream(writer.toByteArray()));
}
catch (Exception e) {
System.out.println("Can't extract contents for: " + f.getName());
}
return null;
}
/**
* @see org.apache.poi.poifs.eventfilesystem.POIFSReaderListener#processPOIFSReaderEvent(org.apache.poi.poifs.eventfilesystem.POIFSReaderEvent)
*/
public void processPOIFSReaderEvent(POIFSReaderEvent event) {
try {
if (!event.getName().equalsIgnoreCase("PowerPoint Document")) {
return;
}
DocumentInputStream input = event.getStream();
System.out.println("event.toString()" + event.toString());
byte[] buffer = new byte[input.available()];
System.out.println("input.available" + input.available());
input.read(buffer, 0, input.available());
System.out.println("Buffer" + buffer.toString());
for (int i = 0; i < buffer.length - 20; i++) {
long type = LittleEndian.getUShort(buffer, i + 2);
long size = LittleEndian.getUInt(buffer, i + 4);
if (type == 4008) {
System.out.println("type" + type);
writer.write(' ');
writer.write(buffer, i + 4 + 4, (int) size);
i = i + 4 + 4 + (int) size - 1;
}
}
}
catch (Exception e) {
}
}
private FileDocument() {}
}
sorry man, i am new to this, these are working fine for ascii char, but i want it to work for japanese charcters also, please guide me..
