个人中心
gpt4 book ai didi

java - 如何读取Excel多个工作表并将Excel工作表的内容存储到数据库中

转载 作者：行者123 更新时间：2023-12-01 13:38:08 24 4
gpt4 key购买 nike

我是java新手，现在我已经创建了用于读取 Excel 工作表并使用 apache poi 将其发送到数据库的代码。我已使用第一个查询成功读取第一张表并将其发送到数据库，但我不知道如何使用第二个查询和表读取第二张表。这里 2 张纸有 2 个不同的查询，如何在准备好的语句中提及。或者有没有其他方法可以做到。请帮我解决这个问题。代码是

<html>
<head>
<meta http-equiv="Content-Type" content="text/html">
<title>Excel</title>
</head>
<body>
<FORM ENCTYPE="multipart/form-data" ACTION="Excel.jsp" METHOD=POST>
<br><br><br>
<center><table border="2" >
<tr><center><td colspan="2"><p align="center"><B>UPLOAD THE FILE</B><center></td></tr>
<tr><td><b>Choose the file To Upload:</b>
</td>
 <td><INPUT NAME="file" TYPE="file" accept=".csv, application/vnd.openxmlformats-  officedocument.spreadsheetml.sheet, application/vnd.ms-excel"></td></tr>
 <tr><td colspan="2">
 <p align="right"><INPUT TYPE="submit" VALUE="Send File" ></p></td></tr>
 </center>
 </FORM>
  </body>

 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">
 <jsp:setProperty name="connection" property="*"/>
 </jsp:useBean> 
  </html>

 <%!     
   Connection con;
   PreparedStatement ps=null;
   PreparedStatement ps2=null;
   public static ArrayList readExcelFile(String fileName) throws SQLException
   {
     /** --Define a ArrayList
      --Holds ArrayList Of Cells

      */

      ArrayList cellArrayLisstHolder = new ArrayList();

     try{
           /** Creating Input Stream**/
          FileInputStream myInput = new FileInputStream(fileName);

          /** Create a POIFSFileSystem object**/
           POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

          /** Create a workbook using the File System**/
           HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

          /** Get the first sheet from workbook**/

            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            HSSFSheet mySheet2 = myWorkBook.getSheetAt(1);


         /** We now need something to iterate through the cells.**/
            Iterator rowIter = mySheet.rowIterator();
            Iterator rowIter1 = mySheet2.rowIterator();
             while(rowIter.hasNext()){
             HSSFRow myRow = (HSSFRow) rowIter.next();
             Iterator cellIter = myRow.cellIterator();
              ArrayList cellStoreArrayList=new ArrayList();

             while(cellIter.hasNext()){
             HSSFCell myCell = (HSSFCell) cellIter.next();

              HSSFCell cell;

          short i= 1;

          do {
              cell = myRow.getCell(i);// Start from first column

              if (cell == null) {
                 break;
              }
              if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                 // Plain String Cell.
                 System.out.println("The Cell is a String with value : "
                       + cell.getStringCellValue());
              } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

                 // Excel stores the Date as a Numeric Contents. POI provides
                 // a Date utility to check
                 // if a particular cell is formatted as a date.
                 if (HSSFDateUtil.isCellDateFormatted(cell)) {

                    Date date = HSSFDateUtil.getJavaDate((double) cell
                          .getNumericCellValue());

                    SimpleDateFormat df = new SimpleDateFormat("dd/mm/yyyy");
                    System.out.println("The cell is a Date : " + df.format(date));
                 } else {
                    // treat the cell as 'double' number
                    System.out.println("The cell is a number : "
                          + cell.getNumericCellValue());
                 }

              } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                 // Some of the cells will be rendered blank when cells in
                 // Excel are merged... one such cell
                 System.out
                 .println("A Blank Cell Encountered.... any merging happened???");
              } else {
                 System.out.println("The cell is nothing we're interested in ...");
              }
              i++; // increment cell counter!!!
           } while (cell != null);

          cellStoreArrayList.add(myCell);

                    }
      cellArrayLisstHolder.add(cellStoreArrayList);
  }
  while(rowIter1.hasNext()){
      HSSFRow myRow1 = (HSSFRow) rowIter.next();
      Iterator cellIter1 = myRow1.cellIterator();
      ArrayList cellStoreArrayList1=new ArrayList();
      while(cellIter1.hasNext()){
          HSSFCell myCell1 = (HSSFCell) cellIter1.next();
          HSSFCell cell1;
          short i= 1;
          do {
              cell1 = myRow1.getCell(i); // Start from first column
              if (cell1 == null) {
                 break;
              }
              if (cell1.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                 // Plain String Cell.
                 System.out.println("The Cell is a String with value : "
                       + cell1.getStringCellValue());
              } else if (cell1.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

                 // Excel stores the Date as a Numeric Contents. POI provides
                 // a Date utility to check
                 // if a particular cell is formatted as a date.
                 if (HSSFDateUtil.isCellDateFormatted(cell1)) {
                    Date date = HSSFDateUtil.getJavaDate((double) cell1
                          .getNumericCellValue());
                    SimpleDateFormat df = new SimpleDateFormat("mm/dd/yyyy");
                    System.out.println("The cell is a Date : " + df.format(date));
                 } else {
                    // treat the cell as 'double' number
                    System.out.println("The cell is a number : "
                          + cell1.getNumericCellValue());
                 }

              } else if (cell1.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                 // Some of the cells will be rendered blank when cells in
                 // Excel are merged... one such cell
                 System.out
                 .println("A Blank Cell Encountered.... any merging happened???");
              } else {
                 System.out.println("The cell is nothing we're interested in ...");
              }
              i++; // increment cell counter!!!
           } while (cell1 != null);

          cellStoreArrayList1.add(myCell1);

                    }
      cellArrayLisstHolder.add(cellStoreArrayList1);
  }
}catch (Exception e){e.printStackTrace(); }
return cellArrayLisstHolder;
}


%>
<%

 File f = new File("DeptHosp.xls");
   System.out.println(f.getAbsolutePath());

  File file = new File("."); 
 for(String fileNames : file.list()) System.out.println(fileNames);
String fileName="D://PROJECT//SOFTWARES//eclipse_Juno//eclipse//DeptHosp.xls";             
System.out.println(" path found");
ArrayList dataHolder=readExcelFile(fileName);
//Print the data read
//printCellDataToConsole(dataHolder);

con=connection.getConn();
 System.out.println("Inserting the details");
 String query="insert into   departmentmaster(Dept_id,Dept_Groupid,Dept_Kid,Dept_Groupkid,Dept_Group,Dept_Name,Dept_type,Dept_HospitalId,Dept_datecreated,Dept_datelastrefreshed)values(?,?,?,?,?,?,?,?,?,?)";
  String query1="insert into hospitalmaster(hospital_group,hospital_name)values(?,?)";
   ps=con.prepareStatement(query);
  ps2=con.prepareStatement(query1);
   System.out.println("Database");
  int count=0;
     ArrayList cellStoreArrayList=null;
    Date datevalue=null;

  //For inserting into database
    for (int i=1;i < dataHolder.size(); i++) {
    cellStoreArrayList=(ArrayList)dataHolder.get(i);


   ps.setString(1,((HSSFCell)cellStoreArrayList.get(1)).getStringCellValue());
   ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).getStringCellValue());
   ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).getStringCellValue());
   ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).getStringCellValue());
   ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).getStringCellValue());
   ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).getStringCellValue());
   ps.setString(7,((HSSFCell)cellStoreArrayList.get(6)).getStringCellValue());
   ps.setString(8,((HSSFCell)cellStoreArrayList.get(7)).getStringCellValue());
   ps.setString(9,((HSSFCell)cellStoreArrayList.get(8)).getStringCellValue());
   ps.setString(10,((HSSFCell)cellStoreArrayList.get(9)).getStringCellValue());
   count= ps.executeUpdate();
   ps2.setString(1,((HSSFCell)cellStoreArrayList.get(1)).getStringCellValue());
   ps2.setString(2,((HSSFCell)cellStoreArrayList.get(1)).getStringCellValue());
   count= ps2.executeUpdate();

   }

 </body>
</html>

最佳答案

也许你正在混合一些东西。您已经解决了如何呈现 JSP、使用 POI 读取 Excel 文件以及将行写入数据库。我建议您重新设计代码以分离这些任务。可能是一个简单的类 (Reader.java)，它仅读取 excel 文件并为每个工作表生成一个 ArrayList，然后是一个具有 2 个方法的简单类 (Writer.java)，每个方法都采用 ArrayList 并根据需要将它们写入数据库。这导致了更简单的自顶向下设计，更容易调试。

顺便说一下，您正在代码中创建一个数据库连接，但没有返回它。您应该为您使用的每个数据库资源(ResultSets、Statements 和 Connections)调用 .close() 方法。在您的应用程序中使用，或者使用一些惯用语(尝试使用资源、Spring 资源管理等)来确保这样做。

奖励轨道:当您对 Java 感觉更舒服时，请查看

http://en.wikipedia.org/wiki/Connection_pool

这将是在可能有数百或数千并发用户的 Web 应用程序中连接到数据库的首选方式。您的想法是为每次访问页面打开一个新连接，这对于学术练习来说是完全正确的，但在现实环境中会导致效率问题。也许您的应用服务器有内置的连接池机制(例如最新版本的tomcat、weblogic、websphere都有)或者您可以考虑使用apache DBCP或C3PO。

关于java - 如何读取Excel多个工作表并将Excel工作表的内容存储到数据库中，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21085977/

24 4 0
文章推荐： java - 从另一个应用程序上下文注入(inject) bean？
文章推荐： java - 将 JSON 转换为 XML 时出现的问题
文章推荐： java - 从 ListView Adapter 中的循环构造 ID 数组 android
文章推荐： AngularJS $http 成功函数不起作用
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com