Categories

  • articles

Tags

  • java
  • poi
  • excel

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