java中批量操作 java批量操作excel

java中Excel数据批量导入

相关依赖

<dependency>
    <groupId>org.jeecg</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>2.4.0</version>
</dependency>

大致思路：

1、判断获取的文件后缀 是xls还是xlsx

2、声明WorkBook，获取Sheet

3、通过sheet 获取row，注意观察，是不是默认从0开始计数

4、获取每一个cell，判断其类型，给每一种类型赋值

5、调用mapper层，进行批量操作

相关代码示例：

1、实体类

package com.xw.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelBatchImportData implements Serializable {

    /**
     * 手机号
     */
    private String preferentialPhone;
    /**
     * 车牌号
     */
    private String preferentialCarNumber;

    /**
     * 优惠分组的id
     */
    private String groupingId;

    /**
     * 创建时间
     */
    private String createTime;

    /**
     * 修改时间
     */
    private String modifyTime;

}

2、controller

package com.xw.controller;

import com.xw.service.GroupConfigService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.util.HashMap;
import java.util.Map;

@Controller
@RequestMapping("/rs")
public class TestController {
    /**
     * GroupConfigService groupConfigService
     */
    @Autowired
    private GroupConfigService groupConfigService;

    /**
     * logger
     */
    private Logger logger = LoggerFactory.getLogger(TestController.class);


    /**
     * Excel导入
     * @param file 文件对象
     * @return Map<String, Object>
     */
    @RequestMapping(value = "/importExcel",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file){
        Map<String, Object> map = new HashMap<>();
        try {
            map = groupConfigService.importExcel(file);
        }catch (Exception e){
            map.put("status",-1);
            map.put("data", "导入异常");
            logger.error(e.getMessage(),e);
        }

        return map;
    }
}

3、service

package com.xw.service;

import com.xw.mapper.GroupConfigMapper;
import com.xw.model.ExcelBatchImportData;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class GroupConfigService {

    /**
     * 定义全局的常量值
     */
    private static final String XLS = "xls";
    /**
     * 定义全局的常量值
     */
    private static final String XLSX = "xlsx";

    /**
     * GroupConfigDao groupConfigDao
     */
    @Autowired
    private GroupConfigMapper groupConfigMapper;

    /**
     * logger
     */
    private Logger logger = LoggerFactory.getLogger(GroupConfigService.class);

    /**
     * Excel导入
     *
     * @param file 文件对象
     * @return Map<String, Object>
     */
    public Map<String, Object> importExcel(MultipartFile file) {
        List<ExcelBatchImportData> tblFixChangeList = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();

        Workbook workbook = null;

        String filename = file.getOriginalFilename();
        try {
            if (filename != null) {
                if (filename.endsWith(XLS)) {
                    // 2003
                    workbook = new HSSFWorkbook(file.getInputStream());
                } else if (filename.endsWith(XLSX)) {
                    // 2007
                    workbook = new XSSFWorkbook(file.getInputStream());
                } else {
                    throw new Exception("文件不是Excel文件");
                }
            } else {
                logger.error("文件为空");
            }
            // 获取sheet
            Sheet sheet = workbook.getSheet("Sheet1");

            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum == 0) {
                throw new Exception("请填写行数");
            }

            for (int i = 1; i < lastRowNum + 1; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    // 读取cell单元格内容
                    ExcelBatchImportData tblFixChange = new ExcelBatchImportData();
                    // 手机号
                    String phone = getCellValue(row.getCell(0));
                    tblFixChange.setPreferentialPhone(phone);
                    // 车票号
                    String carNumber = getCellValue(row.getCell(1));
                    tblFixChange.setPreferentialCarNumber(carNumber);

                    String groupId = getCellValue(row.getCell(2));
                    tblFixChange.setGroupingId(groupId);
                    // 日期格式化操作
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = sdf.format(new Date());
                    tblFixChange.setCreateTime(time);
                    tblFixChange.setModifyTime(time);

                    tblFixChangeList.add(tblFixChange);
                }
            }
            // 批量插入
            groupConfigMapper.addBatchMembers(tblFixChangeList);
            map.put("status", 1);
            map.put("data", "导入数据成功");
        } catch (Exception e) {
            map.put("status", -1);
            map.put("data", "导入数据异常");
            logger.error(e.getMessage(), e);
        }
        return map;
    }

    /**
     * 获取每个单元格内容
     *
     * @param cell cell
     * @return String
     */
    private String getCellValue(Cell cell) {
        // 单元格内容
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                // 数字
                case HSSFCell
                        .CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    // 判断cell是不是日期类型的
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 获取日期类型的单元格
                        Date date = cell.getDateCellValue();
                        if (null != date) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                // 字符串
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                // Boolean
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                // 公式
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                // 空格
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                // 错误
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }
}

4、mapper

package com.xw.mapper;

import com.xw.model.ExcelBatchImportData;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface GroupConfigMapper {

    /**
     * 批量插入数据
     * @param excelBatchImportData tblFixChangeList
     */
    void addBatchMembers(@Param("excelBatchImportData") List<ExcelBatchImportData> excelBatchImportData);
}

5、mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xw.mapper.GroupConfigMapper">


    <insert id="addBatchMembers">
        insert into group_config(preferential_phone, preferential_carNumber, grouping_id, create_time, modify_time)
        values
        <foreach collection="excelBatchImportData" item="item" separator=",">
            (
                #{item.preferentialPhone},
                #{item.preferentialCarNumber},
                #{item.groupingId},
                #{item.createTime},
                #{item.modifyTime},
            )
        </foreach>
    </insert>
    
</mapper>