Javaweb 导出excel

2019独角兽企业重金招聘Python工程师标准>>>

修改Excel导出方式：

A.添加了Annotation定义导出标题及属性（有些多余的感觉，为了用Annotation而添加Annotation）。

B.保留原来用数组列表保存标题及属性。

C.添加ajax 导出脚本。

1.Excel导出工具（最大行数限制：65535，超出薪资Sheet）。

/**
 * 
 */
package cc.rico.utils;

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

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * excel 导出工具类
 * @author rico 2016年5月24日
 *
 */
public class ExcelExportUtils<T> {
	private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtils.class);
	
	// 最大行数限制：65535
	private static final int EXCEL_MAX_ROW_NUM = 65534; 
	// 默认sheet标题
	private static final String DEFAULT_EXCEL_SHEET_NAME = "excel";
	
	// excel标题头
	private String[] titles = null;
	// 数据源bean 属性名
	private String[] properties = null;
	// excel 导出数据源
	private List<T> datasources;
	// sheet 标题
	private String sheetTitle;
	
	//TODO excel单元格类型
	
	
	public ExcelExportUtils() {
		
	}
	
	 public ExcelExportUtil(List<T> datasources, String sheetTitle) {
        this.datasources = datasources;
        this.sheetTitle = sheetTitle;
    }

    /**
     * @param titles      excel标题头
     * @param properties  数据源属性名
     * @param datasources 导出数据源
     * @param sheetTitle  sheet标题
     */
    public ExcelExportUtil(String[] titles, String[] properties, List<T> datasources, String sheetTitle) {
        this(datasources, sheetTitle);

        this.titles = titles;
        this.properties = properties;
    }

    public ExcelExportUtil(List<T> datasources, String sheetTitle, Class annotationClazz) {
        this(datasources, sheetTitle);

        List<ExcelFieldAnnotation> annotations = ExcelFieldAnnotationUtil.excelFieldAnnotations(annotationClazz);
        ExcelFieldBean excelFieldBean = ExcelFieldAnnotationUtil.excelFieldBean(annotations);

        this.titles = excelFieldBean.toTitleArray();
        this.properties = excelFieldBean.toFieldArray();
    }
	
	/**
	 * 生成workbook
	 * @return
	 */
	public HSSFWorkbook createWorkbook() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		if(titles==null || titles.length==0) {
			throw new Exception("excel标题头不能为空");
		}
		if(properties==null || properties.length==0) {
			throw new Exception("数据源属性名不能为空");
		}
		if(StringUtils.isBlank(sheetTitle)) {
			logger.warn("sheet标题为空,使用默认值{}.", DEFAULT_EXCEL_SHEET_NAME);
			sheetTitle = DEFAULT_EXCEL_SHEET_NAME;
		}
		
		// 计算记录数是否超过最大值
		int sheetSize = 1;
		if(datasources == null) {
			datasources = new ArrayList<T>();
		} else {
			sheetSize = this.calculateSheetSize(datasources.size());
		}
		
		if(sheetSize == 1) {
			// 记录数不超过excel sheet最大数,直接生成workbook
			this.createSheet(workbook, sheetTitle, titles, properties, datasources);
			
		} else {
			for(int i=0; i<sheetSize; i++) {
				List<T> tempList = null;
				String tmpSheetTitle = sheetTitle.concat("-").concat((i+1)+"");
				
				if(i == sheetSize-1) {
					tempList = datasources.subList(i*EXCEL_MAX_ROW_NUM, datasources.size());
				} else {
					tempList = datasources.subList(i*EXCEL_MAX_ROW_NUM, (i+1)*EXCEL_MAX_ROW_NUM);
				}
				
				this.createSheet(workbook, tmpSheetTitle, titles, properties, tempList);
			}
		}
		
		return workbook;
	}
	
	/**
	 * 生成sheet
	 * @param workbook		
	 * @param sheetTitle 	sheet标题
	 * @param titles		excel表格title
	 * @param properties	数据源数据名
	 * @param datasources	数据源
	 */
	private void createSheet(HSSFWorkbook workbook, String sheetTitle, String[] titles, String[] properties, List<T> datasources) {
		HSSFSheet sheet = workbook.createSheet(sheetTitle);
		
		// 创建excel标题行
		this.setSheetTitle(sheet, titles);
		// 创建excel内容
		this.createContent(sheet, properties, datasources);
	}
	
	/**
	 * 计算excel sheet数
	 * @param total
	 * @return
	 */
	private int calculateSheetSize(int total) {
		int ret = 1;
		
		if(total <= EXCEL_MAX_ROW_NUM) {
			return ret;
		} else {
			ret = (int) Math.ceil(total / (Double.valueOf((double)EXCEL_MAX_ROW_NUM)));
		}
		
		return ret;
	}
	
	/**
	 * 创建excel标题行
	 * @param sheet
	 * @param titles
	 */
	private void setSheetTitle(HSSFSheet sheet, String[] titles) {
        Row row = sheet.createRow((short) 0);
        
        Cell cell = null;
        for(int i=0; i<titles.length; i++) {
        	cell = row.createCell(i);
        	cell.setCellValue(titles[i]);
        }
	}
	
	/**
	 * 创建excel内容
	 * @param sheet
	 * @param properties
	 * @param datasources
	 */
	private void createContent(HSSFSheet sheet, String[] properties, List<T> datasources) {
		int startRow = 1;
		
		Row row = null;
		for(int i=0; i<datasources.size(); i++) {
			row = sheet.createRow(startRow+i);
			
			// excel内容cell
			this.createContentCell(row, properties, datasources.get(i));
		}
	}
	
	/**
	 * 创建 excel cell
	 * @param row
	 * @param properties
	 * @param rowData
	 */
	private void createContentCell(Row row, String[] properties, T rowData) {
		try {
			Cell cell = null;
			
			for(int i=0; i<properties.length; i++) {
				cell = row.createCell(i);
				
				//Object propertyValue;
				//propertyValue = FieldUtils.readDeclaredField(rowData, properties[i], true);
                
                // 修改为根据getMethodName读取值
                String methodName = "get".concat(StringUtils.capitalize(properties[i]));
				Object propertyValue = MethodUtils.invokeMethod(rowData, methodName, null);
				
				String cellValue = propertyValue==null?"":propertyValue.toString();
				cell.setCellValue(cellValue);
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}			
	}

	public String[] getTitles() {
		return titles;
	}

	public void setTitles(String[] titles) {
		this.titles = titles;
	}

	public String[] getProperties() {
		return properties;
	}

	public void setProperties(String[] properties) {
		this.properties = properties;
	}

	public List<T> getDatasources() {
		return datasources;
	}

	public void setDatasources(List<T> datasources) {
		this.datasources = datasources;
	}

	public String getSheetTitle() {
		return sheetTitle;
	}

	public void setSheetTitle(String sheetTitle) {
		this.sheetTitle = sheetTitle;
	}
	
}

2.ajax文件下载 AjaxFileDownload.js

/**
 * ajax 文件下载
 * Created by rico on 2017/5/10.
 * config.formUrl：下载URL
 * config.params：参数集合（元素<name, value>）
 *
 */
(function($, window){

    var AjaxFileDownload = function(config) {
        this._init(config);
    };

    AjaxFileDownload.prototype = {
        _init:function(config) {
            var that = this;
            that.$form = that._buildForm(config);

        },
        _buildForm:function(config) {
            var form = $('<form style="display: none;" action="'+config.formUrl+'" method="post"></form>');

            for(var i=0; i<config.params.length; i++) {
                var inputItem = $('<input type="text" name="'+config.params[i].name+'" value="'+config.params[i].value+'" >');
                form.append(inputItem);
            }
            $(document.body).append(form);

            return form;
        },
        _download:function() {
            var that = this;
            that.$form.submit();
        }
    };

    window.AjaxFileDownload = AjaxFileDownload;

}(jQuery, window));

3.Excel 导出Annotation，用于定义导出Excel标题及属性

import java.lang.annotation.*;

/**
 * Excel 导出Annotation
 * Created by rico on 2017/5/10.
 */
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFieldAnnotation {

    String value();
    String title() default "";
    int index() default 0;
}

4.Excel 导出Annotation工具

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * Excel 导出Annotation工具
 * Created by rico on 2017/5/10.
 */
public class ExcelFieldAnnotationUtil {

    /**
     * 排序
     * @param annotations
     */
    public static void sort(List<ExcelFieldAnnotation> annotations) {

        if(CollectionUtils.isEmpty(annotations)) {
            return;
        }

        Collections.sort(annotations, new Comparator<ExcelFieldAnnotation>() {
            public int compare(ExcelFieldAnnotation o1, ExcelFieldAnnotation o2) {
                if(o1.index() < o2.index()) {
                    return -1;
                } else if(o1.index() > o2.index()) {
                    return 1;
                }
                return 0;
            }
        });
    }

    /**
     * 读取Excel导出定义标题和属性
     * @param annotations
     * @return
     */
    public static ExcelFieldBean excelFieldBean(List<ExcelFieldAnnotation> annotations) {
        List<String> titles = new ArrayList<String>();
        List<String> fields = new ArrayList<String>();

        if(CollectionUtils.isEmpty(annotations)) {
            return new ExcelFieldBean();
        }

        for(ExcelFieldAnnotation item : annotations) {
            titles.add(item.title());
            fields.add(item.value());
        }

        return new ExcelFieldBean(titles, fields);
    }

    public static <T> List<ExcelFieldAnnotation> excelFieldAnnotations(Class clazz) {
        List<ExcelFieldAnnotation> annotations = new ArrayList<ExcelFieldAnnotation>();

        //Class clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        if(ArrayUtils.isEmpty(fields)) {
            return null;
        }

        for(Field item : fields) {
            if(!item.isAnnotationPresent(ExcelFieldAnnotation.class)) {
                continue;
            }

            annotations.add(item.getAnnotation(ExcelFieldAnnotation.class));
        }

        return annotations;
    }
}

5.Excel导出Bean

import org.apache.commons.collections4.CollectionUtils;

import java.util.List;

/**
 * Excel导出Bean
 * Created by rico on 2017/5/10.
 */
public class ExcelFieldBean {
    // excel 标题列表
    private List<String> titles;
    // excel 属性列表
    private List<String> fields;

    public ExcelFieldBean() {
    }

    public ExcelFieldBean(List<String> titles, List<String> fields) {
        this.titles = titles;
        this.fields = fields;
    }

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }

    public List<String> getFields() {
        return fields;
    }

    public void setFields(List<String> fields) {
        this.fields = fields;
    }

    public String[] toTitleArray() {
        if(CollectionUtils.isEmpty(titles)) {
            return null;
        }

        return titles.toArray(new String[titles.size()]);
    }

    public String[] toFieldArray() {
        if(CollectionUtils.isEmpty(fields)) {
            return null;
        }

        return fields.toArray(new String[fields.size()]);
    }
}

 

6. 导出例子

A.定义导出标题及属性Bean

public class Product {

    // {"brandName", "productName", "modeDesc", "color", "hdcapDesc"}
    @ExcelFieldAnnotation(value = "brandName", title = "Brand", index = 1)
    private String brandName;

    @ExcelFieldAnnotation(value = "productName", title = "Product Name", index = 2)
    private String productName;

    @ExcelFieldAnnotation(value = "modeDesc", title = "DESC", index = 3)
    private String modeDesc;

    @ExcelFieldAnnotation(value = "color", title = "Color", index = 4)
    private String color;

    @ExcelFieldAnnotation(value = "hdcapDesc", title = "HDCAP DESC", index = 5)
    private String hdcapDesc;

    public Product() {
    }
}

B.Web导出

/**
	 * 导出excel
	 * @throws IOException 
	 */
	@RequestMapping(value="/export")
	public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
		List<ProductMinPrice> list = productMinPriceService.findList();
		
		//String[] titles = {"品牌名称", "商品名称", "制式", "颜色", "内存大小"};
		//String[] properties = {"brandName", "productName", "modeDesc", "color", "hdcapDesc"};
		
		//ExcelExportUtils<ProductMinPrice> exportUtils = new ExcelExportUtils<ProductMinPrice>(titles, properties, list, "文件名");
        ExcelExportUtil<SampleInfo> exportUtils = new ExcelExportUtil(list, "Excel", 
            SampleInfoExcelBean.class);
		HSSFWorkbook workbook = exportUtils.createWorkbook();
		
		OutputStream os = null;
		try {
			String filename = "中文名称.xls";
			//处理中文文件名
			filename = URLEncoder.encode(filename, "UTF-8"); 
			
	        response.setContentType("application/vnd.ms-excel");       
	        response.setHeader("Content-disposition", "attachment;filename=" + filename);       
	        os = response.getOutputStream();
	        workbook.write(os);  
	        
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			os.close();
		}
	}

转载于:https://my.oschina.net/u/733161/blog/680658