一、最重要的pom依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
二、三个工具类
①ExcelUtils(导出工具类,复制粘贴即可使用)
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
/**
* 导出Excel工具类
*/
public class ExcelUtils {
/**
* 导出excel
*
* @param response
* @param excelName 数据表格名称
* @param sheetName 工作蒲名称
* @param pojo 实体类
* @param dataList 数据源列表
* @throws Exception
*/
public static void exportData(HttpServletResponse response, String excelName, String sheetName, Class pojo, List dataList) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), pojo).registerWriteHandler(new CustomCellWriteHandler()).sheet(sheetName).doWrite(dataList);
}
}
②ExcelListener(监听工具类,复制粘贴即可使用)
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* 导入工具类
*
* @author coder
* @date 2021/12/7
*/
public class ExcelListener<T> extends AnalysisEventListener<T> {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
datas.add(o);
//根据自己业务做处理
doSomething(o);
}
private void doSomething(Object object) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// datas.clear();//解析结束销毁不用的资源
}
}
③CustomCellWriteHandler(样式工具类,复制粘贴即可使用)
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 导出excel设置列自动宽度
*/
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
/**
* 设置列宽
*
* @param writeSheetHolder
* @param cellDataList
* @param cell
* @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 设置行高
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
三、逻辑代码(导出)
1、serviceImpl层
@Resource
private 自己的Service 自己的Service;
/**
* 下载数据模板
*
* @param response
*/
@Override
public void downloadModel(HttpServletResponse response) {
List<自己的实体类> dataList = new ArrayList<>();
自己的实体类 pojo= new 自己的实体类();
dataList.add(pojo);
try {
ExcelUtils.exportData(response, "数据模板", "sheet1", 自己的实体类.class, dataList);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导出数据列表
*
* @param response
*/
@Override
public void exportData(HttpServletResponse response) {
//换成自己的实体类
QueryWrapper<自己的实体类> queryWrapper = new QueryWrapper<>();
//获取表中的所有数据
queryWrapper .exists("SELECT * FROM `表名`");
String excelName = "全部数据列表";
//数据列表
List<自己的实体类> dataList = 自己的Service.list(queryWrapper);
try {
ExcelUtils.exportData(response, excelName, "sheet1", 自己的实体类.class, dataList);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导入数据列表
*
* @param file
* @throws IOException
*/
@Override
public boolean importData(MultipartFile file) throws IOException {
//获取文件流
InputStream inputStream = file.getInputStream();
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
EasyExcelFactory.read(inputStream, 自己的实体类.class, listener).headRowNumber(1).build().readAll();
//获取数据
List<自己的实体类> list = listener.getDatas();
return 自己的Service.saveBatch(list);
}
2、service层
/**
* 下载数据模板
*
* @param response
*/
void downloadModel(HttpServletResponse response);
/**
* 导出数据列表
*
* @param response
*
*/
void exportData(HttpServletResponse response);
/**
* 导入数据列表
*
* @param file
* @throws IOException
*/
boolean importData(MultipartFile file) throws IOException;
3、controller层
@Autowired
private 自己的Service 自己的Service;
/**
* 下载数据模板
*
* @param response
*/
@ApiOperation("下载题库模板")
@GetMapping("/downloadModel")
public void downloadModel(HttpServletResponse response) {
自己的Service.downloadModel(response);
}
/**
* 导出数据列表
*
* @param response
*/
@ApiOperation("导出题库列表")
@GetMapping("/exportData")
public void exportData(HttpServletResponse response) {
自己的Service.exportData(response);
}
/**
* 导入数据列表
*
* @param file
* @throws IOException
*/
@ApiOperation("导入题库列表")
@PostMapping("/importData")
public boolean importData(MultipartFile file) throws IOException {
return 自己的Servicee.importData(file);
}
四、最终效果
①下载模板
② 导出数据列表
③ 导入数据列表