java读加密excel_java poi 读取加密的excel文件

读取xls加密文件没有问题，但读取xlss加密文件总出现：

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

经过查询资料总算解决，参考：

https://stackoverflow.com/questions/26729618/read-xlsx-file-using-poifsfilesystem

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.PushbackInputStream;

import java.text.SimpleDateFormat;

import java.util.Date;

import org.apache.poi.POIXMLProperties;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.openxml4j.opc.OPCPackage;

import org.apache.poi.poifs.crypt.Decryptor;

import org.apache.poi.poifs.crypt.EncryptionInfo;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelEncryt {

public static void main(String[] args) throws Exception {

System.out.println("begin");

String excelPath = "d:\\note2010_pwd.xlsx";

excelPath = "d:\\note2010.xlsx";

String password = "pwd";

unprotectXLSXSheet(excelPath, password);

}

public static void unprotectXLSXSheet(String fileName, String password) throws Exception {

InputStream is = null;

FileOutputStream fileOut = null;

SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

try {

is = new FileInputStream(fileName);

if (!is.markSupported()) {

is = new PushbackInputStream(is, 8);

}

if (POIFSFileSystem.hasPOIFSHeader(is))

{

POIFSFileSystem fs = new POIFSFileSystem(is);

EncryptionInfo info = new EncryptionInfo(fs);

Decryptor d = Decryptor.getInstance(info);

d.verifyPassword(password);

is = d.getDataStream(fs);

}

System.out.println(is.available());

//XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(is));

XSSFWorkbook wb = new XSSFWorkbook(is);

Sheet sheet = wb.getSheetAt(0);

int nRow = sheet.getPhysicalNumberOfRows();

int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数

//遍历每一行

for (int r = 0; r < rowCount; r++) {

Row row = sheet.getRow(r);

int cellCount = row.getPhysicalNumberOfCells(); //获取总列数

//遍历每一列

for (int c = 0; c < cellCount; c++) {

Cell cell = row.getCell(c);

if (cell == null) continue;

int cellType = cell.getCellType();

String cellValue = null;

switch(cellType) {

case Cell.CELL_TYPE_STRING: //文本

cellValue = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_NUMERIC: //数字、日期

if(DateUtil.isCellDateFormatted(cell)) {

cellValue = fmt.format(cell.getDateCellValue()); //日期型

}

else {

cellValue = String.valueOf(cell.getNumericCellValue()); //数字

}

break;

case Cell.CELL_TYPE_BOOLEAN: //布尔型

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_BLANK: //空白

cellValue = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_ERROR: //错误

cellValue = "错误";

break;

case Cell.CELL_TYPE_FORMULA: //公式

cellValue = "错误";

break;

default:

cellValue = "错误";

}

System.out.print(cellValue + "    ");

}

System.out.println();

}

//fileOut = new FileOutputStream(fileName);

//wb.write(fileOut);

//fileOut.flush();

}catch(Exception e){

e.printStackTrace();

} finally {

if (is != null) {

is.close();

}

if (fileOut != null) {

fileOut.close();

}

}

}

}

阅读(2480) | 评论(0) | 转发(0) |