1. pom.xml에 의존성을 추가한다.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
2. 파일의 확장자를 비교하는 class를 만든다.
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelFileType {
public static Workbook getWorkbook(String filePath) {
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Workbook wb = null;
if (filePath.toUpperCase().endsWith(".XLS")) {
try {
wb = new HSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
} else if (filePath.toUpperCase().endsWith(".XLSX")) {
try {
wb = new XSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
}
return wb;
}
}
3. 파일을 읽어올 때 옵션을 설정하는 class를 만든다.
import java.util.ArrayList;
import java.util.List;
public class ExcelReadOption {
private String filePath;
private List<String> outputColumns;
private int startRow;
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public List<String> getOutputColumns() {
List<String> temp = new ArrayList<String>();
temp.addAll(outputColumns);
return temp;
}
public void setOutputColumns(String ... outputColumns) {
if(this.outputColumns == null) {
this.outputColumns = new ArrayList<String>();
}
for(String outputColumn : outputColumns) {
this.outputColumns.add(outputColumn);
}
}
public int getStartRow() {
return startRow;
}
public void setStartRow(int startRow) {
this.startRow = startRow;
}
}
4. 각 셀의 이름과 값을 가져오는 class를 만든다.
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
public class ExcelCellRef {
public static String getName(Cell cell, int cellIndex) {
int cellNum = 0;
if (cell != null) {
cellNum = cell.getColumnIndex();
} else {
cellNum = cellIndex;
}
return CellReference.convertNumToColString(cellNum);
}
public static String getValue(Cell cell) {
String value = "";
if (cell == null) {
value = null;
} else {
if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
value = cell.getCellFormula();
} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
value = (int)cell.getNumericCellValue() + "";
} else if( cell.getCellType() == Cell.CELL_TYPE_STRING ) {
value = cell.getStringCellValue();
} else if( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ) {
value = cell.getBooleanCellValue() + "";
} else if( cell.getCellType() == Cell.CELL_TYPE_ERROR ) {
value = cell.getErrorCellValue() + "";
} else if( cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
value = null;
} else {
value = cell.getStringCellValue();
}
}
return value;
}
}
5. 엑셀 파일을 읽는 class를 만든다.
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelRead {
public static List<Map<String, String>> read(ExcelReadOption excelReadOption) {
Workbook wb = ExcelFileType.getWorkbook(excelReadOption.getFilePath());
//첫번째 시트를 가져온다.
Sheet sheet = wb.getSheetAt(0);
//값이 입력된 행의 총 개수를 가져온다.
int numOfRows = sheet.getPhysicalNumberOfRows();
int numOfCells = 0;
Row row = null;
Cell cell = null;
String cellName = "";
Map<String, String> map = null;
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
for (int rowIndex = excelReadOption.getStartRow()-1; rowIndex < numOfRows; rowIndex++) {
row = sheet.getRow(rowIndex);
if (row != null) {
numOfCells = row.getLastCellNum();
map = new HashMap<String, String>();
for (int cellIndex = 0; cellIndex < numOfCells; cellIndex++) {
cell = row.getCell(cellIndex);
cellName = ExcelCellRef.getName(cell, cellIndex);
if (!excelReadOption.getOutputColumns().contains(cellName)) {
continue;
}
map.put(cellName, ExcelCellRef.getValue(cell));
}
result.add(map);
}
}
return result;
}
}
6. 화면단의 첨부파일을 선택하는 input 태그를 추가한다.
<fieldset class="attach insertFile">
<legend>첨부파일</legend>
<input type="text" readonly="readonly" title="File Route" id="file_route">
<label>불러오기
<input type="file" id="excelFile" name="excelFile"
onchange="javascript:document.getElementById('file_route').value=this.value">
</label>
</fieldset>
7. 선택한 파일이 엑셀인지 확인하는 함수와 ajax로 파일을 전송하는 함수를 스크립트단에 작성한다.
function fnSave() {
let file = $("#excelFile").val();
if (file == "" || file == null) {
alertBox("파일을 선택해주세요.");
return false;
}
else if (!fnCheckFileType(file)) {
alertBox("엑셀 파일만 업로드 가능합니다.");
return false;
}
if (confirm("업로드 하시겠습니까?")) {
let form = new FormData();
form.append("excelFile", excelFile.files[0]);
loadingImgShow();
$.ajax({
url: "/upload.do"
, type: "POST"
, processData : false
, contentType : false
, data: form
, success: function(jqxhr) {
if (jqxhr.statusCode != 500) {
opener.parent.location.reload();
window.close();
} else {
alertBox(jqxhr.responseMessage + "가 발생 했습니다. 관리자에게 문의 하세요");
}
}
, error: function(jqxhr, status, error) {
let msg = jqxhr.status + " , " + status + " , " + error;
alertBox(msg + "\n에러가 발생 했습니다. 관리자에게 문의 하세요");
},
complete : function(){
// loading
loadingImgHide();
}
})
}
}
function fnCheckFileType(file) {
let fileFormat = file.split(".");
if (fileFormat.indexOf("xlsx") > -1) {
return true;
} else {
return false;
}
}
이 때 첨부파일을 FormData에 append하여 전송하게 되는데 content-type을 form 태그로 전송할 때와 같이 multipart/form-data로 설정하면 안된다. FormData에 파일이 있을 경우 브라우저에서 자동으로 boundary를 붙여주게 되는데 ajax 전송 시 content-type을 설정하면 Override되어 boundary가 사라지게 된다. (no multipart boundary was found 발생)
8. controller를 작성한다.
@PostMapping("/upload.do")
@ResponseBody
public ResponseEntity<ApiResponse> upload(HttpServletRequest request,
@RequestParam("excelFile") MultipartFile file) {
int stCode = StatusCode.OK;
String reMsg = ResponseMessage.DATA_SUCCESS;
//업로드 파일 저장경로 확인 필요
File destFile = new File("D:\\" + file.getOriginalFilename());
try {
file.transferTo(destFile);
mmdService.excelUpload(destFile);
} catch (Exception e) {
e.printStackTrace();
stCode = StatusCode.INTERNAL_SERVER_ERROR;
reMsg = ResponseMessage.INTERNAL_SERVER_ERROR;
}
ApiResponse res = new ApiResponse(stCode, reMsg, "none", "none");
return new ResponseEntity<>(res, HttpStatus.OK);
}
9. 엑셀 파일을 읽고 DB에 저장하는 비즈니스 로직을 담은 service를 작성한다.
public void excelUpload(File destFile) throws Exception {
ExcelReadOption excelReadOption = new ExcelReadOption();
excelReadOption.setFilePath(destFile.getAbsolutePath());
excelReadOption.setOutputColumns("A","B","C","D");
excelReadOption.setStartRow(2);
List<Map<String, String>> excelContent = ExcelRead.read(excelReadOption);
ContentVO contentVO = new ContentVO();
//파싱한 각 컬럼명과 값을 객체에 담는 반복문
for(Map<String, String> row : excelContent) {
//java reflect를 이용하여 필드에 값을 set한다.
//첫번째 A 컬럼에서 가져온 이름에 해당하는 VO의 필드를 찾는다.
Field f = contentVO.getClass().getDeclaredField(row.get("A"));
//private으로 선언한 필드에 접근할 수 있도록 한다.
f.setAccessible(true);
//해당 필드에 네번째 D 컬럼에서 가져온 값을 넣는다.
f.set(contentVO, row.get("D"));
f.setAccessible(false);
}
//db에 저장하는 mapper 실행 함수
insertList(contentVO);
}
+++ 위험한 파일(.exe, .sh, .bat 등)이 서버에 업로드되는 것을 제한할 수 있게 파일 형식을 확인하는 코드를 추가했다. 클라이언트로부터 전달받은 Content-Type 값은 조작이 가능하므로 서버에서 파일의 형식을 판단해야 한다.
Apache Tika 라이브러리를 사용하였다.
<!-- https://mvnrepository.com/artifact/org.apache.tika/tika-core -->
<dependency>
<groupId>org.apache.tika</groupId>
<artifactId>tika-core</artifactId>
<version>2.8.0</version>
</dependency>
먼저 pom.xml에 의존성을 추가해준다.
Tika tika = new Tika();
String mimeType = tika.detect(file.getInputStream());
if (mimeType.equals("application/x-tika-ooxml")) {
String path = ResourceUtils.getFile("classpath:").getPath();
String fileName = file.getOriginalFilename();
fileName = fileName.replaceAll("[\\/]", "");
File destFile = new File(path + fileName);
file.transferTo(destFile);
result = service.excelUpload(destFile);
destFile.delete();
} else {
throw new Exception();
}
이번의 경우는 엑셀 파일만 업로드되어야 하기 때문에 "application/x-tika-ooxml" 로만 형식 확인을 했다.
6번 라인에는 올바른 식별자만 사용되도록 위험한 문자열(.., ", /, \ 등)을 제거하는 코드도 추가했다.
외부의 입력값을 사용할 때는 보안에 좀 더 철저할 필요가 있겠다.
