[Spring 강좌] Spring 에서 SAX 이용한 대용량 엑셀 읽기

1. ExcelReader.java

import java.io.File;
import java.io.InputStream;
 
import org.apache.poi.openxml4j.opc.OPCPackage;
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.model.StylesTable;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
 
public class ExcelReader {
 
  public static void main(String[] args) {
    // TODO Auto-generated method stub
       
 
//읽어올 엑셀파일 경로
    SheetHandler excelData = readExcel(new File("C:\\work\\excel\\mysample.xlsx"));
 
    System.out.println("Header List" + excelData.getHeader().size());
    System.out.println("Rows List" + excelData.getRows().size());
 
  }
 
   
  public static SheetHandler readExcel(File excelFile) {
 
   
      SheetHandler sheetHandler = new SheetHandler();
 
      try {
 
          OPCPackage pkg = OPCPackage.open(excelFile);
 
          XSSFReader xssfReader = new XSSFReader(pkg);
 
          ReadOnlySharedStringsTable data = new ReadOnlySharedStringsTable(pkg);
           
          StylesTable styles = xssfReader.getStylesTable();
 
 
          InputStream sheetStream = xssfReader.getSheetsData().next();
          InputSource sheetSource = new InputSource(sheetStream);
          
          ContentHandler handler = new XSSFSheetXMLHandler(styles, data, sheetHandler, false);
          
          XMLReader sheetParser = SAXHelper.newXMLReader();
 
          sheetParser.setContentHandler(handle
          sheetParser.parse(sheetSource);
          sheetStream.close();
        
      }catch (Exception e) {
        throw new RuntimeException(e);
      }
 
      return sheetHandler;
  }
   
}

 

2. SheetHandler.java

import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
public class SheetHandler implements SheetContentsHandler {
 
    private List<List<String>> rows = new ArrayList<>();
 
    private List<String> row = new ArrayList<>();
 
    private List<String> header = new ArrayList<>();
 
    private int currentCol = -1;
 
    private int currRowNum = 0;
     
    public List<String> getHeader() {
      return header;
    }
     
    public List<List<String>> getRows() {
      return rows;
    }
 
    public void startRow(int rowNum) {
 
        this.currentCol = -1;
        this.currRowNum = rowNum;
 
    }
 
    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();
    }
 
    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);
    }
 
    public void headerFooter(String text, boolean isHeader, String tagName) {
 
    }
 
}