Springboot 整合easyExcel导出excel表格数据
程序员文章站
2022-03-15 10:17:10
...
1.导入包
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
<!-- xls格式excel依赖包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--xlsx格式excel依赖包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.使用Excel工具类
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* ExcelUtil
* 基于easyExcel的开源框架,poi版本3.17
* BeanCopy ExcelException 属于自定义数据,属于可自定义依赖
* 工具类尽可能还是需要减少对其他java的包的依赖
* @author wenxuan.wang
*/
public class ExcelUtil {
/**
* 私有化构造方法
*/
private ExcelUtil(){}
/**
* 读取 Excel(多个 sheet)
* 将多sheet合并成一个list数据集,通过自定义ExcelReader继承AnalysisEventListener
* 重写invoke doAfterAllAnalysed方法
* getExtendsBeanList 主要是做Bean的属性拷贝 ,可以通过ExcelReader中添加的数据集直接获取
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel,Class<T> rowModel) throws ExcelException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return new ArrayList<>();
}
for (Sheet sheet : reader.getSheets()) {
sheet.setClazz(rowModel);
reader.read(sheet);
}
return getExtendsBeanList(excelListener.getDataList(),rowModel);
}
/**
* 读取某个 sheet 的 Excel
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo) throws ExcelException{
return readExcel(excel, rowModel, sheetNo, 1);
}
/**
* 读取某个 sheet 的 Excel
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数,默认为1
* @return Excel 数据 list
*/
public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo,
int headLineNum) throws ExcelException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return new ArrayList<>();
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel));
return getExtendsBeanList(excelListener.getDataList(),rowModel);
}
/**
* 导出 Excel :一个 sheet,带表头
* 自定义WriterHandler 可以定制行列数据进行灵活化操作
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
String sheetName, BaseRowModel model,ExcelTypeEnum excelTypeEnum) throws Exception {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response, excelTypeEnum),
excelTypeEnum);
Sheet sheet = new Sheet(1, 0, model.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出 Excel :多个 sheet,带表头
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object, ExcelTypeEnum excelTypeEnum) throws ExcelException {
ExcelWriterFactory writer = null;
try {
writer = new ExcelWriterFactory(getOutputStream(fileName, response,excelTypeEnum), excelTypeEnum);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
return writer;
}
/**
* 导出文件时为Writer生成OutputStream
* @throws Exception
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) throws Exception{
//创建本地文件
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + excelTypeEnum.getValue());
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
/**
* 返回 ExcelReader
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel,
ExcelListener excelListener) throws ExcelException{
String fileName = excel.getOriginalFilename();
if (fileName == null ) {
throw new ExcelException("文件格式错误!");
}
if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
throw new ExcelException("文件格式错误!");
}
InputStream inputStream;
try {
inputStream = excel.getInputStream();
return new ExcelReader(inputStream, null, excelListener, false);
} catch (IOException e) {
//do something
}
return null;
}
/**
* 利用BeanCopy转换list
*/
public static <T extends BaseRowModel> List<T> getExtendsBeanList(List<?> list,Class<T> typeClazz){
return MyBeanCopy.convert(list,typeClazz);
}
}
3. 实体类(重点)
import java.util.Date;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data //相当于get set方法,需要下载插件和包
public class ExportPerson extends BaseRowModel{
@ApiModelProperty(value="姓名")
@ExcelProperty(value = "姓名", index = 0)
private String personName;
@ApiModelProperty(value="身份证号")
@ExcelProperty(value = "身份证号", index = 1)
private String idCardNumber;
@ApiModelProperty(value="手机号")
@ExcelProperty(value = "手机号", index = 2)
private String phoneNumber;
@ApiModelProperty(value="现住址")
@ExcelProperty(value = "现住址", index = 3)
private String presentAddress;
}
通过 @ExcelProperty注释声明导出文元素 value列名 ,index 列的序号
4. 控制层写法
@RequestMapping("exportPersonData")
public void exportPersonData(HttpServletResponse response){
List<ExportPerson> list = personDao.personsList();
String fileName = "文件名";
String sheet = "表名";
ExcelUtil.writeExcel(response,list,fileName,sheet,new ExportPerson(), ExcelTypeEnum.XLS);
}
最后导出excel文件,当然也有excel文件读取,在此不多赘述,感兴趣得到可以自己研究
上一篇: 69. x 的平方根(python)
推荐阅读