I am not able to read an xlsm file using the POI Framework (HSSF). I am getting the following error while reading an xlsm file.
The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
I tried reading the file through XSSF also. Even that doesn't solve the problem. Can anyone tell me how to read a xlsm file in java code using the poi framework and write a new sheet to that file.
First download these JARs and add them in build path:
Now you can try this code. It will read XLSX and XLSM files:
import java.io.File;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadMacroExcel {
public static void main(String[] args) {
try {
//Create a file from the xlsx/xls file
File f=new File("F:\\project realated file\\micro.xlsm");
//Create Workbook instance holding reference to .xlsx file
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(f);
System.out.println(workbook);
//printing number of sheet avilable in workbook
int numberOfSheets = workbook.getNumberOfSheets();
System.out.println(numberOfSheets);
org.apache.poi.ss.usermodel.Sheet sheet=null;
//Get the sheet in the xlsx file
for (int i = 0; i < numberOfSheets; i++) {
sheet = workbook.getSheetAt(i);
System.out.println(sheet.getSheetName());
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "t");
break;
}
}
System.out.println("");
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}