Hiding data on a Excel Spreadsheet using POI

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);  
           return null;  
      } 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!");  
      }  
 }