CODEWITHSUNDEEP
javaapache-poi
LeO
LeO

Reputation: 5248

Excel footer with different size

I have the following code:

final Footer footer = getSheet().getFooter();
final StringBuilder strFooterText = new StringBuilder();
strFooterText.append(DETAILS_FOOTER.get(0));
strFooterText.append("\n");

// another line
strFooterText.append(DETAILS_FOOTER.get(1));
strFooterText.append(getDetails());

final String fnt = HeaderFooter.font(DEFAULT_FONT_NAME, "regular")
  + HeaderFooter.fontSize(DEFAULT_DETAILS_FOOTER_FONT_HEIGHT);
footer.setLeft(fnt + strFooterText.toString());

That kind of code works fine when I open the resulting XLSX with LibreOffice. When I open it with Excel 2016 the used fnt won't work. With the repair option this is gone be removed.

Is there a way to change for the footer the font size to have it working with Excel2016 (and later)?

UPDATE

After the responses I figured out that I have next to the left footer a right footer with data:

footer.setRight("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

This result into CDATA .

From the updates I figured out that the font-name is the trouble maker. Without the font-name it works. So the following code works:

final Footer footer = getSheet().getFooter();
final StringBuilder strFooterText = new StringBuilder();
strFooterText.append(DETAILS_FOOTER.get(0));
strFooterText.append("\n");

// another line
strFooterText.append(DETAILS_FOOTER.get(1));
strFooterText.append(getDetails());

//final String fnt = HeaderFooter.font(DEFAULT_FONT_NAME, "regular")
//  + HeaderFooter.fontSize(DEFAULT_DETAILS_FOOTER_FONT_HEIGHT);
footer.setLeft("&8" + strFooterText.toString());
footer.setRight("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

Don't know if there is a chance to set the font-name as well.

Upvotes: 0

Views: 484

Answers (1)

Axel Richter
Axel Richter

Reputation: 61880

The CDATA issue comes from settings in xmlbeans.

Xmlbeans uses XmlOptions while reading and saving XML. There are following settings: XmlOptions.setSaveCDataLengthThreshold and XmlOptions.setSaveCDataEntityCountThreshold. The setSaveCDataLengthThreshold sets a minimal length of text content containing entities beyond which CDATA gets used. The setSaveCDataEntityCountThreshold sets a count of entities in text beyond which CDATA gets used. The default of setSaveCDataEntityCountThreshold is 5. So if text contents contain more than 5 entities, then it will be wrapped into CDATA.

So the CDATA problem occurs if a header or footer text contains more than 5 &, which in XML must be &. Then xmlbeans uses CDATA blocks for that text.

To avoid this the only way would be changing the POIXMLTypeLoader.DEFAULT_XML_OPTIONS. There appropriate settings for setSaveCDataLengthThreshold and setSaveCDataEntityCountThreshold would must be placed. As Excel itself never uses CDATA blocks, apache poi also should not.

But you should more precise tell which version of Excel has problems with these CDATA blocks. For me the following complete example produces the wanted results in Excel 2016 as well as in Excel 365 (all in Windows), even if CDATA blocks are used in the XML.

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 {

 static final String DEFAULT_FONT_NAME = "Arial";
 static final short DEFAULT_DETAILS_FOOTER_FONT_HEIGHT = 8;

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

  StringBuilder strFooterText = new StringBuilder();
  strFooterText.append("The footer text");
  strFooterText.append("\n");
  strFooterText.append("containing ");
  strFooterText.append("multiple lines");

  String fnt = HeaderFooter.font(DEFAULT_FONT_NAME, "regular")
  + HeaderFooter.fontSize(DEFAULT_DETAILS_FOOTER_FONT_HEIGHT);

  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");

  Footer footer = sheet.getFooter();
  footer.setLeft(fnt + strFooterText.toString());  
  footer.setCenter("&\"Times New Roman,bold\"&24&K00FF00Center footer\n&\"Arial,regular\"&8&K000000further Text");
  footer.setRight("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

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

 }
}

So if you run this complete example, which exact version of Excel will not properly show the *.xlsx file then?

So the problem was not the CDATA usage but the hit of a Excel limit. See: Excel specifications and limits:

Characters in a header or footer: 255

So a header and/or footer must not contain more than 255 characters in sum.

Upvotes: 1

Related Questions