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
        }
      }
    }
  }
}

happy · Accepted Answer · 2012-07-26 12:05:01Z

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, 2012 at 12:05

happy

2,62819 gold badges68 silver badges113 bronze badges

answered Apr 21, 2010 at 11:19

zaib mughal

192 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dimdm Over a year ago

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!

Jen S. · Accepted Answer · 2013-06-18 11:36:23Z

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, 2013 at 11:36

Jen S.

4,2344 gold badges38 silver badges44 bronze badges

4 Comments

dimdm Over a year ago

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 Over a year ago

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...

Jen S. Over a year ago

@dimdm - This compiles and runs under Java 7. It is called a "try with resources statement" - docs.oracle.com/javase/tutorial/essential/exceptions/… Since Java 6 is no longer publicly updated, it is a good time to move onto Java 7 :) 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.

dimdm Over a year ago

@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.

Collectives™ on Stack Overflow

Preserve images in Excel headers using Apache POI

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related