티스토리 뷰

DEV/JAVA

POI 로 엑셀파일 읽기(xls , xlsx)

초록매실원액 2016. 3. 14. 17:08
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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
package test2;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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;
import org.apache.poi.ss.usermodel.WorkbookFactory;
 
public class test3 {
 
    /**
     * @param args
     */
    public static void main(String[] args)   throws Exception{
        // TODO Auto-generated method stub
          InputStream is;
          
          List<Map<String , Object>> list = new ArrayList<Map<String,Object>>();
          
          try {
           
              
              File file = new File("d:/test.xlsx");
              
              if(file.exists()){
                  
                  System.out.println(file.getPath());
                 
                  
                  InputStream inp = new FileInputStream(file.getPath());
                  //InputStream inp = new FileInputStream("workbook.xlsx");
 
                  String fileName = file.getName();
                  int idx = fileName.lastIndexOf(".");
                  String fileExt = fileName.substring(idx+1);
                  
                  System.out.println("fileExt : " + fileExt);
                  
                  if("xls".equals(fileExt)){
                                            
                      POIFSFileSystem fileSystem = new POIFSFileSystem(inp);
                      HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
                      
                      HSSFRow row;
                      HSSFRow rowNm;
                      HSSFCell cell;
                      HSSFSheet sheet = workbook.getSheetAt(0);
                      
                      int rows; // No of rows
                      rows = sheet.getPhysicalNumberOfRows();
                      
                      System.out.println("the total number of rows are"+rows);
                      int cols = 0; // No of columns
                      int tmp = 1;
                        
                      for(int i = 0; i < 10 || i < rows; i++) {
                          row = sheet.getRow(i);
                          if(row != null) {
                              tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                              if(tmp > cols){
                                  cols = tmp;
                              }
                          }
                      }
                      
                      rowNm = sheet.getRow(0);
                      
                      for(int r1 = 1; r1< rows; r1++) {
                          row = sheet.getRow(r1);
                          if(row == null || row.getCell(0) == null){
                              continue;
                          }else{
                              System.out.println("<row>");
                              Map<String,Object> map = new HashMap<String, Object>();
                              for(int c = 0; c < cols; c++) {
                                  cell = row.getCell(c);
                                  if(c == 0 && cell == null){
                                      break;
                                  }
                                  if(cell == null){
                                      map.put(rowNm.getCell(c).getRichStringCellValue().getString(), "");
                                  }else{
                                      map.put(rowNm.getCell(c).getRichStringCellValue().getString(), cell.getRichStringCellValue().getString());
                                  }
                                  System.out.println("\t<"+rowNm.getCell(c)+">" +cell+"</"+rowNm.getCell(c).getRichStringCellValue().getString()+">");
                                }
                                list.add(map);
                                System.out.println("</row>");
                                //cnt ++;
                            }
                      }
                      
                  }else {
                      Workbook wb = WorkbookFactory.create(inp);
                      Sheet sheet = wb.getSheetAt(0);
                    
                    Row row;
                    Row rowNm;
                    Cell cell;
                   
                    
                    int rows; // No of rows
                    rows = sheet.getPhysicalNumberOfRows();
                     
                    System.out.println("the total number of rows are"+rows);
                  
                    int cols = 0; // No of columns
                    int tmp = 1;
                    
                    for(int i = 0; i < 10 || i < rows; i++) {
                        
                        row = sheet.getRow(i);
                        if(row != null) {
                            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                           //out.println("tmp value"+tmp);
                            if(tmp > cols){
                                cols = tmp;
                            }
                        }
                    }
                   // out.println("<row><cell>"+cols+
                    rowNm = sheet.getRow(0);
                    int cnt = 1;
                    String blank = "";
                    for(int r1 = 1; r1< rows; r1++) {
                        row = sheet.getRow(r1);
                        if(row == null || row.getCell(0) == null){
                            continue;
                        }else{
                            System.out.println("<row>");
                            Map<String,Object> map = new HashMap<String, Object>();
                            for(int c = 0; c < cols; c++) {
                                cell = row.getCell((short)c);
                                if(c == 0 && cell == null){
                                    break;
                                }
                                if(cell == null){
                                    map.put(rowNm.getCell(c).getStringCellValue(), "");
                                }else{
                                    map.put(rowNm.getCell(c).getStringCellValue(), cell.getStringCellValue());
                                }
                                //System.out.println("\t<"+rowNm.getCell(c)+">" +cell+"</"+rowNm.getCell(c).getStringCellValue()+">");
                                
                               
                            }
                            list.add(map);
                            System.out.println("</row>");
                            cnt ++;
                        }
                    }
                    
                    System.out.println("cnt : " + cnt);
                    
                    System.out.println(list.size());
                    
                    System.out.println(list.toString());
                  }
                  
                  
         }
             
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
            
          
 
    }
 
}
cs


반응형
저작자표시 (새창열림)

'DEV > JAVA' 카테고리의 다른 글

Spring AI, 자바 개발자를 위한 AI 활용법: 장점과 단점 심층 분석  (0) 2026.01.04
자바, 버전별 주요 특징 및 변화  (3) 2025.07.15
AWS S3 파일업로드  (0) 2019.12.31
spring boot + jasypt  (0) 2017.09.15
StringUtils.isEmtpty() vs StringUtils.isBlank()  (0) 2016.03.18
공유하기 링크
댓글
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
TAG more
«   2026/01   »
일 월 화 수 목 금 토
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
글 보관함