一、Excel导出概述
在开发中,有时候需要将数据导出为Excel格式,在Excel中进行查看、分析、汇总等操作。对于Java Web开发者而言,一款好用的Excel导出插件是必不可少的。在SpringBoot中,我们可以使用EasyExcel、POI和JExcel等插件来实现Excel导出。
二、EasyExcel实现Excel导出
EasyExcel是基于POI封装的一款Java解析和生成Excel的开源项目,具有高效、易用、功能强大的特点。EasyExcel支持大文件读写、百万数据导出、快速生成Excel样式等功能。下面介绍一下在SpringBoot中如何实现Excel导出。
1、引入EasyExcel依赖
我们可以通过在pom.xml文件中引入如下EasyExcel的maven依赖来使用EasyExcel插件:
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6-beta5</version> </dependency>
2、定义实体类
假设我们要将一个人的信息导出为Excel表格,我们首先需要定义一个实体类:
public class Person { private String name; private Integer age; private String email; //省略get、set方法 }
3、创建ExcelWriter对象并写入数据
接下来是最关键的一步,即创建ExcelWriter对象并写入数据。我们可以通过如下代码来实现:
@RequestMapping("/export") public void export(HttpServletResponse response) throws IOException { Listlist = new ArrayList<>(); //省略添加数据操作 String fileName = "person.xlsx"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8")); response.setContentType("multipart/form-data"); EasyExcel.write(response.getOutputStream(), Person.class).sheet("sheet").doWrite(list); }
4、代码示例
下面是完整的Excel导出代码示例,其中还包括了生成Excel样式的操作:
@RequestMapping("/export") public void export(HttpServletResponse response) throws IOException { // 数据准备 Listlist = new ArrayList<>(); Person person = new Person(); person.setName("张三"); person.setAge(20); person.setEmail("zhangsan@demo.com"); list.add(person); // 生成Excel样式 ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build(); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 14); headWriteCellStyle.setWriteFont(headWriteFont); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); WriteFont contentWriteFont = new WriteFont(); contentWriteFont.setFontHeightInPoints((short) 12); contentWriteCellStyle.setWriteFont(contentWriteFont); // 写入数据 excelWriter.write(list, writeSheet, new TableWriteData(), new CellWriteData(headWriteCellStyle,contentWriteCellStyle)); // 输出Excel excelWriter.finish(); response.setContentType("multipart/form-data"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("person.xlsx","UTF-8")); }
三、POI实现Excel导出
POI(Poor Obfuscation Implementation)是由Apache组织提供的用于操作Microsoft Office格式文件的Java API。下面介绍一下在SpringBoot中如何实现Excel导出。
1、引入POI依赖
我们可以通过在pom.xml文件中引入如下POI的maven依赖来使用POI插件:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency>
2、创建Excel对象
接下来是最关键的一步,即创建Excel对象。我们可以通过如下代码来实现:
Workbook workbook = new XSSFWorkbook();//或者new HSSFWorkbook() Sheet sheet = workbook.createSheet("sheet"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("姓名"); row.createCell(1).setCellValue("年龄"); row.createCell(2).setCellValue("邮箱");
3、写入数据并导出
我们可以通过如下代码将数据写入Excel中:
int rowIndex = 1; for (Person person : list) { Row nRow = sheet.createRow(rowIndex++); nRow.createCell(0).setCellValue(person.getName()); nRow.createCell(1).setCellValue(person.getAge()); nRow.createCell(2).setCellValue(person.getEmail()); }
接下来是导出的代码:
String fileName = "person.xlsx"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8")); workbook.write(response.getOutputStream()); workbook.close();
4、代码示例
下面是完整的Excel导出代码示例:
@RequestMapping("/export") public void export(HttpServletResponse response) throws IOException { Listlist = new ArrayList<>(); Person person = new Person(); person.setName("张三"); person.setAge(20); person.setEmail("zhangsan@demo.com"); list.add(person); Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("sheet"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("姓名"); row.createCell(1).setCellValue("年龄"); row.createCell(2).setCellValue("邮箱"); int rowIndex = 1; for (Person person : list) { Row nRow = sheet.createRow(rowIndex++); nRow.createCell(0).setCellValue(person.getName()); nRow.createCell(1).setCellValue(person.getAge()); nRow.createCell(2).setCellValue(person.getEmail()); } response.setContentType("application/octet-stream"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("person.xlsx","UTF-8")); workbook.write(response.getOutputStream()); workbook.close(); }
四、JExcel实现Excel导出
JExcel是一款功能强大的Excel处理库,支持Excel 97/2000/XP/2003格式,使用JExcel可以实现单元格样式设置、单元格合并、单元格格式转换以及数据写入等功能。下面介绍一下在SpringBoot中如何实现Excel导出。
1、引入JExcel依赖
我们可以通过在pom.xml文件中引入如下JExcel的maven依赖来使用JExcel插件:
<dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
2、创建Excel对象
接下来是最关键的一步,即创建Excel对象。我们可以通过如下代码来实现:
WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream()); WritableSheet sheet = workbook.createSheet("Sheet1", 0); Label label1 = new Label(0, 0, "姓名"); Label label2 = new Label(1, 0, "年龄"); Label label3 = new Label(2, 0, "邮箱"); sheet.addCell(label1); sheet.addCell(label2); sheet.addCell(label3);
3、写入数据并导出
我们可以通过如下代码将数据写入Excel中:
int rowIndex = 1; for (Person person : list) { Label labelN1 = new Label(0, rowIndex, person.getName()); Label labelN2 = new Label(1, rowIndex, person.getAge().toString()); Label labelN3 = new Label(2, rowIndex, person.getEmail()); sheet.addCell(labelN1); sheet.addCell(labelN2); sheet.addCell(labelN3); rowIndex++; }
接下来是导出的代码:
workbook.write(); workbook.close(); response.flushBuffer();
4、代码示例
下面是完整的Excel导出代码示例:
@RequestMapping("/export") public void export(HttpServletResponse response) throws Exception { Listlist = new ArrayList<>(); Person person = new Person(); person.setName("张三"); person.setAge(20); person.setEmail("zhangsan@demo.com"); list.add(person); response.setContentType("application/octet-stream"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("person.xls","UTF-8")); WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream()); WritableSheet sheet = workbook.createSheet("Sheet1", 0); Label label1 = new Label(0, 0, "姓名"); Label label2 = new Label(1, 0, "年龄"); Label label3 = new Label(2, 0, "邮箱"); sheet.addCell(label1); sheet.addCell(label2); sheet.addCell(label3); int rowIndex = 1; for (Person person : list) { Label labelN1 = new Label(0, rowIndex, person.getName()); Label labelN2 = new Label(1, rowIndex, person.getAge().toString()); Label labelN3 = new Label(2, rowIndex, person.getEmail()); sheet.addCell(labelN1); sheet.addCell(labelN2); sheet.addCell(labelN3); rowIndex++; } workbook.write(); workbook.close(); response.flushBuffer(); }