POI导出功能

调用工具类

try {
   List<FinanceStats> list = financeStatsService.find(search);
   String filename = "CWBB" + DateTimeTools.get8BitDate(new Date()) + ".xlsx";             
   ExportUtil.exportExcel(filename,list,FinanceStats.class,response);
}catch(Exception ex){
   log.error(ex);
}

导出工具类封装了POI导出Excel的功能

import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

/**类名：ExportUtil <br/>
 * 功能：导出工具类封装了POI导出Excel的功能<br/>
 * 日期：2016年5月19日 <br/>
 */
public class ExportUtil {
    
    /*
     * 方便导出Excel的工具类
     */
    public static void exportExcel(String filename,List<?> list,Class<?> clazz,
            HttpServletResponse response)throws Exception{
        ExcelUtil excelUtil = ExcelUtil.newInstance();
        ExportProperties properties = ExcelUtil.getExcelHeaders(clazz);
        response.setHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");        
        response.setContentType("application/octet-stream;charset=UTF-8");
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        out = new BufferedOutputStream(response.getOutputStream());
        excelUtil.exportExcelV07(out, list, properties);    
        out.flush();
        out.close();
    }
}

导出Excel的POI工具类

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

/**类名：ExcelUtil <br/>
 * 功能：导出Excel的POI工具类<br/>
 * 日期：2016年4月13日 <br/>
 */
public class ExcelUtil{    
    
    private static ExcelUtil instance = new ExcelUtil();    
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");    
    private ExcelUtil(){}        
    
    public static ExcelUtil newInstance(){    
        return instance;
    }    
    
    /*
     * 导出Excel2003
     */
    public void exportExcelV03(OutputStream out, List<?> rows, ExportProperties properties)
            throws Exception{        
        Workbook wbk = exportExcel(rows, properties, false);
        wbk.write(out);
    }
    
    /*
     * 导出Excel2007
     */
    public void exportExcelV07(OutputStream out, List<?> rows, ExportProperties properties)
            throws Exception{        
        Workbook wbk = exportExcel(rows, properties, true);
        wbk.write(out);
    }    
    
    private Workbook exportExcel(List<?> rows, ExportProperties properties, boolean isXSSF) 
            throws Exception{                
        Workbook wbk = null;
        if(isXSSF){        
            wbk = new XSSFWorkbook();
        }else{    
            wbk = new HSSFWorkbook();
        }
        Sheet sheet = wbk.createSheet();        
        Row row = sheet.createRow(0);            
        List<ExcelHeader> headers = properties.getProperties();
        for(int i = 0; i < headers.size(); i++){        
            ExcelHeader he = headers.get(i);
            row.createCell(i).setCellValue(he.getCname());
        }            
        for(int j = 0; j < rows.size(); j++){    
            Object obj = rows.get(j) ;
            Row rowData = sheet.createRow(j+1);
            for(int g = 0; g < headers.size(); g++){            
                ExcelHeader header = headers.get(g);
                Method method = obj.getClass().getMethod(getMethodName(header.getFname()));
                method.setAccessible(true);
                Object result = method.invoke(obj);
                rowData.createCell(g).setCellValue(convert(result));
            }
        }
        return wbk ;
    }    
    private String convert(Object obj){    
        if (obj == null){
            return "";
        } else {
            if (obj instanceof Date){
                return sdf.format(obj);
            }            
            return obj.toString();
        }        
    }
    
    private String getMethodName(String fname){
        String mname = "get" + fname.substring(0,1).toUpperCase();
        return mname + fname.substring(1);
    }
    
    //获取行标题的数据    
    public static ExportProperties getExcelHeaders(Class<?> clazz){    
        ExportProperties headers = new ExportProperties();        
        Field[] fileds = clazz.getDeclaredFields();
        for(Field f : fileds){        
            String fname = f.getName();
            ExcelCol excelCol = f.getAnnotation(ExcelCol.class) ;
            if(f.isAnnotationPresent(ExcelCol.class)){            
                headers.addProperty(new ExcelHeader(fname,excelCol.title(),excelCol.order()));
            }
        }        
        //根据注解order排序
        Collections.sort(headers.getProperties()) ;
        return headers;
    }    
}

自定义注解

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**类名：ExcelCol <br/>
 * 功能：将Excel文档中的列标题和
 *         实体类的属性对应并支持对列标题位置的排序<br/>
 * 日期：2016年5月20日 <br/>
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCol {
    /*
     * Excel文档中的列标题名称
     */
    String title();
    /*
     * Excel列标题的位置标识
     */
    int order();
}

实体类

import java.math.BigDecimal;
import com.hqz.dto.admin.ExcelCol;

public class FinanceStats {    
    
    @ExcelCol(title="统计日期",order=1)
    private String statsDate;
    
    @ExcelCol(title="回款额",order=2)
    private BigDecimal returnMoney;    
    
 public String getStatsDate() {
        return statsDate;
    }

    public void setStatsDate(String statsDate) {
        this.statsDate = statsDate;
    }    public BigDecimal getReturnMoney() {
        return returnMoney;
    }

    public void setReturnMoney(BigDecimal returnMoney) {
        this.returnMoney = returnMoney;
    }
}

 

Excel列标题与实体类属性名对应关系

package com.hqz.dto.admin;

/**类名：ExcelHeader <br/>
 * 功能：Excel列标题与实体类属性名对应关系<br/>
 */
public class ExcelHeader implements Comparable<ExcelHeader>{
    
    /*实体类属性名称*/
    private String fname;    
    
    /*Excel标题列名称*/
    private String cname;
    
    /*排序的标识*/
    private int order;

    public String getFname() {
        return fname;
    }

    public void setFname(String fname) {
        this.fname = fname;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }        
    
    public int getOrder() {
        return order;
    }

    public void setOrder(int order) {
        this.order = order;
    }
    
    public ExcelHeader(String fname, String cname, int order) {
        this.fname = fname;
        this.cname = cname;
        this.order = order;
    }

    public ExcelHeader(String fname, String cname) {
        this.fname = fname;
        this.cname = cname;
    }

    
    public int compareTo(ExcelHeader obj) {
        if(this.order > obj.order){        
            return 1 ;            
        }else if(this.order < obj.order){                    
            return -1;            
        }else {            
            return 0;
        }
    }    
}

Excel标题行

public class ExportProperties {

    private List<ExcelHeader> properties = new ArrayList<ExcelHeader>();

    public ExportProperties addProperty(ExcelHeader excelHeader) {
        properties.add(excelHeader);
        return this;
    }

    public List<ExcelHeader> getProperties() {
        return properties;
    }
}

 

转载于:https://www.cnblogs.com/sam-w21/p/9721103.html