마카로닉스

1671 No

★ 스프링 엑셀 업로드

2019-12-06 22:04:13 By 라이트 0 3085 댓글수 4 포스트 하기

4월 2024
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

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;
}

-------------------------------------------------------

예)

컨트롤

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>