基于apache POI excel导出，支持多列多条件。

每次用到excel导出基本上都要重新写，这次是写了一个公共的，以后有想用的时侯直接复制就好，大部份都可以兼容，有问题后期再优化。

准备jar包

poi包:http://poi.apache.org/download.html

核心代码

import com.wd.common.anno.Merge;
import com.wd.common.utils.DateUtil;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import org.apache.log4j.Logger;
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.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
 * <p>导出excel类</p> 
 * @ClassName: ExportExcelUtils     
 * @author: sunhr
 * @date: 2016年8月9日 下午7:18:33   
 * @version: V2.0  
 */
public class ExportExcelUtils<T>
{
   /**
    * 
    * <p>根据对象导出excel表格内容--泛型对象里面的属性顺序就已经确认了excel列的顺序，支持根据多个条件合并多列</p>
    * @author sunhr
    * @date 2016年9月30日 下午5:20:13 
    * @param workbook 是否想要多个sheet页
    * @param sheetName sheet页名称
    * @param heading 第一行标题
    * @param titles 列头
    * @param dataset 表格数据
    * @param mergeCells 要合并的列 -->int[] mergeCells={0,1,2,3,4,9,10,11,12};，根据rowMergeFlag属性值是否相等判别要合并多少行（只要每一行的此属性值相等，便合并)
    * @param total 合计数据
    * @param cols 自定义列宽
    * @return
    * Workbook
    */
   public Workbook ExportExcelMoreMergeCells(Workbook workbook,
                                 String sheetName,
                                 String heading,
                                 String[] titles,
                                 Collection<T> dataset,
                                 Map<String,int[]> mergeCellsMap,
                                 Map<String, Object> total,
                                 Map<Integer, Integer> cols){
      Workbook[] wbs = new Workbook[]{new HSSFWorkbook(), new XSSFWorkbook()};
      //设置表格版本
      Workbook wb = null;
      if(workbook == null){
         wb = wbs[0];
      }else{
         wb = workbook;
      }
      // 创建标题样式
      CellStyle titleStyle = wb.createCellStyle();
      titleStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
      titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
      titleStyle.setBorderTop(CellStyle.BORDER_THIN);
      titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
      titleStyle.setBorderRight(CellStyle.BORDER_THIN);
      titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
      titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
      titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

      Font titleFont = wb.createFont();
      titleFont.setColor(HSSFFont.COLOR_NORMAL);
      titleFont.setFontHeightInPoints((short)11);
      titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      titleStyle.setFont(titleFont);

      // 创建内容样式
      CellStyle dataStyle = wb.createCellStyle();
      dataStyle.setBorderTop(CellStyle.BORDER_THIN);
      dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
      dataStyle.setBorderRight(CellStyle.BORDER_THIN);
      dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
      dataStyle.setAlignment(CellStyle.ALIGN_CENTER);
      dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      Font dataFont = wb.createFont();
      dataFont.setFontHeightInPoints((short)11);
      dataStyle.setFont(dataFont);

      // 创建工作薄
      Sheet sheet = wb.createSheet(sheetName);
      sheet.setDefaultColumnWidth((short)17);// 默认列宽
      sheet.createFreezePane(0, 2, 0, 2);
      //设置列宽
      if(cols != null){
         for(Map.Entry<Integer, Integer> entry : cols.entrySet()){
            sheet.setColumnWidth(entry.getKey(), entry.getValue());
         }
      }
      Row row = null;
      Cell cell = null;
      // 1. 创建标题(第一行)
      row = sheet.createRow(0);
      sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (short)(titles.length - 1)));// 合并标题列
      row.setHeightInPoints(20);
      cell = row.createCell(0);
      cell.setCellValue(heading);
      cell.setCellStyle(titleStyle);

      // 2. 创建列头(第二行)
      row = sheet.createRow(1);
      row.setHeightInPoints(20);
      for(short i = 0; i < titles.length; i++){
         cell = row.createCell(i);
         cell.setCellStyle(titleStyle);
         HSSFRichTextString text = new HSSFRichTextString(titles[i]);
         cell.setCellValue(text);
      }

      // 3. 创建内容
      Iterator<T> it = dataset.iterator();
      int index = 1;// 内容启始行     
      String[] rowMergeFlag = new String[mergeCellsMap.size()];// 要合并的数值标识
      int[] startRow = new int[mergeCellsMap.size()];// 要合并的开始行号
      // 初始化起始行
      for(int i = 0; i < startRow.length; i++){
         startRow[i] = index+1;
      }
      int[] endRow = new int[mergeCellsMap.size()];// 要合并的结束行号
      int rowNumber = 1;// 合并后的行号
      // 遍历行
      while(it.hasNext()){
         index++;
         row = sheet.createRow(index);
         T t = it.next();
         Field[] fields = t.getClass().getDeclaredFields();
         // 遍历列
         for(int i = 0; i < fields.length; i++){
            Field field = fields[i];
            String fieldName = field.getName();
            String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

            if(!getMethodName.contains("MergeFlag")){
               cell = row.createCell(i);
               cell.setCellStyle(dataStyle);
            }

            // 第一列为序号
            if(i == 0){
               cell.setCellValue(rowNumber);
               continue;
            }

            Class tCls = t.getClass();
            try{
               Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
               Object value = getMethod.invoke(t, new Object[]{});
               String textVlaue = null;
               if(value != null){
                  boolean continueFlag = false;
                  int mergeCellIndex = -1;
                  for(String key : mergeCellsMap.keySet()){
                     mergeCellIndex++;
                     if(fieldName.equals(key)){
                        if(rowMergeFlag[mergeCellIndex] != null){                          
                           // 验证上一行和下一行是否相等，进行合并。
                           if(rowMergeFlag[mergeCellIndex].equals(value.toString())){
                              endRow[mergeCellIndex] = index;
                              // 最后一行直接全并
                              if(!it.hasNext()){
                                 for(int j = 0; j < mergeCellsMap.get(key).length; j++){
                                    sheet.addMergedRegion(new CellRangeAddress(startRow[mergeCellIndex], endRow[mergeCellIndex], mergeCellsMap.get(key)[j], mergeCellsMap.get(key)[j]));// 合并
                                 }
                              }
                           }else{
                              // 不相等才进行合并
                              if(startRow[mergeCellIndex] < endRow[mergeCellIndex]){
                                 for(int j = 0; j < mergeCellsMap.get(key).length; j++){
                                    sheet.addMergedRegion(new CellRangeAddress(startRow[mergeCellIndex], endRow[mergeCellIndex], mergeCellsMap.get(key)[j], mergeCellsMap.get(key)[j]));// 合并
                                 }
                                 // 行号
                                 if(key.contains("MergeFlagKey")){
                                    rowNumber++;
                                 }
                              }else{
                                 // 行号
                                 if(key.contains("MergeFlagKey")){
                                    rowNumber++;
                                 }
                              }                              
                              startRow[mergeCellIndex] = index;
                           }
                        }else{
                           // 行号
                           if(key.contains("MergeFlagKey")){
                              rowNumber++;
                           }
                        }
                        rowMergeFlag[mergeCellIndex] = value.toString();
                        continueFlag = true;
                     }
                     
                  }
                  // 如果有合并标志位，不用写入excel
                  if(continueFlag){                     
                     continue;
                  }
                  
                  if(value instanceof Date){
                     Date date = (Date)value;
                     textVlaue = DateUtil.fmtDateToStr(date, "yyyy-MM-dd HH:mm:ss");
                  }else if(value instanceof BigDecimal){
                     NumberFormat point = NumberFormat.getCurrencyInstance();
                     point.setMaximumFractionDigits(2);
                     textVlaue = point.format(value);
                  }else{
                     textVlaue = value.toString();
                  }
               }
               if(textVlaue != null){
                  HSSFRichTextString text = new HSSFRichTextString(textVlaue);
                  cell.setCellValue(text);
               }else{
                  cell.setCellValue("");
               }
            }catch(NoSuchMethodException e){
               // TODO Auto-generated catch block
               e.printStackTrace();
            }catch(SecurityException e){
               // TODO Auto-generated catch block
               e.printStackTrace();
            }catch(IllegalAccessException e){
               // TODO Auto-generated catch block
               e.printStackTrace();
            }catch(IllegalArgumentException e){
               // TODO Auto-generated catch block
               e.printStackTrace();
            }catch(InvocationTargetException e){
               // TODO Auto-generated catch block
               e.printStackTrace();
            }
         }
      }

      /** 合计处理 */
      // 金额样式
      CellStyle moneyStyle = wb.createCellStyle();
      moneyStyle.setBorderTop(CellStyle.BORDER_THIN);
      moneyStyle.setBorderLeft(CellStyle.BORDER_THIN);
      moneyStyle.setBorderRight(CellStyle.BORDER_THIN);
      moneyStyle.setBorderBottom(CellStyle.BORDER_THIN);
      moneyStyle.setAlignment(CellStyle.ALIGN_RIGHT);
      moneyStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      Font moneyFont = wb.createFont();
      moneyFont.setColor(HSSFColor.RED.index);
      moneyFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      moneyStyle.setFont(moneyFont);

      if(total != null){
         if(!it.hasNext()){
            StringBuffer totalVal = new StringBuffer("合计 - ");
            for(String key : total.keySet()){
               String textVlaue = null;
               if(total.get(key) instanceof BigDecimal){
                  NumberFormat point = NumberFormat.getCurrencyInstance();
                  point.setMaximumFractionDigits(2);
                  textVlaue = point.format(total.get(key));
               }
               totalVal.append(key + ":").append(textVlaue + "   ");
            }

            int lastRow = index + 1;
            row = sheet.createRow(lastRow);
            sheet.addMergedRegion(new CellRangeAddress(lastRow, lastRow, 0, (short)(titles.length - 1)));// 合并标题列
            row.setHeightInPoints(20);
            cell = row.createCell(0);
            cell.setCellValue(totalVal.toString());
            cell.setCellStyle(moneyStyle);
         }
      }

      return wb;
   }
}

调用导出的javabean

import com.wd.bi.financial.view.StudentConsumeListVW;
import com.wd.bi.financial.view.StudentConsumeVW;
import java.math.BigDecimal;
import java.text.NumberFormat;
/**
 * 
 * <p>导出对象</p> 
 * @ClassName: ExportStudentConsume     
 * @author: sunhr
 * @date: 2016年9月13日 上午9:52:10   
 * @version: V2.0  
 */
public class ExportStudentConsume extends IExportStudentConsume{
   
   /* 标识，必须在第一行 */
   private Integer serialNumber = 0; // 序号（必须要的属性）   

   /* 实际要输出的列 */
   private String studentName;//学生姓名
   
   // 合
   private String schoolNameStr;
   
   // 合
   private String contactStr;
   
   private String serialNum;
   // 合
   private String classPeriodNameStr;   
   private String teacherName;//授课教师
   
   // 合
   private String personalProgressStr;  
   
   private String attendCountStr;// 消耗
   
   private String remindCountStr;//剩余
   
   private BigDecimal projectRemindMoney = new BigDecimal(0);//项目剩余总金额
   
   private BigDecimal studentAccount=new BigDecimal(0);//账户金额
   
   private String manager;//班主任、学管
   
   private String signName;//签单人
   
   private String signTime;//首课时间
   
   /* ............................................... */
   
   /* 标识 必须在最后 (必须包含MergeFlag字符) */
   private String studentIdMergeFlagKey ; //行合并标识(为Excel的主键，必须包含MergeFlagKey)
   private String modelMergeFlag;// 行合并条件学生Model
   
   public ExportStudentConsume() {
      super();
   }
   
   public void initialize(StudentConsumeListVW sc,StudentConsumeVW scv){
      this.setStudentName(sc.getStudentName());
      this.setSchoolName(sc.getSchoolName());
      this.setStudentGradeName(sc.getStudentGradeName());
      this.setContactMobile(sc.getContactMobile());
      this.setContactName(sc.getContactName()); 
      this.setSerialNum(scv.getSerialNum());
      this.setClassPeriodName(scv.getClassPeriodName());
      this.setPeriodNum(scv.getPeriodNum());
      this.setTeacherName(scv.getTeacherName());
      this.setPersonalProgress(scv.getPersonalProgress());
      this.setCourseCount(scv.getCourseCount());
      this.setAttendCount(scv.getAttendCount());
      this.setAttendMoney(scv.getAttendMoney());
      this.setRemindCount(scv.getRemindCount());
      this.setRemindMoney(scv.getRemindMoney());
      this.setChargeType(scv.getChargeType());
      this.setProjectRemindMoney(scv.getProjectRemindMoney());
      this.setStudentAccount(scv.getStudentAccount());
      this.setManager(scv.getManager());
      this.setSignName(scv.getSignName());
      this.setSignTime(scv.getSignTime());
      this.setStudentIdMergeFlagKey(String.valueOf(sc.getStudentId()));
      this.setModelMergeFlag(String.valueOf(sc.getStudentId()+"-"+scv.getModel()));
   }
   

   public Integer getSerialNumber(){
      return serialNumber;
   }

   public void setSerialNumber(Integer serialNumber){
      this.serialNumber = serialNumber;
   }

   public String getStudentName(){
      return studentName;
   }

   public void setStudentName(String studentName){
      this.studentName = studentName;
   }

   public String getSchoolNameStr(){
      return String.format("%s %s", super.getSchoolName(),super.getStudentGradeName());
   }

   public String getContactStr(){
      return String.format("%s %s", super.getContactMobile(),super.getContactName());
   }

   public String getSerialNum(){
      return serialNum;
   }

   public void setSerialNum(String serialNum){
      this.serialNum = serialNum;
   }

   public String getClassPeriodNameStr(){
      if(super.getPeriodNum() != null){
         return String.format("%s %s", super.getClassPeriodName(),super.getPeriodNum());               
      }
      return super.getClassPeriodName();
   }

   public String getTeacherName(){
      return teacherName;
   }

   public void setTeacherName(String teacherName){
      this.teacherName = teacherName;
   }

   public String getPersonalProgressStr(){      
      return  String.format("%s/%s%s", super.getPersonalProgress(),super.getCourseCount(),super.getUnit());
   }

   public String getAttendCountStr(){
      NumberFormat point = NumberFormat.getCurrencyInstance();
      point.setMaximumFractionDigits(2);
      return  String.format("%s %s%s", super.getAttendCount(),super.getUnit(),point.format(super.getAttendMoney()));
   }

   public String getRemindCountStr(){
      NumberFormat point = NumberFormat.getCurrencyInstance();
      point.setMaximumFractionDigits(2);
      
      return  String.format("%s %s%s", super.getRemindCount(),super.getUnit(),point.format(super.getRemindMoney()));
   }

   public BigDecimal getProjectRemindMoney(){
      return projectRemindMoney;
   }

   public void setProjectRemindMoney(BigDecimal projectRemindMoney){
      this.projectRemindMoney = projectRemindMoney;
   }

   public BigDecimal getStudentAccount(){
      return studentAccount;
   }

   public void setStudentAccount(BigDecimal studentAccount){
      this.studentAccount = studentAccount;
   }

   public String getManager(){
      return manager;
   }

   public void setManager(String manager){
      this.manager = manager;
   }

   public String getSignName(){
      return signName;
   }

   public void setSignName(String signName){
      this.signName = signName;
   }

   public String getSignTime(){
      return signTime;
   }

   public void setSignTime(String signTime){
      this.signTime = signTime;
   }

   
   public String getStudentIdMergeFlagKey(){
      return studentIdMergeFlagKey;
   }

   public void setStudentIdMergeFlagKey(String studentIdMergeFlagKey){
      this.studentIdMergeFlagKey = studentIdMergeFlagKey;
   }

   public String getModelMergeFlag(){
      return modelMergeFlag;
   }

   public void setModelMergeFlag(String modelMergeFlag){
      this.modelMergeFlag = modelMergeFlag;
   }
}


import java.math.BigDecimal;
/**
 * 
 * <p>导出对象(临时使用的属性，不做导出)</p> 
 * @ClassName: StudentConsumeExport     
 * @author: sunhr
 * @date: 2016年9月12日 下午7:09:44   
 * @version: V2.0  
 */
public class IExportStudentConsume{
   
   // 合
   private String schoolName;//学校名称
   private String studentGradeName;// 学级
   
   // 合
   private String contactName;//主联系人称谓
   private String contactMobile;//主联系方式   
   
   // 合
   private String classPeriodName;//学习项目（课程名称）
   private Integer periodNum;//班级期数
   
   // 合
   private BigDecimal personalProgress=new BigDecimal(0);//个人进度
   private BigDecimal courseCount=new BigDecimal(0);//报名次数
   
   private BigDecimal attendCount=new BigDecimal(0);//本月消耗次数
   private BigDecimal attendMoney=new BigDecimal(0);//本月消耗金额
   
   private BigDecimal remindCount=new BigDecimal(0);//剩余次数
   
   private BigDecimal remindMoney=new BigDecimal(0);//剩余金额
   
   private Integer chargeType;///班级期收费标准
   private String unit;// 单位
   
   
   public String getSchoolName(){
      return schoolName;
   }
   public void setSchoolName(String schoolName){
      this.schoolName = schoolName;
   }
   public String getStudentGradeName(){
      return studentGradeName;
   }
   public void setStudentGradeName(String studentGradeName){
      this.studentGradeName = studentGradeName;
   }
   public String getContactName(){
      return contactName;
   }
   public void setContactName(String contactName){
      this.contactName = contactName;
   }
   public String getContactMobile(){
      return contactMobile;
   }
   public void setContactMobile(String contactMobile){
      this.contactMobile = contactMobile;
   }
   public String getClassPeriodName(){
      return classPeriodName;
   }
   public void setClassPeriodName(String classPeriodName){
      this.classPeriodName = classPeriodName;
   }
   
   public Integer getPeriodNum(){
      return periodNum;
   }
   
   public void setPeriodNum(Integer periodNum){
      this.periodNum = periodNum;
   }
   
   public BigDecimal getPersonalProgress(){
      return personalProgress;
   }
   public void setPersonalProgress(BigDecimal personalProgress){
      this.personalProgress = personalProgress;
   }
   public BigDecimal getCourseCount(){
      return courseCount;
   }
   public void setCourseCount(BigDecimal courseCount){
      this.courseCount = courseCount;
   }
   public BigDecimal getAttendCount(){
      return attendCount;
   }
   public void setAttendCount(BigDecimal attendCount){
      this.attendCount = attendCount;
   }
   
   public BigDecimal getAttendMoney(){
      return attendMoney;
   }
   public void setAttendMoney(BigDecimal attendMoney){
      this.attendMoney = attendMoney;
   }
   public BigDecimal getRemindCount(){
      return remindCount;
   }
   public void setRemindCount(BigDecimal remindCount){
      this.remindCount = remindCount;
   }
   public BigDecimal getRemindMoney(){
      return remindMoney;
   }
   public void setRemindMoney(BigDecimal remindMoney){
      this.remindMoney = remindMoney;
   }
   public Integer getChargeType(){
      return chargeType;
   }
   public void setChargeType(Integer chargeType){
      this.chargeType = chargeType;
   }
   public String getUnit(){
      if(this.getChargeType() != null){
         if(getChargeType() == 1 || getChargeType() == 2){
            return "次";
         }else if(getChargeType() == 3 || getChargeType() == 4){
            return "月";
         }else{
            return "课时";
         }
      }
      return "";
   }
}

使用案例

        // sheet页名称
        String sheetName ="学生课耗分析";
        /* 表头处理 */
        StringBuffer topParam = headerProcess(studentConsumeParam);
        /** .............................................................................. */   
        /* 合计 */        
        Map<String, Object> total = new LinkedHashMap<String, Object>();
        total.put("课耗收入", studentConsumeParam.getAttendMoney());
        total.put("剩余学费", studentConsumeParam.getRemindMoney());
        total.put("个人账户", studentConsumeParam.getStudentAccount());
        /** .............................................................................. */
        // 合并列，必须和实体类一样key
        int[] StudentId=new int[]{0,1,2,3,10};
        Map<String,int[]> mergeCellsMap=new HashMap<String,int[]>();
        mergeCellsMap.put("studentIdMergeFlagKey", StudentId);
        mergeCellsMap.put("modelMergeFlag", new int[]{9});
        // 设置指定列宽度
        Map<Integer,Integer> cols=new HashMap<Integer,Integer>();
        cols.put(4, 1000*2*4);
        cols.put(5, 1000*2*5);
        
        String topName = studentConsumeParam.getMonthNum()+"月份学生课耗统计-"+"("+topParam.toString()+")";
        String[] title = {"序号","姓名","学校","电话","电子票号","学习项目","授课教师","个人进度","产生课耗","剩余课耗","总剩余","个人账户","班主任/学管师","签单人","首课时间"};
        ExportExcelUtils<ExportStudentConsume> eet = new ExportExcelUtils<ExportStudentConsume>();
        Workbook wb = eet.ExportExcelMoreMergeCells(null,sheetName, topName, title,exportstudentconsumeList,mergeCellsMap,total,cols);
        HttpServletResponse resp = inv.getResponse();
        try {
           resp.setHeader("Content-disposition", "attachment;filename="+new String(sheetName.getBytes("GB2312"),"ISO8859_1")+".xls");
           OutputStream out = resp.getOutputStream();
           wb.write(out);
           out.close();
        } catch (UnsupportedEncodingException e) {
           e.printStackTrace();
        } catch (IOException e) {
           e.printStackTrace();
        }

这个应该已经写的很详细了，有疑问可以问我哟。

本文链接：http://blog.csdn.net/u010264560/article/details/52870301
本文作者：CSDN - jerry.sun