导出Excel
程序员文章站
2024-03-20 18:07:46
...
@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();
}
}
上一篇： Java中的自定义异常类
下一篇： js中取session的值