excel文件:example.xls
文件内容大概是下面这样的:
现在要读取excel的内容写入数据库中
String filepath = "example.xls";
String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
InputStream is = null;
Workbook wb = null;
try {
is = new FileInputStream(filepath);
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(is);
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(is);
} else {
throw new Exception("读取的不是excel文件");
}
Sheet sheet = wb.getSheetAt(0);
List> sheetList = new ArrayList>();
List titles = new ArrayList();
int rowSize = sheet.getLastRowNum() + 1;
for (int j = 0; j < rowSize; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
int cellSize = row.getLastCellNum();
if (j == 0) {
for (int k = 0; k < cellSize; k++) {
Cell cell = row.getCell(k);
titles.add(cell.toString());
}
} else {
Map rowMap = new HashMap();
for (int k = 1; k < titles.size(); k++) {
Cell cell = row.getCell(k);
String key = titles.get(k);
String value = null;
if (cell != null) {
value = cell.toString();
}
rowMap.put(key, value);
}
sheetList.add(rowMap);
//rowMap.clear();
}
}
wb.close();
is.close();
titles.clear();
String keyword;
String cityid;
String showtype;
String cateid;
String cityname;
for (Map params : sheetList) {
keyword = params.get("查询词");
cityid = params.get("cityid");
showtype = params.get("showtype");
cateid = params.get("cateid");
cityname = cityService.getCityNameByCityId(Integer.parseInt(cityid));
correctTopQueryService.insertRecode(keyword, cityid, cityname, showtype, cateid);
}
} catch (Exception e) {
e.printStackTrace();
}