上传Excel至Oracle数据库时遇java.io.FileNotFoundException问题求助

Hey there, let's tackle that java.io.FileNotFoundException you're hitting when uploading Excel files to Oracle. The root issue is clear—your current code uses a hardcoded file path instead of dynamically handling the file uploaded from the frontend. Let's fix this step by step.

What's Going Wrong? #

Your JSP is trying to read a file from D:\\GODBFILES\\NETBEANS PROJECTS\\upload\\hello.xlsx directly, but that's not the file the user uploads. The error path shows null because you're not properly retrieving the uploaded file from the multipart request, leading the code to look for a non-existent file at a broken path.

Step-by-Step Fix #

We'll use the MultipartRequest (from cos.jar, which you already have) to handle the uploaded file, then process it with POI without hardcoding any paths.

1. Updated JSP Code (xlsupload_01.jsp)

Here's the revised code with dynamic file handling, plus fixes for resource management and error handling:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@page import ="java.util.Date" %>
<%@page import ="java.io.*" %>
<%@page import ="java.util.Iterator" %>
<%@page import ="java.util.ArrayList" %>
<%@page import="org.apache.poi.hssf.usermodel.*" %>
<%@page import="org.apache.poi.ss.usermodel.Cell" %>
<%@page import ="org.apache.poi.ss.usermodel.Row"%>
<%@page import="org.apache.poi.ss.usermodel.Sheet" %>
<%@page import="org.apache.poi.ss.usermodel.Workbook" %>
<%@page import="com.oreilly.servlet.MultipartRequest" %>
<%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
<%@page import="org.apache.poi.ss.usermodel.DateUtil"%>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Excel Upload Result</title>
</head>
<body>
<%
    ArrayList<ArrayList<Object>> cellArrayListHolder = new ArrayList<>();
    int count = 0;
    Connection con = null;
    Workbook workbook = null;
    InputStream inputStream = null;

    try {
        // 1. Handle multipart request to get the uploaded file
        // Set a temporary directory to store uploaded files (adjust this path as needed)
        String tempDir = application.getRealPath("/") + "temp_uploads";
        File tempDirFile = new File(tempDir);
        if (!tempDirFile.exists()) {
            tempDirFile.mkdirs(); // Create directory if it doesn't exist
        }

        // Parse the multipart request
        MultipartRequest multi = new MultipartRequest(request, tempDir);
        
        // Get the uploaded file object
        File uploadedFile = multi.getFile("xlsfile");
        if (uploadedFile == null) {
            out.println("<h3>No file selected for upload!</h3>");
            return;
        }

        // 2. Open input stream from the uploaded file
        inputStream = new FileInputStream(uploadedFile);

        // 3. Determine if it's .xls or .xlsx and create the appropriate Workbook
        String fileName = multi.getFilesystemName("xlsfile");
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            out.println("<h3>Unsupported file type! Please upload .xls or .xlsx.</h3>");
            return;
        }

        // 4. Read Excel data (type-safe conversion for database insertion)
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();

        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            ArrayList<Object> rowArrayList = new ArrayList<>();
            Iterator<Cell> cellIterator = nextRow.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                // Convert cell to appropriate Java type
                switch (cell.getCellType()) {
                    case STRING:
                        rowArrayList.add(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            rowArrayList.add(cell.getDateCellValue());
                        } else {
                            rowArrayList.add(cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        rowArrayList.add(cell.getBooleanCellValue());
                        break;
                    default:
                        rowArrayList.add("");
                }
            }
            cellArrayListHolder.add(rowArrayList);
        }

        // 5. Insert data into Oracle database
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:@172.18.114.213:1821:xe","se","Spacess");
        
        // Adjust INSERT query based on your table structure (example uses one column)
        PreparedStatement st = con.prepareStatement("INSERT INTO DYNAMIC_INSERT VALUES (?)");
        
        // Skip header row (start from index 1)
        for (int i = 1; i < cellArrayListHolder.size(); i++) {
            ArrayList<Object> rowArrayList = cellArrayListHolder.get(i);
            if (!rowArrayList.isEmpty()) {
                st.setObject(1, rowArrayList.get(0));
                count += st.executeUpdate();
            }
        }

        if (count > 0) {
            out.println("<script type='text/javascript'>alert('" + count + " records added successfully!'); window.history.back();</script>");
        } else {
            out.println("<h3>No records were inserted. Check your Excel data.</h3>");
        }

    } catch (FileNotFoundException e) {
        out.println("<h3>Error: File not found - " + e.getMessage() + "</h3>");
        e.printStackTrace();
    } catch (IOException e) {
        out.println("<h3>Error processing file - " + e.getMessage() + "</h3>");
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        out.println("<h3>Oracle driver not found - " + e.getMessage() + "</h3>");
        e.printStackTrace();
    } catch (SQLException e) {
        out.println("<h3>Database error - " + e.getMessage() + "</h3>");
        e.printStackTrace();
    } finally {
        // Clean up resources to prevent leaks
        try {
            if (inputStream != null) inputStream.close();
            if (workbook != null) workbook.close();
            if (con != null) con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
%>
</body>
</html>

Key Changes Made:

• Dynamic File Handling: Used MultipartRequest to retrieve the uploaded file directly from the frontend form, eliminating hardcoded paths.
• Temporary Upload Directory: Created a temp folder to store uploaded files (adjust the path to match your server's file system permissions).
• Dual Format Support: Added logic to handle both .xls (HSSF) and .xlsx (XSSF) Excel files.
• Resource Cleanup: Used a finally block to close database connections, streams, and workbooks to prevent resource leaks.
• Error Feedback: Added specific catch blocks to display user-friendly error messages for common issues.
• Type-Safe Cell Reading: Converted Excel cell values to appropriate Java types for smoother database insertion.

2. Frontend Notes

Your existing HTML form is good to go—just double-check that the file input's name attribute is xlsfile (matches the JSP's multi.getFile("xlsfile") call).

Why This Fixes Your Original Error #

By using MultipartRequest, we're directly accessing the file the user selects from any location on their machine, instead of forcing a fixed local path. This fully supports your requirement of allowing uploads from arbitrary paths.

内容的提问来源于stack exchange，提问作者Atul