7

Was trying to implement an excel export feature on an app using apache POI 3.17.

All was working fine on my local tomcat server and on a windows dev environment. However, the SXSSFWorkbook workbook.createSheet() method fails on a linux tomcat server without throwing any kind of meaningful error (it's just hanging).

Strangely, the same method on the XSSFWorkbook createSheet class works fine. Below are the snippets of code. Has anyone experienced a similar issue before?

    final SXSSFWorkbook workbook = new SXSSFWorkbook();
    workbook.setCompressTempFiles(true);
    SXSSFSheet sheet = workbook.createSheet("Sheet 1"); //this method fails

    final XSSFWorkbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Sheet 1"); // this works fine

Edit

I created a custom TempFileCreationStrategy to ensure tomcat is writing the file to a directory it has full access to. I can see the file has been created but it's hanging as it tries to write any data to the file.

I cannot figure this one out.

Edit2

I've enabled POI logging but it I'm still not getting anything meaningful that I can investigate. On my local server POI logging spits out the following as it begins to write the file:

[20:13:05,005]DEBUG (?:?) - Save core properties part
[20:13:05,005]DEBUG (?:?) - Save package relationships
[20:13:05,005]DEBUG (?:?) - Save content types part
[20:13:05,005]DEBUG (?:?) - Save part 'docProps/app.xml'
[20:13:05,005]DEBUG (?:?) - Save part 'docProps/core.xml'
[20:13:05,005]DEBUG (?:?) - Save part 'xl/sharedStrings.xml'
[20:13:05,005]DEBUG (?:?) - Save part 'xl/styles.xml'
[20:13:05,005]DEBUG (?:?) - Save part 'xl/workbook.xml'
[20:13:05,005]DEBUG (?:?) - Save part 'xl/worksheets/sheet1.xml'

On the Linux box, it's not even getting as far as the first log output. Need to find a way to get more detail about the failure!

Edit3

Is it possible to get more detailed logging beyond the default logging I have enabled below?

System.setProperty("org.apache.poi.util.POILogger", "org.apache.poi.util.SystemOutLogger" );

String tmpDir = System.getProperty("java.io.tmpdir")+File.separator+"excelfiles";
ExcelFileCreationStrategy tfcs = new ExcelFileCreationStrategy();

try {
    tfcs.createTempDirectory(tmpDir);
} catch (IOException e) {
    e.printStackTrace();
    LOG.error(e);
}

TempFile.setTempFileCreationStrategy(tfcs);

final SXSSFWorkbook workbook = new SXSSFWorkbook();
workbook.setCompressTempFiles(true);

LOG.debug("creating work sheet - next line fails");
Sheet sheet = workbook.createSheet(); //hangs here
LOG.debug("It's worked!!!!");
shanahobo86
  • 467
  • 2
  • 7
  • 23
  • 3
    Can you add a stacktrace to the question when it is hanging? I.e. via jstack ? – centic Feb 26 '18 at 14:17
  • There **must** be an exception somewhere. Go through all related log files. `SXSSFWorkbook` is using temporary files in `System.getProperty("java.io.tmpdir")` per default. Maybe accessing this is the problem? – Axel Richter Feb 26 '18 at 15:59
  • SXSSF works just fine on Linux, as that's the default platform for all the Apache POI continuous integration unit tests! You must've misconfigured Tomcat or Java on that specific machine – Gagravarr Feb 26 '18 at 16:59
  • @AxelRichter all the logs are dumped into the catalina.out file but there really isn't anything unusual that I can see. I configured it to write to my own custom directory, ensuring tomcat had full rw access. It creates a .xml file which is part of the streaming operation, but doesn't seem to write any data to the file. It's at this point it hangs. I'm stumped! – shanahobo86 Mar 13 '18 at 18:55
  • @Gagravarr Misconfigured tomcat or Java? Hard to imagine how I could have done that but if you have some suggestions I'll certainly investigate further. – shanahobo86 Mar 13 '18 at 19:01
  • @centic I'm not sure how I can get more detailed logs on the specific method that fails. Certainly not getting any exceptions through from the main thread. – shanahobo86 Mar 13 '18 at 19:02
  • 1
    "It creates a .xml file": This is surprising. Since you are using `workbook.setCompressTempFiles(true);` it should creating a `*.gz` file and not a `*.xml` file. Sure we are talking about the same code? – Axel Richter Mar 13 '18 at 19:26
  • You are completely right. It's a .gz file with a size of 10 bytes. Apologies! – shanahobo86 Mar 13 '18 at 19:31
  • @AxelRichter any idea how I might go about investigating this further? – shanahobo86 Mar 14 '18 at 09:39
  • @shanahobo86: Sorry, I have no clue what this could be. The only thing, I can tell you is, that `SXSSFWorkbook.createSheet` does not writing something into the temporary file. It only creates one for later storing the sheet's data. So 0 byte `*.xml` file or 10 byte `*.gz` file ia absolutelly correct. But no clue why it hangs then without any message. – Axel Richter Mar 14 '18 at 10:11
  • @AxelRichter Thanks for taking the time to answer, I appreciate it. I'll continue investigating. – shanahobo86 Mar 14 '18 at 11:52
  • Have you checked thread dump? – Ivan Mar 15 '18 at 20:44
  • do you have SELinux enabled? also have a look at the syslog of your production environment. – Ilya Dyoshin Mar 20 '18 at 09:37
  • still no solution here? Im facing the same problem ... – Manu May 11 '20 at 14:05

2 Answers2

1

I have checked with strace what happens under the hood, and the relevant output was:

11924 openat(AT_FDCWD, "/tmp/out.bin", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 48
11924 openat(AT_FDCWD, "/tmp/poifiles/poi-sxssf-template2618545805950425148.xlsx", O_RDWR|O_CREAT|O_EXCL, 0666) = 49
11924 openat(AT_FDCWD, "/tmp/poifiles/poi-sxssf-template2618545805950425148.xlsx", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 49
11924 openat(AT_FDCWD, "/tmp/poifiles/poi-sxssf-template2618545805950425148.xlsx", O_RDONLY) = 49
11924 openat(AT_FDCWD, "/tmp/poifiles/poi-sxssf-sheet-xml261863645047955641.gz", O_RDONLY) = 21

I guess you need to make sure the /tmp/poifiles is writable by your user. However in my case when I make it non-writable, the app won't hang, but instead throws IOException.

Or, if your java.io.tmpdir is not /tmp, I guess you need to make sure your poifiles subdirectory inside java.io.tmpdir is writable.

BaRoN
  • 449
  • 3
  • 13
  • Thanks @BaRoN, I already changed the directory when testing a solution (although tomcat has full r/w access to the /tmp dir). I've unfortunately had to bench this one in favor of the XSSF writer but it's a lot slower and I need to cap it at around 100k records to preserve heap. The linux box is a protected server and it means I need to debug through a middleware team which complicates the process even more. – shanahobo86 Mar 26 '18 at 11:20
0

I had the same problem and after a long time of debugging and analysis, the "bug" is that when streaming, it tries to use the font to determine the width of the characters to adjust the columns. This fails without a message and simply aborts the creation if the font cannot be created / determined. The only solution I could find was to install the package "fontconfig".

sudo apt-get update
sudo apt-get install fontconfig

Without the package java unfortunately cannot work with java.awt.Fonts.

Manu
  • 182
  • 2
  • 7