Java实现DBF及Excel文件的导入解析

Java实现DBF与Excel文件的导入解析

DBF工具类

package com.test.server.utils;

import com.linuxense.javadbf.DBFReader;
import lombok.extern.slf4j.Slf4j;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: dbf相关工具类
 */
@Slf4j
public class DBFUtils {

    public static List<Map<String, Object>> readDbf(byte[] fileBytes) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try {
            // 读取dbf文件，处理中文乱码问题
            DBFReader reader = new DBFReader(new ByteArrayInputStream(fileBytes), StandardCharsets.ISO_8859_1);
            resultList = processDbfStream(reader);
        } catch (Exception e) {
            log.error("读取dbf文件出现异常", e);
        }
        return resultList;
    }

    public static List<Map<String, Object>> readDbf(String filePath) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try {
            // 读取指定路径的dbf文件
            DBFReader reader = new DBFReader(new FileInputStream(filePath));
            resultList = processDbfStream(reader);
        } catch (Exception e) {
            log.error("读取dbf文件失败", e);
        }
        return resultList;
    }

    private static List<Map<String, Object>> processDbfStream(DBFReader reader) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            int fieldCount = reader.getFieldCount();
            int recordCount = reader.getRecordCount();
            for (int i = 0; i < recordCount; i++) {
                Object[] recordData = reader.nextRecord();
                Map<String, Object> dataMap = new HashMap<>();
                for (int j = 0; j < fieldCount; j++) {
                    String fieldName = reader.getField(j).getName();
                    Object fieldValue = recordData[j];
                    dataMap.put(fieldName, fieldValue);
                }
                list.add(dataMap);
            }
        } catch (Exception e) {
            log.error("读取dbf文件时发生错误", e);
        }
        return list;
    }
}

Excel工具类

package com.test.server.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelTool<T> {
    private HSSFWorkbook workbook; // excel对象
    private String sheetTitle; // 表格标题
    private int columnWidth = 20; // 单元格宽度
    private int rowHeight = 20; // 单元格行高度
    private HSSFCellStyle headerStyle; // 表头样式
    private HSSFCellStyle bodyStyle; // 主体样式
    private SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化，默认格式

    /**
     * 无参数构造，初始化对象
     */
    public ExcelTool() {
        this.sheetTitle = "sheet1";
        this.workbook = new HSSFWorkbook();
        initializeStyles(0);
    }

    /**
     * 带参数构造，初始化对象
     * @param title 表格标题
     * @param colWidth 单元格宽度
     * @param rowHeight 单元格行高度
     * @param dateFormat 日期格式
     */
    public ExcelTool(String title, int colWidth, int rowHeight, String dateFormat) {
        this.columnWidth = colWidth;
        this.rowHeight = rowHeight;
        this.sheetTitle = title;
        this.workbook = new HSSFWorkbook();
        this.dateFormatter = new SimpleDateFormat(dateFormat);
        initializeStyles(0);
    }

    public ExcelTool(String title, int colWidth, int rowHeight) {
        this.columnWidth = colWidth;
        this.rowHeight = rowHeight;
        this.sheetTitle = title;
        this.workbook = new HSSFWorkbook();
        initializeStyles(0);
    }

    public ExcelTool(String title, int colWidth, int rowHeight, int flag) {
        this.columnWidth = colWidth;
        this.rowHeight = rowHeight;
        this.sheetTitle = title;
        this.workbook = new HSSFWorkbook();
        initializeStyles(flag);
    }

    public ExcelTool(String title) {
        this.sheetTitle = title;
        this.workbook = new HSSFWorkbook();
        initializeStyles(0);
    }

    // ExcelTool属性的get、set方法省略...

    private void initializeStyles(int styleFlag) {
        this.headerStyle = this.workbook.createCellStyle();
        this.headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        this.headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        this.headerStyle.setRightBorderColor(HSSFColor.BLACK.index);
        this.headerStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        if (styleFlag == 1) {
            this.bodyStyle = this.workbook.createCellStyle();
            this.bodyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
            this.bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            this.bodyStyle.setRightBorderColor(HSSFColor.BLACK.index);
            this.bodyStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            this.bodyStyle.setBorderRight((short) 1); // 右边框
            this.bodyStyle.setBorderBottom((short) 1); // 下边框
        } else {
            this.bodyStyle = this.workbook.createCellStyle();
            this.bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            this.bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            this.bodyStyle.setRightBorderColor(HSSFColor.BLACK.index);
            this.bodyStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            this.bodyStyle.setBorderRight((short) 1); // 右边框
            this.bodyStyle.setBorderBottom((short) 1); // 下边框
        }
    }

    // 导出Excel相关方法省略...

    // 解析Excel相关方法省略...

    /**
     * 根据HSSFCell类型获取数据值
     * @param cell 单元格对象
     * @return 单元格内容字符串
     */
    public static String getCellFormatValue(Cell cell) {
        String cellContent = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellContent = sdf.format(date);
                    } else {
                        cellContent = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING:
                    cellContent = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellContent = "";
            }
        }
        return cellContent;
    }

    /**
     * 根据文件后缀获取对应的Workbook类型
     * @param inputStream 文件输入流
     * @param fileName 文件名
     * @return Workbook对象
     * @throws Exception 导入格式错误异常
     */
    public static Workbook getWorkbookType(InputStream inputStream, String fileName) throws Exception {
        Workbook workbook = null;
        String fileExtension = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileExtension)) {
            workbook = new HSSFWorkbook(inputStream); // 2003及以下版本
        } else if (".xlsx".equals(fileExtension)) {
            workbook = new XSSFWorkbook(inputStream); // 2007及以上版本
        } else {
            throw new Exception("导入文件格式不正确");
        }
        return workbook;
    }

    // 其他解析Excel相关方法省略...
}

资源接口

@RequestMapping(value = "/importDbf", method = RequestMethod.POST)
Result<?> importDbf(@ApiParam(value = "dbf文件", required = true) @RequestBody MultipartFile file,
                    @RequestParam(value = "genNo") String genNo,
                    @RequestParam(value = "importType") String importType);

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
Result<?> importExcel(@ApiParam(value = "excel文件", required = true) @RequestBody MultipartFile file,
                      @RequestParam(value = "genNo") String genNo,
                      @RequestParam(value = "importType") String importType);

控制器类

@Autowired
private NewStudentImportSettingService newStudentImportSettingService;

@Autowired
private HttpServletRequest request;

@Override
public Result<?> importDbf(MultipartFile file, @RequestParam(value = "genNo") String genNo, @RequestParam(value = "importType") String importType) {
    if (file == null) {
        throw new ServiceException(SimpleErrorCode.ParamsError);
    }
    List<Map<String, Object>> dataList = new ArrayList<>();
    try {
        dataList = DBFUtils.readDbf(file.getBytes());
    } catch (Exception e) {
        log.error("读取文件失败", e);
        throw new ServiceException(2, "读取文件失败");
    }
    if (CollectionUtils.isEmpty(dataList)) {
        throw new ServiceException(2, "文件内无有效数据");
    }
    ImportMessageVo resultVo = newStudentImportSettingService.importNewStuData(dataList, genNo, importType);
    StringBuilder resultInfo = new StringBuilder();
    String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
    resultInfo.append(message).append("\r\n").append(resultVo.getImportFailMessages());
    return Result.buildSuccessResult(resultInfo);
}

@Override
public Result<?> importExcel(MultipartFile file, @RequestParam(value = "genNo") String genNo, @RequestParam(value = "importType") String importType) {
    if (file == null) {
        throw new ServiceException(SimpleErrorCode.ParamsError);
    }
    List<Map<String, Object>> dataList = new ArrayList<>();
    try {
        dataList = ExcelTool.getExcelMapVal(file.getInputStream(), 1);
    } catch (Exception e) {
        log.error("读取文件失败", e);
        throw new ServiceException(2, "读取文件失败");
    }
    if (CollectionUtils.isEmpty(dataList)) {
        throw new ServiceException(2, "文件内无有效数据");
    }
    ImportMessageVo resultVo = newStudentImportSettingService.importNewStuData(dataList, genNo, importType);
    StringBuilder resultInfo = new StringBuilder();
    String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
    resultInfo.append(message).append("\r\n").append(resultVo.getImportFailMessages());
    return Result.buildSuccessResult(resultInfo);
}

服务接口与实现类

“`java
// 服务接口
ImportMessageVo importNewStuData(List> data, String genNo, String importType);

// 服务实现类
@Override
public ImportMessageVo importNewStuData(List> data, String genNo, String importType) {
NewStudentImportSettingDto searchDto = new NewStudentImportSettingDto();
searchDto.setImportType(importType);
searchDto.setTableName(Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey());
List importSettingList = newStudentImportSettingMapper.select(searchDto);
if (CollectionUtils.isEmpty(importSettingList)) {
throw new ServiceException(2, “请先配置导入字段映射”);
}
Map mappingMap = importSettingList.stream()
.collect(Collectors.toMap(NewStudentImportSettingVo::getFileFieldCode, NewStudentImportSettingVo::getLibraryTableCode));

ImportMessageVo importResult = new ImportMessageVo();
int totalRecords = data.size();
AtomicInteger successCount = new AtomicInteger(0);
AtomicInteger insertSuccess = new AtomicInteger(0);
AtomicInteger updateSuccess = new AtomicInteger(0);
AtomicInteger failureCount = new AtomicInteger();
AtomicReference<StringBuilder> failureMessages = new AtomicReference<>(new StringBuilder());
AtomicInteger currentRow = new AtomicInteger(1);

for (Map<String, Object> rowData : data) {
    StringBuilder sqlBuilder = new StringBuilder("insert into " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " (");
    StringBuilder updateSql = new StringBuilder("update " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " set ");
    boolean updateFlag = false;

    try {
        Map<String, String> filteredMap = new HashMap<>();
        boolean kshEmpty = false;
        String kshValue = "";
        boolean sfzjhEmpty = false;
        String sfzjhValue = "";

        for (Map.Entry<String, Object> entry : rowData.entrySet()) {
            if (mappingMap.containsKey(entry.getKey())) {
                filteredMap.put(mappingMap.get(entry.getKey()).trim(), entry.getValue().toString().trim());
                String value = entry.getValue().toString().trim();
                if ("KSH".equals(mappingMap.get(entry.getKey()))) {
                    if (StringUtils.isBlank(value)) {
                        kshEmpty = true;
                        continue;
                    } else {
                        kshValue = value;
                    }
                }
                if ("SFZJH".equals(mappingMap.get(entry.getKey()))) {
                    if (StringUtils.isBlank(value)) {
                        sfzjhEmpty = true;
                        continue;
                    } else {
                        sfzjhValue = value;
                    }
                }
            }
        }

        if (!filteredMap.containsKey("KSH")) {
            failureCount.getAndIncrement();
            failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：excel中缺少考生编号(KSBH)映射列").append("\n");
            currentRow.getAndIncrement();
            continue;
        }
        if (!filteredMap.containsKey("SFZJH")) {
            failureCount.getAndIncrement();
            failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：excel中缺少身份证件号(SFZJH)映射列").append("\n");
            currentRow.getAndIncrement();
            continue;
        }
        if (kshEmpty) {
            failureCount.getAndIncrement();
            failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：考生编号(KSBH)列值为空").append("\n");
            currentRow.getAndIncrement();
            continue;
        }
        if (sfzjhEmpty) {
            failureCount.getAndIncrement();
            failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：身份证件号(SFZJH)列值为空").append("\n");
            currentRow.getAndIncrement();
            continue;
        }

        List<NewStudentInformationVo> existingData = newStudentImportSettingMapper.selectBySFZH(sfzjhValue);
        if (!CollectionUtils.isEmpty(existingData)) {
            if (existingData.size() > 1) {
                failureCount.getAndIncrement();
                failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：系统存在多条相同身份证数据").append("\n");
                currentRow.getAndIncrement();
                continue;
            } else if (existingData.size() == 1) {
                if (!kshValue.equals(existingData.get(0).getKsH())) {
                    failureCount.getAndIncrement();
                    failureMessages.get().append("第").append(currentRow.get()).append("行导入失败，原因：系统存在相同身份证但考生号不同的数据").append("\n");
                    currentRow.getAndIncrement();
                    continue;
                } else {
                    updateFlag = true;
                }
            }
        }

        int executionResult = 0;
        if (!updateFlag) {
            for (Map.Entry<String, Object> entry : rowData.entrySet()) {
                if (mappingMap.containsKey(entry.getKey())) {
                    sqlBuilder.append(mappingMap.get(entry.getKey()).trim()).append(",");
                }
            }
            sqlBuilder.setLength(sqlBuilder.length() - 1);
            sqlBuilder.append(") values (");
            for (Map.Entry<String