算是封装的工具类EasyExcel

这是我参与8月更文挑战的第24天，活动详情查看：8月更文挑战

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.9</version>
</dependency>
复制代码

定义导入和导出模型

• 定义导出类模型

@ContentRowHeight(30)// 表体行高
@HeadRowHeight(20)// 表头行高
@ColumnWidth(20)
@Data
public class BusinessResouceExcel  {
 
	@ExcelProperty(value = "店铺ID",index = 0)
	private String businessId;
 
	@ExcelProperty(value = "法人名称",index = 1)
	private String legalPerson;
 
	@ExcelProperty(value = "法人身份证号码",index = 2)
	private String legalPersonIdcard;
 
}
复制代码

• 定义导入类模型

@Data
public class BusinessResouceExcel  {
 
    @ExcelProperty(index = 0)
    private String businessId;
 
    @ExcelProperty(index = 1)
    private String legalPerson;
}
复制代码

对于动态导入示例，还在研究过程。

Object与实体类转换工具类

 
import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
 
/**
 * @ClassName ExportModel
 * @Description Object与实体类转换工具类
 */
public final class BeanConvert {
    private BeanConvert(){}
    /**
     * 将List<Object> 转换为List<Bean>
     * @param sources 源对象
     * @param targetClass 目标类
     * @param <T>
     * @return
     */
    public static <T> List<T> objectConvertBean(List<?> sources, Class<T> targetClass) {
        List<?> sourcesObj = sources;
        if (sourcesObj == null) {
            sourcesObj = Collections.emptyList();
        }
        List<T> targets = new ArrayList<>(sourcesObj.size());
        convert(sourcesObj, targets, targetClass);
        return targets;
    }
 
    /**
     * 复制源对象到目的对象
     * 注意：
     *   org.springframework.beans.BeanUtils.copyProperties 是一个Spring提供的名称相同的工具类
     *   但它不支持类型自动转换，如果某个类型属性不同，则不予转换那个属性
     *   org.apache.commons.beanutils.BeanUtils 是一个Apache提供的名称相同的工具类
     *   支持类型自动转换，如Date类型会自动转换为字符串
     * @param sources  源对象
     * @param targets 目的对象
     * @param targetClass 目标类
     * @param <T>
     */
    private static <T> void convert(List<?> sources, List<T> targets, Class<T> targetClass) {
        if (sources == null) {
            return;
        }
        if (targets == null) {
            return;
        }
        targets.clear();
        for (Object obj : sources) {
            try {
                T target = targetClass.newInstance();
                targets.add(target);
                BeanUtils.copyProperties(obj, target);
            } catch (Exception e) {
                return;
            }
        }
    }
 
}
复制代码

设置监听器ExcelListener

 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @Description 监听类
 *
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> dataList = new ArrayList<>();
 
 
    @Override
    public void invoke(Object object, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        dataList.add(object);
        handleBusinessLogic();
 
          /*
        如数据过大，可以进行定量分批处理
        if(dataList.size()>=200){
            handleBusinessLogic();
            dataList.clear();
        }
         */
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //非必要语句，查看导入的数据
        //System.out.println("导入的数据 " + dataList.toString());
        //解析结束销毁不用的资源
        //dataList.clear();
    }
 
    //根据业务自行实现该方法，例如将解析好的dataList存储到数据库中
    private void handleBusinessLogic() {
 
    }
 
    public List<Object> getDataList() {
        return dataList;
    }
 
    public void setDataList(List<Object> dataList) {
        this.dataList = dataList;
    }
}
复制代码

核心工具类ExcelUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
 
/**
 *
 * @Description Excel读写工具
 */
public class ExcelUtils {
 
    private ExcelUtils(){}
 
 
    /**
     * 下载模板
     * @param filePath 模板路径
     * @param response 响应信息数据
     */
    public static void downLoadExcel(String filePath, HttpServletResponse response) {
        try {
            ClassPathResource classPathResource = new ClassPathResource(filePath);
            Workbook workbook =WorkbookFactory.create(classPathResource.getInputStream());
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
 
    /**
     * 读取Excel（一个sheet）
     * @param excel 文件
     * @param clazz 实体类
     * @param sheetNo sheet序号
     * @return 返回实体列表(需转换)
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz,int sheetNo) {
 
        ExcelListener excelListener = new ExcelListener();
 
        ExcelReader excelReader = getReader(excel,clazz,excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
 
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        excelReader.read(readSheet);
        excelReader.finish();
 
        return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
    }
 
 
    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     * @param excel 文件
     * @param clazz 实体类
     * @return 返回实体列表(需转换)
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz) {
 
        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = getReader(excel,clazz,excelListener);
 
        if (excelReader == null) {
            return new ArrayList<>();
        }
 
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
 
        for (ReadSheet readSheet:readSheetList){
            excelReader.read(readSheet);
        }
 
        excelReader.finish();
 
        return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
    }
 
 
    /**
     * 导出Excel(一个sheet)
     *
     * @param response  HttpServletResponse
     * @param list      数据list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的sheet名
     * @param clazz 实体类
     */
    public static <T> void  writeExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz) {
 
        OutputStream outputStream = getOutputStream(response, fileName);
 
        ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
 
        excelWriter.write(list, writeSheet);
 
        excelWriter.finish();
    }
 
 
    /**
     * 导出Excel(带样式)
     *
     * @return
     */
    public static  <T> void writeStyleExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz) {
        //表头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //背景浅灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);
 
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 否则无法显示背景颜色；头默认了FillPatternType
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //背景浅绿
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        //字体大小
        contentWriteFont.setFontHeightInPoints((short)15);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
 
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
 
        OutputStream outputStream = getOutputStream(response, fileName);
        EasyExcel.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(list);
 
    }
 
 
    /**
     * 导出Excel(动态表头)
     * write时不传入class,table时传入并设置needHead为false
     * @return
     */
    public static  <T> void writeDynamicHeadExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz,List<List<String>> headList) {
 
        OutputStream outputStream = getOutputStream(response, fileName);
 
        EasyExcel.write(outputStream)
                .head(headList)
                .sheet(sheetName)
                .table().head(clazz).needHead(Boolean.FALSE)
                .doWrite(list);
    }
 
 
    /**
     * 导出时生成OutputStream
     */
    private static OutputStream getOutputStream(HttpServletResponse response,String fileName) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException  e) {
            e.printStackTrace();
        }
        return null;
    }
 
    /**
     * 返回ExcelReader
     * @param excel         文件
     * @param clazz         实体类
     * @param excelListener
     */
    private static <T> ExcelReader getReader(MultipartFile excel, Class<T> clazz, ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
 
        try {
            if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
                return null;
            }
 
            InputStream inputStream = new BufferedInputStream(excel.getInputStream());
 
            ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
 
            inputStream.close();
 
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
复制代码

public class SetResponseUtil {
 
    public static HttpServletResponse setResponse(HttpServletResponse response,String exportName){
 
        //添加响应头信息
 
        try {
            //response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(exportName, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/msexcel;charset=UTF-8");//设置类型
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");//告诉前端可以拿到名字
        response.setHeader("Pragma", "No-cache");//设置头
        response.setHeader("Cache-Control", "no-cache");//设置头
        response.setDateHeader("Expires", 0);//设置日期头
 
        return response;
    }
}

复制代码

导出

//设置输入流，设置响应域
String  fileName = URLEncoder.encode("导出名字.xlsx","utf-8");
response = SetResponseUtil.setResponse(response,fileName);
//需要合并的列
int[] mergeColumeIndex = {0,1,2};
//需要从第一行开始，列头第一行
int mergeRowIndex = 1;
EasyExcel//将数据映射到DownloadDTO实体类并响应到浏览器
    .write(new BufferedOutputStream(response.getOutputStream()), ComboExcel.class)
    //07的excel版本,节省内存
    .excelType(ExcelTypeEnum.XLSX)
    //是否自动关闭输入流
    .autoCloseStream(Boolean.TRUE)
    //.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
    //               // 自定义列宽度，有数字会
    //                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
    //设置excel保护密码
    //                .password("123456")
    .sheet().doWrite(list);
复制代码

导入

 @ApiOperation("导入商城订单")
@PostMapping("/importProductOrder")
public Resp getImportProductOrder(@RequestParam("file")MultipartFile file){

    List<ImportProductOrderExcel> orderExcels = ExcelUtils.readExcel(file, ImportProductOrderExcel.class);
    productOrderService.getImportUpdate(orderExcels);
    return Resp.ok(null);
}
复制代码

异常处理工具类

public class WriteTXTUtils {
 
 
    private final static String URL_LIUNX = "/serverHome/doc/2021/";
    private final static String URL = "D:\\2020work\\idea\\ssht\\";
 
    public  static void writeTXT(List<String> strings, String title){
        try {
            // 防止文件建立或读取失败，用catch捕捉错误并打印，也可以throw
            /* 写入Txt文件 */
            boolean flag = SystemUtil.checkSystem();
            String url = "";
            if(flag){
                url = URL_LIUNX;
            }else {
                url= URL;
            }
            File writename = new File(url);// 相对路径，如果没有则要建立一个新的output。txt文件
            if(!writename.exists()){
                writename.mkdirs();
            }
            writename = new File(url+title);// 相对路径，如果没有则要建立一个新的output。txt文件
            writename.createNewFile(); // 创建新文件
            BufferedWriter out = new BufferedWriter(new FileWriter(writename));
            for (String l:strings){
                out.write(l + "\r\n");//换行写入
            }
            out.flush(); // 把缓存区内容压入文件
            out.close(); // 最后记得关闭文件
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
复制代码