结合struts2实现的通用导出excel实现详细介绍

step1

package @@.bcp.report.utils;

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

/**
  * @date: 2012-4-28
 * @time: 16:38:06
 * @desc:导出报表数据参数配置类
 */
public class ReportConfig {
 /* 上行报表查询参数配置 */
 public static final String reportNameAccept = "上行短信查询";// 报表名称
 public static final String reportHeadAccept = null;// 报表头
 public static final String reportTailAccept = null;// 报表尾

 // 设置报表字段名称
 public static HashMap<String,HashMap<String,String>> getRecordName() {
  /* 存放所有报表对象 */
  HashMap<String,HashMap<String,String>>  allRecordName = new HashMap<String,HashMap<String,String>> ();

  /* 1.上行报表 */
  /* 存放上行报表字段名称 */
  HashMap acceptReport = new HashMap();
  acceptReport.put("业务登记号","regId");
  acceptReport.put("sp业务号","operId");
  acceptReport.put("手机号码","phoneId");
  acceptReport.put("短信内容","reContent");
  acceptReport.put("状态","flag");
  acceptReport.put("时间","reTime");
  /* 存放上行报表字段对象acceptReport到allRecordName中 */
  allRecordName.put("Accept", acceptReport);

  
  
  return allRecordName;
 }

 
 
}

 

 

 

step2

package com.##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;

/**
 * * @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,HashMap 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<HashMap> dataList) {
  List<List> result = new ArrayList();
  List displayNames = new ArrayList();
  if (!dataList.isEmpty() && dataList.size() > 0) {
   Map map = dataList.get(0);
   for (Iterator iter = map.entrySet().iterator(); iter.hasNext();) {
    Map.Entry entry = (Map.Entry) iter.next();
    Object key = entry.getKey();
    displayNames.add(key);
   }
   result.add(displayNames);
   for (int i = 1; i < dataList.size(); i++) {
    Map data = 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);

  // 设置页眉
  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, HSSFCell.CELL_TYPE_STRING,
      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: {
   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;
  }
 }

 /**
  * 设置列宽
  */
 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;
 }
}

 

step3在action中编写如下方法

 /* 导出报表 */

 @SuppressWarnings("all")
 public String exportAcceptReport() {

  HashMap acceptParam = new HashMap();
  acceptParam.put("phoneId", getEntity().getPhoneId());
  acceptParam.put("reBeginTime", getEntity().getReBeginTime());
  acceptParam.put("reEndTime", getEntity().getReEndTime());
  acceptParam.put("organiseCode", getLoginer().getOrganise()
    .getOrganiseCode());
  
  //1.获取数据源
  List dataList = tmcIntspAcceptService
    .getTmcInspAcceptList(acceptParam);
  //2.把TmcIntspAcceptInfo对象专为HashMap对象
  List savedata=new ArrayList();
  for(int i=0;i<dataList.size();i++){
   TmcIntspAcceptInfo tmi=(TmcIntspAcceptInfo) dataList.get(i);
   HashMap map=new LinkedHashMap();
   map.put("regId",tmi.getRegId());
   map.put("operId",tmi.getOperId());
   map.put("phoneId",tmi.getPhoneId());
   map.put("reContent",tmi.getReContent());
   map.put("flag",tmi.getFlag());
   map.put("reTime",tmi.getReTime());
   savedata.add(map);
  }
  //3.传递参数导出报表
  return new ExportExcelPdf().exportExcelReport(
    ReportConfig.reportNameAccept, ReportConfig.reportHeadAccept,
    ReportConfig.reportTailAccept, savedata,ReportConfig.getRecordName().get("Accept"));
 }