Java Servlet 导入Excel实现

1、导入Excel 模板

**2、按模板的格式 进行数据导入直接存储数据库**

**Servlet 方法中 进行导入功能实现：**
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		(1)、前端调用后端接口传参，编写代码上传Excel 附件
		// 创建json 对象
		JSONObject returnJson = new JSONObject();
			// 创建map对象
			Map<String, String> resultMap = null;
			// 创建file对象
			String filePath = null;
			// 创建excel book对象
			Workbook workbook = null;
try {
		// 调用文件上传工具类，返回文件路径
		String savePath = this.getClass().getClassLoader().getResource("/").getPath(); // 上传文件的保存目录
		String tempPath = this.getClass().getClassLoader().getResource("/").getPath(); // 上传时生成的临时文件保存目录
		resultMap = FileUtil.upload(savePath, tempPath, req); // 实现map对象
		
		/* 1. 判断文件是否上传 */
				filePath =  resultMap.get("arg_req_file");
				if (null == filePath) {
					throw new ExceptionUtils.CheckException("未上传文件");
				}
				// 2、 判断文件格式
				File file = new File(filePath);
				InputStream in = FileUtils.openInputStream(file);
				workbook = checkFileValid(file, in);

**// 4、 通过需要验证的参数 先查询数据是否已经导入**
			String queryExistSql = "SELECT develop_plan_id,version_upload FROM 表名 WHERE year = '"
					+ argThisYear + "' AND ou_id = '" + argOuId + "';";
			JSONObject transResultExist = this.queryDb(sqlSelectUrl, queryExistSql);
			int rowCount = Integer.valueOf(transResultExist.getString("RowCount"));
			// 如果 数据存在进行对存在的数据进行更新，不存在 直接进行插入
			if (0 != rowCount) {
				if (transResultExist.has("DataRows")) {
					// 查询 dataRows 获取数据值
					String Query = transResultExist.get("DataRows").toString();
					JSONArray jsonQueryNameArray = new JSONArray(Query);
					String developPlanId = jsonQueryNameArray.getJSONObject(0).getString("develop_plan_id");
					String version = jsonQueryNameArray.getJSONObject(1).getString("version_upload");
					Integer versionNum = Integer.parseInt(version)+1;
					
**// 通过其他参数验证数据已存在，并把之前数据 更改状态变为历史数据 通过字段 is_new（0历史、1在用）**
					ArrayList<String> operateList = new ArrayList<String>();
					operateList.add("SET SQL_SAFE_UPDATES = 0;");
					operateList.add("UPDATE 表名 set is_new = '0' " + " WHERE year = '" + argThisYear
							+ "' AND ou_id = '" + argOuId + "';");
					JSONObject oprateDBRet = operateDb(sqlTransUrl, operateList, mapWebinterpData);
					if ("1".equals(CommonUtils.getStrFromJsonobjet(oprateDBRet,
							ConstantUtility.StandardWebConstant.KEY_RETCODE))) {
						/*
						 * 5、 解析文件，获取文件中的数据
						 */
						returnJson = parseFile(workbook, sqlSelectUrl, sqlTransUrl, argThisYear, argStaffId,
								argOuId, "1",developPlanId,versionNum);
					} else {
						returnJson.put("RetCode", "0");
						returnJson.put("RetVal", "更新原始数据失败");
					}
				}
			} else {
				returnJson = parseFile(workbook, sqlSelectUrl, sqlTransUrl, argThisYear, argStaffId,
						argOuId,"0","",1);
			}
		}catch (IOException e) {
			this.commonLogout(e);
			returnJson.put("RetCode", "0");
			returnJson.put("RetVal", "文件读取异常");
		} catch (NullPointerException e) {
			this.commonLogout(e);
			returnJson.put("RetCode", "0");
			returnJson.put("RetVal", "文档中表格读取数据为空");
		} catch (Exception e) {
			this.commonLogout(e);
			returnJson.put("RetCode", "0");
			returnJson.put("RetVal", e.getMessage());
		} finally {
			if (workbook != null) {
				workbook.close();
			}
			resp.getOutputStream().write(returnJson.toString().getBytes("UTF-8"));
			resp.flushBuffer();
		}

}



**//  3检查文件格式并返回workbook**
private Workbook checkFileValid(File file, InputStream in) throws Exception {
	Workbook workbook = null;
	if (!file.exists()) {
		throw new Exception("文件不存在！");
	}
	if (!(file.isFile() && file.getName().endsWith("XLSX") || file.getName().endsWith("XLS"))) {
		throw new Exception("文件不是Excel！");
	}
	if (file.getName().endsWith("XLS")) { // Excel 2003
		workbook = new HSSFWorkbook(in);
	} else if (file.getName().endsWith("XLSX")) { // Excel 2007/2010
		workbook = new XSSFWorkbook(in);
	}
	return workbook;
}


/**
* 解析文件数据并插入到数据库
*workbook ：获取Excel数据
// 以下参数 仅个人接口使用 ，如各位需求 可根据情况定义
*sqlSelectUrl/sqlTransUrl/argThisYear 
*argThisYear 前端传参 当前数据年份
* Id 前端传参 上传人ID
* type 控制当前数据是否为0更新或 1插入 
* version 控制数据版本
*/
private JSONObject parseFile(Workbook workbook, String sqlSelectUrl, String sqlTransUrl, String argThisYear,
			String Id, String type,Integer version) throws Exception {
		JSONArray jsonArray = new JSONArray();
		ArrayList<String> operateList = new ArrayList<String>();
		// 获取要读取的sheet
		Sheet sheet = workbook.getSheet("Sheet1");

		String plan_UUID = CommonUtils.getUUID();
		String nowString = CommonUtils.getCurrentTime();
		// 遍历所有的行
		for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			// 舍弃表头和末尾
			if (getValue(row.getCell(3)) == null || row == null || i < sheet.getFirstRowNum() + 3 || i > sheet.getLastRowNum()) {
				continue;
			}
			// 1新建json
			JSONObject jsonObject = new JSONObject();
			// 2放入年份和季度 通用
			jsonObject.put("year", argThisYear);
			jsonObject.put("plan_list_id", CommonUtils.getUUID()); //清单详情主键ID
			jsonObject.put("proj_state", "0"); // 项目状态 0 正常
			jsonObject.put("is_new", "1"); // 是否最新版本0历史版本，1在用
			jsonObject.put("create_user", Id); // 创建人
			jsonObject.put("create_time", nowString); // 创建时间
			jsonObject.put("update_user", Id); // 更新人
			jsonObject.put("update_time", nowString); // 更新时间
			jsonObject.put("version_upload", version); // 版本号

			Object firstOrder = getValue(row.getCell(0));
			jsonObject.put("first_order", firstOrder); // 优先排序

			Object argOuId= getValue(row.getCell(1));
			jsonObject.put("ou_id", argOuId);

			String mainDepart = (String) getValue(row.getCell(2));
			String substring = mainDepart == null ? mainDepart : mainDepart.substring(mainDepart.indexOf("-") + 1);
			jsonObject.put("dept_name", substring); // 部门名称

			Object major = getValue(row.getCell(3));
			jsonObject.put("major_line_ou", major); // 专业线

			Object projName = getValue(row.getCell(4));
			jsonObject.put("proj_name", projName); // 项目名称

			Object major_line_proj = getValue(row.getCell(5));
			jsonObject.put("major_line_proj", major_line_proj); // 应用主体

			Object expense_expenditure = getValue(row.getCell(7));
			jsonObject.put("expense_expenditure", expense_expenditure); //费用化支出(万元)

			Object expense_entrust = getValue(row.getCell(8));
			jsonObject.put("expense_entrust", expense_entrust); // 费用化支出-委托开发费用(万元)

			Object expense_own = getValue(row.getCell(9));
			jsonObject.put("expense_own", expense_own); // 费用化支出-自有研发人工成本(万元)

			Object capitalize_expenditure = getValue(row.getCell(10));
			jsonObject.put("capitalize_expenditure", capitalize_expenditure); // 资本化支出(万元)

			Object capitalize_entrust = getValue(row.getCell(11));
			jsonObject.put("capitalize_entrust", capitalize_entrust); // 资本化支出-委托开发费用(万元)

			Object capitalize_own = getValue(row.getCell(12));
			jsonObject.put("capitalize_own", capitalize_own); //资本化支出-自有研发人员工时(人年)

			Object develop_content = getValue(row.getCell(13));
			jsonObject.put("develop_content", develop_content); //主要研发内容

			Object proj_value = getValue(row.getCell(14));
			jsonObject.put("proj_value", proj_value); // 预期研发成果200字左右
			jsonObject.put("proj_id", CommonUtils.getUUID()); // 项目编号
			jsonArray.put(jsonObject);
		}

		**// 判断数据是否为空**
		if(jsonArray.length() > 0) {
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject jsonObject = jsonArray.getJSONObject(i);
				**// 循环遍历Excel 数据源  分别按Excel 的字段值插入数据表所对应的字段值**
				String insertBPListSql = "INSERT INTO  表名(plan_list_id,develop_plan_id"
						+ ",ou_id,first_order,proj_state,is_new,year,dept_name,major_line_ou,proj_id"
						+ ",proj_name,major_line_proj,expense_expenditure,expense_entrust,expense_own"
						+ ",capitalize_expenditure,capitalize_entrust,capitalize_own,develop_content,proj_value"
						+ ",create_user,create_time,update_user,update_time)"
						+ " SELECT '"+ jsonObject.get("plan_list_id").toString()
						+ "','"+ jsonObject.get("develop_plan_id").toString()
						+ "','"+ jsonObject.get("ou_id").toString()
						+ "','"+ jsonObject.get("first_order").toString()
						+ "','"+jsonObject.get("proj_state").toString()
						+ "','"+jsonObject.get("is_new").toString()
						+ "','"+jsonObject.get("year").toString()
						+ "','"+jsonObject.get("dept_name").toString()
						+ "','"+jsonObject.get("major_line_ou").toString()
						+ "','"+jsonObject.get("proj_id").toString()
						+ "','"+jsonObject.get("proj_name").toString()
						+ "','"+jsonObject.get("major_line_proj").toString()
						+ "','"+jsonObject.get("expense_expenditure").toString()
						+ "','"+jsonObject.get("expense_entrust").toString()
						+ "','"+jsonObject.get("expense_own").toString()
						+ "','"+jsonObject.get("capitalize_expenditure").toString()
						+ "','"+jsonObject.get("capitalize_entrust").toString()
						+ "','"+jsonObject.get("capitalize_own").toString()
						+ "','"+jsonObject.get("develop_content").toString()
						+ "','"+jsonObject.get("proj_value").toString()
						+ "','"+jsonObject.get("create_user").toString()
						+ "','"+jsonObject.get("create_time").toString()
						+ "','"+jsonObject.get("update_user").toString()
						+ "','"+jsonObject.get("update_time").toString()
						+ "';";
				operateList.add(insertBPListSql);
			}
		} else {
			throw new ExceptionUtils.CheckException("文件中没有数据!");
		}
		JSONObject operateDb = operateDb(sqlTransUrl, operateList);
		this.commonLogout(operateList.toString());
		this.commonLogout(operateDb.toString());
		return operateDb;
	}

// 获取cell数据类型并返回
private static Object getValue(Cell cell) {
		Object obj = null;
		switch (cell.getCellTypeEnum()) {
		case BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case ERROR:
			obj = cell.getErrorCellValue();
			break;
		case NUMERIC:
			obj = cell.getNumericCellValue();
			break;
		case STRING:
			obj = cell.getStringCellValue().trim().replaceAll(" ", "").replaceAll("\\s*", "");
			break;
		default:
			break;
		}
		return obj;
	}