Search code examples
javaexcelapache-poims-office

How to disable "Remove Personal Information from file properties on save" in Excel using Java and Apache POI

I am working on streamlining an Excel file tracking process. Basically when an excel file is uploaded to our server we need to keep a copy of the original version, as well as the new version with changes (via using the "track changes" option in excel). I have all the steps of this figured out except the following.

I am able to use Apache POI to disable any protection on the sheet and make a copy. However if I want to enable "Tracking Changes" on the spreadsheet I must go into excel -> options -> Trust Center settings -> privacy options then uncheck the box that says "Remove personal information from file properties on save" once this is done, the "track changes" option becomes available.

My question is, can this setting be disabled via apache poi before the user even opens it so that the user does not need to open the file and navigate to the trust center settings every time? I have been googling but cant get a clear answer. any help would be much appreciated!

An overview of how I want this to work ->
user drops an xlsx file in a folder
My app makes a copy of that file with locking and privacy disabled
the user can then open that file and enable track changes to make changes.

How it is working now
user drops an xlsx file in the folder
my app makes a copy of that file with locking disabled.
The user then needs to open the file, go several layers deep into options to disable "remove personal information"
Then the user can enable "track changes"

I know this seems like a small change, but the idea is to make this as simple as possible.

Thanks for reading!

Solution

  • The setting filter privacy is set in workbook properties of /xl/workbook.xml.

    The XML looks like:

    <workbookPr filterPrivacy="1" ...>

    It can be removed as so:

    XSSFWorkbook workbook ...
workbook.getCTWorkbook().getWorkbookPr().unsetFilterPrivacy();

    After that the Option "Remove personal information from file properties on save" in Trust Center - Privacy Options should show as unchecked.

    Complete example:

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

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ExcelRemoveFilterPrivacy {

 public static void main(String[] args) throws Exception {
  String fileIn = "./TestIn.xlsx";
  String fileOut = "./TestOut.xlsx";

  try (XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream(fileIn));
       FileOutputStream out = new FileOutputStream(fileOut)) {

   if (workbook.getCTWorkbook().getWorkbookPr().isSetFilterPrivacy())
    workbook.getCTWorkbook().getWorkbookPr().unsetFilterPrivacy();

   workbook.write(out);
  } 
 }
}