poi 이용 : 한글 REF
영문: REF
1. maven dependency 추가.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-contrib</artifactId>
<version>3.6</version>
</dependency>
2. JSP
function fnCheckUpload() {
var file = $("#excel").val();
if(file == "" || file == null){
alert("먼저 파일을 선택하세요.");
return false;
}
var fileFormat = file.split(".");
var fileType = fileFormat[1];
if(confirm("업로드 하시겠습니까?")){
$('#excelUpForm').attr('action','/uploadMoveinExcel.do');
var options = {
fail:function(data) {
alert('저장 오류가 발행하였습니다.');
$('#popup').bPopup().close();
},
success:function(data){
alert(data + "건 업로드 완료");
$('#popup').bPopup().close();
},
type: "POST",
data : {"excelType" : fileType}
};
$('#excelUpForm').ajaxSubmit(options);
}
}
-----HTML--------
<form id="excelUpForm" method="post" action="" role="form" enctype="multipart/form-data">
<input id="excel" name="excel" class="file" type="file" >
</form>
3. controller
@ResponseBody
@RequestMapping(value="/uploadMoveinExcel.do")
public String uploadExcel( MultipartHttpServletRequest request, HttpSession session) {
List<ExcelVo> list = new ArrayList<>();
String excelType = request.getParameter("excelType");
log.info("uploadExcel.do + excelType:" + excelType);
if(excelType.equals("xls")){
list = moveinService.xlsExcelReader(request);
}else if(excelType.equals("xlsx")){
//IN_STUDY: list = adminAccountsMngService.xlsxExcelReader(req);
}
int cnt = service.uploadList(list);
return String.valueOf(cnt);//success cnt : media처리의 경우, JSON포맷으로 잘 안변함(config필요), 따라서 간단히 숫자만 리턴.
}
4. service
public List<MoveinExcelVo> xlsExcelReader(MultipartHttpServletRequest request) {
// 반환할 객체를 생성
List<MoveinExcelVo> list = new ArrayList<>();
MultipartFile file = request.getFile("excel");
HSSFWorkbook workbook = null;
try {
// HSSFWorkbook은 엑셀파일 전체 내용을 담고 있는 객체
workbook = new HSSFWorkbook(file.getInputStream());
// 0번째 sheet 반환
HSSFSheet curSheet = workbook.getSheetAt(0); //workbook.getNumberOfSheets()
// row 탐색 for문
boolean isInTable = false;
for (int rowIndex = 0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {
//헤더 Cell 찾기.
String firstCellValue = curSheet.getRow(rowIndex).getCell(0).getStringCellValue();
if ("구분".equals(firstCellValue.trim())) { //table헤더 발견.
isInTable = true;
continue; //헤더는 pass. (다움줊부터 paring시작.)
}
if (isInTable) { //in Table 파싱 시작. ///////////////////////////////////////////
HSSFRow curRow = curSheet.getRow(rowIndex);
//필수 Cell들.
String cellStr = curRow.getCell(2).getStringCellValue(); //String 읽기
String cellDate = StringUtil.toYYMMDD(curRow.getCell(5).getDateCellValue()); //날짜 읽기
String cellNum = curRow.getCell(7).getNumericCellValue() + ""; //산차.
String cellLongNUm = StringUtil.toIntString(curRow.getCell(12).getNumericCellValue() ; //엄청 긴 숫자 읽기
. . . .
} catch (IOException e) {
e.printStackTrace();
}
// vo로 만들면서 db에 삽입코드는 생략.
return list;
}
public class StringUtil {
private static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
public static String datePattern = "^\\d{4}[\\-](0[1-9]|1[012])[\\-](0[1-9]|[12][0-9]|3[01])$";
public static boolean isDate(String str) {
return Pattern.matches(datePattern, str);
}
//return YYYY-MM-DD String
public static String toYYMMDD(Date date) {
return formatter.format(date);
}
public static boolean isNumericOrFloat(String str) {
if (str == null) {
return false;
}
int sz = str.length();
for (int i = 0; i < sz; i++) {
if (Character.isDigit(str.charAt(i)) == false) {
if (str.charAt(i) != '.') //Added KY. '.'도 numeric으로 간주. float때문에.
return false;
}
}
return true;
}
//For Excel processing.
// 엑셀에선 숫자를 항상 double로 읽기 때문에 int형태의 String으로 변환하면 유용.
public static String toIntString(double doubleVal) {
try {
BigInteger k = new BigDecimal(doubleVal).toBigInteger();
return String.valueOf(k);
} catch (Exception e) {
return null;
}
}
