1.前台页面代码：
<tr>
<td><input dataId="excel" type="button" value="导出Excel" onclick="exportExcel(this)"/></td>
</tr>
2.js代码：
function exportExcel(){
var pros = $('#problemType').combotree('getValues');
var pro = "";
if(pros.length>0){
for(var i=0;i<pros.length;i++){
if(i!=pros.length-1){
pro += pros[i] + ",";
}else{
pro += pros[i];
}
}
}
location.href='<@s.url namespace="/app/pollEnterprise" action="psCheckInfor!exportExcel" includeParams="none" />?startTime='+$("#startTime").val()+
'&endTime='+$("#endTime").val()+'&areacode='+$("#areaCode").combotree('getValue')+'&hasProblem='+$("#hasProblem").val()+
'&psname='+$("#psname").val()+'&deal='+$('#deal').val()+'&dealOther='+$('#dealOther').val()+'&pstype='+$('#hy').val()+
'&problemType='+pro+'&isComplete='+$('#isComplete').val();
};
3.java代码：
（1）导出方法exportExcel()代码：
/**
* 获取数据,导出excel
*/
public void exportExcel() throws Exception {
String excelName = "排污企业检查.xls";
String sheetName = "排污企业检查";
//结果集，表头，excel名，页签名
Map<String,Object> map = new HashMap<String,Object>();
map = this.argMap(map);
List<Object> list = this.psCheckInforManager.findAllObject(map);
this.generateExcel(list,excelName, sheetName);
}
（2）拼装excel文件方法generateExcel(list,excelName, sheetName)代码：
/**
* 生成excel，并触发下载的方法
*/
public void generateExcel(List<Object> list,String excelName,String sheetName) throws Exception {
//文件生成到临时目录中
String parentPath = JointFrameConfigManager.getDatafileRootRelPath("excel/temp/");
//在parentPath路径下创建一个文件
File pFile = new File(parentPath);
if (!pFile.exists()) {
//创建此抽象路径名，包括必要的和不存在的父目录的目录
pFile.mkdirs();
}
//System.nanoTime():系统计时器的当前值;file.separator:考虑到了跨平台，将分隔符用File.separator 代替
String filePath = parentPath + File.separator + System.nanoTime()
+ ".xls";
//创建一个可读写的工作簿
WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath));
//生成名为"sheetName"的工作表，参数0表示这是第一页
WritableSheet sheet = workbook.createSheet(sheetName, 0); //标题样式
WritableFont titleFont = new WritableFont(WritableFont.TIMES, 12,
WritableFont.NO_BOLD, false);
//设置标题字体颜色
titleFont.setColour(Colour.WHITE);
//设置标题字体样式
WritableCellFormat titleStyle = new WritableCellFormat(titleFont);
//设置标题的背景颜色
titleStyle.setBackground(Colour.LIGHT_BLUE);
//设置边框
titleStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
//将标题的字体设置为居中显示
titleStyle.setAlignment(Alignment.CENTRE); //报表尾样式
WritableFont toolFont = new WritableFont(WritableFont.TIMES, 11,
WritableFont.NO_BOLD, false);
//设置字体颜色
toolFont.setColour(Colour.WHITE);
//设置样式
WritableCellFormat toolStyle = new WritableCellFormat(toolFont);
//设置背景颜色
toolStyle.setBackground(Colour.LIGHT_BLUE);
//设置边框
toolStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
//设置居中
toolStyle.setAlignment(Alignment.RIGHT); // 表格头样式
WritableFont times16font = new WritableFont(WritableFont.TIMES, 11,
WritableFont.NO_BOLD, false);
//设置字体颜色
times16font.setColour(Colour.WHITE);
//设置样式
WritableCellFormat headStyle = new WritableCellFormat(times16font);
//设置背景颜色
headStyle.setBackground(Colour.LIGHT_BLUE);
//设置边框
headStyle.setBorder(Border.ALL, BorderLineStyle.THIN); // 高亮行样式
WritableFont highLigthRowFont = new WritableFont(WritableFont.TIMES,
10, WritableFont.NO_BOLD, false);
highLigthRowFont.setColour(Colour.BLACK);
WritableCellFormat highLigthRowStyle = new WritableCellFormat(
highLigthRowFont);
highLigthRowStyle.setBackground(Colour.LIGHT_GREEN);
highLigthRowStyle.setBorder(Border.ALL, BorderLineStyle.THIN); // 非高亮行样式
WritableFont unHighLigthRowFont = new WritableFont(WritableFont.TIMES,
10, WritableFont.NO_BOLD, false);
unHighLigthRowFont.setColour(Colour.BLACK);
WritableCellFormat unHighLigthRowStyle = new WritableCellFormat(
unHighLigthRowFont);
unHighLigthRowStyle.setBackground(Colour.WHITE);
unHighLigthRowStyle.setBorder(Border.ALL, BorderLineStyle.THIN); //定义表头列名数组
// String[] columns =cols;
// 设置Excel页签名字
sheet.setName(sheetName);
//合并标题单元格（mergeCells(a,b,c,d)）a:要合并单元格的开始x坐标，b:要合并单元格的开始y坐标
//c:要合并单元格的结束x坐标,d:要合并单元格的结束y坐标 sheet.mergeCells(0, 0, 42, 0);
//设置标题
sheet.mergeCells(0, 1, 0, 3);
sheet.mergeCells(1, 1, 1, 3);
sheet.mergeCells(2, 1, 2, 3);
sheet.mergeCells(3, 1, 3, 3);
sheet.mergeCells(4, 1, 4, 3);
sheet.mergeCells(5, 1, 5, 3);
sheet.mergeCells(6, 1, 6, 3);
sheet.mergeCells(7, 1, 7, 3);
sheet.mergeCells(8, 1, 8, 3);
sheet.mergeCells(9, 1, 9, 3);
sheet.mergeCells(10, 1, 10, 3);
sheet.mergeCells(11, 1, 17, 1);//合并“存在问题类型”
sheet.mergeCells(11, 2, 11, 3);
sheet.mergeCells(12, 2, 12, 3);
sheet.mergeCells(13, 2, 13, 3);
sheet.mergeCells(14, 2, 14, 3);
sheet.mergeCells(15, 2, 15, 3);
sheet.mergeCells(16, 2, 16, 3);
sheet.mergeCells(17, 2, 17, 3);
sheet.mergeCells(18, 1, 25, 1);//合并“处理情况”
sheet.mergeCells(18, 2, 18, 3);
sheet.mergeCells(19, 2, 19, 3);
sheet.mergeCells(20, 2, 20, 3);
sheet.mergeCells(21, 2, 21, 3);
sheet.mergeCells(22, 2, 22, 3);
sheet.mergeCells(23, 2, 24, 2);//合并“处罚”
sheet.mergeCells(23, 3, 23, 3);
sheet.mergeCells(24, 3, 24, 3);
sheet.mergeCells(25, 2, 25, 3);
sheet.mergeCells(26, 1, 30, 1);//合并“其它措施”
sheet.mergeCells(26, 2, 26, 3);
sheet.mergeCells(27, 2, 27, 3);
sheet.mergeCells(28, 2, 28, 3);
sheet.mergeCells(29, 2, 29, 3);
sheet.mergeCells(30, 2, 30, 3);
sheet.mergeCells(31, 1, 31, 3);
sheet.mergeCells(32, 1, 32, 3);
sheet.mergeCells(33, 1, 33, 3);
sheet.mergeCells(34, 1, 34, 3);
sheet.mergeCells(35, 1, 35, 3);
sheet.mergeCells(36, 1, 36, 3);
sheet.mergeCells(37, 1, 37, 3);
sheet.mergeCells(38, 1, 38, 3);
sheet.mergeCells(39, 1, 39, 3);
sheet.mergeCells(40, 1, 40, 3);
sheet.mergeCells(41, 1, 41, 3);
sheet.mergeCells(42, 1, 42, 3); //将定义好的单元格添加到工作表中
/*例如：label=new Label(0,2,"编号",format2);
sheet.addCell(label);*/
sheet.addCell(new Label(0, 0, sheetName, titleStyle));
sheet.addCell(new Label(0, 1, "企业名称", headStyle));
sheet.addCell(new Label(1, 1, "行业", headStyle));
sheet.addCell(new Label(2, 1, "企业所属地", headStyle));
sheet.addCell(new Label(3, 1, "填报单位", headStyle));
sheet.addCell(new Label(4, 1, "检查时间", headStyle));
sheet.addCell(new Label(5, 1, "建设项目名称", headStyle));
sheet.addCell(new Label(6, 1, "建成投运时间", headStyle));
sheet.addCell(new Label(7, 1, "是否属于工业园区", headStyle));
sheet.addCell(new Label(8, 1, "工业园区名称", headStyle));
sheet.addCell(new Label(9, 1, "是否存在问题", headStyle));
sheet.addCell(new Label(10, 1, "存在问题", headStyle));
sheet.addCell(new Label(11, 1, "存在问题类型", headStyle));
sheet.addCell(new Label(11, 2, "超标排放 ", headStyle));
sheet.addCell(new Label(12, 2, "未批先建 ", headStyle));
sheet.addCell(new Label(13, 2, "未执行三同时 ", headStyle));
sheet.addCell(new Label(14, 2, "不正常使用污染防治设施 ", headStyle));
sheet.addCell(new Label(15, 2, "偷排偷放 ", headStyle));
sheet.addCell(new Label(16, 2, "伪造或篡改环境监测数据 ", headStyle));
sheet.addCell(new Label(17, 2, "其它 ", headStyle));
sheet.addCell(new Label(18, 1, "处理情况", headStyle));
sheet.addCell(new Label(18, 2, "责令停止建设", headStyle));
sheet.addCell(new Label(19, 2, "责令停产", headStyle));
sheet.addCell(new Label(20, 2, "责令限期改正或治理 ", headStyle));
sheet.addCell(new Label(21, 2, "责令关停取缔 ", headStyle));
sheet.addCell(new Label(22, 2, "限期补办环评手续 ", headStyle));
sheet.addCell(new Label(23, 2, "处罚", headStyle));
sheet.addCell(new Label(23, 3, "是/否 ", headStyle));
sheet.addCell(new Label(24, 3, "处罚金额", headStyle));
sheet.addCell(new Label(25, 2, "其它", headStyle));
sheet.addCell(new Label(26, 1, "其它措施", headStyle));
sheet.addCell(new Label(26, 2, "实施查封、扣押 ", headStyle));
sheet.addCell(new Label(27, 2, "实施按日连续处罚", headStyle));
sheet.addCell(new Label(28, 2, "实施限产、停产 ", headStyle));
sheet.addCell(new Label(29, 2, "移送司法机关及相关部门", headStyle));
sheet.addCell(new Label(30, 2, "其它 ", headStyle));
sheet.addCell(new Label(31, 1, "按日处罚开始时间", headStyle));
sheet.addCell(new Label(32, 1, "按日处罚结束时间", headStyle));
sheet.addCell(new Label(33, 1, "按日处罚金额", headStyle));
sheet.addCell(new Label(34, 1, "整改时限", headStyle));
sheet.addCell(new Label(35, 1, "完成情况", headStyle));
sheet.addCell(new Label(36, 1, "完成时间 ", headStyle));
sheet.addCell(new Label(37, 1, "完成情况描述 ", headStyle));
sheet.addCell(new Label(38, 1, "未完成情况描述 ", headStyle));
sheet.addCell(new Label(39, 1, "责任单位", headStyle));
sheet.addCell(new Label(40, 1, "监管单位", headStyle));
sheet.addCell(new Label(41, 1, "填报人", headStyle));
sheet.addCell(new Label(42, 1, "审核人", headStyle)); //循环设置单元格宽度和高度
for(int j=0;j<42;j++){
sheet.setColumnView(j, 15);//设excel单元格的宽度
sheet.setRowView(j, 300);//设excel单元格的高度
if(j==0 || j==1 || j==2 || j==3){
sheet.setColumnView(j, 25);
sheet.setRowView(j, 400);
}
} //生成数据行，并且间隔行高亮显示
int rowIndex = 4; //循环遍历结果集
for (int i=0;i<list.size();i++) {
int colIndex2 = 0;
Object[] objs = (Object[]) list.get(i);
//{objs[0]==null ? "" : objs[0].toString()}：三目运算：如果?前面的结果为true，那么执行冒号后面第一个条件""，如果?前面的结果为false，那么执行后面的结果，即objs[0].toString();
sheet.addCell(new Label(colIndex2++, rowIndex, objs[0]==null ? "" : objs[0].toString()));//企业名称
sheet.addCell(new Label(colIndex2++, rowIndex, objs[1]==null ? "" : objs[1].toString()));//行业
sheet.addCell(new Label(colIndex2++, rowIndex, objs[3]==null ? "" : objs[3].toString()));//填报单位
sheet.addCell(new Label(colIndex2++, rowIndex, objs[2]==null ? "" : objs[2].toString()));//企业所属地
sheet.addCell(new Label(colIndex2++, rowIndex, objs[4]==null ? "" : objs[4].toString()));//检查时间
sheet.addCell(new Label(colIndex2++, rowIndex, objs[5]==null ? "" : objs[5].toString()));//建设项目名称
sheet.addCell(new Label(colIndex2++, rowIndex, objs[6]==null ? "" : objs[6].toString()));//建成投运时间
sheet.addCell(new Label(colIndex2++, rowIndex, objs[7]==null ? "" : objs[7].toString()));//是否属于<br/>工业园区
sheet.addCell(new Label(colIndex2++, rowIndex, objs[8]==null ? "" : objs[8].toString()));//工业园区名称
sheet.addCell(new Label(colIndex2++, rowIndex, objs[9]==null ? "" : objs[9].toString()));//是否存在问题
sheet.addCell(new Label(colIndex2++, rowIndex, objs[10]==null ? "" : objs[10].toString()));//存在问题
sheet.addCell(new Label(colIndex2++, rowIndex, objs[11]==null ? "" : objs[11].toString()));//超标排放
sheet.addCell(new Label(colIndex2++, rowIndex, objs[12]==null ? "" : objs[12].toString()));//未批先建
sheet.addCell(new Label(colIndex2++, rowIndex, objs[13]==null ? "" : objs[13].toString()));//未执行三同时
sheet.addCell(new Label(colIndex2++, rowIndex, objs[14]==null ? "" : objs[14].toString()));//不正常使用污染防治设施
sheet.addCell(new Label(colIndex2++, rowIndex, objs[15]==null ? "" : objs[15].toString()));//偷排偷放
sheet.addCell(new Label(colIndex2++, rowIndex, objs[16]==null ? "" : objs[16].toString()));//伪造或篡改环境监测数据
sheet.addCell(new Label(colIndex2++, rowIndex, objs[17]==null ? "" : objs[17].toString()));//其它
sheet.addCell(new Label(colIndex2++, rowIndex, objs[18]==null ? "" : objs[18].toString()));//责令停止建设
sheet.addCell(new Label(colIndex2++, rowIndex, objs[19]==null ? "" : objs[19].toString()));//责令停产
sheet.addCell(new Label(colIndex2++, rowIndex, objs[20]==null ? "" : objs[20].toString()));//责令限期改正或治理
sheet.addCell(new Label(colIndex2++, rowIndex, objs[21]==null ? "" : objs[21].toString()));//责令关停取缔
sheet.addCell(new Label(colIndex2++, rowIndex, objs[22]==null ? "" : objs[22].toString()));//限期补办环评手续
sheet.addCell(new Label(colIndex2++, rowIndex, objs[23]==null ? "" : objs[23].toString()));//是/否 处罚
sheet.addCell(new Label(colIndex2++, rowIndex, objs[24]==null ? "" : objs[24].toString()));//处罚金额
sheet.addCell(new Label(colIndex2++, rowIndex, objs[25]==null ? "" : objs[25].toString()));//其它
sheet.addCell(new Label(colIndex2++, rowIndex, objs[26]==null ? "" : objs[26].toString()));//实施查封、扣押
sheet.addCell(new Label(colIndex2++, rowIndex, objs[27]==null ? "" : objs[27].toString()));//实施按日连续处罚
sheet.addCell(new Label(colIndex2++, rowIndex, objs[28]==null ? "" : objs[28].toString()));//实施限产、停产
sheet.addCell(new Label(colIndex2++, rowIndex, objs[29]==null ? "" : objs[29].toString()));//移送司法机关及相关部门
sheet.addCell(new Label(colIndex2++, rowIndex, objs[30]==null ? "" : objs[30].toString()));//其它
sheet.addCell(new Label(colIndex2++, rowIndex, objs[31]==null ? "" : objs[31].toString()));//按日处罚开始时间
sheet.addCell(new Label(colIndex2++, rowIndex, objs[32]==null ? "" : objs[32].toString()));//按日处罚结束时间
sheet.addCell(new Label(colIndex2++, rowIndex, objs[33]==null ? "" : objs[33].toString()));//按日处罚金额
sheet.addCell(new Label(colIndex2++, rowIndex, objs[34]==null ? "" : objs[34].toString()));//整改时限
sheet.addCell(new Label(colIndex2++, rowIndex, objs[35]==null ? "" : objs[35].toString()));//完成情况
sheet.addCell(new Label(colIndex2++, rowIndex, objs[36]==null ? "" : objs[36].toString()));//完成时间
if(objs[37]!=null && !"".equals(objs[37].toString())){
sheet.addCell(new Label(colIndex2++, rowIndex, objs[37].toString()));//完成情况描述
}else{
sheet.addCell(new Label(colIndex2++, rowIndex, ""));
}
sheet.addCell(new Label(colIndex2++, rowIndex, objs[38]==null ? "" : objs[38].toString()));//未完成情况描述
if(objs[39]!=null && !"".equals(objs[39].toString())){
sheet.addCell(new Label(colIndex2++, rowIndex, objs[39].toString()));//责任单位
}else{
sheet.addCell(new Label(colIndex2++, rowIndex, ""));
}
if(objs[40]!=null && !"".equals(objs[40].toString())){
sheet.addCell(new Label(colIndex2++, rowIndex, objs[40].toString()));//监管单位
}else{
sheet.addCell(new Label(colIndex2++, rowIndex, ""));
}
sheet.addCell(new Label(colIndex2++, rowIndex, objs[41]==null ? "" : objs[41].toString()));//填报人
sheet.addCell(new Label(colIndex2++, rowIndex, objs[42]==null ? "" : objs[42].toString()));//审核人
rowIndex++;
} //获取总记录条数
int totle = 0;
if (list != null) {
totle = list.size();
} //设置报表尾
sheet.mergeCells(0, rowIndex, 42, rowIndex);
sheet.mergeCells(0, rowIndex+1, 42, rowIndex+1);
sheet.addCell(new Label(0, rowIndex+1, "报告已完成 / 共计：" + totle + " 条。 ",toolStyle));
workbook.write();
workbook.close();
//生成完毕 //下载生成的报表文件
File xlsFile = new File(filePath);
if (xlsFile.exists()) {
response.setHeader("Content-Disposition", "attachment; filename="
+ new String(excelName.getBytes("GBK"), "ISO8859-1")); // filename应该是编码后的(utf-8)
response.setHeader("charset", "utf-8");
response.setContentType("application/vnd.ms-excel");//设置下载文件的格式，此处为msexcel
OutputStream os = response.getOutputStream();
InputStream is = new FileInputStream(xlsFile);
byte[] tmp = new byte[1024];
int len;
while ((len = is.read(tmp)) != -1) {
os.write(tmp, 0, len);
}
is.close();
os.flush();
os.close(); // 下载完成后删除临时文件
xlsFile.delete();
} }
（3）封装查询条件方法argMap(Map<String,Object> map)代码:
/**
* 封装查询条件
* @param map
* @return
*/
public Map<String,Object> argMap(Map<String,Object> map){
String userId = LoginUtil.getAppLoginUserId(request);
UserInforVo mdvo = enterpriseManager.findById(userId);
//appadmin不进行数据过滤
if(!mdvo.getUserName().equals("平台管理员")){
level = enterpriseManager.getUserRoleRegion(mdvo.getRegionCode());
String regions=enterpriseManager.getUserRoleChildRegion(mdvo.getRegionCode(), level);
if(regions.length()>0){
regions = regions.substring(0, regions.length()-1);
}
if(areacode!=null && !"".equals(areacode)){
level = enterpriseManager.getUserRoleRegion(areacode);
String regions_=enterpriseManager.getUserRoleChildRegion(areacode, level); //得到该行政区划下所有子区划
//如果行政区划选“全部”
if(areacode.equals("all"))
{
map.put("regions",regions);
}else if("hz".equals(areacode)){ //以下是行政区划选“**本级”
map.put("regions", "610700000");
}else if("ak".equals(areacode)){
map.put("regions", "610900000");
}else if("bj".equals(areacode)){
map.put("regions", "610300000");
}else if("tc".equals(areacode)){
map.put("regions", "610200000");
}else if("hc".equals(areacode)){
map.put("regions", "610581000");
}else if("xy".equals(areacode)){
map.put("regions", "610400000");
}else if("ya".equals(areacode)){
map.put("regions", "610600000");
}else if("xa".equals(areacode)){
map.put("regions", "610100000");
}else if("xx".equals(areacode)){
map.put("regions", "610101000");
}else if("ylq".equals(areacode)){
map.put("regions", "610403000");
}else if("yl".equals(areacode)){
map.put("regions", "610800000");
}else if("wn".equals(areacode)){
map.put("regions", "610500000");
}else if("sl".equals(areacode)){
map.put("regions", "611000000");
}else if("sx".equals(areacode)){
map.put("regions", "610000000");
}else if(level==2 || level==1){ //选择省级或市级
map.put("regions", regions_);
}else {
map.put("regions", areacode);
}
}
else
{
map.put("regions",regions);
} } if((startTime!=null && !"".equals(startTime)) && (endTime!=null && !"".equals(endTime))){
map.put("startTime", startTime);
map.put("endTime", endTime);
}
if(isComplete!=null && !"".equals(isComplete)){
if(!"2".equals(isComplete)){
map.put("isComplete", isComplete);
}
}
if(pstype!=null && !"".equals(pstype)){
map.put("pstype", pstype);
}
if(psname!=null && !"".equals(psname)){
map.put("psname", psname);
} if(hasProblem!=null && !"".equals(hasProblem)){
if(!"2".equals(hasProblem)){
map.put("hasProblem", hasProblem);
}
}
if(deal!=null && !"".equals(deal)){
if("0".equals(deal)){ }
if("1".equals(deal)){
map.put("dealStopBuild", 1);
}
if("2".equals(deal)){
map.put("dealStopProduction", 1);
}
if("3".equals(deal)){
map.put("dealPunishment", 1);
}
if("4".equals(deal)){
map.put("dealClampdown", 1);
}
if("5".equals(deal)){
map.put("dealXqbbhpsx", 1);
}
if("6".equals(deal)){
map.put("dealRestrict", 1);
}
if("7".equals(deal)){
map.put("dealPunishmentOther", 1);
}
map.put("deal", deal.equals("0") ? "" : deal);//导出excel时用
}
if(problemType!=null && !"".equals(problemType)){
String[] problemTypes = problemType.split(",");
if(problemTypes.length>0){
for(int k=0;k<problemTypes.length;k++){
if("0".equals(problemTypes[k])){
}
if("1".equals(problemTypes[k])){
map.put("dealNotExecutCbpf", 1);
}
if("2".equals(problemTypes[k])){
map.put("dealNotapprovedBeforeConstruction", 1);
}
if("3".equals(problemTypes[k])){
map.put("dealNotExecutThreetongshizhidu", 1);
}
if("4".equals(problemTypes[k])){
map.put("dealbzcsywrfzcs", 1);
}
if("5".equals(problemTypes[k])){
map.put("dealStealingDischarge", 1);
}
if("6".equals(problemTypes[k])){
map.put("dealCounterfeitMisrepresentData", 1);
}
if("7".equals(problemTypes[k])){
map.put("dealOther", 1);
}
}
} }
if(dealOther!=null && !"".equals(dealOther)){
if("0".equals(dealOther)){ }
if("1".equals(dealOther)){
map.put("dealOtherAttachment", 1);
}
if("2".equals(dealOther)){
map.put("dealOtherPunishmentperday", 1);
}
if("3".equals(dealOther)){
map.put("dealOtherControlProduction", 1);
}
if("4".equals(dealOther)){
map.put("dealOtherForlaw", 1);
}
if("5".equals(dealOther)){
map.put("dealOtherOther", 1);
}
//map.put("dealOther", dealOther.equals("0") ? "" : dealOther);//导出excel时用
}
return map; }
（4）查找符合条件信息的方法findAllObject(Map<String, Object> map)代码:
public List<Object> findAllObject(Map<String, Object> map) {
int isComplete = 3;
String psname = "";
String pstype = "";
String hasProblem = "";
String startTime = "";
String endTime = "";
String deal = "";
String dealOther = "";
List<String> psids = new ArrayList<String>();
// 完成状态
if (map.get("isComplete") != null && !"".equals(map.get("isComplete"))) {
isComplete = Integer.parseInt(map.get("isComplete").toString());
}
// 是否存在问题
if (map.get("hasProblem") != null && !"".equals(map.get("hasProblem"))) {
hasProblem = (String) map.get("hasProblem");
}
// 企业名称
if (map.get("psname") != null && !"".equals(map.get("psname"))) {
psname = (String) map.get("psname");
}
// 行业
if (map.get("pstype") != null && !"".equals(map.get("pstype"))) {
pstype = (String) map.get("pstype");
}
// 检查时间
if (map.get("startTime") != null && !"".equals(map.get("startTime"))) {
startTime = (String) map.get("startTime");
}
if (map.get("endTime") != null && !"".equals(map.get("endTime"))) {
endTime = (String) map.get("endTime");
}
// 填报单位
String newstr = "";
if (map.get("regions") != null && !"".equals(map.get("regions"))) {
String str = map.get("regions").toString();
String[] str1 = str.split(",");
if (str1.length > 0) {
for (int i = 0; i < str1.length; i++) {
if (i == str1.length - 1) {
newstr += "'" + str1[i] + "'";
} else {
newstr += "'" + str1[i] + "'" + ",";
}
}
}
}
if (map.get("deal") != null && !"".equals(map.get("deal"))) {
deal = (String) map.get("deal");
}
if (map.get("dealOther") != null && !"".equals(map.get("dealOther"))) {
dealOther = (String) map.get("dealOther");
}
boolean flag = false;
StringBuffer str = new StringBuffer();
if (map.get("dealNotExecutCbpf") != null
&& !"".equals(map.get("dealNotExecutCbpf"))) {
str.append(" and (ill.deal_not_execut_cbpf = 1");
flag = true;
}
if (map.get("dealNotapprovedBeforeConstruction") != null
&& !"".equals(map.get("dealNotapprovedBeforeConstruction"))) {
if (flag) {
str.append(" or ill.deal_not_approved_before_construction = 1");
} else {
str.append(" and (ill.deal_not_approved_before_construction = 1");
flag = true;
}
}
if (map.get("dealNotExecutThreetongshizhidu") != null
&& !"".equals(map.get("dealNotExecutThreetongshizhidu"))) {
if (flag) {
str.append(" or ill.deal_not_Execut_threetongshizhidu = 1");
} else {
str.append(" and (ill.deal_not_Execut_threetongshizhidu = 1");
flag = true;
}
}
if (map.get("dealbzcsywrfzcs") != null
&& !"".equals(map.get("dealbzcsywrfzcs"))) {
if (flag) {
str.append(" or ill.deal_bzcsywrfzcs = 1");
} else {
str.append(" and (ill.deal_bzcsywrfzcs = 1");
flag = true;
}
}
if (map.get("dealStealingDischarge") != null
&& !"".equals(map.get("dealStealingDischarge"))) {
if (flag) {
str.append(" or ill.deal_stealing_discharge = 1");
} else {
str.append(" and (ill.deal_stealing_discharge = 1");
flag = true;
}
}
if (map.get("dealCounterfeitMisrepresentData") != null
&& !"".equals(map.get("dealCounterfeitMisrepresentData"))) {
if (flag) {
str.append(" or ill.deal_counterfeit_misrepresentData = 1");
} else {
str.append(" and (ill.deal_counterfeit_misrepresentData = 1");
flag = true;
}
}
if (map.get("dealOther") != null && !"".equals(map.get("dealOther"))) {
if (flag) {
str.append(" or ill.deal_other = 1");
} else {
str.append(" and (ill.deal_other = 1");
flag = true;
}
}
if (flag) {
str.append(")");
}
String str_psids = "";
if (isComplete == 0) {
psids = this.getPollEnterprises(startTime, endTime, newstr);
if (psids != null && psids.size() > 0) {
for (int i = 0; i < psids.size(); i++) {
if (i == psids.size() - 1) {
str_psids += "'" + psids.get(i) + "'";
} else {
str_psids += "'" + psids.get(i) + "',";
}
}
}
}
Session session = this.getSession();
SQLQuery query = session
.createSQLQuery("{Call psCheck_ExcelData(?,?,?,?,?,?,?,?,?,?,?)}");
query.setString(0, "".equals(startTime) ? null : startTime);
query.setString(1, "".equals(endTime) ? null : endTime);
query.setString(2, "".equals(newstr) ? null : newstr);
query.setString(3, "".equals(String.valueOf(hasProblem)) ? null
: String.valueOf(hasProblem));
if(psname!=null && !"".equals(psname)){
try {
psname = new String(psname.getBytes("iso8859-1"),"utf-8");
query.setString(4, psname);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}else{
query.setString(4, null);
}
query.setString(5, "".equals(deal) ? null : deal);
query.setString(6, "".equals(dealOther) ? null : dealOther);
query.setString(7, pstype.equals("0") ? null : pstype);
query.setString(8, "".equals(str.toString()) ? null : str.toString());
query.setString(9, "3".equals(String.valueOf(isComplete)) ? null
: String.valueOf(isComplete));
query.setString(10, "".equals(str_psids) ? null : str_psids);
List<Object> list = query.list();
return list;
}
4.导出excel文件总结：
（1）所需jar包：
（2）exportExcel()方法：
这个方法使用与生成文件名和sheet名（即列表名），设置map是为了接收查询条件的。
其中调用了argMap()方法，是用来添加查询条件用于限制结果集的；
调用findAllObject()方法，是用来查找满足条件的对象的，将所有满足条件的对象放入list中；
然后调用了生成excel文件并触发下载的generateExcel()方法。
（3）argMap(Map<String,Object> map)方法：
此方法用于将页面中需要查询的条件及search_username和search_password放入map集合中封装起来。
（4）findAllObject(Map<String, Object> map)方法：
此方法通过封装好的查询条件：map中的值来拼装出hql语句，然后通过调用find() 方法来查找出，所有符合此条件查询的对象放入list集合中。
（5）generateExcel(list,excelName, sheetName)方法：
此方法用于创建excel文件，生成成功后触发下载。