最近项目里需要读取加密的excel,有xls和xlsx两种类型,对于加解密apache官方网站有简要说明
需要的jar包poi-3.9,jar poi-ooxml-3.9.jar poi-ooxml-schemas-3.9.jar xmlbeans-2.3.0.jar dom4j-1.6.1.jar
注意poi的3个jar版本号要对应,不然会有一些问题。
上代码咯~
/**
* Excel导入
*/
@SuppressWarnings("unchecked")
@RequestMapping(value = {"importExcel"})
@ResponseBody
public Result importExcel(@RequestParam(value = "ym")String ym,@RequestParam(value = "filedata")MultipartFile file){
int count=0;
Result result = null;
try {
if (file != null) {
String password = "5151";
String fileName=file.getOriginalFilename();
String prefix = fileName.substring(fileName.lastIndexOf(".") + 1);
String yyyyMM=ym.replace("-", "");
Workbook workbook;
//解密excel
if (prefix.toUpperCase().equals("XLS")) {
org.apache.poi.hssf.record.crypto.Biff8EncryptionKey
.setCurrentUserPassword(password);
workbook = WorkbookFactory.create(file.getInputStream());
file.getInputStream().close();
} else {
// POIFSFileSystem pfs = newPOIFSFileSystem(POIDataSamples.getPOIFSInstance().openResourceAsStream("**.xlsx"));
POIFSFileSystem pfs = new POIFSFileSystem(file.getInputStream());
file.getInputStream().close();
EncryptionInfo encInfo = new EncryptionInfo(pfs);
Decryptor decryptor = Decryptor.getInstance(encInfo);
decryptor.verifyPassword(password);
workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));//不支持2007+的EXCEL
// XSSFWorkbook workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));//支持2007+的EXCEL
}
//读取excel数据
Sheet sheet = workbook.getSheetAt(0);//不支持2007+的EXCEL
// XSSFSheet sheet = workbook.getSheetAt(0); 支持2007+的excel