Excel파일 Read/Write

Read

1. pom.xml에 Excel Read/Write 를 위한 (poi library) Dependency 추가

    <!-- Excel Read/Write 를 위한 Dependency 추가 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.11</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.11</version>
    </dependency>

 

2. HuCloud -> src/main/java -> utilities -> excel -> read -> util -> FileType.java

엑셀파일을 읽어서 Workbook 객체에 리턴

package kr.co.hucloud.utilities.excel.read.util;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
// 확장자 찾는 클래스
public class FileType {
    
    /**
     * 
     * 엑셀파일을 읽어서 Workbook 객체에 리턴한다.
     * XLS와 XLSX 확장자를 비교한다.
     * 
     * @param filePath
     * @return
     * 
     */
    public static Workbook getWorkbook(String filePath) {
        
        /*
         * FileInputStream은 파일의 경로에 있는 파일을
         * 읽어서 Byte로 가져온다.
         * 
         * 파일이 존재하지 않는다면은
         * RuntimeException이 발생된다.
         */
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        
        Workbook wb = null;
        
        /*
         * 파일의 확장자를 체크해서 .XLS 라면 HSSFWorkbook에
         * .XLSX라면 XSSFWorkbook에 각각 초기화 한다.
         */
        if(filePath.toUpperCase().endsWith(".XLS")) {
            try {
                wb = new HSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
        else if(filePath.toUpperCase().endsWith(".XLSX")) {
            try {
                wb = new XSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
        
        return wb;
        
    }
 
    
}
 

 

3. HuCloud -> src/main/java -> utilities -> excel -> option -> ReadOption.java 

엑셀파일 읽기 옵션 설정

 

package kr.co.hucloud.utilities.excel.option;
 
import java.util.ArrayList;
import java.util.List;
 
public class ReadOption {
 
    /**
     * 엑셀파일의 경로
     */
    private String filePath;
    
    /**
     * 추출할 컬럼 명
     */
    private List<String> outputColumns;
    
    /**
     * 추출을 시작할 행 번호
     */
    private int startRow;
    
    public String getFilePath() {
        return filePath;
    }
    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }
    public List<String> getOutputColumns() {
        
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
        
        return temp;
    }
    public void setOutputColumns(List<String> outputColumns) {
        
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
        
        this.outputColumns = temp;
    }
    
    public void setOutputColumns(String ... outputColumns) {
        
        if(this.outputColumns == null) {
            this.outputColumns = new ArrayList<String>();
        }
        
        for(String ouputColumn : outputColumns) {
            this.outputColumns.add(ouputColumn);
        }
    }
    
    public int getStartRow() {
        return startRow;
    }
    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }
    
}
 

반응형

 

4. HuCloud -> src/main/java -> utilities -> excel -> read -> ExcelRead

엑셀파일을 읽어 들이는 클래스

package kr.co.hucloud.utilities.excel.read;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import kr.co.hucloud.utilities.excel.option.ReadOption;
import kr.co.hucloud.utilities.excel.read.util.CellRef;
import kr.co.hucloud.utilities.excel.read.util.FileType;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
 
public class ExcelRead {
    
    public static List<Map<String, String>> read(ReadOption readOption) {
        
        /*
         * 엑셀 파일 자체
         * 엑셀파일을 읽어 들인다.
         * FileType.getWorkbook() : 파일의 확장자를 구분해서 가져온다.
         */
        Workbook wb = FileType.getWorkbook(readOption.getFilePath());
        
        // 엑셀 파일에서 첫번째 시트를 가지고 온다.
        Sheet sheet = wb.getSheetAt(0);
        
        /*
         * 시트에서 유효한(데이터가 있는) 행의 갯수를 가져온다.
         */
        int numOfRows = sheet.getPhysicalNumberOfRows();
        int numOfCells = 0;
        
        Row row = null;
        Cell cell = null;
        
        String cellName = "";
        
        // key : 컬럼 / value: 데이터
        /*
         * 각 Row마다의 값을 저장할 맴 객체
         * 저장되는 형식은 다음과 같다
         * put("A","이름");
         * put("B","게임명");
         */
        Map<String, String> map = null;
        
        /*
         * 각 Row를 리스트에 담는다
         * 하나의 Row는 하나의 Map으로 표현되며
         * List에는 모든 Row가 포함될 것이다.
         */
        List<Map<String, String>> result = new ArrayList<Map<String, String>>(); 
        
        /*
         * Row만큼 반복을 한다.
         */
        for(int rowIndex = readOption.getStartRow() - 1; rowIndex < numOfRows; rowIndex++) {
            
            /*
             * 워크북에서 가져온 시트에서 rowIndex에 해당하는 Row를 가져온다.
             * 하나의 Row는 여러개의 Cell을 가진다.
             */
            row = sheet.getRow(rowIndex);
            
            if(row != null) {
                
                // 유효한 셀의 갯수
                /*
                 * 가져온 Row의 Cell의 갯수를 구한다.
                 */
                numOfCells = row.getPhysicalNumberOfCells();
                
                /*
                 * 데이터를 담을 맵 객체 초기화
                 */
                map = new HashMap<String, String>();
                
                /*
                 * Cell의 수 만큼 반복
                 */
                for(int cellIndex = 0; cellIndex < numOfCells; cellIndex++) {
                    
                    /*
                     * Row에서 CellIndex에 해당하는 Cell을 가져온다.
                     */
                    cell = row.getCell(cellIndex);
                    
                    /*
                     * 현재 Cell의 이름을 가져온다.
                     * 이름의 예 : A,B,C,D,E..... 
                     */
                    cellName = CellRef.getName(cell, cellIndex);
                    
                    /*
                     * 추출 대상 컬럼인지 확인한다.
                     * 추출 대생 컬럼이 아니라면
                     * for로 다시 올라간다. 
                     */
                    if( !readOption.getOutputColumns().contains(cellName) ) {
                        continue;
                    }
                    
                    /*
                     * 맵 객체의 Cell의 이름을 키(Key)로 데이터를 담는다.
                     */
                    map.put(cellName, CellRef.getValue(cell));
                }
                
                /*
                 * 만들어진 Map 객체를 List에 넣는다.
                 */
                result.add(map);
                
            }
            
        }
        
        return result;
        
    }
    
    
    
    public static void main(String[] args) {
        
        ReadOption ro = new ReadOption();
        ro.setFilePath("D:/game.xlsx");
        ro.setOutputColumns("A", "B");
        ro.setStartRow(1);
        
        // Map을 리스트로 만들기
        List<Map<String, String>> result = ExcelRead.read(ro);
        
        for(Map<String, String> map : result) {
            System.out.println(map.get("A"));
        }
    }
    
}
 

 

Write

BoardController.java

@RequestMapping("/board/massiveWrite")
    public ModelAndView writeMassiveArticle(MultipartHttpServletRequest request){
        
        MultipartFile excelFile = request.getFile("excelFile");
        if(excelFile==null || excelFile.isEmpty()){
            throw new RuntimeException("엑셀파일을 선택해 주세요");
        }
 
        File destFile = new File("D:\\"+excelFile.getOriginalFilename());
        try {
            excelFile.transferTo(destFile);
        } catch (IllegalStateException | IOException e) {
            throw new RuntimeException(e.getMessage(),e);
 
        }
        
        boardService.insertMassiveArticleInBoard(destFile);
        
        FileUtils.deleteFile(destFile.getAbsolutePath());
        
        ModelAndView view = new ModelAndView();
        view.setViewName("redirect:/board/list");
        return view;
    }

 

BoardService.java

 public void insertMassiveArticleInBoard(File destFile);
 
 
 

BoardServiceImpl.java

@Override
 public void insertMassiveArticleInBoard(File destFile) {
 
  ReadOption readOption = new ReadOption();
  readOption.setFilePath(destFile.getAbsolutePath());
  readOption.setOutputColumns("A","B","C","D");
  readOption.setStartRow(2);
  
  List<Map<String, String>> excelContent = ExcelRead.read(readOption);
  
  BoardVO boardVO = null;
  for(Map<String, String> article : excelContent){
   
   boardVO = new BoardVO();
   boardVO.setSubject(article.get("A"));
   boardVO.setContent(article.get("B"));
   boardVO.setUserId(article.get("C"));
   boardVO.setFileName(article.get("D"));
 
   this.insertArticleInBoard(boardVO);
  }
 }
 

 

list.jsp 에 form 추가

    <!-- excel file 읽어오기 -->
    <form id="massiveForm" name="massiveForm" enctype="multipart/form-data" method="post" action="<c:url value="/board/massiveWrite"/>" >
        <input type="file" name="excelFile"/>
        <input type="submit" value="업로드"/> 
    </form>

 

실행화면

 

게시판 List를 Read해서 엑셀 파일에 Write하기

BoardController.java

@RequestMapping("/board/list/excel")
       public void excelExport(){
          
          ArticleSearchVO articleSearch = null;
          if(articleSearch == null) {
             articleSearch = new ArticleSearchVO("0", "", "");
          }
          
          BoardListVO boardList = boardService.getArticleInBoardByCondition(articleSearch);
          List<BoardVO> articleList = boardList.getList();
          
          WriteOption writeOption = new WriteOption();
          writeOption.setFileName("articleList.xlsx");
          writeOption.setSheetName("ARTICLE");
          List<String> titles = new ArrayList<String>();
          titles.add("num");
          titles.add("subject");
          titles.add("author");
          titles.add("hit");
          titles.add("recommend");
          titles.add("created date/modified date");
          
          writeOption.setTitles(titles);
          
          List<String[]> contents = new ArrayList<String[]>();
          String[] row = new String[6];
          
          for(BoardVO boardVO : articleList){
             row = new String[6];
             
             row[0] = boardVO.getId() + "";
             row[1] = boardVO.getSubject();
             row[2] = boardVO.getUserName();
             row[3] = boardVO.getHit() + "";
             row[4] = boardVO.getRecommend() + "";
             row[5] = boardVO.getCreatedDate() +"/"+ boardVO.getModifiedDate();
             
             contents.add(row);
          }
          
          writeOption.setContents(contents);
          ExcelWrite.write(writeOption);
          
          
       }

 

ExcelWrite.java

package kr.co.hucloud.utilities.excel.write;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
 
import kr.co.hucloud.utilities.excel.option.WriteOption;
import kr.co.hucloud.utilities.excel.write.util.FileType;
import kr.co.hucloud.utilities.file.FileUtils;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
 
public class ExcelWrite {
 
    //게시판 List D 드라이브에 Write하기 위해 설정
    private static String downloadPath = "D:\\";
    
    private static Sheet sheet;
    private static int rowIndex;
    
    public static File write(WriteOption writeOption) {
        
        Workbook wb = FileType.getWorkbook(writeOption.getFileName());
        sheet = wb.createSheet(writeOption.getSheetName());
        
        setTitle(writeOption.getTitles());
        setContents(writeOption.getContents());
        
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(downloadPath + writeOption.getFileName());
            wb.write(fos);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        finally {
            if(fos != null) {
                try {
                    fos.flush();
                    fos.close();
                } catch (IOException e) {}
            }
        }
        
        return getFile(writeOption.getFileName());
    }
    
    private static void setTitle(List<String> values) {
        
        Row row = null;
        Cell cell = null;
        
        int cellIndex = 0;
        
        if( values != null && values.size() > 0 ) {
            row = sheet.createRow(rowIndex++);
            for(String value : values) {
                cell = row.createCell(cellIndex++);
                cell.setCellValue(value);
            }
        }
        
    }
    
    private static void setContents(List<String[]> values) {
        
        Row row = null;
        Cell cell = null;
        
        int cellIndex = 0;
        
        if( values != null && values.size() > 0 ) {
            
            for(String[] arr : values) {
                row = sheet.createRow(rowIndex++);
                cellIndex = 0;
                for(String value : arr) {
                    cell = row.createCell(cellIndex++);
                    cell.setCellValue(value);
                }
            }
        }
        
    }
    
    private static File getFile(String fileName) {
        return new File(downloadPath + fileName);
    }
    
    public static void remove(File file) {
        FileUtils.deleteFile(file.getAbsolutePath());
    }
    
    
    
    public static void main(String[] args) {
        WriteOption wo = new WriteOption();
        wo.setSheetName("Test");
        wo.setFileName("test.xlsx");
        List<String> titles = new ArrayList<String>();
        titles.add("Title1");
        titles.add("Title2");
        titles.add("Title3");
        wo.setTitles(titles);
        
        List<String[]> contents = new ArrayList<String[]>();
        contents.add(new String[]{"1", "2", "3"});
        contents.add(new String[]{"11", "22", "33"});
        contents.add(new String[]{"111", "222", "333"});
        wo.setContents(contents);
        
        ExcelWrite.write(wo);
        
    }
    
}
 

 

List.jsp

    <a href="<c:url value="/board/list/excel"/>">Excel Export</a>
 

 

실행화면