需要源码小伙伴可以在我的微信小程序:”MINIIT资源库“获取,作者承诺免费
springboot实现easypoi
依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
实现代码:
package com.doll.easypoiexcel.controller;
import org.apache.commons.io.FileUtils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.doll.easypoiexcel.pojo.Goods;
import com.doll.easypoiexcel.service.GoodsService;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
public class ExcelController {
@Resource
private GoodsService goodsService;
/**
* 导出商品列表
* @param response
*/
@GetMapping("/exportAll")
public void exportAll(HttpServletResponse response) {
List<Goods> goodsList = goodsService.goodsList();
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("商品信息表", "商品"), Goods.class, goodsList);
response.setHeader("content-Type", "application/vnd.ms-excel");
try {
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("商品信息表.xlsx", "utf-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导入图片文件
* @param multipartFile
*/
@PostMapping("/importAll")
public void importAll(MultipartFile multipartFile) {
if (multipartFile.isEmpty()) {
return;
}
ImportParams importParams = new ImportParams();
importParams.setTitleRows(1);
importParams.setHeadRows(1);
File file = new File(multipartFile.getOriginalFilename());
try {
FileUtils.copyInputStreamToFile(multipartFile.getInputStream(), file);
} catch (IOException e) {
e.printStackTrace();
}
List<Goods> goodsList = ExcelImportUtil.importExcel(file, Goods.class, importParams);
System.out.println(goodsList.size());
}
/**
* 使用模板导出
* @param response
*/
@GetMapping("/exportByTemp")
public void exportByTemp(HttpServletResponse response) {
TemplateExportParams templateExportParams = new TemplateExportParams("templates/专项支出用款申请书.xlsx");
Map<String, Object> map = new HashMap<>();
map.put("date", "2021-01-17");
map.put("username", "张三");
map.put("telephone", "13012345678");
map.put("money", "5000");
ArrayList<Map<String, Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> itemMap = new HashMap<>();
itemMap.put("no", i);
itemMap.put("accountType", "资金性质");
itemMap.put("code", "Code1000" + i);
itemMap.put("name", "名称");
itemMap.put("projectName", "项目名称");
itemMap.put("fullName", "全称");
itemMap.put("account", "银行账户");
itemMap.put("bankName", "开户银行");
itemMap.put("amount", "申请金额");
itemMap.put("realAmount", "核定金额");
list.add(itemMap);
}
map.put("list", list);
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, map);
response.setHeader("content-Type", "application/vnd.ms-excel");
try {
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("专项支出用款申请书.xlsx", "utf-8"));
workbook.write(response.getOutputStream());
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
导出效果
使用模板导出效果