I received the following exception in a java program I am using to learn about
the POI project:
org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Rule M2.4
exception : this error should NEVER happen, if so please send a mail to the
developers team, thanks !
The offending method is called closeWorkbook
/**
* closes a previously opened xlsx workbook opened by openWorkbook
* @param opcPackage
* @return
*/
public boolean closeWorkbook(String filename){
boolean success = true;
try{
this.getOpcPackage().close();
java.io.FileOutputStream fileOut = new
java.io.FileOutputStream(filename);
this.getWorkbook().write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("Cannot close workbook: " + e);
success = false;
}
return success;
}
The exception directed me to send a mail to the developers team. This is my
attempt to do so.
Thanks,
James
Here is my code:
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package areacontrol;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.openxml4j.opc.*;
/**
*
* @author jbatchelor
*/
public class AreaControl {
public OPCPackage opcPackage;
public XSSFWorkbook workbook;
public XSSFSheet sheet;
String[] columns;
public OPCPackage getOpcPackage() {
return opcPackage;
}
public void setOpcPackage(OPCPackage opcPackage) {
this.opcPackage = opcPackage;
}
public XSSFSheet getSheet() {
return sheet;
}
public void setSheet(XSSFSheet sheet) {
this.sheet = sheet;
}
public XSSFWorkbook getWorkbook() {
return workbook;
}
public void setWorkbook(XSSFWorkbook workbook) {
this.workbook = workbook;
}
/**
* opens an xlsx workbook with a given name. It will error and return false
* if the file already exists. Make sure to close the workbook when
finished.
* Please only open one workbook at a time.
* @param filename the name of the file workbook file to open
* @return boolean indicating true if no errors were encountered opening
the file
*/
public boolean openWorkbook(String filename){
//get the maximum number of columns and get the letter representation of
//each column stored in the columns[] object.
//replace with CellReference implementation later.
int max =
org.apache.poi.ss.SpreadsheetVersion.EXCEL2007.getMaxColumns();
this.getColumnLetters(max);
boolean success = true;
try{
setOpcPackage(OPCPackage.open(filename));
setWorkbook(new XSSFWorkbook(getOpcPackage()));
}catch(Exception e){
System.out.println("There was an error opening the workbook:");
System.out.println(e);
success = false;
}
return success;
}
/**
* closes a previously opened xlsx workbook opened by openWorkbook
* @param opcPackage
* @return
*/
public boolean closeWorkbook(String filename){
boolean success = true;
try{
this.getOpcPackage().close();
java.io.FileOutputStream fileOut = new
java.io.FileOutputStream(filename);
this.getWorkbook().write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("Cannot close workbook: " + e);
success = false;
}
return success;
}
/**
* get the sheet object from the workbook given a sheet number
* @param sheetNumber the positional number of a sheet
*/
public void getSheet(int sheetNumber){
setSheet(this.getWorkbook().getSheetAt(sheetNumber));
}
/**
* get the sheet object from the workbook given a sheet name
* @param sheetName the name of the sheet
*/
public void getSheet(String sheetName){
setSheet(this.getWorkbook().getSheet(sheetName));
}
/**
* read a string value from a cell given the column and row for the cell
* @param column a letter string value of column for the cell
* @param row a numerical row number for the cell
* @return the value of the cell represented as a string
*/
public String readStringCell(String column, int row){
int numColumn = getColumnFromLetters(column);
numColumn++; //adjust for zero index.
return readStringCell(numColumn,row);
}
/**
* read a string value from a cell given the column and row for the cell
* @param column a numerical column number for the cell
* @param row a numerical row number for the cell
* @return the value of the cell represented as a string
*/
public String readStringCell(int column, int row){
column--; //adjust for zero index
row--; //adjust for zero index
String cellValue = "";
try{
if(this.getSheet() == null){
this.setSheet(this.getWorkbook().getSheetAt(0));
}
XSSFRow sheetRow = this.getSheet().getRow(row);
XSSFCell rowCell = sheetRow.getCell(column);
cellValue = rowCell.toString();
}catch(java.lang.NullPointerException ne){
cellValue = null;
}
catch(Exception e){
System.out.println("There was an error reading the cell "+
column + "," + row + ":" + e);
}
return cellValue;
}
/**
* write a string value to a cell at a given column and row
* @param column a letter string value of the column for the cell
* @param row a numerical row for the cell
* @param cellData the value to write to the cell
* @return a boolean indicating if the operation was successful or not
*/
public boolean writeStringCell(String column, int row, String cellData){
int numColumn = getColumnFromLetters(column);
numColumn++; //adjust for zero index.
return writeStringCell(numColumn,row,cellData);
}
/**
* write a string value to a cell at a given column and row
* @param column a numerical value of the column for the cell
* @param row a numerical row for the cell
* @param cellData the value to write to the cell
* @return a boolean indicating if the operation was successful or not
*/
public boolean writeStringCell(int column, int row, String cellData){
column--;
row--;
boolean success = true;
try{
if(this.getSheet() == null){
this.setSheet(this.getWorkbook().getSheetAt(0));
}
XSSFRow sheetRow = getSheet().createRow(row);
XSSFCell rowCell = sheetRow.createCell(column);
rowCell.setCellValue(cellData);
}catch(Exception e){
System.out.println("There was an error writing the string to the
cell:" +e);
success = false;
}
return success;
}
/**
* iterates through the columns array to find the numerical index from the
* column name in letters
* @param column the string value of a column
* @return the numerical index for a column
*/
public int getColumnFromLetters(String column){
int count = 0;
while(!column.equalsIgnoreCase(this.columns[count])){
count++;
}
return count;
}
/**
* printThreeDigits is used for determining the excel column name from a
* column number. Excel 2007 has a maximum number of columns of 16834
* which is column XFD. This method will return columns A through ZZZ which
* is column 18278.
* @param max the column number
* @return the column name expressed as letters
*/
public String getColumnLetters(int columnNumber){
this.columns = new String[columnNumber];
int count = 0;
int numDigits = 1;
char[] output = {'A','A','A'};
String column = "";
while(count<columnNumber){
switch (numDigits) {
case 1:
//System.out.println(output[2]);
column = ""+output[2];
columns[count] = column;
output[2]++;
if(output[2] > 'Z'){
output[2] = 'A';
numDigits = 2;
}
break;
case 2:
//System.out.println(output[1]+""+output[2]);
column = "" + output[1] + "" + output[2];
columns[count]=column;
output[2]++;
if(output[2] > 'Z'){
output[2] = 'A';
output[1]++;
}
if(output[1] > 'Z'){
output[1] = 'A';
numDigits = 3;
}
break;
case 3:
//System.out.println(output[0]+""+output[1]+""+output[2]);
column = "" + output[0] + "" + output[1] + "" + output[2];
columns[count] = column;
output[2]++;
if(output[2] > 'Z'){
output[2] = 'A';
output[1]++;
}
if(output[1] > 'Z'){
output[1] = 'A';
output[0]++;
}
if(output[0] > 'Z'){
count = columnNumber;
}
break;
}
count++;
}
return(column);
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
String filename = "C:\\Users\\jbatchelor\\Desktop\\values.xlsx";
AreaControl ac = new AreaControl();
ac.openWorkbook(filename);
System.out.println(ac.readStringCell("A", 1));
System.out.println(ac.writeStringCell("A", 1, "Hi"));
System.out.println(ac.readStringCell("A", 1));
System.out.println(ac.getWorkbook().getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
ac.closeWorkbook(filename);
System.out.println(ac.getWorkbook().getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
//testing CellReference functionality
//org.apache.poi.ss.util.CellReference cr = new
org.apache.poi.ss.util.CellReference("A7");
//cr = new org.apache.poi.ss.util.CellReference("XFD3");
//System.out.println(cr.getRow());
//System.out.println(cr.getCol());
}
}
