POI解析Excel导入数据到mysql

所用jar poi-3.7.jar  poi-oxml-3.7.jar  poi-oxml-schemas-3.7.jar 
        xmlbeans-2.6.0.jar dom4j-1.6.1.jar

代码 
 
@RequestMapping("/importExcel")
@ResponseBody
public Map importExcel(HttpServletRequest request, MultipartFile file) throws Exception {
		Map result = new HashMap();
		Map param = new HashMap();
		List> list = getList(file);
		if (list.size() > 0) {
			try {
				for (int i = 0; i < list.size(); i++) {
					param = list.get(i);
					visitService.saveStatAdv(param);
				}
				result.put("msg", "保存成功");
			} catch (Exception e) {
				result.put("msg", "保存失败");
				e.printStackTrace();
			}
		}
		return result;
	}

 private List> getList(MultipartFile file) throws Exception {
		// TODO Auto-generated method stub
		List> list = new ArrayList>();
		InputStream stream = file.getInputStream();
//			String ext=file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
		Workbook wb = WorkbookFactory.create(stream);
		Sheet sheet = wb.getSheetAt(0);

		for (Row row : sheet) {
			int w = row.getRowNum();
			if (w >= 1) {
				Map map = new HashMap();
				for (Cell cell : row) {
					int j = cell.getColumnIndex();
					
					Object obj;
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_NUMERIC:// 数值型
						if (HSSFDateUtil.isCellDateFormatted(cell)) {// date类型
							obj = cell.getDateCellValue();
						} else {// 纯数字
							obj = cell.getNumericCellValue();
						}
						break;
					case Cell.CELL_TYPE_STRING:// 字符串型
						obj = cell.getRichStringCellValue().getString();
						break;
					case Cell.CELL_TYPE_BOOLEAN:// 布尔
						obj = cell.getBooleanCellValue();
						break;
					case Cell.CELL_TYPE_FORMULA:// 公式型
						obj = cell.getCellFormula();
						break;
					case Cell.CELL_TYPE_BLANK:// 空值
						obj = "";
						break;
					case Cell.CELL_TYPE_ERROR: // 故障
						obj = "";
						break;
					default:
						obj = "";
					}
					if (j == 0) {
						map.put("date", obj);
					} else if (j == 1) {
						map.put("channel", obj);
					} else if (j == 2) {
						map.put("product", obj);
					} else if (j == 3) {
						map.put("qtyview", obj);
					} else if (j == 4) {
						map.put("qtyrequest", obj);
					} else if (j == 5) {
						map.put("qtyshow", obj);
					} else if (j == 6) {
						map.put("qtyadshow", obj);
					} else if (j == 7) {
						map.put("ratecover", String.format("%.4f", obj));
					} else if (j == 8) {
						map.put("qtyclk", obj);
					} else if (j == 9) {
						map.put("rateclk", String.format("%.4f", obj));
					} else if (j == 10) {
						map.put("cpc", obj);
					} else if (j == 11) {
						map.put("ecpm", String.format("%.4f", obj));
					} else if (j == 12) {
						map.put("ratevisible", String.format("%.4f", obj));
					} else if (j == 13) {
						map.put("revenue", obj);
					}
				}
				list.add(map);
			}
		}
		return list;
	}

 

你可能感兴趣的