I don't have permission to save files to the filesystem. All the examples I could find online are using filesystem.
I want to create workbook in memory as below:
HSSFWorkbook workbook = new HSSFWorkbook();
Write data to workbook... Make the workbook password protected.?
Then write the workbook to output stream as below:
workbook.write(servlet.getOutputStream());
I was able to get the following to work with an HttpServlet and HSSFWorkbook .xls file - WorkbookServlet.java:
package com.joshden;
import java.io.IOException;
import java.io.OutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
@WebServlet("/workbook")
public class WorkbookServlet extends HttpServlet {
private static final long serialVersionUID = 4087954595439224462L;
private static final String password = "foobar1";
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename=password_protected.xls");
createAndWriteEncryptedWorkbook(response.getOutputStream());
}
private void createAndWriteEncryptedWorkbook(OutputStream requestOutputStream) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
populateWorkbook(workbook);
Biff8EncryptionKey.setCurrentUserPassword(password);
workbook.write(requestOutputStream);
workbook.close();
}
private void populateWorkbook(Workbook workbook) {
workbook.createSheet("TestSheet");
// TODO create other sheets, populate cell values
}
}
With requests to /workbook, the servlet creates the HSSF workbook in memory with a sheet named TestSheet. The password is set to "foobar1". It prompts the browser to download it as a file with the name password_protected.xls.
Also see Apache POI - Encryption support. Encryption for the binary formats (HSSF) has only been available since Apache POI 3.16. At the time of this writing, 3.16 hasn't been released yet, and I used 3.16-beta1 in my example.
The encyprtion support guide also has info for creating encrypted (password protected) .xlsx (XSSF) files. Here's an example I created that will prompt the file to be named password_protected.xlsx:
package com.joshden;
import java.io.IOException;
import java.io.OutputStream;
import java.security.GeneralSecurityException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@WebServlet("/workbook")
public class WorkbookServlet extends HttpServlet {
private static final long serialVersionUID = 4087954595439224462L;
private static final String password = "foobar1";
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-Disposition", "attachment; filename=password_protected.xlsx");
createAndWriteEncryptedWorkbook(response.getOutputStream());
}
private void createAndWriteEncryptedWorkbook(OutputStream requestOutputStream) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
OPCPackage opc = workbook.getPackage();
populateWorkbook(workbook);
try {
POIFSFileSystem fileSystem = new POIFSFileSystem();
opc.save(getEncryptingOutputStream(fileSystem, password));
fileSystem.writeFilesystem(requestOutputStream);
}
finally {
workbook.close();
}
}
private void populateWorkbook(Workbook workbook) {
workbook.createSheet("TestSheet");
// TODO create other sheets, populate cell values
}
private OutputStream getEncryptingOutputStream(POIFSFileSystem fileSystem, String password) throws IOException {
EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
Encryptor encryptor = encryptionInfo.getEncryptor();
encryptor.confirmPassword(password);
try {
return encryptor.getDataStream(fileSystem);
}
catch (GeneralSecurityException e) {
// TODO handle this better
throw new RuntimeException(e);
}
}
}