Excel学习4_ java poi 读取有合并单元格的EXCEL文件

数据的样子是这样的

[java]  view plain  copy

1. public String addReportByExcel(Long userId,InputStream inputStream,String fileName)    
2.             throws BusinessException{  
3.         String message = "Import success";  
4.           
5.         boolean isE2007 = false;    //判断是否是excel2007格式    
6.         if(fileName.endsWith("xlsx")){  
7.             isE2007 = true;  
8.         }  
9.           
10.         int rowIndex = 0;  
11.         int columnIndex = 0;  
12.         try {  
13.             InputStream input = inputStream;  //建立输入流    
14.             Workbook wb  = null;    
15.             //根据文件格式(2003或者2007)来初始化    
16.             if(isE2007){   
17.                 wb = new XSSFWorkbook(input);  
18.             }else{    
19.                 wb = new HSSFWorkbook(input);  
20.             }  
21.             Sheet sheet = wb.getSheetAt(0);    //获得第一个表单    
22.               
23.             //System.out.println("总行数:"+sheet.getLastRowNum());  
24.               
25.             List<CellRangeAddress> cras = getCombineCell(sheet);  
26.             //isMergedRegion(Sheet sheet,int row ,int column);判断是不是合并单元格\  
27.             int count = sheet.getLastRowNum()+1;//总行数  
28.              
29.             List<InspectionReport> irs = new ArrayList<>();  
30.             for(int i = 1; i < count;i++){  
31.                 rowIndex = i;  
32.                 Row row = sheet.getRow(i);  
33.                 InspectionReport ir = new InspectionReport();  
34.                   
35.                 ir.setReportName(getCellValue(row.getCell(0)));  
36.                 ir.setShift(Double.valueOf(getCellValue(row.getCell(1))).intValue());  
37.                 ir.setLine(getCellValue(row.getCell(2)));  
38.                 ir.setStationCode(getCellValue(row.getCell(3)));  
39.                 ir.setArea(Double.valueOf(getCellValue(row.getCell(4))).intValue());  
40.                 ir.setReportStatus(Double.valueOf(getCellValue(row.getCell(5))).intValue());  
41.                   
42.                 List<InspectionItem> items = new ArrayList<>();  
43.                 if(isMergedRegion(sheet,i,0)){  
44.                     int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);  
45.                       
46.                     for(;i<=lastRow;i++){  
47.                         row = sheet.getRow(i);  
48.                         InspectionItem item = new InspectionItem();  
49.                         item.setItem(getCellValue(row.getCell(6)));  
50.                         item.setMethod(getCellValue(row.getCell(7)));  
51.                         item.setMode(getCellValue(row.getCell(8)));  
52.                         item.setStandardValue(getCellValue(row.getCell(9)));  
53.                         item.setDeviationValue(getCellValue(row.getCell(10)));  
54.                         String pinci = getCellValue(row.getCell(11));  
55.                         Double d = Double.valueOf(pinci);  
56.                         item.setFrequency(d.intValue());  
57.                         items.add(item);  
58.                     }  
59.                     i--;  
60.                 }else{  
61.                     row = sheet.getRow(i);  
62.                     InspectionItem item = new InspectionItem();  
63.                     item.setItem(getCellValue(row.getCell(6)));  
64.                     item.setMethod(getCellValue(row.getCell(7)));  
65.                     item.setMode(getCellValue(row.getCell(8)));  
66.                     item.setStandardValue(getCellValue(row.getCell(9)));  
67.                     item.setDeviationValue(getCellValue(row.getCell(10)));  
68.                     String pinci = getCellValue(row.getCell(11));  
69.                     Double d = Double.valueOf(pinci);  
70.                     item.setFrequency(d.intValue());  
71.                     items.add(item);  
72.                 }  
73.                 ir.setItems(items);  
74.                 irs.add(ir);  
75.                   
76.             }  
77.              
78.             XaResult<List<InspectionReport>> saved = inspectionReportService.createByXls(1l, irs);  
79.             message = saved.getMessage();  
80.               
81.            /*JSONArray js= new JSONArray(); 
82.            js.addAll(irs); 
83.            System.out.println(js.toJSONString());*/  
84.   
85.         } catch (Exception ex) {    
86.             //xr.setMessage("Import failed, please check the data in "+rowIndex+" rows "+columnIndex+" columns ");  
87.             message =  "Import failed, please check the data in "+rowIndex+" rows ";  
88.         }  
89.         return message;  
90.     }  

使用到的 工具类：

[java]  view plain  copy

1. /**    
2.     * 获取单元格的值    
3.     * @param cell    
4.     * @return    
5.     */      
6.     public String getCellValue(Cell cell){      
7.         if(cell == null) return "";      
8.         if(cell.getCellType() == Cell.CELL_TYPE_STRING){      
9.             return cell.getStringCellValue();      
10.         }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){      
11.             return String.valueOf(cell.getBooleanCellValue());      
12.         }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){      
13.             return cell.getCellFormula() ;      
14.         }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){      
15.             return String.valueOf(cell.getNumericCellValue());      
16.         }  
17.         return "";      
18.     }<pre code_snippet_id="2306825" snippet_file_name="blog_20170401_2_6166356" name="code" class="java">/**  
19.     * 合并单元格处理,获取合并行  
20.     * @param sheet  
21.     * @return List<CellRangeAddress>  
22.     */    
23.     public List<CellRangeAddress> getCombineCell(Sheet sheet)    
24.     {    
25.         List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();    
26.         //获得一个 sheet 中合并单元格的数量    
27.         int sheetmergerCount = sheet.getNumMergedRegions();    
28.         //遍历所有的合并单元格    
29.         for(int i = 0; i<sheetmergerCount;i++)     
30.         {    
31.             //获得合并单元格保存进list中    
32.             CellRangeAddress ca = sheet.getMergedRegion(i);    
33.             list.add(ca);    
34.         }    
35.         return list;    
36.     }  
37.       
38.     private int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){  
39.         int xr = 0;  
40.         int firstC = 0;    
41.         int lastC = 0;    
42.         int firstR = 0;    
43.         int lastR = 0;    
44.         for(CellRangeAddress ca:listCombineCell)    
45.         {  
46.             //获得合并单元格的起始行, 结束行, 起始列, 结束列    
47.             firstC = ca.getFirstColumn();    
48.             lastC = ca.getLastColumn();    
49.             firstR = ca.getFirstRow();    
50.             lastR = ca.getLastRow();    
51.             if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)     
52.             {    
53.                 if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)     
54.                 {    
55.                     xr = lastR;  
56.                 }   
57.             }    
58.               
59.         }  
60.         return xr;  
61.           
62.     }  
63.     /**  
64.      * 判断单元格是否为合并单元格，是的话则将单元格的值返回  
65.      * @param listCombineCell 存放合并单元格的list  
66.      * @param cell 需要判断的单元格  
67.      * @param sheet sheet  
68.      * @return  
69.      */   
70.      public String isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)  
71.      throws Exception{   
72.          int firstC = 0;    
73.          int lastC = 0;    
74.          int firstR = 0;    
75.          int lastR = 0;    
76.          String cellValue = null;    
77.          for(CellRangeAddress ca:listCombineCell)    
78.          {  
79.              //获得合并单元格的起始行, 结束行, 起始列, 结束列    
80.              firstC = ca.getFirstColumn();    
81.              lastC = ca.getLastColumn();    
82.              firstR = ca.getFirstRow();    
83.              lastR = ca.getLastRow();    
84.              if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)     
85.              {    
86.                  if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)     
87.                  {    
88.                      Row fRow = sheet.getRow(firstR);    
89.                      Cell fCell = fRow.getCell(firstC);    
90.                      cellValue = getCellValue(fCell);    
91.                      break;    
92.                  }   
93.              }    
94.              else    
95.              {    
96.                  cellValue = "";    
97.              }    
98.          }    
99.          return cellValue;    
100.      }  
101.       
102.     /**    
103.     * 获取合并单元格的值    
104.     * @param sheet    
105.     * @param row    
106.     * @param column    
107.     * @return    
108.     */      
109.     public String getMergedRegionValue(Sheet sheet ,int row , int column){      
110.         int sheetMergeCount = sheet.getNumMergedRegions();      
111.               
112.         for(int i = 0 ; i < sheetMergeCount ; i++){      
113.             CellRangeAddress ca = sheet.getMergedRegion(i);      
114.             int firstColumn = ca.getFirstColumn();      
115.             int lastColumn = ca.getLastColumn();      
116.             int firstRow = ca.getFirstRow();      
117.             int lastRow = ca.getLastRow();      
118.                   
119.             if(row >= firstRow && row <= lastRow){      
120.                 if(column >= firstColumn && column <= lastColumn){      
121.                     Row fRow = sheet.getRow(firstRow);      
122.                     Cell fCell = fRow.getCell(firstColumn);      
123.                     return getCellValue(fCell) ;      
124.                 }      
125.             }      
126.         }      
127.               
128.         return null ;      
129.     }  
130.       
131.       
132.     /**   
133.     * 判断指定的单元格是否是合并单元格   
134.     * @param sheet    
135.     * @param row 行下标   
136.     * @param column 列下标   
137.     * @return   
138.     */    
139.     private boolean isMergedRegion(Sheet sheet,int row ,int column) {    
140.       int sheetMergeCount = sheet.getNumMergedRegions();    
141.       for (int i = 0; i < sheetMergeCount; i++) {    
142.         CellRangeAddress range = sheet.getMergedRegion(i);    
143.         int firstColumn = range.getFirstColumn();    
144.         int lastColumn = range.getLastColumn();    
145.         int firstRow = range.getFirstRow();    
146.         int lastRow = range.getLastRow();    
147.         if(row >= firstRow && row <= lastRow){    
148.             if(column >= firstColumn && column <= lastColumn){    
149.                 return true;    
150.             }    
151.         }  
152.       }    
153.       return false;    
154.     }</pre><br>  
155. <pre></pre>  
156. <p></p>  
157. <pre></pre>  
158. <br>  
159. <br>  
160. <br>  
161. <p></p>  
162.      

原文来自：http://blog.csdn.net/a919423654/article/details/68946507