真实项目中struts2 导出excel文件

---从数据库去取需要导出数据的java类

/**
 *  com.test.bcp.report.view.EveryDayRankAction
 * 
 */
package com.test.bcp.report.view;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.kms.framework.core.dao.support.ModelSetup;
import com.kms.framework.core.dao.support.ORMType;
import com.kms.framework.core.dao.support.ibatis.IBatisModelSetup;
import com.kms.framework.core.logger.LoggerFactory;
import com.kms.framework.core.logger.type.DebugLogger;
import com.kms.framework.core.view.support.entity.BaseEntityAction;
import com.test.bcp.report.domain.ProTopReport;
import com.test.bcp.report.service.IProTopReportService;
import com.test.bcp.report.utils.ExportExcelPdf;
import com.test.bcp.report.utils.ReportConfig;
import com.test.bcp.user.vo.Loginer;
/**
 * @date: 2012-4-17
 * @time: 16:38:06
 * @desc: 每日排行榜 查询Action
 */
public class ProTopReportAction extends
BaseEntityAction<ProTopReport, IProTopReportService>{
	
	private static final long serialVersionUID = -2520269272172780318L;
	
	private DebugLogger debugLogger = LoggerFactory.getDebugLogger(ProTopReportAction.class);
	
	private String beNum;
	
	private String enNum;
	
	private IProTopReportService proTopReportService;

	protected ModelSetup setupModel() {
		IBatisModelSetup modelSetup = (IBatisModelSetup) getModelSetupFromRequest(ORMType.IBATIS);
		modelSetup.addParameter("code", getLoginer().getOrganise()
				.getOrganiseCode());
		modelSetup.addParameter("beginTime", getEntity().getBeginTime());
		modelSetup.addParameter("endTime", getEntity().getEndTime());		
		return modelSetup;
	} 
	
	public String list() {
		setupModel();
		HashMap acceptParam = new HashMap();
    	acceptParam.put("beginTime", entity.getBeginTime());
		acceptParam.put("endTime", entity.getEndTime());
		acceptParam.put("code", getLoginer().getOrganise()
				.getOrganiseCode());
		System.out.println(getLoginer().getOrganise()
				.getOrganiseCode());
		setEntitys(proTopReportService.getProTopReportList(acceptParam));
		return SUCCESS;
	}
	
	private Loginer getLoginer() {
		HttpSession session = ServletActionContext.getRequest().getSession();
		return (Loginer) session.getAttribute(Loginer.USER_IN_SESSION);
	}
 
	 /**
	    *  导出Excel
	    * @return   Excel
	    */   
	    public String exportProTopReport() {
	    	HashMap acceptParam = new HashMap();
	    	acceptParam.put("beginTime", entity.getBeginTime());
			acceptParam.put("endTime", entity.getEndTime());
			acceptParam.put("beNum", this.getBeNum());
			acceptParam.put("enNum", this.getEnNum());
			acceptParam.put("organiseCode", getLoginer().getOrganise()
					.getOrganiseCode());

			//1.获取数据源
			List dataList = proTopReportService.getProTopReportList(acceptParam);
			//2.把TmcIntspAcceptInfo对象专为HashMap对象
			List savedata=new ArrayList();
			for(int i=0;i<dataList.size();i++){
				ProTopReport proTopReport=(ProTopReport) dataList.get(i);
				HashMap map=new LinkedHashMap();
				map.put("organiseName",proTopReport.getOrganiseName());
				map.put("top1_pro",proTopReport.getTop1_pro());
				map.put("top1_c",proTopReport.getTop1_c());
				map.put("top2_pro",proTopReport.getTop2_pro());
				map.put("top2_c",proTopReport.getTop2_c());
				map.put("top3_pro",proTopReport.getTop3_pro());
				map.put("top3_c",proTopReport.getTop3_c());
				map.put("top4_pro",proTopReport.getTop4_pro());
				map.put("top4_c",proTopReport.getTop4_c());
				map.put("top5_pro",proTopReport.getTop5_pro());
				map.put("top5_c",proTopReport.getTop5_c());
				savedata.add(map);
			}
			//3.传递参数导出报表
			return new ExportExcelPdf().exportExcelReport(
					ReportConfig.proTopReport, ReportConfig.proTopHead,
					ReportConfig.proTopTail, savedata,ReportConfig.getRecordName().get("top"));
	    	
	    }
	
	public void setProTopReportService(IProTopReportService proTopReportService) {
		this.proTopReportService = proTopReportService;
	}
	
	protected IProTopReportService getEntityManager() {
		return proTopReportService;
	}


	public IProTopReportService getProTopReportService() {
		return proTopReportService;
	}
	
	public String getEnNum() {
		return enNum;
	}


	public void setEnNum(String enNum) {
		this.enNum = enNum;
	}

	public String getBeNum() {
		return beNum;
	}

	public void setBeNum(String beNum) {
		this.beNum = beNum;
	}

}

 

 

------导出excel的常用变量定义类(表名，表头,等等常用变量)-----

/**
 * com.newyulong.bcp.report.utils.ReportConfig
 6  */
package com.newyulong.bcp.report.utils;

import java.util.ArrayList;
import java.util.HashMap;

/**
 * @author: lifeng@foxmail.com
 * @date: 2012-4-28
 * @time: 16:38:06
 * @desc:导出报表数据参数配置类
 */

public class ReportConfig {
    
    /*产品TOP查询参数配置 */
    public static final String proTopReport = "产品TOP统计";// 报表名称
    public static final String proTopHead  = null;// 报表头
    public static final String proTopTail = null;// 报表尾

    // 设置报表字段名称
    public static HashMap<String,ArrayList<String>> getRecordName() {
    ArrayList<String>  top=new ArrayList<String>();     
        top.add("组织机构名称");
        top.add("TOP1");
        top.add("成功量");
        top.add("TOP2");
        top.add("成功量");
        top.add("TOP3");
        top.add("成功量");
        top.add("TOP4");
        top.add("成功量");
        top.add("TOP5");
        top.add("成功量");
        allRecordName.put("top",top); 
        return allRecordName;     
    }
 
}

-----导出excel的工具类

/**
 * com.test.bcp.report.utils.ExportExcelPdf  5  * @licences:
 */
package com.test.bcp.report.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
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.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
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.struts2.ServletActionContext;

/**
 * @author: lifeng
 * @date: 2012-4-27
 * @time: 16:38:06
 * @desc: 导出excel pdf 功能实现
 */
public class ExportExcelPdf {

    /**
     * 导出报表到excel方法
     * @param reportName 报表名称
     * @param reportHead 报表头
     * @param reportTail 报表尾
     * @param dataList 需要的数据集
     * @param columnName 表头显示字段名称   
     * @return
     */
 
    public String exportExcelReport(String reportName,String reportHead,String reportTail,List dataList,ArrayList columnName) {
        //增加表头显示字段名称     
        dataList.add(0, columnName);
        
        if (dataList.size() > 0 && !dataList.isEmpty()) {
            if (!dataList.isEmpty() && dataList.size() > 0) {
                List<List> excelData = this.getExportData(dataList);
                List displayNames = excelData.get(0);
                List filldataList = null;
                if (excelData.remove(displayNames)) {
                    filldataList = excelData;
                }

                // filldataList:为要填充的数据 displayNames要显示的title
                HSSFWorkbook workbook = null;
                try {
                    workbook = exportExcel(reportHead, reportTail, reportName,
                            filldataList, displayNames);
                } catch (Exception e) {
                    e.printStackTrace();
                }// 绑定excel与数据
                HttpServletResponse response = ServletActionContext
                        .getResponse();
                if (workbook != null) {
                    // 打印导出excel
                    this.printExcel(workbook, response,
                            getExportFileName(".xls"));
                }

            }
        }
        return null;
    }

    /**
     * 
     * 获取导出的数据
     */
    private List<List> getExportData(List dataList) {
        List<List> result = new ArrayList();
    
        if (!dataList.isEmpty() && dataList.size() > 0) {
            ArrayList<String> displayNames=(ArrayList)dataList.get(0);
            result.add(displayNames);
        
            for (int i = 1; i < dataList.size(); i++) {
                Map data =(Map) dataList.get(i);
                List<String> filldataList = new ArrayList();
                for (Iterator iter = data.entrySet().iterator(); iter.hasNext();) {
                    Map.Entry entry = (Map.Entry) iter.next();
                    Object value = entry.getValue();
                    if (value == null) {
                        value = "";
                    }
                    filldataList.add(value.toString());
                }
                result.add(filldataList);
            }
        }
        return result;
    }

    /**
     * 打印导出的Excel
     */
    private void printExcel(HSSFWorkbook workbook,
            HttpServletResponse response, String excelFileName) {
        try {
            OutputStream out = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename="
                    + excelFileName);
            // 设置响应文档类型为excel
            response.setContentType("application/msexcel");
            response.setCharacterEncoding("UTF-8");
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 写入输入流中
     */
    public HSSFWorkbook exportExcel(String reportHead, String reportTail,
            String reprotName, List<List> filldataList, List displayNames)
            throws Exception {
        HSSFWorkbook workbook = null;

        // 这里的数据即时你要从后台取得的数据

        // 创建工作簿实例
        workbook = new HSSFWorkbook();
        // 创建工作表实例
        HSSFSheet sheet = workbook.createSheet("TscExcel");
        // 设置列宽
        this.setSheetColumnWidth(displayNames.size(), sheet);
        // 获取样式
        HSSFCellStyle style = this.createTitleStyle(workbook);
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        // 设置页眉
        HSSFHeader header = sheet.getHeader();
        HSSFHeader.fontSize((short) 16);
        header.setCenter(reportHead);

        // 设置报表名称
        HSSFRow rowReportName = sheet.createRow((short) 0);// 建立新行
        HSSFFont boldFont = workbook.createFont();
        boldFont.setFontHeight((short) 400);
        HSSFCellStyle styleT = workbook.createCellStyle();
        styleT.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleT.setFont(boldFont);
        this.createCell(rowReportName, 0, styleT, HSSFCell.CELL_TYPE_STRING,
                reprotName);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, displayNames.size()));

        // 1. 创建第一行:标题,
        HSSFRow row = sheet.createRow((short) 1);// 建立新行
        for (int i = 0; i < displayNames.size(); i++) {
            this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING,
                    displayNames.get(i));
        }

        // 2. 给excel填充数据
        int i = 0;
        for (List<List> tableBody : filldataList) {
            HSSFRow rowBody = sheet.createRow((short) (i + 2));// 建立新行
            for (int j = 0; j < tableBody.size(); j++) {
                this.createCell(rowBody, j, style,tableBody.get(j));
            }
            i++;
        }
        // 设置页脚
        HSSFFooter footer = sheet.getFooter();
        HSSFFooter.fontSize((short) 16);
        footer.setCenter(reportTail);

        return workbook;
    }

    /**
     * 生成导出文件的名称 ：按导出时间生成文件名称
     */
    private String getExportFileName(String fix) {
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        return dateFormat.format(date) + fix;
    }

    /**
     * 创建Excel单元格
     */
    private void createCell(HSSFRow row, int column, HSSFCellStyle style,
            int cellType, Object value) {
        HSSFCell cell = row.createCell((short) column);
        if (style != null) {
            cell.setCellStyle(style);
        }
        switch (cellType) {
        case HSSFCell.CELL_TYPE_BLANK: {
        }
            break;
        case HSSFCell.CELL_TYPE_STRING: {
            if(value==null){
                value = "";
            }
            cell.setCellValue(value.toString() + "");
            
        }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC: {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        }
            break;
        default:
            break;
        }
    }

    /**
     * 创建Excel单元格及默认格式
     */
    private void createCell(HSSFRow row, int column, HSSFCellStyle style, Object value) {
        HSSFCell cell = row.createCell((short) column);
        String data ="";
        if(value!=null) data += value;
        if(!"".equals(data)&&isNumeric(data) && data.indexOf("0")!=0){
            if (data.indexOf("86")==0 && data.length() == 13){
                cell.setCellValue(data);
            }else{
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(data.trim()));
            }
        }else{
            cell.setCellValue(data);
        }
 
    }
    
    /**
     * 判断是否为数字
     * @param str
     * @return
     */
    public static boolean isNumeric(String str){ 
        for (int i = str.length();--i>=0;){ 
        if (!Character.isDigit(str.charAt(i))){
        return false; 
        } 
        }
        return true; 
    } 

    /**
     * 设置列宽
     */
    private void setSheetColumnWidth(int titles_CN, HSSFSheet sheet) {
        // 根据你数据里面的记录有多少列，就设置多少列
        for (int i = 0; i < titles_CN; i++) {
            sheet.setColumnWidth((short) i, (short) 3000);
        }

    }

    /**
     * 设置excel的title样式
     */
    private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
        HSSFFont boldFont = wb.createFont();
        boldFont.setFontHeight((short) 200);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(boldFont);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
        return style;
    }
}