[Java知识库]Java实现excel 2003、2007导入功能

Java实现excel2003、2007导入功能完整版demo, 内置sql文件: https://gitee.com/PanGuanQing/excel-import-demo.git?

1.要导入的excel数据

2.导入依赖

<!--excel 2003-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.8</version>
</dependency>
<!--excel 2007-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.8</version>
</dependency>

3.UserController

import com.poi.exceldeal.response.ResponseResult;
import com.poi.exceldeal.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class UserController {

    @Autowired
    private IUserService iUserService;

    /**
     * 导入excel文件
     * @param file
     * @param type 0增量更新  1全量更新
     * @return
     */
    @ResponseBody
    @PostMapping(value = "/doExcel")
    public ResponseResult<String> doExcel(@RequestParam(value="file") MultipartFile file, int type){
        return iUserService.readExcelFile(file,type);
    }
}

?4.UserServiceImpl

import com.poi.exceldeal.domain.User;
import com.poi.exceldeal.exception.MyException;
import com.poi.exceldeal.mapper.UserMapper;
import com.poi.exceldeal.response.ResponseResult;
import com.poi.exceldeal.service.IUserService;
import com.poi.exceldeal.util.ExcelUtil;
import com.poi.exceldeal.util.SpringUtil;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

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

@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public ResponseResult<String> readExcelFile(MultipartFile file, int type) {

        //解析excel，获取上传的事件单
        Map<String, List> map = null;
        try {
            Workbook web = SpringUtil.getBean(ExcelUtil.class).getExcelInfo(file);
            map = readExcelValue(web);
            //已将excel中的数据转换到list,操作list,保存到数据库;
            List<User> users = map.get("excel");
            for (int i = 0; i < users.size(); i++) {
                User user = users.get(i);
                User existUser = userMapper.selectByPhoneNumber(user.getPhoneNumber());
                //1、增量更新是指在进行导入操作时，已有的解析记录保持不变，然后添加新增的解析记录。
                if (type==0) {
                    if (existUser == null) {
                        userMapper.add(user);
                    }
                    //2、全量更新是指在进行导入操作时，删除已有的所有解析记录，然后添加文件中的解析记录。
                } else if (type==1) {
                    if (existUser == null) {
                        userMapper.add(user);
                    } else {
                        userMapper.delete(user.getId());
                        userMapper.add(user);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("接受excel表格中的数据失败！！！");
        }
        return ResponseResult.createBySuccessMessage("操作成功");
    }

    /**
     * 读取Excel里面的信息
     * @param wb
     * @return
     */
    private Map<String, List> readExcelValue(Workbook wb) throws ParseException {
        // 获取第一个shell
        Sheet sheet = wb.getSheetAt(0);
        ExcelUtil.totalRows = sheet.getLastRowNum();
        // 获取Excel的列数(前提是有行数)
        if (ExcelUtil.totalRows > 1 && sheet.getRow(0) != null) {
            ExcelUtil.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        Map<String,List> map = new HashMap<String,List>();
        List<User> users = new ArrayList<>();
        // 循环Excel行数
        for (int r = 1; r < ExcelUtil.totalRows+1; r++) {//从第二行开始写,标题在第一行
            boolean str = true;
            Row row = sheet.getRow(r);
            if (row==null) {
                continue;
            }
            User user = new User();
            // 循环Excel的列
            for (int c = 0; c < ExcelUtil.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (cell!=null) {
                    if (c==0) {//r行开始的第一个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING); //CellType.STRING
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String serialNumber = firstName1.replaceAll(" +","");
                        user.setSerialNumber(serialNumber);
                    }else if (c==1) {//r行开始的第二个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String phoneNumber = firstName1.replaceAll(" +","");
                        user.setPhoneNumber(phoneNumber);
                    }else if (c==2) {//r行开始的第三个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String organizationName = firstName1.replaceAll(" +","");
                        user.setOrganizationName(organizationName);
                    }else if (c==3) {//r行开始的第四个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String publicStartTime = firstName1.replaceAll(" +","");
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        user.setPublicStartTime(sdf.parse(publicStartTime));
                    }else if (c==4) {//r行开始的第五个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String publicEndTime = firstName1.replaceAll(" +","");
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        user.setPublicEndTime(sdf.parse(publicEndTime));
                    }else if (c==5) {//r行开始的第六个单元格
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String firstName1 = cell.getStringCellValue();
                        if (firstName1 == null || firstName1 == ""){
                            str = false;
                            continue;
                        };
                        String publicCount = firstName1.replaceAll(" +","");
                        user.setPublicCount(Integer.valueOf(publicCount));
                    }
                }
            }
            // 添加到list
            if(str == true) {
                users.add(user);
                map.put("excel",users);
            }
        }
        return map;
    }
}

5.ExcelUtil

import com.poi.exceldeal.exception.MyException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

@Component
public class ExcelUtil {

    //总行数
    public static int totalRows = 0;
    //总条数
    public static int totalCells = 0;

    /**
     * 读EXCEL文件，获取信息集合
     * @param mFile
     * @return
     */
    public Workbook getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        Workbook wb = null;
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                throw new MyException("文件名不是excel格式");
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            wb = createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            return false;
        }
        return true;
    }

    //返回true是2003 excel
    public boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    //返回true是2007 excel
    public boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 根据excel里面的内容
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    private Workbook createExcel(InputStream is, boolean isExcel2003) {
        Workbook wb = null;
        try{
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

}

6.导入成功