导出Excel

疯狂的土豆丶
于 2018-10-15 17:02:44 发布
阅读量145
点赞数
分类专栏：办公文章标签：导出Excel
本文链接：https://blog.csdn.net/qq_43420577/article/details/83061449
版权
办公专栏收录该内容
9 篇文章 0 订阅
订阅专栏
本文介绍了一种使用Java和JExcelAPI从数据库查询数据并将其导出到Excel的方法。通过构造复杂的SQL查询，从多个数据库表中获取单元工程的相关信息，包括编码、名称、桩号等，并将这些数据格式化后输出到Excel工作表。
摘要由CSDN通过智能技术生成
@RequestMapping(params = "doExcel")
	public void doExcel(HttpServletRequest request, HttpServletResponse response) {
		String fxgc = request.getParameter("fxgc");
		String sql = "select x.* from (select x.id, x.fsdygcbm,x.fsfxgcid,x.fsdygcmc,x.fsqzzh,x.fsqzgc,x.FDSJKGSJ,x.FDSJWGSJ," +
				"IFNULL(y.FSUNITSTATUS,3) as FSUNITSTATUS, y.FDSTARTDATE,t2.END_TIME_ as FDENDDATE,y.FSUNITSGRESULT,y.FSUNITJLRESULT," +
				"t2.REALNAME,t2.START_TIME_,t2.END_TIME_ from t_dygcgl_dygcxx x LEFT JOIN t_web_dygcgl_dygcjcb y " +
				"on x.ID = y.FSUNITID LEFT JOIN (select FDJLTIME1 as fdjlpdtime,FSDYGCXXID,s.FSPROINSID,u.REALNAME,k.START_TIME_,k.END_TIME_ from t_dygcgl_dygcxx_qbs s LEFT JOIN" +
				" t_dygcgl_qbsjcxx x on s.FSQBSID = x.ID LEFT JOIN act_hi_procinst v on s.FSPROINSID = v.PROC_INST_ID_ LEFT JOIN act_hi_taskinst k on k.PROC_INST_ID_ = v.PROC_INST_ID_ " +
				" LEFT JOIN t_sys_user u on u.id = k.ASSIGNEE_ where FSPX = '1' AND x.FSQBSBM like '%01' AND k.TASK_DEF_KEY_ = 'usertask99' ) t2  ON x.ID = t2.FSDYGCXXID " +
			    " union select q.id,q.fsdygcbm,q.FSFXGCMC,q.fsdygcmc,q.FSQZZH,q.FSQZGC,q.FSBDH,q.FSCH,IFNULL(q.FSLX,1) as FSUNITSTATUS," +
				"q.FDJHKGSJ,q.FDSJWGSJ,q.FSCREATEDEPTNAME,q.FSCREATEDEPTNAME,q.FSCREATEUSERNAME,q.FDCREATETIME,q.FDJHKGSJ FROM t_dygcgl_dygcxx_pd q) x ";
		List<Map<String, Object>> dygcList = systemService.findForJdbc(sql, new Object[0]);
		String fileName="单元工程清单.xls";
		String mainTitle = "单元工程清单";
		// 以下开始输出到EXCEL
		final String userAgent = request.getHeader("USER-AGENT").toLowerCase();
        try {
            String finalFileName = null;
            if(StringUtils.contains(userAgent, "msie")){//IE浏览器
                finalFileName = URLEncoder.encode(fileName,"UTF8");
            }else if(StringUtils.contains(userAgent, "chrome")){//google
            	finalFileName = URLEncoder.encode(fileName,"UTF8");
            }else if(StringUtils.contains(userAgent, "mozilla")){//火狐浏览器
            	finalFileName = new String(fileName.getBytes(), "ISO8859-1");
            }else{
                finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
            }
			
			//定义输出流，以便打开保存对话框______________________begin
			OutputStream os = response.getOutputStream();// 取得输出流      
			response.reset();// 清空输出流      
			response.setHeader("Content-disposition", "attachment; filename="+ finalFileName);
			// 设定输出文件头      
			response.setContentType("application/msexcel");// 定义输出类型    
			//定义输出流，以便打开保存对话框_______________________end
			
			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			
			/** **********创建工作表************ */
			WritableSheet sheet = workbook.createSheet("单元工程清单", 0);
			
			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);
			
			/** ************设置单元格字体************** */
			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10);//WritableFont.BOLD
			WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 12);
//			WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD);
			
			/** ************以下设置三种单元格样式，灵活备用************ */
			// 用于表头居中
			WritableCellFormat wcf_titlt = new WritableCellFormat(TitleFont);
			wcf_titlt.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_titlt.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_titlt.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_titlt.setWrap(false); // 文字是否换行
			
			
			// 用于表头居中
			WritableCellFormat wcf_top = new WritableCellFormat(BoldFont);
			wcf_top.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_top.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_top.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_top.setWrap(false); // 文字是否换行
			   
			// 用于正文居左
			WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_center.setWrap(false); // 文字是否换行   
			
			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
			wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
			wcf_left.setWrap(false); // 文字是否换行   
			
			
			 
			
			//--------------------------标题------start---
			sheet.mergeCells(0, 0, 8, 0);
			sheet.addCell(new Label(0, 0, mainTitle, wcf_titlt));
			//--------------------------标题------end---
			
			//--------------------------表头------start---
			int colInt = 0;
			int rowInt = 1;
			
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"单元工程编码",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"单元工程名称",wcf_top));
			sheet.setColumnView(colInt, 25);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"桩号",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"起止高程",wcf_top));
			sheet.setColumnView(colInt, 40);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"施工状态",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"施工开始时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"施工结束时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"报验发起时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"报验结束时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"施工评定等级",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"监理评定等级",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"监理评定人",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"监理评定时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"监理签字时间",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			sheet.mergeCells(colInt, rowInt, colInt, rowInt);
			sheet.addCell(new Label(colInt, rowInt,"备注",wcf_top));
			sheet.setColumnView(colInt, 15);
			colInt++;
			rowInt ++;
			
			for (Map<String, Object> map : dygcList) {
				colInt = 0;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("fsdygcbm")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("fsdygcmc")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("fsqzzh")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("fsqzgc")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FSUNITSTATUS")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FDSJKGSJ")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FDSJWGSJ")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FDSTARTDATE")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FDENDDATE")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FSUNITSGRESULT")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FSUNITJLRESULT")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("REALNAME")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("START_TIME_")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("END_TIME_")), wcf_center));
				colInt++;
				sheet.mergeCells(colInt, rowInt, colInt, rowInt);
				sheet.addCell(new Label(colInt, rowInt, PageOfficeUtil.isNull(map.get("FSBZ")), wcf_center));
				colInt++;
				rowInt++;
			}

			/** **********将以上缓存中的内容写到EXCEL文件中******** */
			workbook.write();
			/** *********关闭文件************* */
			workbook.close();   
		} catch (Exception e) {
			e.printStackTrace();
		}
	}