推荐篇：原来阿里也对excel情有独钟
最近发现包下有一个excel导入导出工具类，非常好用，对于普通的场景及业务逻辑用起来非常方便。
记录一下；
位置：
该工具类在 com.包下。
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version>
</dependency>
特性：
该工具类对外提供了：读取和下载excel方法。
package com.gu.alibabaexcel.utils;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Objects;/*** @author Mr.Gu* @date 2020/10/21 9:16* @function : excel工具类**/
public class ExcelUtil<T> {/*** 读取excel数据* @param excel excel文件* @param cls 映射的实体类*/public static <T> List<T> readExcel(MultipartFile excel, Class cls) throws IOException {return readExcel(excel,cls,1);}/*** 读取excel数据* @param excel excel文件* @param cls 映射的实体类* @param headRowNumber 从第几行开始读 0代表从第一行开始 1为第二行*/public static <T> List<T> readExcel(MultipartFile excel,Class cls,int headRowNumber) throws IOException {return EasyExcelFactory.read(excel.getInputStream()).sheet().headRowNumber(headRowNumber).head(cls).doReadSync();}/*** 下载excel*/public static void download(HttpServletResponse response, Class cls, List data, String fileName, String sheetName) throws Exception {download(response, cls, data, fileName, sheetName,null);}public static void download(HttpServletResponse response, Class cls, List data, String fileName, String sheetName, WriteHandler handler) throws Exception {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");//表头样式WriteCellStyle headWriteCellStyle = new WriteCellStyle();//设置表头居中对齐headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//内容样式WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//设置内容靠左对齐contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream(), cls).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);if(Objects.nonNull(handler)){builder.registerWriteHandler(handler);}builder.doWrite(data);}
}使用方式：
/*** 导出excel* @param response*/@GetMapping("/exportExcel")public void exportExcel(HttpServletResponse response) {List<User> userList = new ArrayList<>();userList.add(new User(1,"张三","男",20,new BigDecimal(99.5)));userList.add(new User(2,"李四","女",30,new BigDecimal(89.5)));userList.add(new User(3,"王五","男",40,new BigDecimal(79.5)));try {ExcelUtil.download(response,User.class,userList,"学生信息","Sheet1");} catch (Exception e) {e.printStackTrace();}}
/*** 导入excel* @param file* @return*/@PostMapping("/import")public String importExcel(@RequestBody MultipartFile file) {if (file != null) {try {List<User> objectList = ExcelUtil.readExcel(file, User.class,0);System.out.println(objectList);} catch (IOException e) {e.printStackTrace();}}return "成功";}
注：导入和导出时使用到的User实体类而不是普通的实体类，需要添加该包下的注解，才可在导入或导出的时候和excel中的列映射；
如果实体类中存在许多字段而导出时有些字段不需要 就需要在该字段上添加@注解；就不会使用该字段；如果该字段不使用并且也没有添加的任何注解，那么导出时也会将该字段导出到excel表格中；
import com.alibaba.excel.annotation.ExcelProperty;import java.math.BigDecimal;/*** @author Mr.Gu* @date 2020/10/21 9:20* @function :**/
public class User {@ExcelProperty(value = "编号",index = 0)private Integer number;@ExcelProperty(value = "姓名",index = 1)private String name;@ExcelProperty(value = "性别",index = 2)private String sex;@ExcelProperty(value = "年龄",index = 3)private Integer age;@ExcelProperty(value = "分数",index = 4)private BigDecimal score;//getter/setter方法
最后，使用该工具类导出的excel如下：
非常简单，好用的一个excel工具类。记录完成。
设置单元格自适应宽度：
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class ExcelCellWriteHandler extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();@Overrideprotected 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);}}}}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;}}}}}将该类作为配置类放到项目中；
使用方式：
