I had to read a XLSX file in POI today
Problem 1:
You can no longer use the old
HSSFWorkbook wb = new HSSFWorkbook(fileInputStream);
syntax
or you will get :
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)
org.apache.poi.poifs.filesystem.OfficeXmlFileException: 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)
Problem 2:
So I tried to use the new API
Workbook workbook = WorkbookFactory.create(fileInputStream);
But where is
org.apache.poi.ss.usermodel.WorkbookFactory ??
This is because you need another dependably now. Go figure.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
Problem 3:
So as a 'good' Java dev, I started with unit testing, but I did not want to read a file in my JUnit test.
I came across this : http://stackoverflow.com/questions/2597271/easy-to-get-a-test-file-into-junit
My JUnit now looks like :
public class TestCalculator {
@Rule
public ResourceFile res = new ResourceFile("/Comparative.xlsx");// this file is in my test resources
@Before
public void before() throws IOException{
Assert.assertNotNull( res.getFile());
}
@Test
public void testParseXLSDoc(){
Calculator c = new Calculator(res.file.getAbsolutePath());//the Java File
c.parseActiveDocument();
}
}
//from stackoverflow
public class ResourceFile extends ExternalResource {
String res;
File file = null;
InputStream stream;
public ResourceFile(String res) {
this.res = res;
}
public File getFile() throws IOException {
if (file == null) {
createFile();
}
return file;
}
public InputStream getInputStream() {
return stream;
}
public InputStream createInputStream() {
return getClass().getResourceAsStream(res);
}
public String getContent() throws IOException {
return getContent("utf-8");
}
public String getContent(String charSet) throws IOException {
InputStreamReader reader = new InputStreamReader(createInputStream(),
Charset.forName(charSet));
char[] tmp = new char[4096];
StringBuilder b = new StringBuilder();
try {
while (true) {
int len = reader.read(tmp);
if (len < 0) {
break;
}
b.append(tmp, 0, len);
}
reader.close();
} finally {
reader.close();
}
return b.toString();
}
@Override
protected void before() throws Throwable {
super.before();
stream = getClass().getResourceAsStream(res);
}
@Override
protected void after() {
try {
if (stream != null) {
stream.close();
}
} catch (IOException e) {
// ignore
}
if (file != null) {
file.delete();
}
super.after();
}
private void createFile() throws IOException {
file = new File(".", res);
InputStream stream = getClass().getResourceAsStream(res);
try {
file.createNewFile();
FileOutputStream ostream = null;
try {
ostream = new FileOutputStream(file);
byte[] buffer = new byte[4096];
while (true) {
int len = stream.read(buffer);
if (len < 0) {
break;
}
ostream.write(buffer, 0, len);
}
} finally {
if (ostream != null) {
ostream.close();
}
}
} finally {
stream.close();
}
}
}
