前两天做了一个导出excel的功能,上线以后客户反应说导出的excel文件打不开,原因居然是office不兼容,WPS兼容,所以我就用poi写了一个
下面展示一些 内联代码片
。
// 封装excel
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("流程台账");
Row row = sheet.createRow(0);
//设置标题
if (row != null) {
row.createCell(0).setCellValue("流程ID");
row.createCell(1).setCellValue("需求编号");
row.createCell(2).setCellValue("标题");
row.createCell(3).setCellValue("当前节点");
row.createCell(4).setCellValue("当前审批人");
row.createCell(5).setCellValue("申请人");
row.createCell(6).setCellValue("联系电话");
row.createCell(7).setCellValue("申请日期");
row.createCell(8).setCellValue("申请人部门");
row.createCell(9).setCellValue("申请人科室");
row.createCell(10).setCellValue("当前应用系统");
if (allProcessaccounts != null && allProcessaccounts.size() > 0) {
for (int i = 0; i < allProcessaccounts.size(); i++) {
ImdDemandProcessaccounts p = allProcessaccounts.get(i);
rowIndex++;
Row rowF = sheet.createRow(rowIndex);
// 添加第一层
rowF.createCell(0).setCellValue(p.getHeadDocumentId());
rowF.createCell(1).setCellValue(p.getDocNum());
rowF.createCell(2).setCellValue(p.getDemTitle());
rowF.createCell(3).setCellValue(p.getNodeName());
rowF.createCell(4).setCellValue(p.getApproveUserName());
rowF.createCell(5).setCellValue(p.getEmployeeName());
rowF.createCell(6).setCellValue(p.getTelPhone());
rowF.createCell(7).setCellValue(p.getApplyDate());
rowF.createCell(8).setCellValue(p.getOrgName());
rowF.createCell(9).setCellValue(p.getOrganizeAllName());
rowF.createCell(10).setCellValue(p.getCutSystem());
这种方法虽然简单,但是不够复用,属于硬编码,我后来又有一些大量导出excel的需求,我就决定来写个工具类了,设计了半天,决定用Java特性反射和泛型来做,上代码
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Details {
//详情
String value();
//是否导出
boolean isExport();
}
@Data
@Table(value = "EXTICT_INFO_QUERY_V")
public class ExtictInfoQuery extends MpaasBasePojo {
@Details(value = "出门证单号",isExport = true)
@Style(displayName = "出门证单号", width = "25")
@Column(value = "DOC_NUMBER")
private String docNumber;
@Details(value = "DOCUMENT_ID",isExport = true)
@Column(value = "DOCUMENT_ID")
private Long documentId;
@Details(value = "资产所属单位/发货单位",isExport = true)
@Style(displayName = "资产所属单位/发货单位", width = "25")
@Column(value = "ASSETS_AFF_UNIT")
private String assetsAffUnit;
public class ExportUtil <T>{
public void exportDate(Class o, List<T> list, HttpServletResponse response,String fileName) {
try {
// HSSFWorkbook wb = getHSSFWorkbook(o, list);
XSSFWorkbook wb = getHSSFWorkbook(o,list);
if (wb == null) {
return;
}
//写入response
response.reset();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859_1"));
ServletOutputStream fileOut = response.getOutputStream();
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
}
public XSSFWorkbook getHSSFWorkbook(Class c, List<T> list) throws NoSuchFieldException, IllegalAccessException {
//滤空
if (list == null || list.isEmpty()) {
return null;
}
//反射获取属性
Field[] fields = c.getDeclaredFields();
//创建备用集合,用来存放需要展示的字段
ArrayList<Field> fields1 = new ArrayList<>();
for (Field field : fields) {
//获取自定义注解对象
Details annotation = c.getDeclaredField(field.getName()).getAnnotation(Details.class);
//判断当前字段是否需要导出
if (annotation.isExport()) {
//新增进备用集合
fields1.add(field);
}
}
// 封装excel
XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
//创建sheet页
XSSFSheet sheet = hssfWorkbook.createSheet();
Row row = sheet.createRow(0);
XSSFCellStyle style = hssfWorkbook.createCellStyle();
XSSFFont font = hssfWorkbook.createFont();
font.setBold(true);
//font.setFontHeightInPoints((short) 16);
style.setFont(font);
if (row != null) {
for (int i = 0; i < fields1.size(); i++) {
//获取字段详情解释
Details annotation = c.getDeclaredField(fields1.get(i).getName()).getAnnotation(Details.class);
//添加标题并赋值
row.createCell(i).setCellValue(annotation.value());
row.getCell(i).setCellStyle(style);
//row.setRowStyle(style);
}
}
//设置变量,便于后续方便给文本框更改样式
int rowIndex = 0;
//滤空
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
T p = list.get(i);
rowIndex++;
//新增sheet页
Row rowF = sheet.createRow(rowIndex);
for (int j = 0; j < fields1.size(); j++) {
//获取反射过来的属性对象
Field field = fields1.get(j);
//把成员变量变为公有化
field.setAccessible(true);
//获取集合中成员变量的值field.toString()
Object o1 = field.get(p);
if ("class java.util.Date".equals(field.getGenericType().toString())){
String format = new SimpleDateFormat("yyyy-MM-dd").format(o1);
o1=format;
}
//滤空赋值
if (o1 != null) {
rowF.createCell(j).setCellValue(o1.toString());
} else {
rowF.createCell(j).setCellValue("");
}
}
}
}
//设置样式
for (int i = 0; i <= rowIndex; i++) {
sheet.setColumnWidth(i, 20 * 256);
}
return hssfWorkbook;
}
}