How to create column in footer of Excel using HSSFFooter in Java

Question

I will create an Excel file using footer. In this case, the contents of the footer are three columns for signature. Is it possible to create a column in footer using .apache.poi.hssf.usermodel.HSSFFooter?

The footer I will create is like this picture

But when I try to using the HSSFFooter, I only contain one line. Like this pict

the first line is replace by the second line that I declare,

Here's my code :

HSSFFooter rowSignature0 = sheet0.getFooter();
rowSignature0.setCenter("KOLOM PROSES PENJURNALAN");

// tanggal
String now = new SimpleDateFormat("dd/MM/yyyy").format(new Date());
counter+=1;
rowSignature0.setLeft(now);
rowSignature0.setCenter(now);
rowSignature0.setRight(now);

So, do you have any idea to create this footer?

Answer 1

Excel sheet footers consist of one line having three columns - left, center and right. Texts in columns may have line breaks but not really single table rows. That is how Excel provides sheet footers and even using Excel GUI one cannot really put table rows into a sheet footer.

So what one can create using Excel and comes closest to your requirement is the following:

Code:

import java.io.FileOutputStream ;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HeaderFooter;

public class CreateExcelFooterText {

 public static void main(String[] args) throws Exception {

  StringBuilder strFooterLeftText = new StringBuilder();
  strFooterLeftText.append("\n");
  strFooterLeftText.append(HeaderFooter.date());
  strFooterLeftText.append("\n");
  strFooterLeftText.append("Dibuat oleh");
  
  StringBuilder strFooterCenterText = new StringBuilder();
  strFooterCenterText.append(HeaderFooter.startBold());
  strFooterCenterText.append("KOLOM PROSES PENIJURNALAN");
  strFooterCenterText.append(HeaderFooter.endBold());
  strFooterCenterText.append("\n");
  strFooterCenterText.append(HeaderFooter.date());
  strFooterCenterText.append("\n");
  strFooterCenterText.append("Diperiksa oleh");
  
  StringBuilder strFooterRightText = new StringBuilder();
  strFooterRightText.append("\n");
  strFooterRightText.append(HeaderFooter.date());
  strFooterRightText.append("\n");
  strFooterRightText.append("Diahkan oleh");

  //Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateExcelFooterText.xlsx";
  Workbook workbook = new HSSFWorkbook(); String filePath = "./CreateExcelFooterText.xls";

  Sheet sheet = workbook.createSheet();
  sheet.createRow(0).createCell(0).setCellValue("A1");

  //we need more margin for the footer than the default
  sheet.setMargin(Sheet.BottomMargin, 1.5); //bottom page margin 1.5 inch
  sheet.setMargin(Sheet.FooterMargin, .75); //footer marginb 0.75 inch
  //from version 5.2.3
  // sheet.setMargin(PageMargin.BOTTOM, 1.5); //bottom page margin 1.5 inch
  // sheet.setMargin(PageMargin.FOOTER, .75); //footer marginb 0.75 inch
  Footer footer = sheet.getFooter();
  footer.setLeft(strFooterLeftText.toString());  
  footer.setCenter(strFooterCenterText.toString());
  footer.setRight(strFooterRightText.toString());

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

Answer 2

Have you considered using JXLS?

JXLS is a small wrapper around Apache POI that allows you to define a template using Microsoft Excel instead of doing everything in java code. You have small fragments of markup (for loops, value placeholders etc) in your template but the majority of the presentation logic (eg colours, bold, headers, footers, images etc) can be done in excel.