How to read Excel file by Apache POI

Requirement
Assume we have an excel file, we would like to read the data which in sheet2.
We only need the amount in Column E and column I, and the value of Column A show be 'XXX年'

Here has sample code (focus on Line 17~33)

 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
public class POITest {  
  private final static Logger LOG = LoggerFactory.getLogger(POITest.class);  
  /**  
   * The main method.  
   *   
   * @param args  
   *      the arguments  
   * @throws IOException  
   *       Signals that an I/O exception has occurred.  
    */  
   public static void main(String[] args) throws IOException {  
     String testFile = "D:\\work\\ifms\\TestFiles\\4102294114W75Y7OW0.xls";  
     Integer sheetNum = 1;  
     FileInputStream file = null;  
     try {  
       file = new FileInputStream(new File(testFile));  
       // Get the workbook instance for XLS file  
       HSSFWorkbook workbook = new HSSFWorkbook(file);  
       // Get the specific sheet from the workbook  
       HSSFSheet sheet = workbook.getSheetAt(sheetNum);  
       // Get iterator to all the rows in current sheet  
       Iterator rowIterator = sheet.iterator();  
       List list = new ArrayList<>();  
       while (rowIterator.hasNext()) {  
         Row row = rowIterator.next();  
         if (StringUtils.indexOf(row.getCell(0).toString(), "年") > 0) {//condition  
           Test test = new POITest().new Test();  
           test.setYear(row.getCell(0).toString());//column A  
           test.setGdp(new BigDecimal(row.getCell(4).getNumericCellValue()));//column E  
           test.setGnp(new BigDecimal(row.getCell(8).getNumericCellValue()));//column I  
           list.add(test);  
         }  
       }  
       for (Test vo : list) {  
         LOG.debug("vo=" + vo.toString());  
       }  
     } catch (FileNotFoundException e) {  
       throw new RuntimeException(e);  
     } catch (IOException e) {  
       throw new RuntimeException(e);  
     } finally {  
       file.close();  
     }  
   }  
   private class Test {  
     private String year;  
     private BigDecimal gdp;  
     private BigDecimal gnp;  
     public String getYear() {  
       return year;  
     }  
     public void setYear(String year) {  
       this.year = year;  
     }  
     public BigDecimal getGdp() {  
       return gdp;  
     }  
     public void setGdp(BigDecimal gdp) {  
       this.gdp = gdp;  
     }  
     public BigDecimal getGnp() {  
       return gnp;  
     }  
     public void setGnp(BigDecimal gnp) {  
       this.gnp = gnp;  
     }  
     public String toString() {  
       return ToStringBuilder.reflectionToString(this);  
     }  
   }  
 } 

Here is the log which print in console

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@2d7349d7[year=80年,gdp=4958220,gnp=5093770]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@6bf4d990[year=81年,gdp=5534544,gnp=5655306]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@17f7b44f[year=82年,gdp=6110101,gnp=6224145]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@75ebad4[year=83年,gdp=6685505,gnp=6793022]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@5c3bb813[year=84年,gdp=7277545,gnp=7388464]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@54b216b3[year=85年,gdp=7906075,gnp=8015577]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@77f06d35[year=86年,gdp=8574784,gnp=8664395]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@4991f017[year=87年,gdp=9204174,gnp=9272725]  
[main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@c299bbd[year=88年,gdp=9649049,gnp=9739567]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@7faf9b87[year=89年,gdp=10187394,gnp=10326952]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@620bfd8e[year=90年,gdp=9930387,gnp=10122411]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@133a7ec[year=91年,gdp=10411639,gnp=10654141]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@66557791[year=92年,gdp=10696257,gnp=11025130]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@751d0513[year=93年,gdp=11365292,gnp=11737391]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@44385e76[year=94年,gdp=11740279,gnp=12031145]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@50c1b7f7[year=95年,gdp=12243471,gnp=12555170]  
 [main] DEBUG g.n.e.w.c.Ets402rReportController - vo=gov.nta.ets.web.controller.Ets402rReportController$Test@5e14e28c[year=96年,gdp=12910511,gnp=13243277] 

Reference
[1] http://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/