jsp 将数据导入 excel 保存到本地

最新推荐文章于 2021-02-16 11:25:49 发布

原创最新推荐文章于 2021-02-16 11:25:49 发布 · 919 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #jsp #string #exception #list #工作

ssh 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个用于从数据库中获取特定时间段内各地区工单未完成情况的方法，并将其导出为Excel文件的过程。该过程涉及接收前端参数、构造SQL查询语句、使用JDBC模板执行查询并填充到Excel表格中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public voidwriteToxls() throws Exception { 
		request = ServletActionContext.getRequest();
		response = ServletActionContext.getResponse();
		String orderType = java.net.URLDecoder.decode(request.getParameter("orderType"), "UTF-8");
		String regionName =java.net.URLDecoder.decode(request.getParameter("regionName"), "UTF-8");
		String startDate = request.getParameter("startDate");
		String endDate = request.getParameter("endDate");
		StringBuffer whereSql = new StringBuffer(" where 1=1 ");
		StringBuffer selectSql = new StringBuffer(
				"select flow_name, count(*) count from t_form_info");
 
		List unfinished = jdbcTemplate.queryForList(stateSql);
		List allcount = jdbcTemplate.queryForList(allstateSql);
 		String flowname ="工单未完成率";
		String fileName = flowname + ".xls";
		fileName = new String(fileName.getBytes(), "iso8859-1");// 解决中文
		response.setHeader("content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
		response.setContentType("application/msexcel");// 定义输出类型
		OutputStream os = response.getOutputStream();
		WritableWorkbook wwb = Workbook.createWorkbook(os);
		jxl.write.WritableSheet ws = wwb.createSheet(flowname, 0);
		jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
		jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
		jxl.write.Label labelCity = new jxl.write.Label(0, 0, "所属地市", wcfF);
		ws.addCell(labelCity);
		jxl.write.Label labelflowtype = new jxl.write.Label(1, 0, "工单类型", wcfF);
		ws.addCell(labelflowtype);
		jxl.write.Label labeltitle = new jxl.write.Label(2, 0, "未完成工单总数", wcfF);
		ws.addCell(labeltitle);
		jxl.write.Label labelflowno = new jxl.write.Label(3, 0, "工单总数", wcfF);
		ws.addCell(labelflowno);
		jxl.write.Label labelapplyperson = new jxl.write.Label(4, 0, "未完成率", wcfF);
		ws.addCell(labelapplyperson);
		 
		for (int i = 1; i <= unfinished.size(); i++) {
			Map resultMap = (Map) unfinished.get(i);
			String flowName = resultMap.get("FLOW_NAME").toString();
			String count1 = resultMap.get("count").toString();
			Map resultMap1 = (Map) allcount.get(i);
			String count2 = resultMap1.get("count").toString();
			int rate = Integer.parseInt(count1) / Integer.parseInt(count2);
			Label city = new Label(0, i, regionName);
			ws.addCell(city);
			Label type = new Label(1, i, flowName);
			ws.addCell(type);
			Label no = new Label(2, i, count1);
			ws.addCell(no);
			Label title = new Label(3, i, count2);
			ws.addCell(title);
			Label ratee = new Label(4, i, rate + "");
			ws.addCell(ratee);
		}
		// 写入 Exel 工作表
		wwb.write();
		// 关闭 Excel 工作薄对象
		wwb.close();
		os.close();
		
	}