Apache Poi Excel导出

joozh

引用

引用

字体颜色: 文字  提示：您可以使用 color=#FF0000package com.ths.util;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

import cn.com.chx.bo.AppBo;

/*
* Copyright(C) 2000-2011 THS Technology Limited Company, http://www.ths.com.cn
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
*      http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/



/**
*
*@Title 瀵煎嚭娓呭崟鏄庣粏
* @author sunzm
* @since 2013-6-3
*/
@SuppressWarnings("unchecked")
public class ExportOrderListUtil{
private ExportOrderListUtil(){}
private static ExportOrderListUtil util = new ExportOrderListUtil();
public static ExportOrderListUtil getInstance(){
return util;
}

/**
* @param response
* @param type                     污染源类型:1:污染源,2:污水处理厂
* @param emissid排放清单ID
* @param fileName             文件名称
* @param index                    动态参数的起始位置
*/
@SuppressWarnings("deprecation")
public void createExcel(HttpServletResponse response,String type,String emissid,String fileName,int index){
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格  
HSSFSheet sheet = workbook.createSheet("排放清单导出");  

//*******************************************表头样式***************************************
//生成一个样式（用于单元格）
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setFillForegroundColor((short) 44);// 设置背景色44
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置表头字体
HSSFFont titleFont = workbook.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(titleFont);
//HSSFColor
//****************************************表体样式**********************************************
HSSFCellStyle contextStyle = workbook.createCellStyle();
contextStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
contextStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contextStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contextStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contextStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
contextStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contextStyle.setFillForegroundColor((short) 41);
contextStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置表头字体
HSSFFont contextFont = workbook.createFont();
contextFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
contextStyle.setFont(contextFont);

HSSFCellStyle companyStyle = workbook.createCellStyle();
companyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
companyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
companyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
companyStyle.setFillForegroundColor((short) 41);
companyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//CELL_TYPE_NUMERIC 数值型 0
//CELL_TYPE_STRING 字符串型 1
//CELL_TYPE_FORMULA 公式型 2
//CELL_TYPE_BLANK 空值 3
//CELL_TYPE_BOOLEAN 布尔型 4
//CELL_TYPE_ERROR 错误 5
//Excel表中的第一行显示的数据
HSSFRow row = sheet.createRow(0);    //定义单元格行-表头
HSSFCell cell = row.createCell(0);  
cell.setCellStyle(titleStyle);
cell.setCellType(1);
cell.setCellValue("序号");

cell = row.createCell(1);
cell.setCellStyle(titleStyle);
cell.setCellType(1);
cell.setCellValue("污染源名称");

cell = row.createCell(2);
cell.setCellStyle(titleStyle);
cell.setCellType(1);
cell.setCellValue("流域");

/**
* 获取数据库中的数据
*/
Map<String, Object> dataBase = getDataBase(type, emissid);   
int count = (Integer) dataBase.get("count");  // 污染物的个数
int colcount = (Integer) dataBase.get("colcount");                               //  显示行的个数
ArrayList<HashMap<String, String>> listValue = (ArrayList<HashMap<String, String>>) dataBase.get("listValue");      //得到符合标准的数据列
ArrayList<String> arrColShowzh_cn = (ArrayList<String>) dataBase.get("arrColShowzh_cn");      //显示列对应的中文名称
ArrayList<String> popuList = (ArrayList<String>) dataBase.get("popuList");      //显示列对应的中文名称
    int strindex =index;
for(int i=0; i<count; i++){
     for(int z=0; z<colcount; z++){
    cell = row.createCell(index + i*colcount+z);
    cell.setCellStyle(titleStyle);
cell.setCellType(1);
    cell.setCellValue(popuList.get(i));
     }
     sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)strindex, (short) strindex+colcount-1));
     strindex += colcount;
}


//Excel表中的第二行显示的数据
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellStyle(titleStyle);

cell = row.createCell(1);
cell.setCellStyle(titleStyle);

cell = row.createCell(2);
cell.setCellStyle(titleStyle);
//统计循环次数如果是最后一次循环，则将清单值添加到该表格之中
int end=0;
for(int i = 0;  i<count ; i++){
for(int j=0; j<=colcount ; j++){    //i==1时，3+3
end++;
cell = row.createCell(index + i*colcount+j);
cell.setCellType(1);
cell.setCellStyle(titleStyle);
if(end==colcount){
cell.setCellValue("清单值");                    //清单值
end = 0;
break;
}else{
cell.setCellValue(arrColShowzh_cn.get(j));          //显示排放
}
}
}

//Excel表中的第三行显示的数据

for(int i=0; i<listValue.size(); i++){
sheet.autoSizeColumn(1, true);
HashMap<String, String> colValue = listValue.get(i);
if(i==0){
row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellStyle(contextStyle);
cell.setCellType(0);
cell.setCellValue(1);

cell = row.createCell(1);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue("合计");

cell = row.createCell(2);
cell.setCellStyle(contextStyle);

for(int k = 0;  k<count ; k++){
for(int j=0; j<colcount ; j++){
cell = row.createCell(index + k*colcount+j);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("CENTRALVALUES"+k));

cell = row.createCell(index + k*colcount+j+1);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("ONLINEMONIT"+k));

cell = row.createCell(index + k*colcount+j+2);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("OTHER"+k));

cell = row.createCell(index + k*colcount+j+3);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("POLLCENS"+k));

cell = row.createCell(index + k*colcount+j+4);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("SEWDISS"+k));

cell = row.createCell(index + k*colcount+j+5);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("SUPERVISORYMONVALUES"+k));

cell = row.createCell(index + k*colcount+j+6);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("LISTVALUES"+k));
break;
}
}
}else{
row = sheet.createRow(i+2);
cell = row.createCell(0);
cell.setCellStyle(contextStyle);
cell.setCellType(0);
cell.setCellValue(i+1);

cell = row.createCell(1);
cell.setCellType(1);
cell.setCellStyle(companyStyle);
cell.setCellValue(colValue.get("COMPNAME"));

cell = row.createCell(2);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("RIVERBASIN"));

for(int k = 0;  k<count ; k++){
for(int j=0; j<colcount ; j++){
cell = row.createCell(index + k*colcount+j);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("CENTRALVALUES"+k));

cell = row.createCell(index + k*colcount+j+1);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("ONLINEMONIT"+k));

cell = row.createCell(index + k*colcount+j+2);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("OTHER"+k));

cell = row.createCell(index + k*colcount+j+3);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("POLLCENS"+k));

cell = row.createCell(index + k*colcount+j+4);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("SEWDISS"+k));

cell = row.createCell(index + k*colcount+j+5);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("SUPERVISORYMONVALUES"+k));

cell = row.createCell(index + k*colcount+j+6);
cell.setCellStyle(contextStyle);
cell.setCellType(1);
cell.setCellValue(colValue.get("LISTVALUES"+k));
break;

}

}

}
}
try {
String encodedfileName = new String(fileName.getBytes(), "ISO8859-1");
response.setHeader("Content-Disposition", "attachment; filename=\""+ encodedfileName + "\"");
response.setContentType("application/vnd.ms-excel");
workbook.write(response.getOutputStream());
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* @param type          污染源类型
* @param emissid    排放清单ID
* @return   生成Excel表中的数据
*/
private Map<String,Object>  getDataBase(String type,String emissid){
AppBo appBo = new AppBo();
appBo.setDefaultDSN("java:comp/env/jdbc/mydsAir");
String sqlshow = "select COL,COLNAME from T_BAS_VALUE_COL where ISHOW='"+Constant.EXCEL_SHOW_COL+"' and TYPE='"+type+"'"+" order by COL  ASC";   //得到显示的列
ArrayList<HashMap<String, String>> allCollist = null;
try {
allCollist = appBo.query(sqlshow);
} catch (Exception e) {
e.printStackTrace();
}

ArrayList<String> arrColShow = new ArrayList<String>(); //名称
ArrayList<String> arrColShowzh_cn = new ArrayList<String>(); //名称
for(HashMap<String, String> maps : allCollist){
for(Map.Entry<String, String> map : maps.entrySet()){
if("COL".equals(map.getKey())){
arrColShow.add(map.getValue());
}else{
arrColShowzh_cn.add(map.getValue());
}
}
}

/*
* 污染物的名称
*/
String popultName = "select POLLUTANTSNAME from T_BAS_POLLUTANTS where ISHOW='1' and POLLUTANTSTYPE='"+type+"' order by POLLUTANTSID ASC";
ArrayList<HashMap<String, String>> popultLists = null;
try {
popultLists = appBo.query(popultName);
} catch (Exception e) {
e.printStackTrace();
}
//得到污染物的集体名称
List<String> popuList = new ArrayList<String>();
for(HashMap<String, String> popName : popultLists){
for(Map.Entry<String, String> mp : popName.entrySet()){
popuList.add(mp.getValue());
}
}

StringBuilder sqlbuid = new StringBuilder();
StringBuilder sum = new StringBuilder();
int i = 0;
for(String str :popuList){
//for循环列明
for(String strName : arrColShow){
sqlbuid.append(" sum(case when NAMEPOLLUTANTS = '"+str+"' then "+strName+" else 0 end) as "+strName+i+",");
sum.append("sum("+strName+i+") as "+strName+i+",");
}
sqlbuid.append(" sum(case when NAMEPOLLUTANTS = '"+str+"' then LISTVALUES else 0 end) as "+"LISTVALUES"+i+",");
sum.append("sum(LISTVALUES"+i+") as "+"LISTVALUES"+i+",");
i++;
}
sqlbuid.delete(sqlbuid.length()-1, sqlbuid.length());
sum.delete(sum.length()-1, sum.length());
//select * from (
//
//select null as compname,null as RIVERBASIN,null as SOURPOLLCOMPID,null as SOUR_POLLCOMPID,sum(tx),sum(tes),sum(tts) from(
//(select compname,RIVERBASIN,SOURPOLLCOMPID from T_BAS_SOURPOLLCOMPANY where COMPTYPE='1' and EMISSID='1234567890') s
//
//left join(
//
//select SOUR_POLLCOMPID, sum(case when NAMEPOLLUTANTS = '化学需氧量' then CENTRALVALUES else 0 end) as tx
//, sum(case when NAMEPOLLUTANTS = '氨氮' then CENTRALVALUES else 0 end) as tes
//, sum(case when NAMEPOLLUTANTS = '石油类' then CENTRALVALUES else 0 end) as tts
//from T_BAS_EMISSIONSINVEVALUE group by SOUR_POLLCOMPID) t on s.SOURPOLLCOMPID = t.SOUR_POLLCOMPID)
//
//union all(
//select * from
//(select compname,RIVERBASIN,SOURPOLLCOMPID from T_BAS_SOURPOLLCOMPANY where COMPTYPE='1' and EMISSID='1234567890') s
//left join(
//select SOUR_POLLCOMPID, sum(case when NAMEPOLLUTANTS = '化学需氧量' then CENTRALVALUES else 0 end) as tx
//, sum(case when NAMEPOLLUTANTS = '氨氮' then CENTRALVALUES else 0 end) as tes
//, sum(case when NAMEPOLLUTANTS = '石油类' then CENTRALVALUES else 0 end) as tts
//from T_BAS_EMISSIONSINVEVALUE group by SOUR_POLLCOMPID) t on s.SOURPOLLCOMPID = t.SOUR_POLLCOMPID
//)
//)

String allCol = "select * from ( select null as compname,null as RIVERBASIN,null as SOURPOLLCOMPID,null as SOUR_POLLCOMPID,"+sum+" from(" +
"(select compname,RIVERBASIN,SOURPOLLCOMPID from T_BAS_SOURPOLLCOMPANY where COMPTYPE='"+type+"' and EMISSID='"+emissid+"') s "+
"left join(select SOUR_POLLCOMPID, " +sqlbuid+
" from T_BAS_EMISSIONSINVEVALUE group by SOUR_POLLCOMPID) t on s.SOURPOLLCOMPID = t.SOUR_POLLCOMPID)" +
"union all( select * from" +
"(select compname,RIVERBASIN,SOURPOLLCOMPID from T_BAS_SOURPOLLCOMPANY where COMPTYPE='"+type+"' and EMISSID='"+emissid+"') s " +
"left join( select SOUR_POLLCOMPID, " +sqlbuid+
" from T_BAS_EMISSIONSINVEVALUE group by SOUR_POLLCOMPID) t on s.SOURPOLLCOMPID = t.SOUR_POLLCOMPID))";

ArrayList<HashMap<String, String>> arraylist = null;  //查询得到的污染物名称、公司名称
try {
arraylist = appBo.query(allCol);
} catch (Exception e) {
e.printStackTrace();
}

Map<String, Object> dateMap = new HashMap<String, Object>();  //组织返回参数
dateMap.put("listValue", arraylist);          //得到符合标准的数据列
dateMap.put("popuList", popuList);          //污染源的中文名称
dateMap.put("count", popuList.size());   // 污染物的名称
dateMap.put("colcount", allCollist.size()+1);   // 显示行的个数
dateMap.put("arrColShowzh_cn", arrColShowzh_cn);   // 显示行的个数
return dateMap;
}



}