Preserve images in Excel headers using Apache POI

Question

I am trying to generate Excel reports using Apache POI 3.6 (latest).

Since POI has limited support for header and footer generation (text only), I decided to start from a blank excel file with the header already prepared and fill the Excel cells using POI (cf. question 714172).

Unfortunately, when opening the workbook with POI and writing it immediately to disk (without any cell manpulation), the header seems to be lost.

Here is the code I used to test this behavior:

public final class ExcelWorkbookCreator {

  public static void main(String[] args) {
    FileOutputStream outputStream = null;
    try {
      outputStream = new FileOutputStream(new File("dump.xls"));
      InputStream inputStream = ExcelWorkbookCreator.class.getResourceAsStream("report_template.xls");
      HSSFWorkbook workbook = new HSSFWorkbook(inputStream, true);
      workbook.write(outputStream);
    } catch (Exception exception) {
      throw new RuntimeException(exception);
    } finally {
      if (outputStream != null) {
        try {
          outputStream.close();
        } catch (IOException exception) {
          // Nothing much to do
        }
      }
    }
  }
}

score 1 · Answer 1 · answered Jun 18 '13 at 11:36

1

The headers of the Excel file are preserved as long as those headers are supported in Excel 97-2003. For example, images are supported (I just tried it), but colored text is not.

The tricky part of this is that your Excel template file "dump.xls" must be in Excel 97-2003 format. Please note: this is not the file extension, but the actual contents of the file. The newest Excel will happily save the newest formatting in a .xls file, which POI cannot read.

To test this, save your Excel file as an .xls file. Important - If you receive a compatibility warning, then you must click the "Correct" link in the dialog to correct the Excel. Just clicking Proceed makes the Excel file invalid to POI.

Once you have a real .xls file (with compatible contents) then your code works. I just tested it myself:

public static void main(String[] args) throws Exception {
  try (FileInputStream fis = new FileInputStream("./report_template.xls"); 
      FileOutputStream fos = new FileOutputStream("./dump.xls")) {
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    wb.write(fos); 
  }
}

answered Jun 18 '13 at 11:36

Jen S.

4,106
4
35
44

I don't have the right setup to test this right now but thanks for dropping by and clarifying the issue. At the time, I was trying to integrate some kind of custom headers (think customer-specific layout) and I didn't find a reliable way of generating them. Hence the idea of starting from a pre-decorated spreadsheet and generating the content on the fly with POI. – dimdm Jun 25 '13 at 20:07
Could you reformat your example to be "idomatic" Java? Your try/catch statement doesn't read like it should. I don't think this would compile... – dimdm Jun 25 '13 at 20:25
@dimdm - This compiles and runs under Java 7. It is called a "try with resources statement" - http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html Since Java 6 is no longer publicly updated, it is a good time to move onto Java 7 :) http://www.oracle.com/technetwork/java/eol-135779.html . And I agree with you... the only way I was able to get images and color headers/footers was with a template. This is the correct approach. – Jen S. Jun 26 '13 at 08:33
@hamlet-darcy That's what I get for not practicing any Java this year :) Thanks for pointing it out! I'll finally be able to get rid of these convoluted finally statements when I get back to Java: very nice. – dimdm Jul 03 '13 at 09:33

score 1 · Answer 2 · edited Jul 26 '12 at 12:05

1

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet    sheet    = new HSSFSheet();

Header header = sheet.getHeader() //get header from workbook's sheet
header.setCenter(HSSFHeader.font("COURIER", "Normal")+ HSSFHeader.fontSize((short) 15) + "Hello world" +new Date()); // set header with desire font style 

FileOutputStream fileOut = new FileOutputStream("C:\\book.xls");
workbook.write(fileOut);

edited Jul 26 '12 at 12:05

happy

2,550
17
64
109

answered Apr 21 '10 at 11:19

zaib mughal

19
2

1

This works for creating a new header in a blank sheet but what I'm trying to do is to preserve the header of an existing sheet loaded from a file. Thanks for the pointer though! – dimdm Aug 26 '10 at 11:21

Preserve images in Excel headers using Apache POI

2 Answers2