java - 读取一个excel表格并将其插入数据库mysql

Question

我是java新手。我正在使用 apache poi 将数据从 excel 表插入到 mysql。在后端，我使用 mysql 创建了表。我已经导入了所有的 jar 文件。我面临的错误是:1.无法插入日期值。2.当我删除日期列时，它只发送数据的地址而不发送值。3.有时会提示找不到源。请帮我解决这个问题。

  public static ArrayList readExcelFile(String fileName) throws SQLException
 {
/** --Define a ArrayList
    --Holds ArrayList Of Cells

 */

ArrayList cellArrayLisstHolder = new ArrayList();


enter code here
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);

/** We now need something to iterate through the cells.**/
  Iterator rowIter = mySheet.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 = myRow.createCell((short) 0);
          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

          SimpleDateFormat datetemp = new SimpleDateFormat("mm-dd-yyyy");
          Date cellValue = datetemp.parse("1994-01-01");
          cell.setCellValue(cellValue);

          //binds the style you need to the cell.
          HSSFCellStyle dateCellStyle = myWorkBook.createCellStyle();
          short df = myWorkBook.createDataFormat().getFormat("dd-mmm");
          dateCellStyle.setDataFormat(df);
          cell.setCellStyle(dateCellStyle);
          cellStoreArrayList.add(myCell);

                    }
      cellArrayLisstHolder.add(cellStoreArrayList);
  }
}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";                    
 //Read an Excel File and Store in a ArrayList
 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(?,?,?,?,?,?,?,?,?,?)";
  ps=con.prepareStatement(query);
 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(0)).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();
   System.out.println(((HSSFCell)cellStoreArrayList.get(9)).getStringCellValue() +   "\t");
   }
  //For checking data is inserted or not?
  if(count>0)
   { %>

               <table>
                   <tr>
                       <th>Dept_Id</th>
                       <th>Dept_GroupId</th>
                       <th>Dept_KId</th>
                       <th>Dept_GroupKid</th>
                       <th>Dept_Group</th>
                       <th>Dept_Name</th>
                       <th>Dept_Type</th>
                       <th>Hospital_Id</th>
                       <th>Dept_datecreated</th>
                       <th>Dept_datelastrefreshed</th>


                   </tr>

   <% for (int i=1;i < dataHolder.size(); i++) {
cellStoreArrayList=(ArrayList)dataHolder.get(i);%>
<tr>
    <td><%=((HSSFCell)cellStoreArrayList.get(0)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(1)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(2)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(3)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(4)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(5)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(6)).getStringCellValue() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(7)).getStringCellValue() %></td>
     <td><%=((HSSFCell)cellStoreArrayList.get(8)).getStringCellValue() %></td>
      <td><%=((HSSFCell)cellStoreArrayList.get(9)).getStringCellValue() %></td>




</tr>
   <%}
  }
else
{%>
<center> Details have not been inserted!!!!!!!!!</center>

<% }%>

我得到的错误是: 2014 年 1 月 6 日下午 12:05:00 调用 org.apache.catalina.core.StandardWrapperValve 严重:Servlet.service() for servlet [jsp] in context with path [/Excel] throw exception [An exception occurred processing JSP page/Excel.jsp at line 139

136:        ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).getStringCellValue());
137:        ps.setString(7,((HSSFCell)cellStoreArrayList.get(6)).getStringCellValue());
138:        ps.setString(8,((HSSFCell)cellStoreArrayList.get(7)).getStringCellValue());
139:        ps.setString(9,((HSSFCell)cellStoreArrayList.get(8)).getStringCellValue());
140:        ps.setString(10,    ((HSSFCell)cellStoreArrayList.get(9)).getStringCellValue());
141:        
 142:        count= ps.executeUpdate();


     Stacktrace:] with root cause
    java.lang.IndexOutOfBoundsException: Index: 8, Size: 8
at java.util.ArrayList.rangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at org.apache.jsp.Excel_jsp._jspService(Excel_jsp.java:234)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Answer 1

我无法对您的问题发表评论，因此我将根据我的理解尝试回答您在 Excel 中读取/写入 DATE 字段时遇到的问题。

我认为您缺少对日期字段的检查，excel 在内部将日期存储为数字内容，并在显示时显示格式化日期。您还需要确保该列在您正在阅读的工作表中被格式化为 DATE 列。

您需要使用以下代码片段来读取 DATE 的内容。

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 (DateUtil.isCellDateFormatted(cell)) {
   Date date = DateUtil.getJavaDate((double) cell
         .getNumericCellValue());
   SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm z");
   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());
}

}

完整的代码片段你可以引用这个 link .请注意我已经创建了链接。