I'm trying to do my first tests of reading large xlsx file with POI, but to do a simple test with a small file I fail to show the value of a cell.
Someone can tell me what is my mistake. All the suggestions are welcome. Thanks.
Test.java:
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
public static void main(String[] args) throws Throwable {
File file = new File("/tmp/test.xlsx");
OPCPackage pkg = OPCPackage.open(new FileInputStream(file.getAbsolutePath()));
XSSFWorkbook xssfwb = new XSSFWorkbook(pkg);
SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100);
Sheet sh = wb.getSheet("Hola");
System.out.println("Name: "+sh.getSheetName()); // Line 19
System.out.println("Val: "+sh.getRow(1).getCell(1).getStringCellValue()); // Line 20
}
}
Result:
Name: Hola
Exception in thread "main" java.lang.NullPointerException
at Test.main(Test.java:20)
test.xlsx:
Where as Apache POI supports both xls and xlsx file formats. To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements
HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements // Will get the workbook instance for XLS and takes excel file to read
Writing excel using POI is very simple and involve following steps: 1 Create a workbook 2 Create a sheet in workbook 3 Create a row in sheet 4 Add cells in sheet 5 Repeat step 3 and 4 to write more data More ...
Please consult: similar question SXSSFWorkBook is write only, it doesn't support reading.
For low memory reading of .xlsx files, you should look at the XSSF and SAX EventModel documentation : Gagravarr
If memory wouldn't be an issue you could use a XSSFSheet instead e.g.
File file = new File("D:/temp/test.xlsx");
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sh = wb.getSheet("Hola");
System.out.println(sh.getLastRowNum());
System.out.println("Name: "+sh.getSheetName());
Row row = sh.getRow(1);
System.out.println(row.getRowNum());
System.out.println("Val: "+sh.getRow(1).getCell(1).getStringCellValue());
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With