IT/Spring

Excel파일 Read/Write

바바옄 2015. 4. 28. 14:38
반응형

Read

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

1
2
3
4
5
6
7
8
9
10
11
    <!-- 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>
cs

 

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

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

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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;
        
    }
 
    
}
 
cs

 

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

엑셀파일 읽기 옵션 설정

 

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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;
    }
    
}
 
cs
반응형

 

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

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

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
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<StringString>> 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<StringString> map = null;
        
        /*
         * 각 Row를 리스트에 담는다
         * 하나의 Row는 하나의 Map으로 표현되며
         * List에는 모든 Row가 포함될 것이다.
         */
        List<Map<StringString>> result = new ArrayList<Map<StringString>>(); 
        
        /*
         * 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<StringString>();
                
                /*
                 * 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<StringString>> result = ExcelRead.read(ro);
        
        for(Map<StringString> map : result) {
            System.out.println(map.get("A"));
        }
    }
    
}
 
cs

 

Write

BoardController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@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;
    }
cs

 

BoardService.java

1
2
3
4
 public void insertMassiveArticleInBoard(File destFile);
 
 
 
cs

 

BoardServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@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<StringString>> excelContent = ExcelRead.read(readOption);
  
  BoardVO boardVO = null;
  for(Map<StringString> 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);
  }
 }
 
cs

 

list.jsp 에 form 추가

1
2
3
4
5
    <!-- 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>
cs

 

실행화면

 

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

BoardController.java

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
@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);
          
          
       }
cs

 

ExcelWrite.java

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
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);
        
    }
    
}
 
cs

 

List.jsp

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

 

실행화면

 

 

 

 

 

 

 

반응형

'IT > Spring' 카테고리의 다른 글

Layer  (0) 2015.04.30
파일내용을 게시판에 Write  (0) 2015.04.28
메이블 플러그인 빌드 에러  (0) 2015.04.27
Enum(이늄)  (0) 2015.04.27
동영상, 자막 컨트롤, 큐브 구현  (0) 2015.04.27