将Excel文件转换成ArrayList数组工具类

 1.工具类

import javax.servlet.http.Part;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelToPayRollConverter {

    public static List<PayRoll> convertExcelToPayRoll(Part part) throws IOException {
        List<PayRoll> payRolls = new ArrayList<>();

        try (InputStream fis = part.getInputStream();
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过标题行
                boolean isEmptyRow = true; // 假设当前行为空行
                for (Cell cell : row) {
                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        isEmptyRow = false; // 发现非空单元格，当前行不是空行
                        break;
                    }
                }
                if (isEmptyRow) continue; // 跳过空行
                PayRoll payRoll = new PayRoll();
                Cell nameCell = row.getCell(0);
                Cell passWaitAmountCell = row.getCell(1);
                Cell amountDue = row.getCell(2);
                Cell actualAmount=row.getCell(3);
                Cell cellValueAsDouble =row.getCell(4);
                Cell nowWaitAmount =row.getCell(5);
                Cell date = row.getCell(6);
                Cell state = row.getCell(7);
                payRoll.setName(getCellValueAsString(nameCell));
                payRoll.setPassWaitAmount(getCellValueAsDouble(passWaitAmountCell));
                payRoll.setAmountDue(getCellValueAsDouble(amountDue));
                payRoll.setActualAmount(getCellValueAsDouble(actualAmount));
                payRoll.setWaitAmount(getCellValueAsDouble(cellValueAsDouble));
                payRoll.setNowWaitAmount(getCellValueAsDouble(nowWaitAmount));
                payRoll.setDate(getCellValueAsString(date));
                payRoll.setState(getCellValueAsString(state));
                // ... 设置其他字段的值
                payRolls.add(payRoll);
            }
        }

        return payRolls;
    }

    private static String getCellValueAsString(Cell cell) {
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell);
    }

    private static double getCellValueAsDouble(Cell cell) {
        if (cell == null) {
            return 0; // 或者抛出异常
        }

        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果需要日期，可以返回 Date 对象或将其转换为字符串
                    return 0; // 这里返回 0 只是为了示例，实际中应该处理为日期
                }
                return cell.getNumericCellValue();
            case STRING:
                try {
                    return Double.parseDouble(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    // 处理无法解析为数字的情况
                    return 0; // 或者抛出异常
                }
            default:
                return 0; // 或者考虑抛出异常
        }
    }


}

 2.实体类（注：PayRoll是excel传过来的文件格式）

@Data
@TableName("n_pay_roll")
@SqlTable(name = "n_pay_roll", describe = "工资表（上传的Excel）")
public class PayRoll {
    private String name;
    private double passWaitAmount;
    private double amountDue;
    private double actualAmount;
    private double waitAmount;
    private double nowWaitAmount;
    private String date;
    private String state;
}

3.实现类

@PostMapping("addBatchByExcel")
    @AutoLog(logType = LogType.Create)
    public Result addBatchByExcel(HttpServletRequest req) throws IOException, ServletException {
        Part file = req.getPart("file");
        List<PayRoll> payRolls=null;
             payRolls = ExcelToPayRollConverter.convertExcelToPayRoll(file);
             //将所有的姓名转换成工人的ID 重新存放
            for (PayRoll payRoll : payRolls) {
                ConstructCrew crewName = constructCrewService.getOne(new QueryWrapper<ConstructCrew>().eq("crew_name", payRoll.getName()));
                if (crewName!=null){
                    payRoll.setName(crewName.getId());
                }else {
                    return Result.fail("解析Excel文件错误，请在人员信息中添加工人："+payRoll.getName()+"后再进行导入");
                }
            }
        return Result.success(payRolls);
    }

4.前端发送请求的方法，这里用的ant-design上传文件的方法改的(注：必须要加headers:{credentials:same-origin'} 不然会报跨域）

async handleUpload(setDetailData) {
      const { fileList } = this // 假设这是从某个状态或props中获取的文件列表
      if (fileList.length > 0) {
        this.uploading = true
        const file = fileList[0] // 假设我们只处理列表中的第一个文件
        const formData = new FormData()
        formData.append('file', file) // 使用'file'作为键名
        const resb = await http({
          url: '/administration-service/PersonSalary/addBatchByExcel',
          method: 'POST',
          data: formData,
          headers: {
            credentials: 'same-origin',
          },
        })
        if(resb.data!=null){
          this.updateDetail(resb.data, setDetailData)
          this.uploading = false
        }else{
          alert(resb.message)
        }
        
      }
    },

5.需要引入的依赖 

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>