复杂Excel，含有多个sheet(合并单元格，公式)导入数据库

最新推荐文章于 2024-09-02 19:14:04 发布

Adj_Seven

最新推荐文章于 2024-09-02 19:14:04 发布

阅读量220

点赞数 4

文章标签： excel

本文链接：https://blog.csdn.net/Adj_Seven/article/details/140771055

版权

excel需要导入的数据，需要导入第2、4、6个sheet

其中，存入时测点号需要与里程相对应

数据库设计

具体实现：

1. 导入依赖

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.2.3</version>
 </dependency>
 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>5.2.3</version>
 </dependency>
 <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>5.1.1</version>
 </dependency>

2. controller层

//合并单元格公式处理
@RestController
@RequestMapping("/importExcel")
public class ImportExcelController extends BaseController {
    @Autowired
    private IXiongshangdetailService xiongshangdetailService;
    @Autowired
    private IXiongshangService xiongshangService;
    @Autowired
    private IBridgeLedgerService bridgeLedgerService; // 注入 BridgeLedgerService

    @RequiresPermissions("reportForm:importExcel:importReportExcel")
    @PostMapping("/importReportExcel")
    public AjaxResult importExcel(@RequestParam("file") MultipartFile file,
                                  @RequestParam("value") String value,
                                  @RequestParam("value2") Date value2 ) throws IOException, InvalidFormatException, ParseException {
        if ("XS".equals(value)) {
            return importXiongshangExcel(file, value2);
        } else if ("JX".equals(value)) {
            return importJXExcel(file, value2);
        } else {
            return AjaxResult.error("Unsupported template type");
        }
    }

    // 雄商导入
    private AjaxResult importXiongshangExcel(@RequestParam("file") MultipartFile file,
                                             @RequestParam("value2") Date value2) throws IOException, InvalidFormatException {
        String fileName = file.getOriginalFilename(); // 获取文件名

        Xiongshang xiongshang = new Xiongshang();
        // 插入主表数据并获取生成的主键
        xiongshang.setXlsxId(String.valueOf(UUID.randomUUID())); // 在应用层生成UUID
        xiongshang.setName(fileName);
        xiongshang.setType("雄商");
        xiongshang.setCreateTime(value2);
        xiongshang.setCreateBy("admin");
        System.out.println("文件名: " + fileName); // 打印文件名
        xiongshangService.insertXiongshang(xiongshang);

        List<Xiongshangdetail> xiongshangdetailList = new ArrayList<Xiongshangdetail>();
        String xlsxId = xiongshang.getXlsxId();

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        // 指定第2、4、6个sheet
        int[] sheetIndexes = {1, 3, 5};
        DataFormatter dataFormatter = new DataFormatter();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formulaEvaluator.setIgnoreMissingWorkbooks(true);

        for (int sheetIndex : sheetIndexes) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            System.out.println("sheet:"+sheetIndex);
            int rowNumber = sheet.getPhysicalNumberOfRows();
            // 从第三行开始获取
            for (int i = 2; i < rowNumber; i += 4) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue; // 跳过空行
                }
                StringBuilder data = new StringBuilder();
                // 获取第一列数据
                Cell firstCell = row.getCell(0);
                Object firstCellValue = getMergedCellValue(sheet, row, firstCell);
                data.append(firstCellValue.toString()).append(" ");

                // 获取第11列及之后的数据
                int cellNumber = row.getLastCellNum();
                if(sheetIndex == 3) {
                    for (int j = 10; j < cellNumber; j++) {
                        String s = dataFormatter.formatCellValue(sheet.getRow(i).getCell(j), formulaEvaluator);
//                        System.out.println("第" + (i + 1) + "行，第" + (j + 1) + "列: " + s); // 调试输出每个单元格的数据
                        data.append(s.toString()).append(" ");
                    }
                }else{
                    for (int j = 10; j < cellNumber; j+=2) {
                        String s = dataFormatter.formatCellValue(sheet.getRow(i).getCell(j), formulaEvaluator);
//                        System.out.println("第" + (i + 1) + "行，第" + (j + 1) + "列: " + s); // 调试输出每个单元格的数据
                        data.append(s.toString()).append(" ");
                    }
                }
                // 拆分字符串分别赋值
                String[] words = data.toString().trim().split(" ");

                if (words.length >= 7) {
                    // 获取 Sheet 的名称
                    String sheetName = workbook.getSheetName(sheetIndex);
                    System.out.println(sheetName);
                    Xiongshangdetail xiongshangdetail = new Xiongshangdetail();
                    // 地区
                    xiongshangdetail.setArea(sheetName);
                    xiongshangdetail.setDetailId(xlsxId);
                    xiongshangdetail.setXsId(String.valueOf(UUID.randomUUID())); // 在应用层生成UUID

                    String pierId = words[0];
                    // 提取和格式化 pierId
                    String formattedPierId = extractPierId(pierId);
                    xiongshangdetail.setPierId(formattedPierId);
                    // 获取映射数据
                    Map<String, String> mappingData = bridgeLedgerService.getMappingData(formattedPierId);
                    xiongshangdetail.setShuipingHengSum(Double.valueOf(words[1]));
                    xiongshangdetail.setShuipingHengDaily(Double.valueOf(words[2]));
                    xiongshangdetail.setShuipingZongSum(Double.valueOf(words[3]));
                    xiongshangdetail.setShuipingZongDaily(Double.valueOf(words[4]));
                    xiongshangdetail.setShuxiangSum(Double.valueOf(words[5]));
                    xiongshangdetail.setShuxiangDaily(Double.valueOf(words[6]));

                    // 设置 mileage
//                    System.out.println(mappingData);
//                    System.out.println(mappingData.containsKey(formattedPierId));
                    if (mappingData.containsKey(formattedPierId)) {
                        xiongshangdetail.setMileage(mappingData.get(formattedPierId));
//                        System.out.println(mappingData.get(formattedPierId));
                    }

                    System.out.println(xiongshangdetail);
                    xiongshangdetailList.add(xiongshangdetail);
                }
            }
        }

        // 批量插入数据
        if (!xiongshangdetailList.isEmpty()) {
            int rows = xiongshangdetailList.size();
            xiongshang.setRecordCounts((long) rows);
            xiongshangService.updateXiongshang(xiongshang);
            xiongshangdetailService.insertReportByBatch(xiongshangdetailList);
        }
        workbook.close(); // 关闭 Workbook
        return AjaxResult.success();
    }

    //京雄导入
    private AjaxResult importJXExcel(MultipartFile file,
                                     @RequestParam("value2") Date value2) throws IOException, InvalidFormatException {
        String fileName = file.getOriginalFilename(); // 获取文件名

        Xiongshang xiongshang = new Xiongshang();
        // 插入主表数据并获取生成的主键
        xiongshang.setXlsxId(String.valueOf(UUID.randomUUID())); // 在应用层生成UUID
        xiongshang.setName(fileName);
        xiongshang.setType("京雄");
        xiongshang.setCreateBy("admin");
        xiongshang.setCreateTime(value2);
        System.out.println("文件名: " + fileName); // 打印文件名
        xiongshangService.insertXiongshang(xiongshang);

        List<Xiongshangdetail> xiongshangdetailList = new ArrayList<Xiongshangdetail>();
        String xlsxId = xiongshang.getXlsxId();

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        // 指定第2、4、6个sheet
        int[] sheetIndexes = {0, 2};
        DataFormatter dataFormatter = new DataFormatter();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formulaEvaluator.setIgnoreMissingWorkbooks(true);

        for (int sheetIndex : sheetIndexes) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            System.out.println("sheet:" + sheetIndex);
            int rowNumber = sheet.getPhysicalNumberOfRows();
            // 从第三行开始获取
            for (int i = 2; i < rowNumber; i += 4) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue; // 跳过空行
                }
                StringBuilder data = new StringBuilder();
                // 获取第一列数据
                Cell firstCell = row.getCell(0);
                Object firstCellValue = getMergedCellValue(sheet, row, firstCell);
                data.append(firstCellValue.toString()).append(" ");

                // 获取第11列及之后的数据
                int cellNumber = row.getLastCellNum();

                for (int j = 7; j < 19; j+=2) {
                    String x = dataFormatter.formatCellValue(sheet.getRow(i).getCell(j), formulaEvaluator);
                    double m=Double.parseDouble(x);
                    double w=customRound(m, 1);
                    String s= Double.toString(w);
                    System.out.println("第" + (i + 1) + "行，第" + (j + 1) + "列: " + s); // 调试输出每个单元格的数据
                    data.append(s).append(" ");
                }

                // 拆分字符串分别赋值
                String[] words = data.toString().trim().split(" ");

                if (words.length >= 7) {
                    // 获取 Sheet 的名称
                    String sheetName = workbook.getSheetName(sheetIndex);
                    System.out.println(sheetName);
                    Xiongshangdetail xiongshangdetail = new Xiongshangdetail();
                    // 地区
                    xiongshangdetail.setArea(sheetName);
                    xiongshangdetail.setDetailId(xlsxId);
                    xiongshangdetail.setXsId(String.valueOf(UUID.randomUUID())); // 在应用层生成UUID

                    String pierId = words[0];
                    // 提取和格式化 pierId
                    String formattedPierId = extractIntegerPart(pierId);
                    xiongshangdetail.setPierId(formattedPierId);
                    // 获取映射数据
                    Map<String, String> mappingData = bridgeLedgerService.getMappingDataJX(formattedPierId);
                    xiongshangdetail.setShuipingHengSum(Double.valueOf(words[1]));
                    xiongshangdetail.setShuipingHengDaily(Double.valueOf(words[2]));
                    xiongshangdetail.setShuipingZongSum(Double.valueOf(words[3]));
                    xiongshangdetail.setShuipingZongDaily(Double.valueOf(words[4]));
                    xiongshangdetail.setShuxiangSum(Double.valueOf(words[5]));
                    xiongshangdetail.setShuxiangDaily(Double.valueOf(words[6]));

                    // 设置 mileage
//                    System.out.println(mappingData);
//                    System.out.println(mappingData.containsKey(formattedPierId));
                    if (mappingData.containsKey(formattedPierId)) {
                        xiongshangdetail.setMileage(mappingData.get(formattedPierId));
//                        System.out.println(mappingData.get(formattedPierId));
                    }

                    System.out.println(xiongshangdetail);
                    xiongshangdetailList.add(xiongshangdetail);
                }
            }
        }


        // 批量插入数据
        if (!xiongshangdetailList.isEmpty()) {
            int rows = xiongshangdetailList.size();
            xiongshang.setRecordCounts((long) rows);
            xiongshangService.updateXiongshang(xiongshang);
            xiongshangdetailService.insertReportByBatch(xiongshangdetailList);
        }
        workbook.close(); // 关闭 Workbook
        return AjaxResult.success();
    }

    private Date parseDate(String dateString) throws ParseException {
        // 使用日期格式来解析输入的日期字符串
        SimpleDateFormat dateFormat = new SimpleDateFormat("EEE MMM dd yyyy HH:mm:ss 'GMT'Z (中国标准时间)", Locale.ENGLISH);
        return dateFormat.parse(dateString);
    }

    private String formatDate(Date date) {
        // 将日期格式化为 yyyy-MM-dd
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        return dateFormat.format(date);
    }
    // 从 pierId 中提取格式化的字符串
    private String extractPierId(String pierId) {
        if (pierId.length() < 5) {
            return ""; // 确保 pierId 长度足够
        }
        // 提取 pierId 的倒数第三到最后两位
        return pierId.substring(pierId.length() - 4, pierId.length() - 2);
    }
    private String extractIntegerPart(String pierId) {
        // 将字符串转换为浮点数
        double numericValue = Double.parseDouble(pierId);
        // 获取整数部分
        int integerValue = (int) numericValue;
        // 将整数部分转换回字符串
        return String.valueOf(integerValue);
    }

    private Object getMergedCellValue(Sheet sheet, Row row, Cell cell) {
        if (cell == null) {
            return "";
        }
        // 检查单元格是否属于合并单元格
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(row.getRowNum(), cell.getColumnIndex())) {
                Row firstRow = sheet.getRow(range.getFirstRow());
                Cell firstCell = firstRow.getCell(range.getFirstColumn());
                return getCellValue(firstCell);
            }
        }
        return getCellValue(cell);
    }

    private Object getCellValue(Cell cell) {
        DataFormatter formatter = new DataFormatter();
        switch (cell.getCellType()) {
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case BLANK:
                return "";
            case ERROR:
                return cell.getErrorCellValue();
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                return cell.getStringCellValue();
            case FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                CellValue cellValue = evaluator.evaluate(cell);
                switch (cellValue.getCellType()) {
                    case BOOLEAN:
                        return cellValue.getBooleanValue();
                    case NUMERIC:
                        return cellValue.getNumberValue();
                    case STRING:
                        return cellValue.getStringValue();
                    default:
                        return "";
                }
            default:
                return "";
        }
    }
}

3. 前端

<el-col :span="1.5">
        <span class="font_1">选择日期</span>
        <el-date-picker
          v-model="value2"
          align="right"
          type="date"
          format="yyyy-MM-dd"
          placeholder="选择日期"
          :picker-options="pickerOptions"
        >
        </el-date-picker>
      </el-col>
<el-col :span="1.5">
        <span class="font_1">选择模板</span>
        <el-select v-model="value" placeholder="请选择">
          <el-option
            v-for="item in options"
            :key="item.value"
            :label="item.label"
            :value="item.value"
          >
          </el-option>
        </el-select>
      </el-col>
      <el-col :span="1.5">
        <el-upload
          action="/dev-api/reportForm/importExcel/importReportExcel"
          :headers="{ Authorization: 'Bearer ' + getTokens() }"
          :before-upload="beforeUpload"
          :data="uploadData"
          :auto-upload="true"
          :show-file-list="true"
          accept=".xls, .xlsx"
          ref="upload"
          :multiple="false"
        >
          <el-button type="primary" icon="el-icon-folder-add" size="medium"
          >导入</el-button
          >
        </el-upload>
      </el-col>

参数和方法

export default {
  name: "DataPreprocessing",
  data() {
    return {
      options: [
        { value: 'XS', label: '雄商' },
        { value: 'JX', label: '京雄' }
      ],
      value: "",
      pickerOptions: {
        disabledDate(time) {
          return time.getTime() > Date.now();
        },
        shortcuts: [
          {
            text: "今天",
            onClick(picker) {
              picker.$emit("pick", new Date());
            },
          },
          {
            text: "昨天",
            onClick(picker) {
              const date = new Date();
              date.setTime(date.getTime() - 3600 * 1000 * 24);
              picker.$emit("pick", date);
            },
          },
          {
            text: "一周前",
            onClick(picker) {
              const date = new Date();
              date.setTime(date.getTime() - 3600 * 1000 * 24 * 7);
              picker.$emit("pick", date);
            },
          },
        ],
      },
      value2: "",