Excel文件上传与数据处理

本文档描述了一个Java类，用于处理Excel文件的上传和数据解析，最终将数据保存至数据库。此过程包括创建临时文件夹、检查文件格式、读取Excel数据并进行数据库操作等步骤。

### 类定义

public class ExcelUploadHandler extends BaseAction {
    // 记录操作日志
    private static final String LOG_TRANSACTION_CODE = "TXN_DOC_0021";
    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        try {
            String tempFolderPath = "D:/temp";
            createTempDirectory(tempFolderPath);
            String tempFilePath = "D:/temp/temp.xls";
            FileOutputStream fileOutputStream = new FileOutputStream(tempFilePath);
            ExcelUploadForm uploadForm = (ExcelUploadForm) form;
            FormFile uploadedFile = uploadForm.getExcelFile();
            String fileName = uploadedFile.getFileName();
            if (fileName != null && !fileName.isEmpty()) {
                String fileExtension = fileName.substring(fileName.lastIndexOf('.') + 1);
                if (!"xls".equalsIgnoreCase(fileExtension)) {
                    throw new IllegalArgumentException("Please select an .xls file.");
                }
                fileOutputStream.write(uploadedFile.getFileData());
                fileOutputStream.flush();
                fileOutputStream.close();
                parseAndSaveData(tempFilePath, request);
                deleteFile(tempFilePath);
                deleteDirectory(tempFolderPath);
                request.setAttribute("txnStatus", "SUCCESS");
                request.setAttribute("message", getLogMessage(LOG_TRANSACTION_CODE, "SUCCESS"));
                request.setAttribute("backUrl", request.getContextPath() + mapping.getInputForward().getPath());
                logSuccess(getLogMessage(LOG_TRANSACTION_CODE, "SUCCESS"));
                return mapping.findForward("success");
            } else {
                throw new IllegalArgumentException("No file selected for upload.");
            }
        } catch (Exception e) {
            logError(getLogMessage(LOG_TRANSACTION_CODE, "FAILURE"), e);
            request.setAttribute("txnStatus", "FAILURE");
            request.setAttribute("errorMessage", getLogMessage(LOG_TRANSACTION_CODE, "FAILURE"));
            request.setAttribute("error", "An unknown error occurred during the upload. Please contact the system administrator!");
            return mapping.getInputForward();
        }
    }

### 辅助方法

private void logSuccess(String message) {
    LOG.info("Operation completed successfully: " + message);
}

private void logError(String message, Throwable cause) {
    LOG.error("Operation failed: " + message, cause);
}

private String getLogMessage(String transactionCode, String status) {
    return RescodeMapping.getRescodeDesc(transactionCode, status);
}

private void createTempDirectory(String path) throws IOException {
    File directory = new File(path);
    if (!directory.exists()) {
        if (!directory.mkdirs()) {
            throw new IOException("Failed to create directory: " + path);
        }
    }
}

private void deleteFile(String filePath) throws IOException {
    File file = new File(filePath);
    if (file.exists() && !file.delete()) {
        throw new IOException("Failed to delete file: " + filePath);
    }
}

private void deleteDirectory(String dirPath) throws IOException {
    deleteFile(dirPath);
    File directory = new File(dirPath);
    if (directory.exists() && !directory.delete()) {
        throw new IOException("Failed to delete directory: " + dirPath);
    }
}

private void parseAndSaveData(String filePath, HttpServletRequest request) throws IOException, BiffException {
    FileInputStream inputStream = new FileInputStream(filePath);
    Workbook workbook = Workbook.getWorkbook(inputStream);
    Sheet sheet = workbook.getSheet(0);
    int rowCount = sheet.getRows();
    String currentOperatorId = UserSessionInfoUtils.getUserSessionInfo(request.getSession()).getOperatorId();
    TPayformDAO payformDAO = (TPayformDAO) ContextUtil.getBean("TPayformDAO");
    for (int i = 1; i         TPayform payform = new TPayform();
        Cell[] rowCells = sheet.getRow(i);
        if (rowCells.length >= 1) {
            payform.setClasscode(rowCells[0].getContents());
        } else {
            throw new IndexOutOfBoundsException("Row does not contain enough data.");
        }
        if (rowCells.length >= 2) {
            payform.setComputeWay(rowCells[1].getContents());
        } else {
            throw new IndexOutOfBoundsException("Row does not contain enough data.");
        }
        payform.setAge(new BigDecimal(rowCells[2].getContents()));
        payform.setSex(rowCells[3].getContents());
        payform.setSingle(new BigDecimal(rowCells[4].getContents()));
        // ... (继续设置其他属性)
        payformDAO.insert(payform);
    }
    workbook.close();
    inputStream.close();
}