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年'
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/
