SpringBoot加Poi仿照EasyPoi实现Excel导出
POI提供API给Java程序对Microsoft Office格式档案读和写的功能,详细功能可以直接查阅API,因为使用EasyPoi过程中总是缺少依赖,没有搞明白到底是什么坑,索性自己写一个简单工具类,来实现无论传入任何对象集合,都能够实现导出Excel的功能,没有看EasyPoi的源码, 只是在功能上模仿一下。
首先导入基本依赖,除了SpringBoot基本依赖依赖,导入Poi的依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
编写自定义注解
@Retention(RetentionPolicy.RUNTIME) public @interface Excel {
//Excel列名称 String name() default "";
//Excel列数 int orderNum() default 0; }
编写javaBean,所有需要导出的属性都使用Excel注解
public class Person{ private String id; @Excel(name = "用户姓名") private String userName; @Excel(name = "电话",orderNum = 1) private String phone; @Excel(name = "邮箱",orderNum = 2) private String email; @Excel(name = "地址",orderNum = 3) private String address; public void setId(String id){ this.id=id; } public String getId(){ return id; } public void setUserName(String userName){ this.userName=userName; } public String getUserName(){ return userName; } public void setphone(String phone){ this.phone=phone; } public String getphone(){ return phone; } public void setEmail(String email){ this.email=email; } public String getEmail(){ return email; } public void setAddress(String address){ this.address=address; } public String getAddress(){ return address; } }
编写工具类
public class PoiUtils { /** * * 功能描述: * 需要导出的属性要加Excel注解,实现Excel导出功能 * @param: [list, fileName, sheetName, response, clazz] * @return: void * @auther: wang * @date: 2019/1/18 15:31 */ public Static <T> void export(Collection<T> collection, String fileName, String sheetName, HttpServletResponse response, Class<?> clazz) throws IOException, IntrospectionException, InvocationTargetException, IllegalAccessException { HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个Excel表单,参数为sheet的名字 HSSFSheet sheet = workbook.createSheet(sheetName); //创建表头 setTitle(workbook, sheet, clazz); //新增数据行,并且设置单元格数据 int rowNum = 1; for (T t : collection) { HSSFRow row = sheet.createRow(rowNum); Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { if (field.isAnnotationPresent(Excel.class)) { PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz); Method getMethod = pd.getReadMethod();//获得get方法 Excel excel = field.getAnnotation(Excel.class); row.createCell(excel.orderNum()).setCellValue(String.valueOf(getMethod.invoke(t))); } } rowNum++; } //清空response response.reset(); // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8")); OutputStream os; //可以将生成Excel默认下载到某个目录下 //os = new BufferedOutputStream(new FileOutputStream("D:\\bmj\\target\\bmj128-0.0.1\\" + fileName));
//也可以通过response得到输出流,写到输出流中,在页面中进行下载 os = new BufferedOutputStream(response.getOutputStream()); //将excel写入到输出流中 workbook.write(os); os.flush(); os.close(); } /*** * 设置表头 * @param workbook * @param sheet */ private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, Class clazz) { HSSFRow row = sheet.createRow(0); Field[] fields = clazz.getDeclaredFields(); //设置为居中加粗 HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); //设置字体 font.setFontName("宋体"); //设置粗体 font.setBold(true); //设置字号 font.setFontHeightInPoints((short) 14); //设置颜色 font.setColor(IndexedColors.BLACK.index); style.setFont(font); HSSFCell cell; for (Field field : fields) { if (field.isAnnotationPresent(Excel.class)) { Excel excel = field.getAnnotation(Excel.class); cell = row.createCell(excel.orderNum()); cell.setCellValue(excel.name()); cell.setCellStyle(style); sheet.setColumnWidth(excel.orderNum(), 30*256); } } } }
页面调用,页面采用的是EasyUI框架
<div style="padding:5px;background:#fafafa;width:100%;"> <a href="javascript:void(0)" class="easyui-linkbutton" plain="true" iconCls="icon-undo" onclick="downLoadExcel()">导出日志</a> </div>
可以按照页面给增加筛选条件,根据筛选条件下载
function downLoadExcel(){ var url=base+'/api/xx?xx='+$("#xx").val()+'&xx='+$("#xx").val()+'&xx='+$("#xx").val(); window.location.href = url; }
编写Controller
@RestController @RequestMapping(value = "/api/xx") public class PersonController { private static final Logger LOG = LoggerFactory.getLogger(PersonController .class); @Resource(name = "personService") private PersonService personService; @RequestMapping("/export") public void export(PersonQuery personQuery, HttpServletResponse response) { List<Person> personList = personService.selectAll(personQuery); try { PoiUtils.export(auditLogList, "审计日志", "审计日志", response,AuditLog.class); } catch (IOException | IntrospectionException | InvocationTargetException | IllegalAccessException e) { LOG.error("审计日志错误{}",e); } } }
页面如下
点击导出日志,显示如下
目前仅仅是针对单个sheet的操作,也没有像合并单元格这种复杂操作,需要继续完善。