Java+Apache POI读取Excel存入MySQL遇OOXML无效错误及文件上传异常

运行基于Apache POI的Java代码时，持续抛出错误：

No valid entries or contents found, this is not a valid OOXML (Office Open XML) file

同时服务器端生成了名为null的空文件。以下是相关代码与场景信息，以及针对性排查修复方案：

原文件上传Servlet代码 #

package finalApplication;

import java.io.File;
import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class uploader extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    {
        try {
            ServletFileUpload sf=new ServletFileUpload(new DiskFileItemFactory());

            List<FileItem> multifiles = sf.parseRequest(request);
            
            for(FileItem item : multifiles)
            {
                String filename =item.getName();
                
                item.write(new File("C:\\Users\\khuha\\eclipse-workspace\\finalApplication"+"\\"+filename));
                HttpSession session=request.getSession();

                session.setAttribute("file",filename);
                System.out.println(session.getAttribute("file"));
            }

            response.sendRedirect(request.getContextPath() +"/sheetsdetails.jsp");
        }
        catch (Exception ex) {  
            System.out.println(ex);
        }
    }
}

原文件处理Servlet代码 #

package finalApplication;
import java.io.FileInputStream;
import java.sql.*;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookType;

public class dataCollect extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        String url="jdbc:mysql://localhost:3306/Aithent";
        String uname="root";
        String pass="mysql";

        String dbUquery="use Aithent;";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection con=DriverManager.getConnection(url,uname,pass);
            Statement st=con.createStatement();
            st.execute(dbUquery);

            HttpSession session=request.getSession();
            String filename=(String) session.getAttribute("file");

            try(FileInputStream fis= new FileInputStream("C:\\Users\\khuha\\eclipse-workspace\\firstDemo\\"+filename);){
                XSSFWorkbook wb=new XSSFWorkbook(fis);
                int sheetnum=Integer.parseInt(request.getParameter("sheetnumber"));
                XSSFSheet sheet=wb.getSheetAt(sheetnum-1);

                Row firstRow=sheet.getRow(0);
                Iterator<Row> itr=sheet.iterator();
                int colnum=firstRow.getPhysicalNumberOfCells();
                String keys[]=new String[colnum-1];

                Iterator<Cell> FirstrowIterator=sheet.getRow(0).cellIterator();{
                    int a=1;
                    while(FirstrowIterator.hasNext()){
                        Cell date_cell=firstRow.getCell(1);
                        String sdate=date_cell.getStringCellValue();
                        boolean isSingleDigit=Pattern.matches("[0-9]-[A-Z]*[a-z]+",sdate);
                        boolean isDoubleDigit=Pattern.matches("[0-9][0-9]-[A-Z]*[a-z]+",sdate);
                        String new_date;
                        String new_month;
                        if (isSingleDigit==true){
                            new_date="0"+sdate.substring(0);
                            new_month=sdate.substring(2-5);
                            keys[a]=appUtils.date_maker(new_date,new_month);
                        }
                        else if (isDoubleDigit=true){
                            new_date=sdate.substring(0,2);
                            new_month=sdate.substring(3,6);
                            keys[a]=appUtils.date_maker(new_date,new_month);
                        }
                    }
                }
                String tbquery="create table if not exists "+filename+"( Name varchar(100));";
                st.execute(tbquery);
                int i=0;
                while (i<colnum){
                    String addQuery="Alter table "+filename+" add "+ keys[i]+" varchar(15);";
                    st.execute(addQuery);
                    i++;
                }

                while(itr.hasNext()) {
                    String values[]=new String[colnum-1];
                    Row row=itr.next();
                    Cell name_cell=row.getCell(0);
                    String name=name_cell.getStringCellValue();
                    values[0]=name;
                    Iterator<Cell> cellIterator=row.cellIterator();
                    int k=1;
                    while(cellIterator.hasNext()) {
                        Cell cell=cellIterator.next();
                        String dbEntry;

                        if (cell.getRowIndex()!=0 && cell.getStringCellValue().equals("")&& cell.getColumnIndex()!=0){
                            dbEntry="Blank";
                        }
                        else if(cell.getRowIndex()!=0 && ( cell.getStringCellValue().equals("F")| cell.getStringCellValue().equals("f"))&& cell.getColumnIndex()!=0) {
                            dbEntry="Full Day";
                        }
                        else if(cell.getRowIndex()!=0 && ( cell.getStringCellValue().equals("H")| cell.getStringCellValue().equals("h"))&& cell.getColumnIndex()!=0) {
                            dbEntry="Half Day"; 
                        }
                        else if(cell.getRowIndex()!=0 &&( cell.getStringCellValue().equals("L")| cell.getStringCellValue().equals("l"))&& cell.getColumnIndex()!=0) {
                            dbEntry="Leave";
                        }
                        else {
                            dbEntry="Invalid Entry";
                        }
                        values[k]=dbEntry;
                        k++;
                    }
                    String insertQ="INSERT into "+filename+"Values (";
                    int l=0;
                    while (l<colnum) {
                        if (l!=colnum-2){
                            insertQ.concat(values[l]+",");
                        }
                        else {
                            insertQ.concat(values[l]+");");
                        }
                    }
                    st.execute(insertQ);
                }
                wb.close();
            }
            catch(Exception e){ 
                e.printStackTrace();  
            }
        }
        catch(Exception e1) {
            System.out.println(e1);
        }
    }
}

Excel文件格式说明 #

表头为文本格式的日期（如1-Jan），首列存储用户姓名，其余列记录考勤状态（F/H/L等）。

输出异常说明 #

文件资源管理器中可见生成了名为null的空文件，无有效内容。

核心问题分析 #

1. 文件路径不匹配：上传Servlet将文件存在finalApplication目录，处理Servlet却从firstDemo目录读取，导致读取错误文件或空文件。
2. 未区分表单字段与文件：遍历所有FileItem时未跳过普通表单字段，此时item.getName()返回null，直接写入生成null空文件，且将null存入session。
3. Excel读取逻辑错误：日期处理中substring(2-5)参数错误，循环始终取同一单元格；SQL拼接用concat但未接收返回值，导致插入语句为空。
4. 未做合法性校验：未校验文件名是否为空、文件大小是否为0，直接写入无效文件。

修复方案 #

1. 修正文件上传Servlet #

package finalApplication;

import java.io.File;
import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class uploader extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    {
        try {
            ServletFileUpload sf = new ServletFileUpload(new DiskFileItemFactory());
            List<FileItem> multifiles = sf.parseRequest(request);
            // 创建统一上传目录
            String uploadDir = "C:\\Users\\khuha\\eclipse-workspace\\finalApplication\\uploads\\";
            File dir = new File(uploadDir);
            if (!dir.exists()) dir.mkdirs();

            for(FileItem item : multifiles)
            {
                // 跳过普通表单字段
                if (item.isFormField()) continue;
                
                String filename = item.getName();
                // 校验文件名有效性
                if (filename == null || filename.trim().isEmpty()) continue;
                // 添加时间戳避免文件名重复
                String uniqueFilename = System.currentTimeMillis() + "_" + filename;
                File saveFile = new File(uploadDir + uniqueFilename);
                item.write(saveFile);
                
                HttpSession session = request.getSession();
                // 存储完整文件路径而非仅文件名
                session.setAttribute("filePath", saveFile.getAbsolutePath());
                System.out.println("上传文件路径：" + saveFile.getAbsolutePath());
            }

            response.sendRedirect(request.getContextPath() + "/sheetsdetails.jsp");
        } catch (Exception ex) {  
            ex.printStackTrace();
            response.getWriter().write("上传失败：" + ex.getMessage());
        }
    }
}

2. 修正文件处理Servlet #

package finalApplication;
import java.io.FileInputStream;
import java.sql.*;
import java.io.IOException;
import java.util.Iterator;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class dataCollect extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        String url = "jdbc:mysql://localhost:3306/Aithent?useUnicode=true&characterEncoding=utf8";
        String uname = "root";
        String pass = "mysql";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, uname, pass);
            Statement st = con.createStatement();

            HttpSession session = request.getSession();
            String filePath = (String) session.getAttribute("filePath");
            // 校验文件有效性
            if (filePath == null || new File(filePath).length() == 0) {
                response.getWriter().write("无效文件或空文件");
                return;
            }

            try(FileInputStream fis = new FileInputStream(filePath);
                XSSFWorkbook wb = new XSSFWorkbook(fis);){
                
                int sheetnum = Integer.parseInt(request.getParameter("sheetnumber"));
                if (sheetnum < 1 || sheetnum > wb.getNumberOfSheets()) {
                    response.getWriter().write("无效工作表编号");
                    return;
                }
                XSSFSheet sheet = wb.getSheetAt(sheetnum - 1);

                Row firstRow = sheet.getRow(0);
                if (firstRow == null) {
                    response.getWriter().write("工作表为空");
                    return;
                }
                int colnum = firstRow.getPhysicalNumberOfCells();
                String[] keys = new String[colnum];

                // 遍历表头处理字段名
                Iterator<Cell> cellIterator = firstRow.cellIterator();
                int a = 0;
                while(cellIterator.hasNext()){
                    Cell cell = cellIterator.next();
                    String cellValue = cell.getStringCellValue().trim();
                    // 修正日期格式处理
                    boolean isSingleDigit = Pattern.matches("[0-9]-[A-Za-z]+", cellValue);
                    if (isSingleDigit){
                        cellValue = "0" + cellValue;
                    }
                    // 替换特殊字符避免SQL语法错误
                    keys[a] = cellValue.replaceAll("[^a-zA-Z0-9_]", "_");
                    a++;
                }

                // 生成合法表名
                String tableName = "excel_attendance_" + System.currentTimeMillis();
                String tbquery = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + keys[0] + " VARCHAR(100))";
                st.execute(tbquery);

                // 添加其他列
                for (int i = 1; i < colnum; i++){
                    String addQuery = "ALTER TABLE " + tableName + " ADD COLUMN " + keys[i] + " VARCHAR(15)";
                    st.execute(addQuery);
                }

                Iterator<Row> rowIterator = sheet.iterator();
                rowIterator.next(); // 跳过表头

                while(rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    if (row == null) continue;

                    StringBuilder insertQ = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
                    for (int i = 0; i < colnum; i++){
                        Cell cell = row.getCell(i);
                        String dbEntry = "Invalid Entry";
                        if (cell != null){
                            String cellStr = cell.getStringCellValue().trim();
                            if (cellStr.isEmpty()){
                                dbEntry = "Blank";
                            } else if (cellStr.equalsIgnoreCase("F")){
                                dbEntry = "Full Day";
                            } else if (cellStr.equalsIgnoreCase("H")){
                                dbEntry = "Half Day";
                            } else if (cellStr.equalsIgnoreCase("L")){
                                dbEntry = "Leave";
                            }
                        }
                        // 转义单引号避免SQL注入
                        dbEntry = dbEntry.replace("'", "''");
                        insertQ.append("'").append(dbEntry).append("'");
                        if (i != colnum - 1){
                            insertQ.append(",");
                        }
                    }
                    insertQ.append(")");
                    st.execute(insertQ.toString());
                }

                response.getWriter().write("数据导入成功");
            } catch(Exception e){ 
                e.printStackTrace();
                response.getWriter().write("文件处理失败：" + e.getMessage());
            } finally {
                if (st != null) st.close();
                if (con != null) con.close();
            }
        } catch(Exception e1) {
            e1.printStackTrace();
            response.getWriter().write("数据库连接失败：" + e1.getMessage());
        }
    }
}

额外注意事项 #

• 确保Apache POI依赖完整：需包含poi、poi-ooxml、poi-ooxml-schemas三个核心包
• 给上传目录配置写入权限，避免文件写入失败
• 建议使用PreparedStatement替代Statement，彻底避免SQL注入风险
• 处理大文件时，可设置DiskFileItemFactory的缓存阈值，防止内存溢出

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