用java实现一个简单的excel数据导入

package com.asto.atg.admin.util;

import com.asto.atg.admin.service.PartyGroupTypeService;
import com.asto.atg.admin.service.PartyVillagerInfoService;
import com.asto.atg.admin.service.SysCommunityService;
import com.asto.atg.common.ATGRException;
import com.asto.atg.common.entity.PartyGroupType;
import com.asto.atg.common.entity.PartyVillagerInfoEntity;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
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.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;


@Slf4j
@Component
public class partyVillagerInfoImportUtil {

    @Autowired
    private PartyVillagerInfoService partyVillagerInfoService;

    @Autowired
    private SysCommunityService sysCommunityService;

    @Autowired
    private PartyGroupTypeService partyGroupTypeService;

    /**
     * 村民信息导入
     *
     * @param file
     * @return
     * @throws Exception
     */
    @Transactional
    public Boolean partyVillagerInfoImport(MultipartFile file, String streetCode, String communityCode) throws Exception {
        InputStream fis = file.getInputStream();
        String fileName = file.getOriginalFilename();
        int len = fileName.length();
        String attribute = fileName.substring(len - 4, len);
        if (!attribute.equals("xlsx")) {
            throw ATGRException.commonRException("文件格式错误，请上传.xlsx格式的文件");
        }
        boolean flag = false;
        //excel文件
        XSSFWorkbook excel = new XSSFWorkbook(fis);
        XSSFSheet xssfSheet = excel.getSheetAt(0);
        int lastRowNum = xssfSheet.getLastRowNum();
        PartyVillagerInfoEntity partyVillagerInfo = new PartyVillagerInfoEntity();
        //循环获取每一行excel文件的单元格值，并存入
        for (int i = 1; i <= lastRowNum; i++) {
            XSSFRow row = xssfSheet.getRow(i);

            String excelStreetCode = getXlsxCellValue(xssfSheet, row, 1, i);

            String excelCommunityCode = getXlsxCellValue(xssfSheet, row, 3, i);

            String groupName = getXlsxCellValue(xssfSheet, row, 4, i);

            String familyGroupName = getXlsxCellValue(xssfSheet, row, 5, i);

            String name =  getXlsxCellValue(xssfSheet, row, 6, i);

            String mobile =  getXlsxCellValue(xssfSheet, row, 7, i);

            String idCard =  getXlsxCellValue(xssfSheet, row, 8, i);

            String responsible = getXlsxCellValue(xssfSheet, row, 9, i).equals("是") ? "1" : "0";

            String tag =  getXlsxCellValue( row, 10);

            String detailedAddress =  getXlsxCellValue( row, 11);

            String remarks =  getXlsxCellValue( row, 12);

                partyVillagerInfo.setStreetCode(excelStreetCode);
                partyVillagerInfo.setCommunityCode(excelCommunityCode);
                partyVillagerInfo.setGroupType(partyGroupType.getCode().toString());
                partyVillagerInfo.setFamilyGroup(partyGroupType.getId().toString());
                partyVillagerInfo.setIfResponsiblePeople(Integer.parseInt(responsible));
                partyVillagerInfo.setName(name);
                partyVillagerInfo.setMobile(mobile);
                partyVillagerInfo.setIdCard(idCard);
                partyVillagerInfo.setTag(tag);
                partyVillagerInfo.setDetailedAddress(detailedAddress);
                partyVillagerInfo.setRemarks(remarks);
                flag = partyVillagerInfoService.save(partyVillagerInfo);
            }
        return flag;
    }

    /**
     * 获取导入单元格的值，单元格对象不能为空
     * @param xssfSheet
     * @param row
     * @param index 第 i 行的第 index 个单元格
     * @param i 第 i 行
     * @return
     * @throws Exception
     */
    private String getXlsxCellValue(XSSFSheet xssfSheet, XSSFRow row, Integer index, Integer i) throws Exception {
        //获取第 0 行
        XSSFRow titleRow = xssfSheet.getRow(0);
        //获取第 0 行的第 index 个单元格的title，便于提示
        XSSFCell title = titleRow.getCell(index);
        title.setCellType(CellType.STRING);
        //获取值
        XSSFCell str = row.getCell(index);
        str.setCellType(CellType.STRING);
        String str1 = str.getStringCellValue().trim();
        if (stringutils.isEmpty(str1))){
            log.error(xssfSheet.getSheetName() + "第" + i + "行，" + title.getStringCellValue().trim() + "不能为空");
            throw ATGRException.commonRException("第" + i + "行，" + title.getStringCellValue().trim() + "不能为空");
        }

        return str1;
    }

    /**
     * 获取导入单元格的值，单元格对象可以为空
     * @param row
     * @param index
     * @return
     */
    private String getXlsxCellValue(XSSFRow row, Integer index){
        XSSFCell remarksStr = row.getCell(index);
        String remarks = "";
        if (remarksStr != null){
            remarks = row.getCell(index).getStringCellValue();
        }
        return remarks;
    }
}