EasyExcel 导入、导出Excel数据

一、导入数据

1、引入相关依赖

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
 
    <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
    </dependency>
 
    <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
    </dependency>
 
    <dependency>
          <groupId>commons-fileupload</groupId>
          <artifactId>commons-fileupload</artifactId>
    </dependency>
</dependencies>

2、编写Excel读取工具类

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Yann
 * @time 2022.10.31
 */
public class ExcelUtil {
    private Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public ExcelUtil(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }


    public ExcelUtil(MultipartFile file) {
        if(file==null){
            return;
        }
        try {
            String ext = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
            InputStream is = file.getInputStream();
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    /**
     * 读取Excel表格表头的内容
     * @return String 表头内容的数组
     */
    public String[] readExcelTitle() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            // title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = row.getCell(i).getCellFormula();
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     * @return Map 包含单元格数据内容的Map对象
     */
    public Map<Integer, Map<Integer,Object>> readExcelContent() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();

        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     * 读取Excel数据内容
     * @return Map 包含单元格数据内容的Map对象
     */
    public Map<Integer, Map<Integer,Object>> readExcelContentBySheet(int sheetNum) throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();

        sheet = wb.getSheetAt(sheetNum);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     * 根据Cell类型设置数据
     * @param cell
     * @return
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
//            switch (cell.getCellType()) {
//                case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
//                case Cell.CELL_TYPE_FORMULA: {
//                    // 判断当前的cell是否为Date
//                    if (DateUtil.isCellDateFormatted(cell)) {
//                        // 如果是Date类型则，转化为Data格式
//                        // data格式是带时分秒的：2013-7-10 0:00:00
//                        // cellvalue = cell.getDateCellValue().toLocaleString();
//                        // data格式是不带带时分秒的：2013-7-10
//                        Date date = cell.getDateCellValue();
//                        cellvalue = date;
//                    } else {// 如果是纯数字
//
//                        // 取得当前Cell的数值
//                        cellvalue = String.valueOf(cell.getNumericCellValue());
//                    }
//                    break;
//                }
//                case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
//                    // 取得当前的Cell字符串
//                    cellvalue = cell.getRichStringCellValue().getString();
//                    break;
//                default:// 默认的Cell值
//                    cellvalue = "";
//            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cellvalue = cell.getRichStringCellValue().getString();
        } else {
            cellvalue = "";
        }

        return cellvalue;
    }

    public static void main(String[] args) {
        try {
            String filepath = "D:/a/aa.xlsx";
            ExcelUtil excelReader = new ExcelUtil(filepath);
            // 对读取Excel表格标题测试
//          String[] title = excelReader.readExcelTitle();
//          System.out.println("获得Excel表格的标题:");
//          for (String s : title) {
//              System.out.print(s + " ");
//          }

            // 对读取Excel表格内容测试
            Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {
                System.out.println(map.get(i));
            }
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3、编写读取Excel数据的业务操作类

        /**
     * 导入产品信息
     * @param excelReader
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public String importProduct(MultipartFile file) throws Exception {
        ExcelUtil excelReader = new ExcelUtil(file);
        // 对读取Excel表格内容
        Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContentBySheet(0); //获取第一个Sheet
        Map<Integer,Object> objectMap;
        String productCode = "";
        for (int i = 1; i <= map.size(); i++) {
            System.out.println(map.get(i));
            objectMap = map.get(i);
            productCode = String.valueOf(objectMap.get(0));// 读取第一列数据
            // ......读取其它列数据，然后写入数据库
             }
}

4、编写导入Excel数据控制器

     /**
     * 导入产品信息，并生成欠缺的产品系列
     * @param
     * @return
     */
    //@Log(title = "导入产品信息，并生成欠缺的产品系列", action = "导入产品信息，并生成欠缺的产品系列")
    @ApiOperation(value = "导入产品信息，并生成欠缺的产品系列", notes = "导入产品信息，并生成欠缺的产品系列")
    @PostMapping("/importProduct")
    @RequiresPermissions("gen:baseProduct:importProduct")
    @ResponseBody
    public ResponseVO importProduct(@RequestPart("file") MultipartFile file){
        ResponseVO responseVO = new ResponseVO();
        try {

            String result = baseProductService.importProduct(file);
            responseVO.setSuccessMsg(result);
        } catch (Exception ex) {
            responseVO.setErrMsg("导入产品信息失败：" + ex.getMessage());
            log.error("导入产品信息失败：", ex);
        }
        return responseVO;
    }

二、导出数据

1、编写导出Excel模板实体类

/**
 * @author Yann
 * @time 2022.10.31
 */
@Data
@ApiModel("测试表")
@TableName("TEST_TABLE")
public class ProductInfo {

    @TableId(type = IdType.AUTO)
    @ApiModelProperty("ID")
    @ExcelIgnore //导出Excel数据忽略改字段
    private String id;

    @TableField("PRODUCT_NAME")
    @ApiModelProperty("产品名称")
    @ExcelProperty(value = "产品名称")
    @ColumnWidth(25) //列宽
    private String productName;
    //......
}

2、导出Excel 接口

     /**
     * 导出产品信息 Excel
     * @param response
     * @param productInfoVO
     * @throws Exception
     */
    @PostMapping("/exportDayProductReport")
    public void exportDayProductReport(HttpServletResponse response, @RequestBody ProductInfo productInfoVO){
        ExcelWriter excelWriter = null;
        try {
            excelWriter = getExcelWriter(response);
            WriteSheet sheet0 = EasyExcel.writerSheet(0, "产品表")
                    .head(ProductInfo.class)
                    .registerWriteHandler(new FreezeAndFilterHandler()).build();
             // 查询产品信息的方法，直接调用数据库查询，将数据返回就可以了
            List<ProductInfo> sheet1Data = productionInfoService.findProductInfo(productInfoVO);
            excelWriter.write(sheet1Data, sheet0);
        } catch (Exception e) {
            log.error("导出产品信息出错：", e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }