[JAVA] POI - SAX 엑셀 대용량 업로드

 

서비스 단

대용량 엑셀파일을 읽어드리고 insert 하는 서비스

public int xlsxUploadSaxBatch(File file, final MultipartHttpServletRequest request, HttpServletResponse response) throws Exception {

		OPCPackage opcPackage = OPCPackage.open(file);  // file
        	// OPCPackage opcPackage = OPCPackage.open(file.getInputStream());  // MultipartFile
		XSSFReader xssfReader = new XSSFReader(opcPackage);
        
		StylesTable styles = xssfReader.getStylesTable(); //excel 스타일 
		SharedStringsTable strings = xssfReader.getSharedStringsTable(); //excel 중복된 문자열 공유
        
        	XMLReader parser = XMLReaderFactory.createXMLReader(); //SAX 파서생성
		
		ExcelSheetHandler handler = new ExcelSheetHandler(styles, strings); 
		
		parser.setContentHandler(handler);

		XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
       
       
       while (sheets.hasNext()) {
            InputStream sheetInputStream = sheets.next();
            InputSource sheetSource = new InputSource(sheetInputStream);
            parser.parse(sheetSource);
            sheetInputStream.close();
        }
        
        opcPackage.close();
        
        List<List<String>> excelData = handler.getExcelData(); // 핸들러 getData 메서드호출
        
        List<Map<String, String>> paramList = new ArrayList<>();
        
        int batchSize = 2100; // 한 번에 처리할 데이터의 개수
        
        for (int i = 0; i < excelData.size(); i++) {
        	List<String> row = excelData.get(i);
            
            Map<String, String> paramMap = new HashMap<>();
            
            paramMap.put("row1",row.get(0));
            paramMap.put("row2",row.get(1));
            paramMap.put("row3",row.get(2));
            paramMap.put("row4",row.get(3));
            
            paramList.add(paramMap); // paramMap을 복제하여 추가
            
            if ((i+1) % batchSize == 0 || (i+1) == excelData.size()) {
                result = query.insert("unboxing_query.insertExcelSax", paramList);
                paramList.clear(); // paramList를 초기화
            }
            
        }
        
        return result;
        
}

 

 

핸들러

DefaultHandler 클래스 상속 받아서 구현

DefaultHandler 클래스를 상속 받은 SheetContentsHandler를 사용해도 된다.

package com.unboxing.it.core.common.util;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
import org.xml.sax.helpers.DefaultHandler;


public class ExcelSheetHandler extends DefaultHandler{
    private SharedStringsTable strings;
    private boolean isCell;
    private boolean sstCell; //SharedStringsTable 사용여부 체크
    private StringBuilder cellValue;
    private String cellRef;
    private List<List<String>> excelData;
    private List<String> cellRow;
    private int headerCnt;
    private int cellIndex;
    private int currentRowNum;
    private int cellHeadIndex = 1; //엑셀 헤더행
    private int cellStartIndex = 2; // 엑셀 업로드 행
	
     public ExcelSheetHandler(StylesTable styles, SharedStringsTable strings) {
         this.strings = strings;
         this.isCell = false;
         this.sstCell = false;
         this.cellValue = new StringBuilder();
         this.cellRef = "";
         this.excelData = new ArrayList<>();
         this.currentRowNum = 0;
     }
     
     @Override
     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXParseException {
    	 if (name.equals("row")) {
    	        // "row" 엘리먼트가 시작되면 현재 행 번호를 가져옴
    	        String rowNumStr = attributes.getValue("r");
    	        if (rowNumStr != null) {
    	            currentRowNum = Integer.parseInt(rowNumStr);
    	        }
	    }else if (currentRowNum == cellHeadIndex && name.equals("c")){
	    	headerCnt++;  // Excel의 헤더 개수
	    }else if (currentRowNum >= cellStartIndex && name.equals("c")) {
	    		
    	        // 2번째 행 이후의 "c" 엘리먼트가 시작되면 셀로 처리
    	        isCell = true;
    	        cellValue.setLength(0);
    	        
    	        // 셀의 내용을 확인
    	        String cellType = attributes.getValue("t"); // 셀의 데이터 타입
    	        cellRef = attributes.getValue("r").replaceAll("\\d",""); //header 열
    	        
    	        if (cellType != null && cellType.equals("s")) {
    	            // "s" 타입은 SharedStringsTable
    	        	sstCell = true;
    	        } else {
    	        	sstCell = false;  //일반형식
    	        }
	    }
     }

     @Override
     public void endElement(String uri, String localName, String name) throws SAXException {
    	 if (name.equals("c")) {
    	        if (sstCell) {
    	        	 // SharedStringsTable 타입인 경우
    	        	if(!cellValue.toString().equals("")) {
    	        		int sharedStringIndex = Integer.parseInt(cellValue.toString()); 
    	        		CTRst xmlData = strings.getEntryAt(sharedStringIndex);
    	        		 
                        cellRow.set(cellIndex,xmlData.getT());
    	        	}
    	        }else if(!sstCell) {
    	        	if(!cellValue.toString().equals("")) {
	    	        	Double numericValue = Double.parseDouble(cellValue.toString());
	    	        	if( numericValue < 0) {
	    	        		cellRow.set(cellIndex,String.valueOf(numericValue));
	    	        	}else {
	    	        		cellRow.set(cellIndex,cellValue.toString());
	    	        	}
    	        	}
    	        }

    	        isCell = false;
    	        sstCell = false;
	    }else if (name.equals("row")) {
	    		if(currentRowNum == cellHeadIndex){
	    			cellRow = new ArrayList<>(Collections.nCopies(headerCnt, "")); // 헤더행일 경우 List size 설정
	    		}else if (currentRowNum >= cellStartIndex) {
    	            excelData.add(new ArrayList<>(cellRow));
    	        }
    	        
    	        Collections.fill(cellRow, ""); //행 추가 이후 cellRow 다시 초기화
	    }
     }

     @Override
     public void characters(char[] ch, int start, int length) throws SAXException {
    	 if (isCell) {
    		 
    		 //셀 열위치 ( List의 위치 )
    		 cellIndex = getIndexFromLetter(cellRef);
    		 
    		 //셀내용
    		 String cellContent = new String(ch, start, length);
    		 cellValue.append(cellContent);
             
         }
     }
     
     // 문자열을 받아서 A부터 시작하는 인덱스로 변환하는 메서드
     private static int getIndexFromLetter(String letter) {
         char c = letter.charAt(0);
         return c - 'A'; // 'A'를 0으로 매핑
     }
     
     public List<List<String>> getExcelData() {
         return excelData;
     }
}

 

 

xml ( postgres ) 

> bulk insert 

<insert id="insertExcelSax">
/* unboxing_query.insertExcelSax */
INSERT INTO unboxing_table
( column1 , column2 , column3 , column4)
VALUES
	<foreach collection="list" item="item" open="(" close=")" index="index">

		<if test="index % 300 == 0 and index != 0">
		     <!-- 매 300번째 반복마다 commit 수행 -->
		     )
		     <![CDATA[; COMMIT;]]>
		     <!-- 새로운 INSERT 구문 시작 -->
		     <![CDATA[
		     INSERT INTO unboxing_table
		     (column1 , column2 , column3 , column4)
		     VALUES(
		     ]]>
		</if> 
		<if test="index % 300 != 0">
		      <![CDATA[), (]]>
		</if>
		
		 #{item.row1}
		,#{item.row2}
		,#{item.row3}
		,#{item.row4}	
	</foreach>
	
	 <if test="list.size() % 300 != 0">
	 	<![CDATA[; COMMIT;]]>
	</if>
 <!-- 남은 데이터에 대한 commit 수행 -->
     
</insert>

 

 

 

엑셀 데이터 100만건 기준 3분 소요