1. 引用pom
<!--读取excl-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2. 写函数
读取的Excel
目标表格
需要注意数字列的读法
private void importExcel() {
try {
FileInputStream in = new FileInputStream("F:\\country.xlsx");//Excel路径
//Workbook wb = new HSSFWorkbook(in);//Excel版本<=Office 2007
Workbook wb = new XSSFWorkbook(in);//Excel版本Office> 2007
Sheet sheet = wb.getSheetAt(1);//sheet页码, 从0开始,1表示第二个sheet
for (Row row : sheet) {
if (row.getRowNum() < 1) {//第一行通常为表头,不读
continue;
}
CountryExchangeRate eInfo = new CountryExchangeRate();
Cell numCell =row.getCell(0);//如果是数字,1读出来是1.0,不能Integer.parseInt
//数字列处理的第一种方法,推荐
if(numCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String str = NumberToTextConverter.toText(numCell.getNumericCellValue());
eInfo.setCountryCode(Integer.parseInt(str));
}
//数字列处理的第二种方法,不推荐
//numCell.setCellType(Cell.CELL_TYPE_STRING);//regardless of how the user formatted the cell
//String test=row.getCell(0).toString();//1
//eInfo.setCountryCode(Integer.parseInt(test));
eInfo.setCountryName(row.getCell(1).getStringCellValue());
eInfo.setCurrency(row.getCell(2).getStringCellValue());
eInfo.setCurrencyName(row.getCell(3).getStringCellValue());
eInfo.setExchange(BigDecimal.ZERO);
eInfo.setDatachangeLasttime(new Timestamp(System.currentTimeMillis()));
eInfo.setDatachangeCreatetime(new Timestamp(System.currentTimeMillis()));
countryExchangeRateDao.insert(eInfo);//插入数据到db
}
in.close();
} catch (Exception ex) {
logger.warn("importExcel Exception", 0, ex);
}
}