包:jxl-2.6.12.jar、commons-beanutils-1.9.3.jar
AttributeModel.java
public class AttributeModel {
/**
* @param attribute 属性 eg:age
* @param name 名称 eg:年龄
* */
public AttributeModel(String attribute, String name){
this.attribute = attribute;
this.name = name;
}
// excel表头
private String name;
// 实体类属性名
private String attribute;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAttribute() {
return attribute;
}
public void setAttribute(String attribute) {
this.attribute = attribute;
}
}
需要导出的数据模型集合 attributeList eg
List<AttributeModel> list = new ArrayList<>();
list.add(new AttributeModel("name","姓名"));
list.add(new AttributeModel("age","年龄"));
list.add(new AttributeModel("sex","性别"));
/**
* @Description: TODO 通用模板
* @Title: writeToFile
* @return void
* @parm list 数据集合
* @parm attributeList 需要导出的数据模型集合
*/
public <T> void writeToFile(List<T> list, List<AttributeModel> attributeList, HttpServletResponse response) {
if (CollectionUtils.isEmpty(attributeList)) {
return;
}
int FIRST_ROW = 0;
try {
WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream());
// 创建一个工作表。
WritableSheet sheet = book.createSheet("数据", 0);
// 在工作表上面添加内容
try {
// 第一行添加标题
Label label = null;
for (int column = 0; column < attributeList.size(); column++) {
label = new Label(column, FIRST_ROW, attributeList.get(column).getName());
sheet.addCell(label);
}
if (!CollectionUtils.isEmpty(list)) {
Object param = null;
for (int row = FIRST_ROW + 1; row <= list.size(); row++) {
T t = list.get(row - 1);
Map<String, Object> properties = conversionToMap(t);
for (int i = 0; i < attributeList.size(); i++) {
param = properties.get(attributeList.get(j).getAttribute());
label = new Label(i, row, objectToString(param));
sheet.addCell(label);
}
}
}
} catch (RowsExceededException e) {
logger.error("行或列参数错误!{}", e);
} catch (WriteException e) {
logger.error("写入失败!{}", e);
} catch (Exception e) {
logger.error("写入失败!{}", e);
} finally {
if (book != null) {
book.write();
try {
book.close();
} catch (WriteException e) {
logger.error("文件关闭失败!{}", e);
}
}
}
} catch (IOException e) {
logger.error("创建文件失败!{}", e);
}
}
/**
*泛型转换为map
*/
private <T> Map<String, Object> conversionToMap(T bean) throws Exception {
Map<String, Object> map = new HashMap<String, Object>();
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(bean);
for (PropertyDescriptor d : descriptors) {
String fieldName = d.getName();
Object value = propertyUtilsBean.getNestedProperty(bean, fieldName);
if (!"class".equals(fieldName))
map.put(fieldName, value);
}
return map;
}
/**
*onject转string
*/
private String objectToString(Object param) {
String value = "";
if (param instanceof Number) {
// 自定义取2位小数
Double processResu = MoneyCalculate.round(new Double(param.toString()));
value = processResu.toString();
} else if (param instanceof String) {
value = (String) param;
} else if (param instanceof Boolean) {
boolean b = ((Boolean) param).booleanValue();
if (b) {
value = "是";
} else {
value = "否";
}
} else if (param instanceof Date) {
// 自定义时间转换
value = DateUtil.formatDateYMDHMS((Date) param);
}
return value;
}