I am trying to write a program that will read an .xlsx file, sort through
the data counting instances it finds based on the if statements, and count each
true instance. I have gotten this program to work with the HSSF library (with
the same file converted to .xls) but have a problem when trying to use the XSSF
library. Here is my code:
package logsheetsorter;
//Import libraries here
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
class SLMonthlyClosed
{
//Declare attributes here.
XSSFWorkbook logBook; //Declare a workbook to capture the excel page to be
read.
XSSFSheet slLotSheet; //Declare a sheet to hold the ML Lot number
information.
XSSFCell currentCell; //Declare a cell to hold the current cell being
looked at.
String cellContents; //Declare a string object to be used to analyze the
cell contents.
String monthSelected; //Declare a string attribute to convert the month
object to.
String yearSelected; //Declare a string attribute to convert the year
object to.
String lotMonth; //Declare a string attribute for the month substring of
the lot number.
String lotYear; //Declare a string attribute for the year substring of the
lot number.
int count = 0; //Declare an attribute to count the number of instances
that found that match the user input.
SLMonthlyClosed()
{
try
{
logBook = new XSSFWorkbook("C:/Documents and
Settings/Cory/Desktop/LOT_NUMBER_LOG.xlsx");
slLotSheet = logBook.getSheet("SL LOTS");
}
catch (Exception e)
{
System.out.println("WorkBook Read Error: " + e);
}
}
int SLMonthlyClosedCount(Object year, Object month)
{
yearSelected = year.toString(); //Convert the object year to a string
attribute.
monthSelected = month.toString(); //Convert the object month to a
string attribute.
yearSelected = yearSelected.substring(3); //Get the last digit of the
year selected in order to compare to the lot number year.
try
{
for (int row = 2; slLotSheet.getRow(row).getCell(1) !=null ||
slLotSheet.getRow(row).getCell(2) != null || slLotSheet.getRow(row).getCell(3)
!= null; row++) //Search through each row that is filled in in the worksheet.
{
cellContents =
slLotSheet.getRow(row).getCell(1).getStringCellValue(); //Get the lot number
of the current row being examined.
if (cellContents.length() > 5)
{
lotYear = cellContents.substring(3, 4); //Get the
substring of the lotYear to compare to the year selected.
if (lotYear.equals(yearSelected)) //Compare the lot year
to the year selected.
{
lotMonth = cellContents.substring(4, 6); //Get the
substring of the lot number representing the month.
if (lotMonth.equals(monthSelected)) //Compare the lot
number month with the selected month.
{
if (slLotSheet.getRow(row).getCell(8) != null)
{
count++;
}
}
}
}
}
}
catch (Exception a)
{
System.out.println("SL Monthly Closed Count Error: " + a);
}
return count;
}
}
I get the following error(s):
log4j:WARN No appenders could be found for logger (org.openxml4j.opc).
log4j:WARN Please initialize the log4j system properly.
Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: Java heap
space
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3039)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3060)
at
org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3250)
at
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1802)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
at
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4808)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
at
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
at
org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3439)
at
org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1270)
at
org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1257)
at
org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at
org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown
Source)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:126)
at
org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:118)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:201)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:164)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:173)
at logsheetsorter.SLMonthlyClosed.<init>(SLMonthlyClosed.java:25)
at logsheetsorter.LogSheetSorter.actionPerformed(Main.java:225)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6041)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
I'm not expereienced with using the log4j class. I understand I need to
configure a log4j.properties file in order for this to work. There are not
that many examples out there after looking around (or I'm looking in the wrong
spots). Anyone have any suggestions or references I can look at to learn this
library and be able to read the .xlsx file? Also, I tried to use
org.apache.poi.ss.usermodel.* (Workbook, Sheet, Cell, and WorkbookFactory) to
read the file with the same result.
Cory
PS For those subscribed to the general list I sent this message there as a
mistake. I apologize for the double post if you are getting this again.
