1. pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency>
2. ExcelSheetHandler
package file.common.util; import java.io.File; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.util.CellReference; import org.apache.poi.util.SAXHelper; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFComment; import org.xml.sax.ContentHandler; import org.xml.sax.InputSource; import org.xml.sax.XMLReader; public class ExcelSheetHandler implements SheetContentsHandler{ private int currentCol = -1; private int currRowNum = 0; String filePath = ""; private List<List<String>> rows = new ArrayList<List<String>>(); //실제 엑셀을 파싱해서 담아지는 데이터 private List<String> row = new ArrayList<String>(); private List<String> header = new ArrayList<String>(); public static ExcelSheetHandler readExcel(File file) throws Exception{ ExcelSheetHandler sheetHandler = new ExcelSheetHandler(); try{ //org.apache.poi.openxml4j.opc.OPCPackage OPCPackage opc = OPCPackage.open(file); //org.apache.poi.xssf.eventusermodel.XSSFReader XSSFReader xssfReader = new XSSFReader(opc); //org.apache.poi.xssf.model.StylesTable StylesTable styles = xssfReader.getStylesTable(); //org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc); //엑셀의 시트를 하나만 가져오기입니다. //여러개일경우 while문으로 추출하셔야 됩니다. InputStream inputStream = xssfReader.getSheetsData().next(); //org.xml.sax.InputSource InputSource inputSource = new InputSource(inputStream); //org.xml.sax.Contenthandler ContentHandler handle = new XSSFSheetXMLHandler(styles, strings, sheetHandler, false); XMLReader xmlReader = SAXHelper.newXMLReader(); xmlReader.setContentHandler(handle); xmlReader.parse(inputSource); inputStream.close(); opc.close(); }catch(Exception e){ //에러 발생했을때 하시고 싶은 TO-DO } return sheetHandler; }//readExcel - end public List<List<String>> getRows(){ return rows; } @Override public void startRow(int arg0){ this.currentCol = -1; this.currRowNum = arg0; } @Override public void cell(String columnName, String value, XSSFComment var3){ int iCol = (new CellReference(columnName)).getCol(); int emptyCol = iCol - currentCol -1; for(int i=0; i< emptyCol; i++){ row.add(""); } currentCol = iCol; row.add(value); } @Override public void headerFooter(String arg0, boolean arg1, String arg2){ //사용안합니다. } @Override public void endRow(int rowNum){ if(rowNum == 0){ header = new ArrayList(row); } else{ if(row.size() < header.size()){ for(int i = row.size(); i<header.size(); i++){ row.add(""); } } rows.add(new ArrayList(row)); } row.clear(); } }
3. class를 호출하는 로직
// 엑셀 데이터 양식 example / A열 B열 1행 test@naver.com Seoul 2행 mouse@gmail.com Busan 3행 apple@daum.net Jeju / //해당 파일은 업로드파일 String filePath = "test.xlsx"; File file = new File(filePath); ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(file); List<List<String>> excelDatas = excelSheetHandler.getRows(); //excelDatas >>>>> [[ test@naver.com, Seoul ],[ mouse@gmail.com, Busan ], [ apple@daum.net, Jeju ]] int iCol = 0; //컬럼 구분값 int iRow = 0; //행 구분값 for(List<String> dataRow : excelDatas){ for(String str : dataRow){ if(iCol == 0){ //test@naver.com System.out.println(str); } else if(iCol == 1){ //Seoul System.out.println(str); } iCol++; } iCol = 0; iRow = 0; }
-------------------------------------------------------
예)
컨트롤
1)
package file.web.controller; import java.io.File; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import file.common.util.ExcelSheetHandler; import file.common.util.PageMakerAndSearch; import file.web.common.CommonController; import file.web.domain.FileCategory; import file.web.domain.FileUploadVO; import file.web.domain.MasterTableVO; import file.web.domain.MemberVO; import file.web.service.FileUploadService; import file.web.service.MasterUpdateService; @Controller @RequestMapping(value="/masterUpdate/**") public class MasterUpdateController extends CommonController{ private static Logger log =LoggerFactory.getLogger(MasterUpdateController.class); private static final String URI="masterUpdate"; private static final String JSP_URI="file/"+URI+"/"; @Autowired private MasterUpdateService masterUpdateService; @Autowired private FileUploadService fileUploadService; @GetMapping(value="") public String index(MemberVO memberVO, FileCategory fileCategory, Model model) throws Exception{ commonMenu(URI, model); model.addAttribute("fileCategory", fileCategory.getArray()); return JSP_URI+URI+"_index"; } @RequestMapping(value="list.do") public String masterUpdatelist(PageMakerAndSearch pageMaker, HttpSession session, HttpServletRequest request, String tableCheck , Model model) throws Exception{ MemberVO memberVO=(MemberVO)session.getAttribute("LOGIN"); Map<String, Object> map=new HashMap<>(); map.put("tableCheck", tableCheck); map.put("userid", memberVO.getUserid()); Integer masterUpdateTotalCount=masterUpdateService.masterUpdateTotalCount(map); pageMaker.setTotalCount(masterUpdateTotalCount); map.put("pageStart", pageMaker.getPageStart()); map.put("perPageNum", pageMaker.getPerPageNum()); List<FileUploadVO> list =masterUpdateService.masterUpdateList(map); String pagination=pageMaker.checkedPagingAjax(request.getContextPath() +"/masterUpdate/list.do"); model.addAttribute("masterUpdateTotalCount", masterUpdateTotalCount); model.addAttribute("pageMaker", pageMaker); model.addAttribute("pagination", pagination); model.addAttribute("list", list); return JSP_URI+URI+"_list"; } @RequestMapping(value = "/dbInput.do", method = RequestMethod.POST) @ResponseBody public String ExcelUp(Integer fid, String tableCheck, HttpServletRequest request,HttpSession session, Model model) throws Exception{ MemberVO memberVO=(MemberVO)session.getAttribute("LOGIN"); FileUploadVO fileUploadVO= fileUploadService.getByfileInfo(fid); String rootPath=request.getSession().getServletContext().getRealPath("/")+"uploads"+File.separator; String realName=fileUploadVO.getUrlPath().replace('/', File.separatorChar); String path= rootPath+realName; //해당 파일은 업로드파일 String filePath = path; File file = new File(filePath); ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(file); List<List<String>> excelDatas = excelSheetHandler.getRows(); int iCol = 0; //컬럼 구분값 int iRow = 0; //행 구분값 List<MasterTableVO> masterTableList=new ArrayList<>(); //컬럼 추가 for(List<String> dataRow : excelDatas){ MasterTableVO masterTableVO=new MasterTableVO(); masterTableVO.setUserid(memberVO.getUserid()); for(String str : dataRow){ if(iCol == 0){ masterTableVO.setCol0(str); }else if(iCol == 1){ masterTableVO.setCol1(str); }else if(iCol == 2){ masterTableVO.setCol2(str); }else if(iCol==3) { masterTableVO.setCol3(str); }else if(iCol==4) { masterTableVO.setCol4(str); } iCol++; } masterTableList.add(masterTableVO); iCol = 0; iRow = 0; } if(tableCheck.equals("A")) { masterUpdateService.insertMsterTableA(masterTableList); }else if(tableCheck.equals("B")) { masterUpdateService.insertMsterTableB(masterTableList); }else if(tableCheck.equals("C")) { masterUpdateService.insertMsterTableC(masterTableList); }else if(tableCheck.equals("D")) { masterUpdateService.insertMsterTableD(masterTableList); } return "success"; } }
2) MasterTableVO
package file.web.domain; import lombok.Data; @Data public class MasterTableVO { private String tableCheck; private String userid; private String col0; private String col1; private String col2; private String col3; private String col4; }
public void insertMsterTableD(List<MasterTableVO> masterTableList)throws Exception { masterUpdateDao.insertMsterTableD(masterTableList); }
package file.web.dao; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Mapper; import org.springframework.stereotype.Repository; import file.web.domain.FileUploadVO; import file.web.domain.MasterTableVO; @Mapper @Repository public interface MasterUpdateDao { void insertMsterTableD(List<MasterTableVO> masterTableList) throws Exception; public List<FileUploadVO> masterUpdateList(Map<String, Object> map) throws Exception; public Integer masterUpdateTotalCount(Map<String, Object> map) throws Exception; }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="file.web.dao.MasterUpdateDao"> <select id="masterUpdateTotalCount" resultType="Integer"> SELECT count(*) FROM <include refid="tableSelect" /> WHERE userid=#{userid} </select> <select id="masterUpdateList" resultType="file.web.domain.MasterTableVO"> SELECT A.* FROM ( SELECT @rownum:=@rownum+1 as no , b.* FROM <include refid="tableSelect" /> b, (select @rownum:=0) tmp WHERE userid=#{userid} ORDER BY idx ASC ) AS A ORDER BY no DESC LIMIT #{pageStart}, #{perPageNum} </select> </mapper>
댓글 ( 4)
댓글 남기기