Struts2 java 对象 导出Excel

从数据库中导出对象信息到Excel 有多种方式，这里我介绍一种：

1、我的项目使用框架：struts2 + spring + hibernate + mysql

2、另外需要导入poi-3.2-FINAL.jar 包

3、使用反射机制动态执行对象方法，动态确定当前对象。

 

－、配置Action.xml文件

<action name="exportExcel"  class="com.wng.action.addressBook.AddressBookAction" method="exportToExcel">  

<!--返回类型为stream -->
   <result name="success"  type="stream"> 

 <!--文件格式 --> 
    <param name="contentType">application/vnd.ms-excel</param>

<!--action 中inputName 参数名excelStream -->
    <param name="inputName">excelStream</param> 

<!--导出文件名 --> 
    <param name="contentDisposition">attachment;filename="${fileName}.xls"</param>

<!-- -->
     <param name="bufferSize">1024</param>
   </result> 

<!-- -->  
   <result name="error">/index.jsp</result>
  </action>

二、Action中方法代码：

package com.wng.action.addressBook;

import java.io.InputStream;
import java.util.HashMap;
import javax.annotation.Resource;
import com.wng.action.ActionBase;
import com.wng.domain.addressBook.AddressBook;
import com.wng.domain.addressBook.AddressBookGroup;
import com.wng.domain.user.User;
import com.wng.service.impl.addressBook.AddressBookGroupServiceImpl;
import com.wng.service.impl.addressBook.AddressBookServiceImpl;
import com.wng.service.impl.user.UserServiceImpl;
import com.wng.util.GridData;
import com.wng.util.PageData;

public class AddressBookAction extends ActionBase {
 private static final long serialVersionUID = 841738988024332218L;
 private User user;
 
 private AddressBookGroupServiceImpl addressBookGroupServiceImpl;
 private AddressBook addressBook;
 public String fileName = null;
 public  InputStream excelStream;
 

 public AddressBookGroupServiceImpl getAddressBookGroupServiceImpl() {
  return addressBookGroupServiceImpl;
 }

 private AddressBookServiceImpl addressBookServiceImpl;
 
 public AddressBookServiceImpl getAddressBookServiceImpl() {
  return addressBookServiceImpl;
 }
 @Resource(name="addressBookServiceImpl")
 public void setAddressBookServiceImpl(
   AddressBookServiceImpl addressBookServiceImpl) {
  this.addressBookServiceImpl = addressBookServiceImpl;
 }

 

public String exportToExcel() throws Exception{
  if(!isNull(user)){

//导出文件名 
   this.fileName = "fileExport2"; // 设置 fileName 
    excelStream = this.addressBookServiceImpl.exportToExcel(user,addressBookGroup);
    return SUCCESS;
  }else{
   excelStream = null;
   return ERROR;
  }
 }
}

三、业务类中的方法：

package com.wng.service.impl.addressBook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;
import com.wng.dao.impl.addressBook.AddressBookDAOImpl;
import com.wng.domain.addressBook.AddressBook;
import com.wng.domain.addressBook.AddressBookGroup;
import com.wng.domain.user.User;
import com.wng.service.ServiceBase;
import com.wng.service.impl.user.UserServiceImpl;
import com.wng.util.ExportExcel;
import com.wng.util.GridData;
import com.wng.util.WhereHql;
import com.wng.util.WhereItem;

@Component("addressBookServiceImpl")
public class AddressBookServiceImpl extends ServiceBase {
 private AddressBookDAOImpl addressBookDAOImpl;
 private UserServiceImpl userServiceImpl;
 private AddressBookGroupServiceImpl addressBookGroupServiceImpl;
 
 
 public AddressBookGroupServiceImpl getAddressBookGroupServiceImpl() {
  return addressBookGroupServiceImpl;
 }
 @Resource(name="addressBookGroupServiceImpl")
 public void setAddressBookGroupServiceImpl(
   AddressBookGroupServiceImpl addressBookGroupServiceImpl) {
  this.addressBookGroupServiceImpl = addressBookGroupServiceImpl;
 }

 public UserServiceImpl getUserServiceImpl() {
  return userServiceImpl;
 }
 
 @Resource(name="userServiceImpl")
 public void setUserServiceImpl(UserServiceImpl userServiceImpl) {
  this.userServiceImpl = userServiceImpl;
 }

 public AddressBookDAOImpl getAddressBookDAOImpl() {
  return addressBookDAOImpl;
 }
 
 @Resource(name="addressBookDAOImpl")
 public void setAddressBookDAOImpl(AddressBookDAOImpl addressBookDAOImpl) {
  this.addressBookDAOImpl = addressBookDAOImpl;
 }

 

public GridData findByUser(Map<?, ?>  params) {
  // 查出要显示的
  String hql = "From AddressBook a";
  // 总记录数
  String thql = "select count(*) from AddressBook a ";
  GridData gd = new GridData();
  if(isNull(params)){
   return gd;
  }else{
  
   // 从传过来的参数Map中得到查询条件，设置到对像中。
   List<WhereItem> whem = new ArrayList<WhereItem>();
   
   if(!isNull(params.get("user_id"))){
    User user = this.userServiceImpl.get((Long)params.get("user_id"));
    whem.add(new WhereItem("a.user", "=", user));
   }
   
   if(!isNull(params.get("addressBookGroup_id"))){
    AddressBookGroup addressBookGroup = this.addressBookGroupServiceImpl.getById((Long)params.get("addressBookGroup_id"));
    whem.add(new WhereItem("a.addressBookGroup", "=", addressBookGroup));
   }
    // 产生条件语句
    WhereHql whql = createHqlWhere(whem);
    if (!isNull(whql.getHql())) {
     hql = hql + " where " + whql.getHql();
     thql = thql + " where " + whql.getHql();
    }
    // 分页显示
    gd = (this.queryForPage(hql, whql.getParamsAsArray(), thql, whql
      .getParamsAsArray(), (String)params.get("start"), (String)params.get("limit")));
    System.out.println("this number is:" + gd.getTotalRows());
    System.out.println(gd.getDatals().size());
    return gd;
  }
 }

 

 

public InputStream exportToExcel(User user,AddressBookGroup addressBookGroup){
  Map<String, Long> params = new HashMap<String, Long>();
  params.put("user_id", user.getId());
  if(!isNull(addressBookGroup)){
   params.put("addressBookGroup_id", addressBookGroup.getId());
  }
  List<AddressBook> datalist = (List<AddressBook>) findByUser(params).getDatals();
  //声明Excel WorkBook 对象
  HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet();
  //定义导出的Excel 表头栏
  String [] headString = {"编号","姓名","工作单位","电话","QQ号码","性别","联系地址","个人主页","邮件","移动电话","MSN号码","分组"};
  //调用辅助类生成Excel
  ExportExcel<AddressBook> exce = new ExportExcel<AddressBook>(workbook, sheet );
  exce.createNormalHead(null, "枫叶飞扬—通迅录", headString.length);
  //生成表格表头栏
  exce.createColumHeader(headString);
  //传入数据对象类型
  AddressBook addressBook = new AddressBook();
  String [] att = {"Id","Name","Company","Telephone","QqNumber","Sex","Address","WebPage","Email","Moblie","MsnNumber"};
  //生成数据行
  exce.cteateRow((int)2,datalist,addressBook,att);
  //输出ImputStream 供页面下载
  return exce.getExcelImputStream("testfile");
 }

 

四、导出Excel 通用类，使用反射自动执行get 方法

package com.wng.util;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.List;
import org.apache.commons.io.output.ByteArrayOutputStream;
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 org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 创建通用EXCEL导出通用类
 * @param <T>
 * @param workbook 定义工作簿对象
 * @param sheet 工作表
 *
 */
public class ExportExcel<T> {
 private HSSFWorkbook workbook =null;
 private HSSFSheet sheet = null;
 
 public HSSFWorkbook getWorkbook() {
  return workbook;
 }

 public void setWorkbook(HSSFWorkbook workbook) {
  this.workbook = workbook;
 }

 public HSSFSheet getSheet() {
  return sheet;
 }
 
 public void setSheet(HSSFSheet sheet) {
  this.sheet = sheet;
 }
 
 public ExportExcel(HSSFWorkbook workbook, HSSFSheet sheet) {
  super();
  this.workbook = workbook;
  this.sheet = sheet;
 }
 
 /**
  * 创建通用EXCEL头部
  *
  * @param headString 头部显示的字符
  * @param colSum 该报表的列数
  */
 public void createNormalHead(String headString, String TextString, int colSum) {
  HSSFRow row = sheet.createRow(0);
  // 设置第一行
  HSSFCell cell = row.createCell(0);
  row.setHeight((short) 400);

  // 定义单元格为字符串类型
  cell.setCellType(HSSFCell.ENCODING_UTF_16);
  cell.setCellValue(new HSSFRichTextString(TextString));

  // 指定合并区域
  sheet.addMergedRegion(new CellRangeAddress(0,0,0,(short) colSum-1));
  HSSFCellStyle cellStyle = workbook.createCellStyle();

  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
  cellStyle.setWrapText(true);// 指定单元格自动换行

  // 设置单元格字体
  HSSFFont font = workbook.createFont();
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  font.setFontName("宋体");
  font.setFontHeight((short) 300);
  cellStyle.setFont(font);
  cell.setCellStyle(cellStyle);
 }
 
 /**
  * 设置报表标题
  *
  * @param columHeader 标题字符串数组
  */
 public void createColumHeader(String[] columHeader) {
  // 设置列头
  HSSFRow row1 = sheet.createRow(1);
  // 指定行高
  row1.setHeight((short) 300);

  HSSFCellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
  cellStyle.setWrapText(true);// 指定单元格自动换行

  // 单元格字体
  HSSFFont font = workbook.createFont();
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  font.setFontName("宋体");
  font.setFontHeight((short) 200);
  cellStyle.setFont(font);
  
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
  
  // 设置单元格背景色
  cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

  HSSFCell cell3 = null;

  for (int i = 0; i < columHeader.length; i++) {
   cell3 = row1.createCell(i);
   cell3.setCellType(HSSFCell.ENCODING_UTF_16);
   cell3.setCellStyle(cellStyle);
   cell3.setCellValue(new HSSFRichTextString(columHeader[i]));
  }
 }
 
 /**
  * 创建数据行
  * @param rowId 开始行号
  * @param dataList对象list
  * @param  att 需要导出的属性，如{"Name","Age"}
  */
 public void cteateRow(int rowId, List<T> dataList,T ob,String [] att) {
  for(int j = 0; j < dataList.size(); j++){
   HSSFRow row = sheet.createRow(rowId + j);
   for(int i = 0; i < att.length; i++){
    Object obj = this.getter(dataList.get(j), att[i]);
    if(null == obj){
     obj = "";
    }
    cteateCell(workbook,row,i,HSSFCellStyle.ALIGN_CENTER,obj.toString());
   }
  }
 }
  
 /**
  * 创建内容单元格
  *
  * @param workbook HSSFWorkbook
  * @param row HSSFRow
  * @param col short型的列索引
  * @param align 对齐方式
  * @param val 列值
  */
 public void cteateCell(HSSFWorkbook workbook, HSSFRow row, int col,
   short align, String val) {
  HSSFCell cell = row.createCell(col);
  cell.setCellType(HSSFCell.ENCODING_UTF_16);
  cell.setCellValue(new HSSFRichTextString(val));
  HSSFCellStyle cellstyle = workbook.createCellStyle();
  cellstyle.setAlignment(align);
  cell.setCellStyle(cellstyle);
 }
 /**
  * 输入EXCEL文件
  *
  * @param fileName 文件名包含本地路径 如：E:\\test.xls
  */
 public void outputExcel(String fileName) {
  FileOutputStream fos = null;
  try {
   fos = new FileOutputStream(new File(fileName));
   workbook.write(fos);
   fos.close();
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 /**
     * @param obj
     *            操作的对象
     * @param att
     *            操作的属性
     * @param value
     *            设置的值
     * @param type
     *            参数的属性
     * */
    public  void setter(Object obj, String att, Object value, Class<?> type) {
        try {
            Method method = obj.getClass().getMethod("set" + att, type);
            method.invoke(obj, value);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
 
  /**
     * @param obj
     *            操作的对象
     * @param att
     *            操作的属性
     * */
    public Object getter(Object obj, String att) {
     System.out.println(obj.getClass());
     System.out.println(att);
        try {
            Method method = obj.getClass().getMethod("get" + att);
            return method.invoke(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
  return null;

    }
    /**
     * 输出 Imputstream
     * @param fileName
     *            文件名称
     * @param excelStream Action 和 Action.xml 文件中定义的参数，名称要一至
     * */
    public InputStream getExcelImputStream(String fileName){
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   InputStream  excelStream = null;
   try {
   workbook.write(baos);
   baos.flush();  
    byte[] aa = baos.toByteArray(); 
    excelStream = new ByteArrayInputStream(aa, 0, aa.length);
    baos.close();
  } catch (IOException e) {
   e.printStackTrace();
  } 
   return excelStream;
 }
 
}