I had a requirement where I needed to hide some meta data in Excel Spreadsheet. Instead of using hidden row/columns/cells I used Custom Properties. Custom properties is a map in the Spreadsheet file where you can store key/value data. I found this much safer than hiding the data on the sheets since users could delete/corrupt the data very easily.
I did thou encounter a bug in Libre/OpenOffice where Custom properties are deleted when changes are made to the spreadsheet. The bug is logged with LibreOffice so I just updated my findings on the issue. See issue.
Due to this I have added extra example of how to store the data in the comments field of a spreadsheet. Both .xls and .xlsx file formats are supported in the example and at the time I was using POI 3.7.
/**
* Retrieve a custom property on the spreadsheet
*
* @param key
* @return
*/
public static String getCustomProperty(final String key) {
if (_workbook instanceof HSSFWorkbook) {
DocumentSummaryInformation documentSummaryInformation = ((HSSFWorkbook) _workbook).getDocumentSummaryInformation();
if (documentSummaryInformation != null && documentSummaryInformation.getCustomProperties() != null) {
return (String) documentSummaryInformation.getCustomProperties().get(key);
} else if (_workbook instanceof XSSFWorkbook) {
System.out.println(((XSSFWorkbook) _workbook).getProperties().getCustomProperties().contains(key));
System.out.println(((XSSFWorkbook) _workbook).getProperties().getCustomProperties().getUnderlyingProperties().sizeOfPropertyArray());
if (((XSSFWorkbook) _workbook).getProperties().getCustomProperties().contains(key)) {
List<CTProperty> propertyList = ((XSSFWorkbook) _workbook).getProperties().getCustomProperties().getUnderlyingProperties().getPropertyList();
for (CTProperty prop : propertyList) {
if (prop.getName().compareTo(key) == 0) {
System.out.println("Key : " + prop.getName() + ", Prop : " + prop.getLpwstr());
return prop.getLpwstr();
}
}
}
System.out.println("Creator : " + ((XSSFWorkbook) _workbook).getProperties().getCoreProperties().getCreator());
System.out.println("Description : " + ((XSSFWorkbook) _workbook).getProperties().getCoreProperties().getDescription());
return ((XSSFWorkbook) _workbook).getProperties().getCoreProperties().getCreator();
}
throw new RuntimeException("Unsupported workbook!");
}
/**
* Add a custom property to the spreadsheet
*
* @param key
* @param value
*/
public static void setCustomProperty(final String key, final String value) {
if (_workbook instanceof HSSFWorkbook) {
HSSFWorkbook workbook = (HSSFWorkbook) _workbook;
DocumentSummaryInformation documentSummaryInformation = workbook.getDocumentSummaryInformation();
if (documentSummaryInformation == null) {
workbook.createInformationProperties();
documentSummaryInformation = workbook.getDocumentSummaryInformation();
}
CustomProperties customProperties = documentSummaryInformation.getCustomProperties();
if (customProperties == null) {
customProperties = new CustomProperties();
}
customProperties.put(key, value);
documentSummaryInformation.setCustomProperties(customProperties);
} else if (_workbook instanceof XSSFWorkbook) {
((XSSFWorkbook) _workbook).getProperties().getCoreProperties().setDescription(value);
((XSSFWorkbook) _workbook).getProperties().getCustomProperties().addProperty(key, value);
} else {
throw new RuntimeException("Unsupported workbook!");
}
}
/**
* Retrieve Comments attached spreadsheet
*
* @return
*/
public static String getComments() {
if (_workbook instanceof HSSFWorkbook) {
return ((HSSFWorkbook) _workbook).getSummaryInformation().getComments();
} else if (_workbook instanceof XSSFWorkbook) {
return ((XSSFWorkbook) _workbook).getProperties().getCoreProperties().getDescription();
}
throw new RuntimeException("Unsupported workbook!");
}
/**
* Set Comment for the spreadsheet
*
* @param comment
*/
public static void setComments(final String comment) {
if (_workbook instanceof HSSFWorkbook) {
HSSFWorkbook workbook = (HSSFWorkbook) _workbook;
SummaryInformation summaryInformation = workbook.getSummaryInformation();
if (summaryInformation == null) {
workbook.createInformationProperties();
summaryInformation = workbook.getSummaryInformation();
}
summaryInformation.setComments(comment);
} else if (_workbook instanceof XSSFWorkbook) {
((XSSFWorkbook) _workbook).getProperties().getCoreProperties().setDescription(comment);
} else {
throw new RuntimeException("Unsupported workbook!");
}
}