文件上传excel文件
一、读取excel文件
/** * 读取excel * @param file * @return * @throws IOException */ public static List<List<Object>> readExcel(MultipartFile file) throws IOException { String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase(); if ("xls".equals(extension)) { return read2003Excel(file.getInputStream()); } else if ("xlsx".equals(extension)) { return read2007Excel(file.getInputStream()); } else { throw new IOException("不支持的文件类型"); } } /** * 读取 office 2003 excel * @param stream * @return * @throws IOException */ private static List<List<Object>> read2003Excel(InputStream stream) throws IOException { List<List<Object>> list = new LinkedList<List<Object>>(); HSSFWorkbook hwb = new HSSFWorkbook(stream); HSSFSheet sheet = hwb.getSheetAt(0); Object value = null; HSSFRow row = null; HSSFCell cell = null; DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串 DecimalFormat nf = new DecimalFormat("###.####");// 格式化数字 //第一行是标题，第二行是列名称，从第三行开始读 for (int i = sheet.getFirstRowNum() + 2; i <= sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } //检查每行的空格数，如果小于最后一列数证明有一个或多个空格，但不是整行 if(CheckRowNull(row) < row.getLastCellNum()){ List<Object> linked = new LinkedList<Object>(); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { linked.add(""); continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: value = ""; break; default: value = cell.toString(); } if (value == null || "".equals(value)) { linked.add(""); continue; } linked.add(value); } list.add(linked); } } return list; } /** * 读取Office 2007 excel * @param stream * @return * @throws IOException */ private static List<List<Object>> read2007Excel(InputStream stream) throws IOException { List<List<Object>> list = new LinkedList<List<Object>>(); // 构造 XSSFWorkbook 对象，strPath 传入文件路径 XSSFWorkbook xwb = new XSSFWorkbook(stream); // 读取第一章表格内容 XSSFSheet sheet = xwb.getSheetAt(0); Object value = null; XSSFRow row = null; XSSFCell cell = null; DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串 DecimalFormat nf = new DecimalFormat("###.####");// 格式化数字 // 第一行是标题，第二行是列名称，从第三行开始读 for (int i = sheet.getFirstRowNum() + 2; i <= sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } if(CheckXSSFRowNull(row) < row.getLastCellNum()){ List<Object> linked = new LinkedList<Object>(); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { linked.add(""); continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: value = ""; break; default: value = cell.toString(); } if (value == null || "".equals(value)) { linked.add(""); continue; } linked.add(value); } list.add(linked); } } return list; }
//判断读取行为空 2003xls
private static int CheckRowNull(HSSFRow row) {
int num = 0;
Iterator<HSSFCell> cellItr = row.iterator();
while (cellItr.hasNext()) {
HSSFCell c = cellItr.next();
if (c.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
num++;
}
}
return num;
}
//判断读取行为空 2007 xlsx
private static int CheckXSSFRowNull(XSSFRow row) {
int num = 0;
Iterator<Cell> cellItr =row.iterator();
while (cellItr.hasNext()) {
Cell c = cellItr.next();
if (c.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
num++;
}
}
return num;
}
List<List<Object>> lists=ImportsExcel.readExcel(file); String result = "";try {int row = 0;
Map<String, Object> m = new HashMap<String, Object>(); // 获取当前时间 Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 备注 String remark=""; for (List<Object> list : lists) { if (list != null && list.size() > 0) { // id m.put("id", IDCode.commonId + IDTool.getWebUserId() + "");
// 获取第一列
m.put("monitor_date",(String) list.get(0)); // 获取第二列 String place=(String) list.get(1);
// 转换成数据库值 if(place.equals("地点一")){ place="0"; } else if (place.equals("地点二")) { place="1"; } else if (place.equals("地点三")) { place="2"; } else if (place.equals("地点四")) { place="3"; } m.put("monitor_place",place); //获取第三列 m.put("monitor_equipment",(String) list.get(2)); // 获取第四列 m.put("dust_concentration",(String) list.get(3)); // 获取第五列 m.put("inspector_name",(String) list.get(4));
// 如果最后一列有值 if(list.size() > 5) { remark=(String) list.get(5); } m.put("remark",remark); m.put("create_time", sdf.format(date)); m.put("creater_id", createId); // 循环之后需要将对应的值改成初始值 remark=""; } row += healthDao.addMonitor(m); } if (row >= 1) { result = RequestResponseTool.getJsonMessage(RespCode.commonSucc, RespMsg.commonSucc); }else{ result = RequestResponseTool.getJsonMessage(RespCode.commonFail, RespMsg.commonFail); } } catch (Exception e) { rollBack(e, log); result = RequestResponseTool.getJsonMessage(RespCode.commonFail, RespMsg.commonFail); }
