2021-01-13 12:03:08 卢浮宫 版权声明:本文为站长原创文章,转载请写明出处
截止2021年1月13日10:39:48,所涉及的项目中关于Excel导出的处理方式已经达到了3种:
① Apache poi
② ezsyPoi
③ mime (这个是前端导出,有兴趣的可以去查阅先关资料)
① 整体来说,无论是哪种方式的实现,都能满足我们的日常需要,也就是说系统日常使用是OK的。
这3中方式被用到的不同的场景之下,但是因为没有一个通用的规则,看起来有些许杂乱
无论是业务增加还是问题排查,都要很好的get这三种场景下的操作代码,增加业务成本
负责该模块的开发人员增加了学习成本和基本使用成本
昨天京东那边反馈说打不开我们导出的excel文件(他们是使用deoffice2016,我们使用的是wps)。
针对上面的问题做一一修复!
统一使用easyPoi作为技术栈
读取:
① 根据文件路径读取
② 前端上传文件处理
导出:
① 简单数据列表导出
② 复杂场景下的模板导出
③ 导出到本地路径
适用场景广泛,且不能有硬编码
整理出一份工具类如下:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.hd.modules.excel.listener.StringExcelListener;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
@Component
public class XaExcelUtil {
/**
* 功能描述: 数据列表型Excel导出 --> 可通过前端请求进行下载
* Param: [list, title, sheetName, pojoClass, fileName, response]
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName
* @param response
* Return: void
*/
public static void exportExcelForSimpleData(List<?> list, String title,
String sheetName,Class<?> pojoClass, String fileName,
HttpServletResponse response) {
Workbook workbook =
ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), pojoClass, list);
if (workbook != null) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") );
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
/**
* 功能描述: 根据模板导出Excel文件
* Param: [response, templatePath, fileName, data]
* @param response response
* @param templatePath 模板文件的路径(可以是一个放置于statics路径下的xls文件)
* @param fileName 导出文件名
* @param data 模板中数据
* Return: void
*/
public static void exportExcelForTemplate(HttpServletResponse response,
String templatePath,String fileName, Map<String, Object> data){
TemplateExportParams params = new TemplateExportParams(convertTemplatePath(templatePath), true);
Workbook workbook = ExcelExportUtil.exportExcel(params, data);
try (OutputStream outputStream = response.getOutputStream()) {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",\
"attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 功能描述: 把静态路径下文件内容复制到ClassPath下
* Param: [path]
* Return: java.lang.String
*/
private static String convertTemplatePath(String path) {
ClassPathResource resource = new ClassPathResource(path);
File tempFile = new File(path);
// 文件存在时 不再写入
//if (tempFile.exists()) {
// return path;
//}
File parentFile = tempFile.getParentFile();
// 判断父文件夹是否存在
if (!parentFile.exists()) {
parentFile.mkdirs();
}
try {
FileOutputStream fileOutputStream = new FileOutputStream(tempFile);
BufferedInputStream bufferedInputStream =
new BufferedInputStream(resource.getInputStream());
IOUtils.copy(bufferedInputStream, fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
}
return path;
}
/**
* 功能描述: 导出excel文件到本地路径
* Param: [list, model, localPath]
* @param list 数据集合
* @param model 接收的实体模型
* @param localPath 导出到的本地路径(C:\Users\Administrator\Desktop\writeExcel.xlsx)
* Return: void
*/
public static void exportExcelToLocal(List<?> list, Class<?> model, String localPath){
EasyExcel.write(localPath, model).sheet("sheet1").doWrite(list);
}
/**
* 功能描述: 读取excel文件,可以是来自前端或本地
* Param: [fileupload]
* Return: java.util.List<java.util.List<java.lang.String>>
*/
public List<List<String>> readExcel(MultipartFile fileupload) {
File file1 = null;
try {
file1 = File.createTempFile("temp", null);
fileupload.transferTo(file1);
FileInputStream fileInputStream = new FileInputStream(file1);
// 也可以是本地路径
//FileInputStream fileInputStream = null;
//try {
//fileInputStream = new FileInputStream("D:/111.xlsx");
//} catch (Exception e) {
//e.printStackTrace();
//}
StringExcelListener listener = new StringExcelListener();
ExcelReader excelReader =
EasyExcelFactory.read(fileInputStream, null, listener).headRowNumber(0).build();
excelReader.read();
List<List<String>> datas = listener.getDatas();
excelReader.finish();
return datas;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
① 创建一个a标签
<a href="/exportExcel">export</a>
② 后端的业务处理
PS: 测试需要,直接写业务在controller中了
实体类:
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.excel.annotation.ExcelIgnore;
import lombok.Data;
@Data
public class ExcelExportTestVo {
@ExcelIgnore
private String vcCustomerId;
@Excel(name = "客户名称",height = 11, width = 15)
private String vcName;
@Excel(name = "客户手机号",height = 11, width = 15)
private String vcPphone;
@Excel(name = "客户电话",height = 11, width = 15)
private String vcTel;
@Excel(name = "业务员",height = 11, width = 15)
private String vcSalesman;
@Excel(name = "是否未提交",replace =
{"是_0", "否_1","否_2","否_3","否_4","否_5"}, height = 11, width = 15)
private Integer orderStatus;
/**
* @Description: 客户订单统计
*/
@Excel(name = "客户订单总计",height = 11, width = 15)
private Integer orderTotal;
/**
* @Description: 客户订单金额统计
*/
@Excel(name = "客户订单总金额",height = 11, width = 15)
private Double orderMoneyTotal;
}
// 业务处理
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response){
List<ExcelExportTestVo> excelExportTestVoList = new ArrayList<>();
for(int i=0;i<10;i++){
ExcelExportTestVo excelExportTestVo = new ExcelExportTestVo();
excelExportTestVo.setOrderMoneyTotal(Double.valueOf(i));
excelExportTestVo.setOrderStatus(i);
excelExportTestVo.setOrderTotal(i);
excelExportTestVo.setVcName("name" + i);
excelExportTestVo.setVcPphone("phone" + i);
excelExportTestVo.setVcSalesman("ss" + i);
excelExportTestVo.setVcTel("tel" + i);
excelExportTestVoList.add(excelExportTestVo);
}
XaExcelUtil.exportExcelForSimpleData(excelExportTestVoList,"客户订单统计表",
"客户订单统计", ExcelExportTestVo.class,"客户订单统计表.xls",response);
}
③ 导出效果如下(兼容office2016和wps):
① 创建模板文件如下图:
② 前端请求如下:
PS: HttpClient相关可参考:https://guangmuhua.com/articleDetail/237
httpClient.download({
url: '/exportExcel',
data: {
}
}).then(res => {
});
③ 后端处理代码
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response){
List<ExcelExportTestVo> excelExportTestVoList = new ArrayList<>();
for(int i=1;i<5;i++){
ExcelExportTestVo excelExportTestVo = new ExcelExportTestVo();
excelExportTestVo.setVcCustomerId(String.valueOf(i));
excelExportTestVo.setOrderMoneyTotal(Double.valueOf(i));
excelExportTestVo.setOrderStatus(i);
excelExportTestVo.setOrderTotal(i);
excelExportTestVo.setVcName("name" + i);
excelExportTestVo.setVcPphone("phone" + i);
excelExportTestVo.setVcSalesman("ss" + i);
excelExportTestVo.setVcTel("tel" + i);
excelExportTestVoList.add(excelExportTestVo);
}
Map<String, Object> data = new HashMap<>();
data.put("bankName", "bankName test");
data.put("owner", "owner test");
data.put("account", "account test");
data.put("address", "addtess test");
data.put("swiftCode", "swiftCode test");
data.put("orderList", excelExportTestVoList);
data.put("messers", "message");
data.put("settlement", "settlement test");
data.put("attn", "attn test");
data.put("fax", "fax test");
data.put("tel", "tel test");
data.put("duration", "duration test");
XaExcelUtil.exportExcelForTemplate(response,
"statics/excel/excelExport.xls", "tmplateExport.xls", data);
}
④ 整体效果如下
PS: 达到效果,然美观不足。兼容Office2016和wps
① 前端请求
<a href="/exportExcel">export</a>
② 后端处理如下:
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response){
List<ExcelModel> modelList = new ArrayList<>();
for(int i=0;i<11;i++){
ExcelModel excelModelTmp = new ExcelModel();
excelModelTmp.setOrderNo("YD" + i);
excelModelTmp.setWarehouseDate(new Date());
modelList.add(excelModelTmp);
}
XaExcelUtil.exportExcelToLocal(modelList, ExcelModel.class,
"C:\\Users\\Administrator\\Desktop\\writeExcel.xlsx");
}
③ 效果如下
直接调readExcel方法即可,会返回一个List<List<String>>
格式的数据。以上述的excel文件为例:
[
[订单号, null, 包裹入库日期],
[YD0, null, 2021-01-13 14:46:41],
[YD1, null, 2021-01-13 14:46:41],
[YD2, null, 2021-01-13 14:46:41],
[YD3, null, 2021-01-13 14:46:41],
[YD4, null, 2021-01-13 14:46:41],
[YD5, null, 2021-01-13 14:46:41],
[YD6, null, 2021-01-13 14:46:41],
[YD7, null, 2021-01-13 14:46:41],
[YD8, null, 2021-01-13 14:46:41],
[YD9, null, 2021-01-13 14:46:41],
[YD10, null, 2021-01-13 14:46:41]
]
内容整理与历史项目,参考网络相关文档。如有错误敬请斧正!
更多精彩请持续关注:guangmuhua.com