java操作excel文件之系列一:《读取excel文件的内容保存到数据库》
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<Map<String, String>> sheetList = new ArrayList<Map<String, String>>(); List<String> titles = new ArrayList<String>(); 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<String, String> rowMap = new HashMap<String, String>(); 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<String, String> 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(); }