[JAVA] Excel multi-sheet upload poi 4.1.2
The Excel file extension tested is xlxs
I know that poi 4.1.2 version is possible for both xls and xlxs, but the test was conducted with xlxs
First, pom.xml. Defendency
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
JSP
<form id="uploadFrm" method="post" action="<c:url value='/excelUpload.do'/>" enctype="multipart/form-data">
<input type="file" name="upFile" id="upFile" accept=".xlsx, .xls" class="buttonFileup"/>
<input type="button" onclick="uploadExcel()" class="button03" value="엑셀 파일 업로드">
</form>
Of course, enable type is "multipart/form-data" and when you click the button in jsp, call the uploadExcel() function to attach the file and call /excelUpload.do
function uploadExcel(){
if($("#upFile").val() == ''){
alert("Please attach the excel file to be uploaded first.");
}else if( $("#upFile").val() != "" ){
var ext = $('#upFile').val().split('.').pop().toLowerCase();
if($.inArray(ext, ['xls','xlsx']) == -1) {
alert('File name cannot be registered. Please select Exe file only.');
$("#upFile").val(""); // input file Clears the file name again.
return false;
}else{
var frm = $("#uploadFrm");
frm.attr("action", "<c:url value='/excelUpload.do'/>");
frm.submit();
}
}
}
Controller
For sheetNum, turn for statements in the order of 0, 1, 2, set them in VO, and save them to DB
It is better to set the excel form file to be uploaded as one..
Often, null checks or empty checks do not work exactly for each file
In my case, if the value of the Excel cell is empty, it sometimes goes to false..
@RequestMapping(value = "/excelInsertUpload")
public ModelAndView mngSaupManageExcelUpload(MultipartHttpServletRequest request, HttpServletResponse response, HttpSession session, ExcelVO excelVO) throws IOException {
DecimalFormat decFormat = new DecimalFormat("###,###");
Iterator<String> iterator = request.getFileNames();
int actResult = 0;
int actResultDatail = 0;
ModelAndView mav = new ModelAndView();
response.setContentType("text/html");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
// 관리 메인이 등록된 것이라면 // boardNo 와 년도로 체크
int actResultChk = bizBoardService.mngBizSupportAmountInsertChk(excelVO);
String value = "";
MultipartFile mFile = null;
while (iterator.hasNext()) {
String uploadFileName = iterator.next();
mFile = request.getFile(uploadFileName);
String originFileName = mFile.getOriginalFilename();
String saveFileName = originFileName;
}
XSSFWorkbook workbook = new XSSFWorkbook(mFile.getInputStream());
for(int sheetNum=0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
XSSFSheet sheet = workbook.getSheetAt(sheetNum);
int rows = sheet.getPhysicalNumberOfRows();
int rowindex = 0;
int columnindex = 0;
if(sheetNum == 0) {
for(rowindex = 2; rowindex < rows; rowindex++){
sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(rowindex);
if(row != null){
int cells = row.getPhysicalNumberOfCells();
for(columnindex=0; columnindex<=cells; columnindex++) {
XSSFCell cell = row.getCell(columnindex);
if(cell==null) {
continue;
}else{
switch(cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = (int) cell.getNumericCellValue()+"";
break;
case STRING:
value = cell.getStringCellValue()+"";
break;
case BLANK:
value = cell.getBooleanCellValue()+"";
break;
case ERROR:
value = cell.getErrorCellValue()+"";
break;
default:
}
// 공통값
excelVO.setBoardDataNo(excelVO.getDataNo());
excelVO.setDeleteStatus("N");
if(columnindex == 0) {
excelVO.setYear(value);
excelVO.setExpYear(value);
}else if(columnindex == 1) {
excelVO.setWriterName(value);
}else if(columnindex == 2) {
if(value == null || "".equals(value) || "N".equals(value)) {
excelVO.setSupDepartment("");
}else {
excelVO.setSupDepartment(value);
}
}else if(columnindex == 3) {
excelVO.setGubunA(value);
}else if(columnindex == 4) {
if(value == null || "".equals(value) || "0".equals(value)) {
excelVO.setGubunAetc("");
}else {
excelVO.setGubunAetc(value);
}
}else if(columnindex == 5) {
excelVO.setGubunE(value);
}else if(columnindex == 6) {
excelVO.setGubunB(value);
}else if(columnindex == 7) {
if(value == null || "".equals(value) || "0".equals(value)) {
excelVO.setGubunBetc("");
}else {
excelVO.setGubunBetc(value);
}
}else if(columnindex == 8) {
excelVO.setGubunC(value);
}else if(columnindex == 9) {
if(value == null || "".equals(value) || "0".equals(value)) {
excelVO.setGubunCetc("");
}else {
excelVO.setGubunCetc(value); /
}
} // else if 끝
} // cell==null 의 else
} // columnindex=0; for 문의 끝
actResult = firstInsertService.firstInsertAct(excelVO);
} // if(row != null) 의 끝
} // rowindex = 2; for 문의 끝
// if sheetNum == 0 끝
// 두번째 시트
}if(sheetNum == 1) {
for(rowindex = 2; rowindex < rows; rowindex++){
sheet = workbook.getSheetAt(1);
XSSFRow row = sheet.getRow(rowindex);
if(row != null){
int cells = row.getPhysicalNumberOfCells();
for(columnindex=0; columnindex<=cells; columnindex++) {
XSSFCell cell = row.getCell(columnindex);
if(cell==null) {
continue;
}else{
switch(cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = (int) cell.getNumericCellValue()+"";
break;
case STRING:
value = cell.getStringCellValue()+"";
break;
case BLANK:
value = cell.getBooleanCellValue()+"";
break;
case ERROR:
value = cell.getErrorCellValue()+"";
break;
default:
}
excelVO.setDeleteStatus("N");
if(columnindex == 0) {
if(value == null || "".equals(value)) {
excelVO.setdetailField("");
}else {
excelVO.setdetailField(value);
}
}else if(columnindex == 1) {
if(value == null || "".equals(value)) {
excelVO.setdetailFieldCategory("");
}else {
excelVO.setdetailFieldCategory(value);
}
}else if(columnindex == 2) {
if(value == null || "".equals(value) || "0".equals(value)) {
excelVO.setdetailGoalNum("");
}else {
excelVO.setdetailGoalNum(value);
}
}else if(columnindex == 3) {
if(value == null || "".equals(value)) {
excelVO.setdetailActResult("");
}else {
excelVO.setdetailActResult(value);
}
}
} // cell==null 의 else
} // columnindex=0; for 문의 끝
actResultDatail = secondInsertService.secondExcelInsertTopAct(excelVO);
} // if(row != null) 의 끝
} // rowindex = 2; for 문의 끝
}if(sheetNum == 2) {
for(rowindex = 2; rowindex < rows; rowindex++){
sheet = workbook.getSheetAt(2);
XSSFRow row = sheet.getRow(rowindex);
if(row != null){
int cells = row.getPhysicalNumberOfCells();
for(columnindex=0; columnindex<=cells; columnindex++) {
XSSFCell cell = row.getCell(columnindex);
if(cell==null) {
continue;
}else{
switch(cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = (int) cell.getNumericCellValue()+"";
break;
case STRING:
value = cell.getStringCellValue()+"";
break;
case BLANK:
value = cell.getBooleanCellValue()+"";
break;
case ERROR:
value = cell.getErrorCellValue()+"";
break;
default:
}
excelVO.setDeleteStatus("N");
if(columnindex == 0) {
if(value == null || "".equals(value)) {
excelVO.setdetailPeriodTerm("");
}else {
if( DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
excelVO.setdetailPeriodTerm(value);
}
}
}else if(columnindex == 1) {
if(value == null || "".equals(value) || "0000-00-00".equals(value)) {
excelVO.setdetailPeriodTermTwo("");
}else {
if( DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
excelVO.setdetailPeriodTermTwo(value);
}
}
}else if(columnindex == 2) {
if(value == null || "".equals(value)) {
excelVO.setdetailContents("");
}else {
excelVO.setdetailContents(value);
}
}else if(columnindex == 3) {
if(value == null || "".equals(value)) {
excelVO.setdetailNote("");
}else {
excelVO.setdetailNote(value);
}
}
} // cell==null 의 else
} // columnindex=0; for 문의 끝
actResultDatail = thirdInsertService.thirdExcelInsertBomAct(excelVO);
} // if(row != null) 의 끝
} // rowindex = 2; for 문의 끝
}if(sheetNum == 3) {
for(rowindex = 2; rowindex < rows; rowindex++){
sheet = workbook.getSheetAt(3);
XSSFRow row = sheet.getRow(rowindex);
if(row != null){
int cells = row.getPhysicalNumberOfCells();
for(columnindex=0; columnindex<=cells; columnindex++) {
XSSFCell cell = row.getCell(columnindex);
if(cell==null) {
continue;
}else{
switch(cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = (int) cell.getNumericCellValue()+"";
break;
case STRING:
value = cell.getStringCellValue()+"";
break;
case BLANK:
value = cell.getBooleanCellValue()+"";
break;
case ERROR:
value = cell.getErrorCellValue()+"";
break;
default:
}
if(columnindex == 0) {
if(value == null || "".equals(value)) {
excelVO.setdetailCorpName("");
}else {
excelVO.setdetailCorpName(value);
}
}else if(columnindex == 1) {
if(value == null || "".equals(value)) {
excelVO.setdetailDate("");
}else {
if( DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
excelVO.setdetailDate(value);
}
}
}else if(columnindex == 2) {
if(value == null || "".equals(value)) {
excelVO.setdetailHistory("");
}else {
excelVO.setdetailHistory(value);
}
}
} // cell==null 의 else
} // columnindex=0; for 문의 끝
actResultDatail = fourthInsertService.fourthExcelInsertLastAct(excelVO);
} // if(row != null) 의 끝
} // rowindex = 2; for 문의 끝
}
}
return null;
😀
Thank you!!
고마워!!
Comments
Post a Comment