修改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();
}
}