Databases
StackOverflow2013
Postsdbo
POInserting data from excel file in MSACCESS database using JSP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting data from excel file in MSACCESS database using JSP
    primarykey
    data
    text
    <p>This code is running correctly for inserting data from excel file into MSACCESS table using "APACHE POI library", but it is not inserting the values. </p> <p>The code is not showing any error but tt is printing "Not able to insert......." on running: </p> <p>Here, I've used DSN to connect to MSACCESS Database.<br> NETBEANS IDE</p> <p>DSN: amandsn<br> MS Office 2007 Access Table: Student<br> MS Office 2007 Excel file: "myexcel.xls" which consists of 3columns i.e Name, Grade and Subject and there data which I want to insert into a MSACCESS table.</p> <pre><code> &lt;%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%&gt; &lt;!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"&gt; &lt;%@ page import ="java.util.Date" %&gt; &lt;%@ page import ="java.io.*" %&gt; &lt;%@ page import ="java.io.FileNotFoundException" %&gt; &lt;%@ page import ="java.io.IOException" %&gt; &lt;%@ page import ="java.util.Iterator" %&gt; &lt;%@ page import ="java.util.ArrayList" %&gt; // Using Apache POI Libraries &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %&gt; &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %&gt; &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %&gt; &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %&gt; &lt;%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %&gt; &lt;html&gt; &lt;head&gt; &lt;meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"&gt; &lt;title&gt;Insert title here&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;% Connection con=null; Statement st =null; PreparedStatement ps=null; Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con=DriverManager.getConnection("jdbc:odbc:amandsn"); st=con.createStatement(); %&gt; &lt;% try { String fileName="myexcel.xls"; //Read an Excel File and Store in a ArrayList ArrayList dataHolder=readExcelFile(fileName); // Print the data read // PrintCellDataToConsole(dataHolder); String query="insert into Student values(?,?,?)"; ps=con.prepareStatement(query); int count=0; ArrayList cellStoreArrayList=null; //For inserting into database for (int i=1;i &lt; 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(); out.println(((HSSFCell)cellStoreArrayList.get(2)).toString() + "\t"); } //For checking data is inserted or not? if(count&gt;0) { %&gt; Following details from Excel file have been inserted in student table of database &lt;table&gt; &lt;tr&gt; &lt;th&gt;Name&lt;/th&gt; &lt;th&gt;Grade&lt;/th&gt; &lt;th&gt;Subject&lt;/th&gt; &lt;/tr&gt; &lt;% for (int i=1;i &lt; dataHolder.size(); i++) { cellStoreArrayList=(ArrayList)dataHolder.get(i); %&gt; &lt;tr&gt; &lt;td&gt;&lt;%=((HSSFCell)cellStoreArrayList.get(0)).toString() %&gt;&lt;/td&gt; &lt;td&gt;&lt;%=((HSSFCell)cellStoreArrayList.get(1)).toString() %&gt;&lt;/td&gt; &lt;td&gt;&lt;%=((HSSFCell)cellStoreArrayList.get(2)).toString() %&gt;&lt;/td&gt; &lt;/tr&gt; &lt;%} } else { %&gt; &lt;center&gt; Not able to insert.......&lt;/center&gt; &lt;% } } catch(Exception e1){e1.printStackTrace(); } %&gt; &lt;%! public static ArrayList readExcelFile(String fileName) { /** --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); /** 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(); cellStoreArrayList.add(myCell); } cellArrayLisstHolder.add(cellStoreArrayList); } }catch (Exception e){e.printStackTrace(); } return cellArrayLisstHolder; }%&gt; &lt;/table&gt; &lt;/body&gt; &lt;/html&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbenRollag
    1. USAman Chawla
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COshow the complete stack trace
      singulars
      1. POInserting data from excel file in MSACCESS database using JSP
      1. USMubin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload