excel导出动态数据列表。。。

/**
* 导出台账
*/
public ActionForward exportQues(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws Exception {

String orgId = request.getParameter("id_org");
String checkId = request.getParameter("checkId");
QueryForm qf = new QueryForm(request, org.apache.commons.collections.map.ListOrderedMap.class);
PageInfo page = new PageInfo(request);
DynaBean bean = new LazyDynaBean();
bean.set("checkId", checkId);
bean.set("orgId", orgId);
List list = inspectDuty1Service.list(qf, page, bean);
System.out.println("" + list.toString());
//开始导出excel....
response.reset();// 可以加也可以不加
response.setContentType("application/x-download");
String filedisplay = "排班日期表.xls";
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
List lists = inspectDuty1Service.queryAllSiteNo(orgId);

List<String> biaoLie = new ArrayList<String>(); //列数据
biaoLie.add("DUTY_DATE");
List<String> biaoTou = new ArrayList<String>(); //表头
biaoTou.add("排班日期");
for (int i = 0; i < lists.size(); i++) {
Map m = (Map) lists.get(i);
String siteNo = (String) m.get("SITE_NO");
String siteName = (String) m.get("SITE_NAME");
biaoLie.add("A" + siteNo);
biaoTou.add(siteName);
}

String[] t = new String[biaoLie.size()];
String[] fileds = biaoLie.toArray(t);

String[] s = new String[biaoTou.size()];
String[] title = biaoTou.toArray(s);
//String[] title = {"排班日期","台席1","台席2","台席3","台席4"};
//String[] fileds = {"DUTY_DATE","A0001","A0002","A0002","A0004"};

ExcelExportService.exportExcel(list, title, fileds, response.getOutputStream());

return null;
}


public static boolean exportExcel(List<Map> values, String[] title, String[] fields, OutputStream out) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sht = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
style.setFillForegroundColor((short) Color.GRAY.getRGB());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
int rownum = 0;
if(title != null && title.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < title.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
cell.setCellStyle(style);
column++;
}
}
if(values != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for(int i = 0; i < values.size(); ++i) {
HSSFRow row = sht.createRow(rownum);
Map map = (Map)values.get(i);
rownum ++;
short column = 0;
for(int j = 0; j < fields.length; ++j) {
HSSFCell cell = row.createCell(column);
Object val = map.get(fields[j]);
/*
if (fields[j].equals("RISK_LINK") && map.get(fields[j])!=null && !"".equals(map.get(fields[j]))) { // BUS_TYPE汉字
Long riskLink = Long.parseLong(map.get(fields[j]).toString());
FiriskRiskLink firiskRiskLink = firiskRiskLinkService.findById(riskLink);
val = firiskRiskLink.getLinkName();
} else {
val = map.get(fields[j]);
}*/
if(val != null){
if(val instanceof java.util.Date){
String dataStr = DateTool.dateToStr(sdf.parse(val.toString()), "yyyy-MM-dd");
cell.setCellValue(new HSSFRichTextString(dataStr));
}else{
cell.setCellValue(new HSSFRichTextString(val.toString()));
}
}
column++;
}
}
}
wb.write(out);
return true;
} catch (Exception e) {
e.printStackTrace();
} finally{
try{
if(out!=null)out.close();
}catch(Exception e){
}
}
return false;
}




整个excel的帮助类

package com.post.bank.finrisk.util;

import java.awt.Color;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;

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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ist.commons.util.DateTool;
import com.ist.util.spring.SpringBeanFactory;
import com.post.bank.finrisk.service.FiriskPersonPointService;
import com.post.bank.finrisk.service.FiriskRiskLinkService;
import com.post.bank.finrisk.service.PersonWentryService;
import com.post.bank.finrisk.vo.FiriskRiskLink;
import com.post.bank.finrisk.vo.PersonWentry;



/**
* Import vulnerability scan result excel file
* @author pengxiao
*
*/
public class ExcelExportService {

private static FiriskRiskLinkService firiskRiskLinkService = (FiriskRiskLinkService) SpringBeanFactory.getBean("firiskRiskLinkService");

/**
* 导出Excel
* @param values:导出的instance集合
* @param title：Excel列标题
* @param instance:List的类
* @param fields:导出的对应instance的字段
* @param outPath:输出文件路径
* @return
*/
public static boolean exportExcel(List<?> values, String[] title, Class<?> instance, String[] fields, String outPath) {
try {
FileOutputStream fos = new FileOutputStream(outPath);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sht = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
style.setFillForegroundColor((short) Color.GRAY.getRGB());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
int rownum = 0;
if(title != null && title.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < title.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
cell.setCellStyle(style);
column++;
}
}
if(values != null) {
for(int i = 0; i < values.size(); ++i) {
HSSFRow row = sht.createRow(rownum);
rownum ++;
short column = 0;
for(int j = 0; j < fields.length; ++j) {
HSSFCell cell = row.createCell(column);
if(fields[j].indexOf(",") > -1){
String[] fcomp = fields[j].split(",");
String value = "";
for(String f : fcomp) {
Method method = instance.getMethod(toGetMethod(f), null);
Object val = method.invoke(values.get(i), null);
value += val.toString();
}
cell.setCellValue(new HSSFRichTextString(value));
} else {
Method method = instance.getMethod(toGetMethod(fields[j]), null);
Object val = method.invoke(values.get(i), null);
if(val != null)
cell.setCellValue(new HSSFRichTextString(val.toString()));
}
column++;
}
}
}
wb.write(fos);
fos.close();
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}


/**
* 导出Excel
* @param values:导出的列表集合,里面是Map,一般是直接通过sql查询出的结果集
* @param title：Excel列标题
* @param instance:List的类
* @param fields:导出的对应instance的字段
* @param outPath:输出文件路径
* @return
*/
public static boolean exportExcel(List<Map> values, String[] title, String[] fields, String outPath) {
try {
FileOutputStream fos = new FileOutputStream(outPath);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sht = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
style.setFillForegroundColor((short) Color.GRAY.getRGB());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
int rownum = 0;
if(title != null && title.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < title.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
cell.setCellStyle(style);
column++;
}
}
if(values != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for(int i = 0; i < values.size(); ++i) {
HSSFRow row = sht.createRow(rownum);
Map map = (Map)values.get(i);
rownum ++;
short column = 0;
for(int j = 0; j < fields.length; ++j) {
HSSFCell cell = row.createCell(column);
Object val = map.get(fields[j]);
if(val != null){
if(val instanceof java.util.Date){
String dataStr = DateTool.dateToStr(sdf.parse(val.toString()), "yyyy-MM-dd");
cell.setCellValue(new HSSFRichTextString(dataStr));
}else{
cell.setCellValue(new HSSFRichTextString(val.toString()));
}
}
column++;
}
}
}
wb.write(fos);
fos.close();
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}

public static boolean exportExcel(List<Map> values, String[] title, String[] fields, OutputStream out) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sht = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
style.setFillForegroundColor((short) Color.GRAY.getRGB());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
int rownum = 0;
if(title != null && title.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < title.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
cell.setCellStyle(style);
column++;
}
}
if(values != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for(int i = 0; i < values.size(); ++i) {
HSSFRow row = sht.createRow(rownum);
Map map = (Map)values.get(i);
rownum ++;
short column = 0;
for(int j = 0; j < fields.length; ++j) {
HSSFCell cell = row.createCell(column);
Object val = map.get(fields[j]);
/*
if (fields[j].equals("RISK_LINK") && map.get(fields[j])!=null && !"".equals(map.get(fields[j]))) { // BUS_TYPE汉字
Long riskLink = Long.parseLong(map.get(fields[j]).toString());
FiriskRiskLink firiskRiskLink = firiskRiskLinkService.findById(riskLink);
val = firiskRiskLink.getLinkName();
} else {
val = map.get(fields[j]);
}*/
if(val != null){
if(val instanceof java.util.Date){
String dataStr = DateTool.dateToStr(sdf.parse(val.toString()), "yyyy-MM-dd");
cell.setCellValue(new HSSFRichTextString(dataStr));
}else{
cell.setCellValue(new HSSFRichTextString(val.toString()));
}
}
column++;
}
}
}
wb.write(out);
return true;
} catch (Exception e) {
e.printStackTrace();
} finally{
try{
if(out!=null)out.close();
}catch(Exception e){
}
}
return false;
}
/**
* 有对象现场检查导出
* @param values：导出的列表集合,里面是Map,一般是直接通过sql查询出的结果集
* @param objTitle：有对象现场检查（对象标题）
* @param title：问题标题
* @param fields：问题标题对应字段
* @param out：输出
* @return
*/
public static boolean exportExcel(List<Map> values,String[] objTitle, String[] title, String[] fields, OutputStream out) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sht = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
style.setFillForegroundColor((short) Color.GRAY.getRGB());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
int rownum = 0;
if(objTitle != null && objTitle.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < objTitle.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(objTitle[i]));
cell.setCellStyle(style);
column++;
}
}
rownum = 2;
if(title != null && title.length > 0) {
HSSFRow row = sht.createRow(rownum);
rownum++;
short column = 0;
for(int i = 0; i < title.length; ++i) {
HSSFCell cell = row.createCell(column, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
cell.setCellStyle(style);
column++;
}
}
if(values != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for(int i = 0; i < values.size(); ++i) {
HSSFRow row = sht.createRow(rownum);
Map map = (Map)values.get(i);
rownum ++;
short column = 0;
for(int j = 0; j < fields.length; ++j) {
HSSFCell cell = row.createCell(column);
Object val = map.get(fields[j]);
if(val != null){
if(val instanceof java.util.Date){
String dataStr = DateTool.dateToStr(sdf.parse(val.toString()), "yyyy-MM-dd");
cell.setCellValue(new HSSFRichTextString(dataStr));
}else{
cell.setCellValue(new HSSFRichTextString(val.toString()));
}
}
column++;
}
}
}
wb.write(out);
return true;
} catch (Exception e) {
e.printStackTrace();
} finally{
try{
if(out!=null)out.close();
}catch(Exception e){
}
}
return false;
}

private static String toGetMethod(String field) {
String tmp = field.toUpperCase();
return "get"+tmp.charAt(0) + field.substring(1, field.length());
}
}