使用 poi 通过浏览器方式导出Excel表格（导出字段与表格字段不一样且取值逻辑改变）

最新推荐文章于 2023-08-06 18:35:56 发布

fatetyjz

最新推荐文章于 2023-08-06 18:35:56 发布

阅读量482

点赞数

本文链接：https://blog.csdn.net/fatetyjz/article/details/112007443

版权

使用 poi 通过浏览器方式导出Excel表格（导出字段与表格字段不一样且取值逻辑改变）

思路：拿到表格页面查询的结果作为查询条件，表格页面的url和点击导出按钮的url是一样的，按钮的url多一个参数用来做判断区分

前端代码：

 <iframe id="exp" style="display:none" src=''></iframe>
 <button onclick="downLoad()" type="button" class="btn btn-default">导出Excel</button>
    <script>        
        function downLoad(){
            url = window.location.href +"&excel=1";
            document.getElementById("exp").src=url;
          //  window.location.href =url;
        }
    </script>

后台逻辑：表格页面action

public ActionForward showStatisticSheetList(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)throws Exception{
		IStatMethod statMethod = (IStatMethod) getBean(mapping.getAttribute());
		statMethod.showStatisticSheetList(mapping, form, request, response);
		String findListForward =request.getParameter("findListForward");
		List attribute = (List)request.getAttribute("estList");
		String excel = request.getParameter("excel");		
		if(excel != null){
			//List attribute = (List)request.getAttribute("estList");
			exportXls(attribute,response);
			return null;
		}
        return  mapping.findForward(findListForward);//mapping.findForward("statisticsheetlist");
	}

poi代码

public void exportXls(List attribute, HttpServletResponse response) throws Exception{
		
		String ids = "";
		for(int i=0;i<attribute.size();i++){
			ComplaintTimeoutMonitorDetailVO object = (ComplaintTimeoutMonitorDetailVO) attribute.get(i);
			String id = object.getMainid();
			ids += "'"+id +"',";
			
		}
		String id = ids.substring(0,ids.length()-1);
		IDownLoadSheetAccessoriesService mgr = (IDownLoadSheetAccessoriesService) ApplicationContextHolder.getInstance().getBean("IDownLoadSheetAccessoriesService");
		String sql = XmlManage.getFile("/config/statistic/complainttimeoutmonitor-config/sqlFile.xml").getProperty("queryCheckRuleList");
		String sqls = sql + " where main.id in ("+id+")";
		List list = mgr.getSheetAccessoriesList(sqls);
		Map object = (Map)list.get(0);
		object.get(object);
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("工单信息表");//定义一个表名
		HSSFRow row = sheet.createRow(0);//定义第一行
		row.createCell(0).setCellValue("工单流水号");//列标题
		row.createCell(1).setCellValue("主题");
		row.createCell(2).setCellValue("派单时间");
		row.createCell(3).setCellValue("受理时限");
		row.createCell(4).setCellValue("处理时限");
		row.createCell(5).setCellValue("故障地市");
		row.createCell(6).setCellValue("客服原始流水");
		row.createCell(7).setCellValue("客户流水号");
		row.createCell(8).setCellValue("电话号码");
		row.createCell(9).setCellValue("处理环节");
		row.createCell(10).setCellValue("处理环节操作类型");
		row.createCell(11).setCellValue("处理环节处理角色或处理人");
		row.createCell(12).setCellValue("操作人部门");
		row.createCell(13).setCellValue("操作人角色");
		row.createCell(14).setCellValue("处理环节退回原因");
		row.createCell(15).setCellValue("到达此操作的处理时间");
		


		for (int i=0; i<list.size();i++){//循环调用查询出来的数据，在循环插入
		    HSSFRow row1 = sheet.createRow(i+1);//定义i+1行，因为第i行且i的值为零则如果从i开始会和上面定义第一行冲突，所以要从第i+1行开始
		    Map map = (Map)list.get(i);
		    row1.createCell(0).setCellValue((String)map.get("sheetid"));
		    row1.createCell(1).setCellValue((String)map.get("title"));
		    row1.createCell(2).setCellValue((String)map.get("sendtime"));
		    row1.createCell(3).setCellValue((String)map.get("acceptlimit"));
		    row1.createCell(4).setCellValue((String)map.get("completelimit"));
		    row1.createCell(5).setCellValue((String)map.get("tocity"));
		    row1.createCell(6).setCellValue((String)map.get("oldserialno"));
		    row1.createCell(7).setCellValue((String)map.get("parentcorrelation"));
		    row1.createCell(8).setCellValue((String)map.get("customphone"));
		    row1.createCell(9).setCellValue((String)map.get("operatetype"));
		    row1.createCell(10).setCellValue((String)map.get("activetemplateid"));
		    row1.createCell(11).setCellValue((String)map.get("toorgroleid"));
		    row1.createCell(12).setCellValue((String)map.get("deptname"));
		    row1.createCell(13).setCellValue((String)map.get("subrolename"));
		    row1.createCell(14).setCellValue((String)map.get("remark"));
		    row1.createCell(15).setCellValue((String)map.get("operatetime"));
		    

		}
		//输出流
		String filename ="工单信息表.xls";
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-Disposition", "attachment;filename="
				.concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
		 OutputStream out = response.getOutputStream();
		try {
			workbook.write(out);// 将数据写出去		
		} catch (Exception e) {
			e.printStackTrace();	
		} finally {
			out.close();
		}
		
	}

sql通过配置文件读取：

<?xml version="1.0" encoding="UTF-8"?>
<boco>
	
	<queryCheckRuleList>
	SELECT DISTINCT
	main.id AS mainid,
	main.sheetId AS sheetid,
	main.title AS title,
	main.STATUS AS STATUS,
	main.mainInterfaceSheetType AS intertype,
	TO_CHAR ( main.sendTime, 'yyyy-MM-dd HH24:MI:ss' ) AS sendtime,
	TO_CHAR ( main.sheetAcceptLimit, 'yyyy-MM-dd HH24:MI:ss' ) AS acceptlimit,
	TO_CHAR ( main.sheetCompleteLimit, 'yyyy-MM-dd HH24:MI:ss' ) AS completelimit,
	( CASE WHEN main.mainComplaintAreaCity IS NULL THEN GET_AREANAME_BY_AREAID (main.customAttribution) ELSE GET_AREANAME_BY_AREAID ( main.mainComplaintAreaCity ) END ) AS tocity,
	main.oldserialNo AS oldserialno,
	main.parentcorrelation AS parentcorrelation,
	main.customphone AS customphone,
	c.operatetype,
	c.activetemplateid,
	c.toorgroleid,
	c.deptname,
	c.subrolename,
	c.remark,
	c.operatetime 
FROM
	complaint_main main
	LEFT JOIN (
SELECT
	t.mainid,
	nvl ( t1.NAME, '新增工单' ) AS operatetype,
	nvl ( t2.NAME, '新建派发' ) AS activetemplateid,
	nvl ( t3.SUBROLENAME, t4.username ) AS toorgroleid,
	t5.deptname AS deptname,
	t6.SUBROLENAME AS subrolename,
CASE
	
	WHEN t.operatetype = '17' THEN
	t.REMARK ELSE '' 
	END AS remark,
	TO_CHAR ( t.operatetime, 'yyyy-MM-dd HH24:MI:ss' ) AS operatetime 
FROM
	( SELECT a.*, row_number ( ) over ( PARTITION BY a.mainid ORDER BY a.operatetime DESC ) rw FROM complaint_link a ) t
	LEFT JOIN v_complaint_operationType t1 ON t1.id = to_char ( t.OPERATETYPE )
	LEFT JOIN v_complaint_operationType t2 ON t2.id = to_char ( t.ACTIVETEMPLATEID )
	LEFT JOIN taw_system_sub_role t3 ON t3.id = t.TOORGROLEID
	LEFT JOIN taw_system_user t4 ON t4.userid = t.OPERATEUSERID
	LEFT JOIN taw_system_dept t5 ON t5.deptid = t.OPERATEDEPTID
	LEFT JOIN taw_system_sub_role t6 ON t6.id = t.OPERATEROLEID 
WHERE
	t.rw = 1 
) c ON c.mainid = main.id 

	</queryCheckRuleList>
	
	
</boco>

"GET_AREANAME_BY_AREAID"自定义函数：

CREATE OR REPLACE
FUNCTION           "GET_AREANAME_BY_AREAID"(area_id in varchar2) return varchar2 is
  Result varchar2(100);
begin
 /*function:获取地域名称 */
  select areaName into result from taw_system_area r where r.areaId=area_id;
  return(Result);
end GET_AREANAME_BY_AREAID;