- 浏览: 10627 次
- 性别:
- 来自: 北京
最新评论
Apache Poi Excel导出
引用
引用
字体颜色: [color=red]文字 提示:您可以使用 color=#FF0000[/color]package 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;
}
}
- ExportOrderListUtil.zip (4.1 KB)
- 描述: Apache Poi Excel导出
- 下载次数: 3
相关推荐
使用Apache POI的3.16版实现,Excel模板填充数据导出Excel报表
Apache POI导入和导出Excel文件
NULL 博文链接:https://keep-going.iteye.com/blog/1907664
POI使用excel模板循环输出行到文件并导出的一个小例子
NULL 博文链接:https://747017186.iteye.com/blog/2163242
apache poi 导出excel、word,替换word内容,导出word-文本、图片、表格
Apache之PoI 报表导出 将数据库中的表数据导入Excel之中
apache POI 导出Excel 设置打印 打印区域 打印方向 缩放比例 水平居中
基于poi的excel导入导出封装,poi版本 <groupId>org.apache.poi <artifactId>poi <version>4.1.0 <groupId>org.apache.poi <artifactId>poi-ooxml <version>4.1.0 </dependency>
apache poi根据模板导出excel的模版
导出EXCEL 2007报表接口,可以学习下。
Apache之 excel的POI报表导出 这是最装逼的一个 可以在页面弹出可提示下载的选择框,存储选择路径 与平常的下载一般无二
Apache POI 导出EXCEL的封装实现工具类,超简单的调用方式,有说明文档示例参考,一看便懂,将繁琐变为简单,是我们一直追求的脚步……
Apache Poi相关Jar包,导出word的方法可在我的个人博客内查看。
利用POI技术将图片插入到excel中,希望对大家有用,直接运行ExportExcel类,即可看到效果!!!
使用JSP+jqueryUI+java Servlet通过Apache POI实现Excel导入导出
zip包中含有poi导出excel需要的包,本人亲测是可以正常使用且比较稳定的
利用apache包来完成excel导入导出 利用apache包来完成excel导入导出