好用的导入导出工具类

package appapi.utils;

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.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;
import java.util.NoSuchElementException;

/**

• Excel工具类

• @author luocheng

• @version 1.0

• @date 2023/06/02 17:51
  */
  public class ExcelUtils {

  /**

  • excel 导出
  • @param list 数据
  • @param title 标题
  • @param sheetName sheet名称
  • @param pojoClass pojo类型
  • @param fileName 文件名称
  • @param isCreateHeader 是否创建表头
  • @param response
    */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
    boolean isCreateHeader, HttpServletResponse response) throws IOException {
    ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
    exportParams.setCreateHeadRows(isCreateHeader);
    defaultExport(list, pojoClass, fileName, response, exportParams);
    }

  /**

  • excel 导出
  • @param list 数据
  • @param title 标题
  • @param sheetName sheet名称
  • @param pojoClass pojo类型
  • @param fileName 文件名称
  • @param response
    */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
    HttpServletResponse response) throws IOException {
    defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

  /**

  • excel 导出
  • @param list 数据
  • @param pojoClass pojo类型
  • @param fileName 文件名称
  • @param response
  • @param exportParams 导出参数
    */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams,
    HttpServletResponse response) throws IOException {
    defaultExport(list, pojoClass, fileName, response, exportParams);
    }

  /**

  • excel 导出
  • @param list 数据
  • @param fileName 文件名称
  • @param response
    */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
    throws IOException {
    defaultExport(list, fileName, response);
    }

  /**

  • 默认的 excel 导出
  • @param list 数据
  • @param pojoClass pojo类型
  • @param fileName 文件名称
  • @param response
  • @param exportParams 导出参数
    */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
    ExportParams exportParams) throws IOException {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    downLoadExcel(fileName, response, workbook);
    }

  /**

  • 默认的 excel 导出
  • @param list 数据
  • @param fileName 文件名称
  • @param response
    */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
    throws IOException {
    Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
    downLoadExcel(fileName, response, workbook);
    }

  /**

  • 下载
  • @param fileName 文件名称
  • @param response
  • @param workbook excel数据
    */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
    throws IOException {
    try {
    response.setCharacterEncoding(“UTF-8”);
    response.setHeader(“content-Type”, “application/vnd.ms-excel”);
    response.setHeader(“Content-Disposition”, “attachment;filename=”
    + URLEncoder.encode(fileName + “.” + ExcelTypeEnum.XLSX.getValue(), “UTF-8”));
    workbook.write(response.getOutputStream());
    } catch (Exception e) {
    throw new IOException(e.getMessage());
    }
    }

  /**

  • 上传文件到本地服务器
  • @param destination 文件全路径
  • @param workbook excel数据
    */
    public static void uploadExcel(String destination, Workbook workbook)
    throws IOException {
    try {
    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    workbook.write(byteArrayOutputStream);
    ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
    writeToLocal(destination, byteArrayInputStream);
    byteArrayInputStream.close();
    byteArrayOutputStream.close();
    } catch (Exception e) {
    throw new IOException(e.getMessage());
    }
    }

  /**

  • 将InputStream写入本地文件
  • @param destination 本地文件全路径
  • @param input 输入流
  • @throws IOException IOException
    */
    public static void writeToLocal(String destination, InputStream input)
    throws IOException {
    int index;
    byte[] bytes = new byte[1024];
    FileOutputStream fileOutputStream = new FileOutputStream(destination);
    while ((index = input.read(bytes)) != -1) {
    fileOutputStream.write(bytes, 0, index);
    fileOutputStream.flush();
    }
    input.close();
    fileOutputStream.close();

  }

  /**

  • excel 导入
  • @param filePath excel文件路径
  • @param titleRows 标题行
  • @param headerRows 表头行
  • @param pojoClass pojo类型
  • @param
  • @return
    */
    public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass)
    throws IOException {
    if (StringUtils.isBlank(filePath)) {
    return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    params.setNeedSave(true);
    params.setSaveUrl(“/excel/”);
    try {
    return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    } catch (NoSuchElementException e) {
    throw new IOException(“模板不能为空”);
    } catch (Exception e) {
    throw new IOException(e.getMessage());
    }
    }

  /**

  • excel 导入
  • @param file excel文件
  • @param pojoClass pojo类型
  • @param
  • @return
    */
    public static List importExcel(MultipartFile file, Class pojoClass) throws IOException {
    return importExcel(file, 1, 1, pojoClass);
    }

  /**

  • excel 导入
  • @param file excel文件
  • @param titleRows 标题行
  • @param headerRows 表头行
  • @param pojoClass pojo类型
  • @param
  • @return
    */
    public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass)
    throws IOException {
    return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

  /**

  • excel 导入
  • @param file 上传的文件
  • @param titleRows 标题行
  • @param headerRows 表头行
  • @param needVerfiy 是否检验excel内容
  • @param pojoClass pojo类型
  • @param
  • @return
    */
    public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class pojoClass) throws IOException {
    if (file == null) {
    return null;
    }
    try {
    return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
    } catch (Exception e) {
    throw new IOException(e.getMessage());
    }
    }

  /**

  • excel 导入
  • @param inputStream 文件输入流
  • @param titleRows 标题行
  • @param headerRows 表头行
  • @param needVerify 是否检验excel内容
  • @param pojoClass pojo类型
  • @param
  • @return
    */
    public static List importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class pojoClass) throws IOException {
    if (inputStream == null) {
    return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    params.setSaveUrl(“/excel/”);
    params.setNeedSave(false);
    params.setNeedVerify(needVerify);
    try {
    return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
    } catch (NoSuchElementException e) {
    throw new IOException(“excel文件不能为空”);
    } catch (Exception e) {
    throw new IOException(e.getMessage());
    }
    }

  /**

  • 一对多模式子类行转列导出数据（指定子类某一项作为子类一级标题） 子项为数值类型带汇总
  • @param fatTitle 父类显示列
  • @param sonTitle 子类一级标题
  • @param sonTitleTow 子类二级标题
  • @param dataList 数据行 数据格式 a,b,c,List
  • @param sheetName 表名
  • @param sheetTitle 标题
  • @throws IOException
    */

  public static XSSFWorkbook exportExcel(
  Map<String,Object> fatTitle,
  List sonTitle,
  Map<String,Object> sonTitleTow,
  List<Map<String,Object>> dataList,
  String sheetName,
  String sheetTitle
  ) throws IOException {
  //创建工作薄对象
  XSSFWorkbook workbook = new XSSFWorkbook();
  //这里也可以设置sheet的Name
  //创建工作表对象
  XSSFSheet sheet = workbook.createSheet();
  //创建单元格空对象
  final XSSFCell[] cell = {null};
  //创建合并空对象
  CellRangeAddress region = null;
  //设置标题
  XSSFRow title = sheet.createRow(0);
  //设置第一行，从零开始
  //设置标题行高
  title.setHeightInPoints(40);
  //统计合并列(父类属性+子类显示属性*子类标题)
  int titleColumn = fatTitle.size()+sonTitle.size()sonTitleTow.size()-1;
  for (int i = 0; i <= titleColumn; i++) {
  cell[0] = title.createCell(i);
  cell[0].setCellValue(sheetTitle);
  cell[0].setCellStyle(SheetStyle.getStyle(workbook));
  }
  //合并单元格
  region = new CellRangeAddress(0, 0, 0, titleColumn);
  sheet.addMergedRegion(region);
  sheet.setColumnWidth(0,3000);
  //先创建父类标题
  XSSFRow row = sheet.createRow(1);
  //设置第二行
  XSSFRow row1 = sheet.createRow(2);
  row.setHeightInPoints(20);
  row1.setHeightInPoints(18);
  int i = 0; // 列数
  //循环添加父类标题
  for (String s : fatTitle.keySet()) {
  //绘制单元格
  cell[0] =row.createCell(i);
  //添加数据
  cell[0].setCellValue(fatTitle.get(s).toString());
  //添加样式
  cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
  //设置要合并的单元格样式
  cell[0] = row1.createCell(i);
  cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
  //合并 (4个参数，分别为起始行，结束行，起始列，结束列)
  region = new CellRangeAddress(1, 2, i, i++);
  sheet.addMergedRegion(region);
  sheet.setColumnWidth(i,3000);
  }
  //循环添加子类一级标题
  int i_1=i;
  for (String s : sonTitle){
  ///绘制单元格
  cell[0] =row.createCell(i_1);
  //添加数据
  cell[0].setCellValue(s);
  //添加样式
  cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
  //设置要合并的单元格样式
  cell[0] = row.createCell(i_1+sonTitleTow.size()-1);
  cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
  */
  for (int m = i_1; m < sonTitleTow.size()+i_1; m++) {
  cell[0] = row.createCell(m);
  cell[0].setCellValue(s);
  cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
  }

       //合并单元格
       region = new CellRangeAddress(1, 1,i_1, i_1+sonTitleTow.size()-1);
       sheet.addMergedRegion(region);
       //循环添加子类二级表头
       final int[] i_2 = {i_1};
       sonTitleTow.forEach((key,value)->{
           cell[0] = row1.createCell(i_2[0]);
           cell[0].setCellValue(sonTitleTow.get(key).toString());
           cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
           sheet.setColumnWidth(i_2[0],2500);
           i_2[0]++;
       });
       i_1 = i_1+sonTitleTow.size();
   }
   //循环添加数据
   for (int j = 0; j < dataList.size(); j++) {
       //创建单元格对象
       //设置数据第一行
       XSSFRow row2 = sheet.createRow(j+3);
       //设置行高
       row2.setHeightInPoints(18);
       //添加父类数据
       int dataCol = 0;
       for (String s : fatTitle.keySet()) {
           cell[0] = row2.createCell(dataCol);
           cell[0].setCellValue(dataList.get(j).get(s).toString());
           cell[0].setCellStyle(SheetStyle.getTextCellStyle(workbook));
           dataCol++;
       }
       //添加子类数据
       List<Map> lis = (List<Map>) dataList.get(j).get("porList");
       //根据二级子标题创建汇总集合
       Map<String,Object> sumMap = sonTitleTow;
       for (Map map : lis) {
           for (String s : sonTitle) {
               //查找和标题行对应的数据
               if(map.get(s)!=null){
                   for (String str : sonTitleTow.keySet()) {
                       cell[0] = row2.createCell(dataCol);
                       cell[0].setCellValue( Double.valueOf(map.get(str).toString()));
                       cell[0].setCellStyle(SheetStyle.getNumCellStyle(workbook));
                       try{
                           sumMap.put(str,Double.valueOf(sumMap.get(str).toString())+Double.valueOf(map.get(str).toString()));
                       }catch (NumberFormatException e){
                           sumMap.put(str,Double.valueOf(map.get(str).toString()));
                       }
                       dataCol++;
                   }
                   break;
               }
           }
       }
   }
   workbook.setSheetName(0, sheetName);
   return workbook;
  

  }

  /**

  • Excel 类型枚举
    */
    enum ExcelTypeEnum {
    XLS(“xls”), XLSX(“xlsx”);
    public String value;

    ExcelTypeEnum(String value) {
    this.value = value;
    }

    public String getValue() {
    return value;
    }

    public void setValue(String value) {
    this.value = value;
    }
    }

}

/**
 * 进场验收导出
 *
 * @return
 * @author lc 2023/06/12
 */
@ApiOperation(value = "进场验收导出", notes = "进场验收导出接口", produces = "application/octet-stream")
@GetMapping(value = "/EquipmentsRecExport")
@ApiOperationSupport(ignoreParameters = {"currPage", "pageSize"})
public ResponseBean EquipmentRecExport(EquiPageReq req, HttpServletResponse response) {
    try {
        List<ExcelEquipVo> list = iEquipmentService.getList(req);
        if (list.size() == 0) {
            return ResponseBean.success("数据源为空");
        } else if (list.size() > 5000) {
            return ResponseBean.fail("记录超过5000，请重新选择查询条件");
        } else {
            ExcelUtils.exportExcel(list, "进场验收", "进场验收sheet", ExcelEquipVo.class, "进场验收", response);
            return ResponseBean.success();
        }
    } catch (Exception e) {
        return ResponseBean.fail("进场验收导出异常");
    }
}

@ApiOperation(value = "进场验收导入")
@PostMapping("/importEquipments")
public ResponseBean EquipmentRecImport(@RequestParam("file") MultipartFile file) {
    try {
        List<ExcelEquipVo> list = ExcelUtils.importExcel(file, 1, 1, true, ExcelEquipVo.class);
        if (list == null) {
            return ResponseBean.fail("数据为空");
        }
        if (list.size() == 0) {
            return ResponseBean.fail("数据为空");
        }
        GenerateImportDto model = iEquipmentService.EquipmentRecImport(list);
        if (model != null) {
            return ResponseBean.success(model);
        } else {
            return ResponseBean.fail();
        }
    } catch (Exception e) {
        //logger.error("导入设备类型出错", e);
        return ResponseBean.fail("导入设备类型出错");
    }
}