[Java] POI 암호걸린 EXCEL 풀고 데이터 읽어오기

[Java] POI 암호걸린 EXCEL 풀고 데이터 읽어오기

 

 

- 의존성 추가 (Maven)

 엑셀 복호화 기능은 POI 3.16 버전 이후로 사용가능하다.

 

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.16</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.16</version>
</dependency>
<dependency>
	<groupId>commons-codec</groupId>
	<artifactId>commons-codec</artifactId>
	<version>1.11</version>
</dependency>

 

 

 

1. Controller

 

 여기서 colM 이나 password를 고정 값으로 넣었지만, 파라메터 데이터나 동적으로 설정가능하다

 

@RequestMapping(value="/uldExcel.do", method = RequestMethod.POST, consumes = "multipart/form-data", produces="text/pain;charset=UTF-8")
@ResponseBody
public String uldFuelCng(MultipartHttpServletRequest mReq){
	CmmnSet set = new CmmnSet();
	Gson gson = new Gson();

	List<MultipartFile> mf = mReq.getFiles("files[]");	// jsp에서 input type file의 name
	List<Map<String, String>> data = null;
	try {
		String pw = "password12";
		String[] colM = {};    
        
		DecryptExcelFile def = new DecryptExcelFile(mf, colM);
		data = def.readFile(pw);	// password
	
		set.setResultCode(200);
		set.setResultMsg("엑셀업로드를 성공하였습니다.");
	} catch (Exception e) {
		if(!"".equals(e.getMessage()) && e.getMessage()!=null){
			set.setResultMsg(e.getMessage());
		}else{
			set.setResultMsg("엑셀업로드를 실패하였습니다.");
		}
		set.setResultCode(300);
		e.printStackTrace();
	}
	return gson.toJson(set);
}

 

 

 

- CmmnSet VO (참고)

 

public class CmmnSet {
	private int resultCode;
	private String resultMsg;
    
	public int getResultCode() {
		return resultCode;
	}
	public void setResultCode(int resultCode) {
		this.resultCode = resultCode;
	}
	public String getResultMsg() {
		return resultMsg;
	}
	public void setResultMsg(String resultMsg) {
		this.resultMsg = resultMsg;
	}
}

 

 

 

2. Read Excel Class 

 

엑셀은 2003(XLS), 2007(XLSX) 이후 버전에 따라 암호 푸는 방식이 다르다.

 

해당 클래스는 컨트롤러에서 버전 체크 없이 넘겨도 내부적으로 버전을 체크한 뒤에 데이터를 읽어서 List 형태로 리턴해주도록 구현한 클래스이다.

 

(1차로 만든 버전이라 부족한 부분이 많으니, 참고만 하시길)

 

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/*
 * 시트 1개 기준 데이터 읽음
 * 엑셀 헤더와 colM의 갯수와 순서는 일치해야함
 * 엑셀 헤더 ROW 갯수 기본값은 1개, 따로 설정이 필요하다면 setStRowsIndex 함수로 데이터 읽을 행 순서 세팅필요
 * */
public class DecryptExcelFile {
	
	private List<MultipartFile> mf;
	private String[] colM;
	private int stRows = 1;
	
	// 생성자함수 : 프런트에서 던진 파일 객체, 엑셀 기준 컬럼 모델(컬럼명 배열)
	public DecryptExcelFile(List<MultipartFile> multifile, String[] colM) {
		this.mf = multifile;
		this.colM = colM;
	}

	// 데이터 읽어오는 body rows 행 index 설정 (선택)
	public void setStRowsIndex(int i) {
		this.stRows = i;
	}
	
	public List<Map<String, String>> readFile(String password) throws Exception {
		
		List<Map<String, String>> data = null;
		
		if(this.mf != null && this.mf.size() > 0) {
			for(int i=0; i<this.mf.size(); i++) {
				String filenm = this.mf.get(i).getOriginalFilename();

				try {
					if(filenm.indexOf("xlsx") != -1) { // 확장자 2007 이후 버전
						NPOIFSFileSystem fs= new NPOIFSFileSystem(mf.get(i).getInputStream());
						EncryptionInfo info = new EncryptionInfo(fs);
						Decryptor d = Decryptor.getInstance(info);
	
						try {
							if (d.verifyPassword(password)) {
								Workbook workbook = new XSSFWorkbook(d.getDataStream(fs));
					            data = readWorkbook(workbook, "XSSF");
							} else {	// 패스워드 일치하지 않음
								throw new Exception(filenm+" 파일의 패스워드가 일치하지 않습니다.");
							}
						} catch (GeneralSecurityException e) {
							throw e;
						}
					} else { // xls 확장자 2003 이전 버전
						try {
							NPOIFSFileSystem fs = new NPOIFSFileSystem(mf.get(i).getInputStream());
							Biff8EncryptionKey.setCurrentUserPassword(password);
							Workbook workbook = new HSSFWorkbook(fs);
				            data = readWorkbook(workbook, "HSSF");
						} catch(EncryptedDocumentException e) {
							throw new Exception(filenm+" 파일의 패스워드가 일치하지 않습니다.");
						}
					}
				} catch (IOException e) {
					throw e;
				}
			}
		}
		return data;
	}

	// xls, xlsx 공통 SHEET 데이터 읽어오기
	private List<Map<String, String>> readWorkbook(Workbook wb, String version) {

		List<Map<String, String>> data = new ArrayList<Map<String, String>>();
		
		Sheet sheet = wb.getSheetAt(0);
		Iterator<Row> rowIter = sheet.rowIterator();
        
		int rowIdx = 0;
		while (rowIter.hasNext()) {
			Map<String, String> obj = new HashMap<String, String>();
        	
			Row row = (version.equals("XSSF") ? (XSSFRow) rowIter.next() : (HSSFRow) rowIter.next());
			Iterator<Cell> cellIterator = row.cellIterator();

			if(rowIdx >= this.stRows) {	// header 제외
				int idx = 0;
				while (cellIterator.hasNext()) {
					String cellvalue = "";
					Cell cell = cellIterator.next();
					if(cell.getCellTypeEnum() == CellType.STRING){
						cellvalue = "" + cell.getStringCellValue();
					} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
						cellvalue = "" + cell.getNumericCellValue();
					} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
						cellvalue = "" + cell.getBooleanCellValue();
					} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
						cellvalue = "" + cell.getCellFormula();
					}	
                        
					obj.put(colM[idx], cellvalue);
					idx ++;
				}
				data.add(obj);
			}
			rowIdx ++;
		}
		
		return data;
		
	}
}

 

 

만약 파라메터로 파일 객체를 받아서 처리하는 것이 아니라면

 

NPOIFSFileSystem fs = new NPOIFSFileSystem(mf.get(i).getInputStream());

 

대신 아래와 같이 처리하도록 코드 수정하면 된다.

 

NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("엑셀파일경로")); 

 

 

3. 데이터 결과 예시

 

- 엑셀 데이터 (1개 파일)

 

 

 

- 컨트롤러 colM 설정 및 반환 데이터 결과

 

String pw = "password12";
String[] colM = {"name", "age", "male", "height", "weight"};    

DecryptExcelFile def = new DecryptExcelFile(mf, colM);
data = def.readFile(pw);	// password
System.out.println(data.toString());

 

 

- 출력결과

 

▼▼

▼▼

[{
	name=홍길동, age=20, male=여, height=162, weight=45
 },{
 	name=김철수, age=10, male=여, height=155, weight=36
 },{
 	name=장영희, age=15, male=남, height=160, weight=48
 },{
 	name=김태희, age=32, male=여, height=163, weight=55
 },{
 	name=정우선, age=41, male=남, height=172, weight=67
 },{
 	name=원빔, age=33, male=여, height=166, weight=61
}]

 

 

 

* Reading Password-protected Excel 2003 Document (XLS)

* Reading Password-protected Excel 2007 Document (XLSX)