I am using Java and POI 3.7 and I want to perform following in xlWrite() function-
1. Check the file. If it doesn't exist create a new one.
If file exists, delete it and create a new one.
(DO I have to delete it or if it exists already my new content will overwrite it?)
2. Once check in 1. is completed, create a new sheet in the workbook if it doesn't exists.
Questions:
In XL write function I get above exception if I uncomment part of creating a file. Otherwise it works fine. What should I change in my program to achieve what I want?
I am not sure. Should I use HSSF or XSSF? Which is good. Right now I have to save my Excel as .xls to run the program. It doesn't accept .xlsx. What should I do?
I have created a xl class to perform read and write operations on Excel and I will be calling these functions from my script. Program I wrote is as given below but I get exception -
package lampsplus.utilities;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class XLOperations{
//variables declaration
private int i,j,k;
private int xRows, xCols;
private String xlData[][];
private HSSFRow row;
private HSSFCell cell;
private File xlFile;
private FileInputStream inStream;
private FileOutputStream outStream;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
/**
* This method returns XL read from file.
*
* @return String[][] - two dimensional array of read data.
*/
public String[][] getXLData()
{
return xlData;
}
/**
* Class constructor to initialize variables.
*
*/
public void XLOperations()
{
System.out.println("initializing constructor dboperations:");
this.xlData= null;
this.row = null;
this.cell = null;
this.xlFile = null;
this.inStream = null;
this.outStream = null;
this.workbook = null;
this.sheet = null;
}
/**
* This method is for reading a excel file in two dimensional array.
*
* @param String - filePath - Path of the file from where to read the information.
* @param String - sheetName -Name of the sheet to read from file.
* @return void
* @throws Exception - An unexpected exception
*/
public void xlRead(String filePath,String sheetName) throws Exception
{
File file = new File(filePath);
filePath= file.getAbsolutePath();
xlFile = new File(filePath);
inStream = new FileInputStream(xlFile);
workbook = new HSSFWorkbook(inStream);
sheet = workbook.getSheet(sheetName);
xRows = sheet.getLastRowNum()+1 ;
xCols = sheet.getRow(0).getLastCellNum();
xlData = new String[xRows][xCols];
for(i=0;i<xRows;i++)
{
row = sheet.getRow(i);
for(j=0;j<xCols;j++)
{
cell = row.getCell(j);
String value = cellToString(cell);
xlData[i][j] = value;
}
}
}
/**
* This method is for writing a writing a excel sheet into a file.
*
* @param String - filePath
* @param String - sheetname
* @param string - two dimensional array to write in the file
* @return void
* @throws Exception - An unexpected exception
*/
public void xlWrite(String filePath,String sheetName,String data[][]) throws Exception
{
File file = new File(filePath);
filePath= file.getAbsolutePath();
xlFile = new File(filePath);
/* if(!xlFile.exists())
{
System.out.println("Created file");
xlFile.createNewFile();
}
*/
inStream = new FileInputStream(xlFile);
workbook = new HSSFWorkbook(inStream);
k = workbook.getSheetIndex(sheetName);
if(k!=-1)
{
workbook.removeSheetAt(k);
}
HSSFSheet sheet = workbook.createSheet(sheetName);
FileOutputStream fOut = new FileOutputStream(xlFile);
xRows = data.length;
xCols = data[0].length;
for(i =0;i<xRows;i++)
{
row = sheet.createRow(i);
for(j=0;j<xCols;j++)
{
cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(data[i][j]);
}
}
workbook.write(fOut);
fOut.flush();
fOut.close();
}
public String cellToString(HSSFCell cell)
{
int type = cell.getCellType();
Object result;
switch(type)
{
case HSSFCell.CELL_TYPE_NUMERIC: //0
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING://1
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA://2
throw new RuntimeException("Java can't evaluate formulas");
case HSSFCell.CELL_TYPE_BLANK://3
result = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN://4
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR://5
throw new RuntimeException("This cell has an error");
default:
throw new RuntimeException("We don't support this cell type:" + type);
}
return result.toString();
}
}
Exception which I get-
java.io.IOException: Unable to read entire header; 0 bytes read; expected 32 bytes
at org.apache.poi.poifs.storage.HeaderBlockReader.alertShortRead(HeaderBlockReader.java:176)
at org.apache.poi.poifs.storage.HeaderBlockReader.<init>(HeaderBlockReader.java:103)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:153)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:305)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:286)
at tryIng.xlWrite(tryIng.java:74)
at tryIng.main(tryIng.java:49)
