java - 使用jsp导入excel数据存入数据库

Question

这是我的索引文件

<form action="DataImport" method="post>"

    <input name="filename" type="file" size="20">

    <input type="submit" value="Import">

DataImport.java(Servlet)

public class DataImport extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    public DataImport()
    {

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

    }

    @SuppressWarnings("rawtypes")
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

        try
        {
            String file = request.getParameter("file");

            String fileName1=""+file+"";

            ArrayList dataHolder = read.readExcelFile(fileName1);

            Connection con = EmployeeDao1.con11();
            System.out.println("Connection :"+con);
            @SuppressWarnings("unused")
            Statement sql_statement = con.createStatement();
            String jdbc_insert_sql = "INSERT INTO emp4"
                    + "(Id,name,salary) VALUES"
                    + "(?,?,?)";

            PreparedStatement ps = con.prepareStatement(jdbc_insert_sql);
            int count=0;

            ArrayList cellStoreArrayList=null;
            for(int i=1;i<dataHolder.size();i++)
            {
                cellStoreArrayList=(ArrayList)dataHolder.get(i);
                ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
                ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
                ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
                count= ps.executeUpdate();
            }
            if(count>0)
            {
                for (int j=1;j < dataHolder.size(); j++)
                {
                    cellStoreArrayList=(ArrayList)dataHolder.get(j);
                    System.out.println(((HSSFCell)cellStoreArrayList.get(0)).toString());
                    System.out.println(((HSSFCell)cellStoreArrayList.get(1)).toString());
                    System.out.println(((HSSFCell)cellStoreArrayList.get(2)).toString());
                }
            }

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

}

这是我的 read.java 类来读取 Excel 文件

public class read {
    @SuppressWarnings({"unchecked", "rawtypes"})

    public static ArrayList readExcelFile(String fileName) {

        ArrayList cellArrayLisstHolder = new ArrayList();
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\LOGA\\Book1.xlsx"));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator cells = row.cellIterator();

                ArrayList cellStoreArrayList = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    cellStoreArrayList.add(cell);
                    int cellType = cell.getCellType();
                    if (cellType == HSSFCell.CELL_TYPE_STRING) {
                        HSSFRichTextString strData = cell.getRichStringCellValue();
                        System.out.println("String data=" + strData.getString());
                    } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                        double data = cell.getNumericCellValue();
                        System.out.println("Numeric data=" + data);
                    }
                }
                cellArrayLisstHolder.add(cellStoreArrayList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cellArrayLisstHolder;
    }
}

当我尝试执行 servlet 时，它显示以下错误

SEVERE: Servlet.service() for servlet DataImport threw exception
java.lang.ClassNotFoundException: org.apache.poi.poifs.filesystem.POIFSFileSystem
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1358)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1204)
    at pack.DataImport.doPost(DataImport.java:47)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)




    at java.lang.Thread.run(Unknown Source)

谁能解决这个问题吗？我已经包含了所有的 poi 和 sql jar 文件，Excel 文件(Book1.xslx)仅位于 D:\\LOGA 中...

Answer 1

您似乎忘记在 CLASSPATH 中包含 POI 库(例如，您的 Web 应用程序未引用它)。您可以将此库包含在 WEB-INF/lib 下(在您的 WAR 存档中或以展开的目录格式)，或者它可以位于您的应用程序服务器/Web 服务器 CLASSPATH 中的其他位置