santosh_tamse
02-22-2009, 05:59 AM
hi all
i my program i want to fetch data from excel sheet & i hav to insert the selected records from excel sheet to Microsoft Access Database. i hav written the following code for it but it is not working it is giving error meassges.other major problem is that excel sheet contains 20,000 records which i hav to fetch & insert into access databaseplz provide a solution for it.
Code:
<%@page language="Java"%>
<%@page import="java.io.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@page contentType="application/vnd.ms-excel" %>
<%
Connection con=null;
PreparedStatement stmt;
String parea="",esgrp="",egroup="",personelsub="",halfname="",esubgroup="",costctr="",psubarea="",designation="",position="",name="";
int bc=0,tno=0,eegrp=0,flag=0;
String name1="";
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:deployment","","");
}
catch(Exception e)
{
out.println(e.getMessage());
}
try
{
name1=request.getParameter("file");
out.println("Path="+name1);
out.println("inserted");
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows;
rows = sheet.getPhysicalNumberOfRows();
out.println("Rows are"+rows);
int cols = 0;
int tmp = 0;
for(int i = 0; i < 10 || i < rows; i++)
{
row = sheet.getRow(i);
if(row != null)
{
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp > cols)
cols = tmp;
}
}
for(int r = 0; r < rows; r++)
{
row = sheet.getRow(r);
if(row != null)
{
for(int c = 0; c < cols; c++)
{
int m=c;
cell = row.getCell((short)c);
if(cell != null)
{
if(m==0)
{
parea=cell.getStringCellValue();
}
if(m==1)
{
eegrp=cell.getNumericCellValue();
}
if(m==2)
{
egroup=cell.getStringCellValue();
}
if(m==3)
{
esgrp=cell.getStringCellValue();
}
if(m==4)
{
esubgroup=cell.getStringCellValue();
}
if(m==5)
{
bc=cell.getNumericCellValue();
}
if(m==6)
{
costctr=cell.getStringCellValue();
}
if(m==7)
{
psubarea=cell.getStringCellValue();
}
if(m==8)
{
personelsub=cell.getStringCellValue();
}
if(m==9)
{
tno=cell.getNumericCellValue();
}
if(m==10)
{
halfname=cell.getStringCellValue();
}
if(m==11)
{
designation=cell.getStringCellValue();
}
if(m==12)
{
position=cell.getStringCellValue();
}
if(m==13)
{
name=cell.getStringCellValue();
}
}//if
else
{
if(m==11)
{
designation="";
}
}
}//for
stmt=con.prepareStatement("insert into Axlemain values(?,?,?,?,?,?,?,?,?)");
stmt.setString(1,egroup);
stmt.setString(2,esubgroup);
stmt.setInt(3,bc);
stmt.setString(4,costctr);
stmt.setString(5,personelsub);
stmt.setInt(6,tno);
stmt.setString(7,designation);
stmt.setString(8,position);
stmt.setString(9,name);
int i6=stmt.executeUpdate();
if(i6>0)
{
flag=1;
}
}//if
}//for
if(flag==1)
{
%>
<script language="JavaScript">
alert("Successfully inserted");
</script>
<%
}
}
catch(Exception e)
{
%>
<script language="JavaScript">
alert("Error while inserting");
</script>
<%
out.println(e.getMessage());
}
%>
errors are:
package org.apache.poi.hssf.eventusermodel does not exist
import org.apache.poi.hssf.eventusermodel.HSSFSheet;
package org.apache.poi.hssf.usermodel does not exist
package org.apache.poi.poifs.filesystem does not exist
cannot find symbol
symbol : class POIFSFileSystem
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
^
cannot find symbol
symbol : class POIFSFileSystem
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
^
cannot find symbol
symbol : class HSSFWorkbook
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFWorkbook wb=new HSSFWorkbook(fs);
^
cannot find symbol
symbol : class HSSFWorkbook
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFWorkbook wb=new HSSFWorkbook(fs);
^
cannot find symbol
symbol : class HSSFSheet
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFSheet sheet = wb.getSheetAt(0);
^
cannot find symbol
symbol : class HSSFRow
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFRow row;
^
cannot find symbol
symbol : class HSSFCell
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFCell cell;
^
plz tell me how to remove these errors & to run successfully. is it is a case that i hav to download some POI.jar file for it.plz give me a reply for it. it is very important for me.im in a dangerous situation.plz send me response as fast as possible.
thanks in advance.
i my program i want to fetch data from excel sheet & i hav to insert the selected records from excel sheet to Microsoft Access Database. i hav written the following code for it but it is not working it is giving error meassges.other major problem is that excel sheet contains 20,000 records which i hav to fetch & insert into access databaseplz provide a solution for it.
Code:
<%@page language="Java"%>
<%@page import="java.io.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@page contentType="application/vnd.ms-excel" %>
<%
Connection con=null;
PreparedStatement stmt;
String parea="",esgrp="",egroup="",personelsub="",halfname="",esubgroup="",costctr="",psubarea="",designation="",position="",name="";
int bc=0,tno=0,eegrp=0,flag=0;
String name1="";
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:deployment","","");
}
catch(Exception e)
{
out.println(e.getMessage());
}
try
{
name1=request.getParameter("file");
out.println("Path="+name1);
out.println("inserted");
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows;
rows = sheet.getPhysicalNumberOfRows();
out.println("Rows are"+rows);
int cols = 0;
int tmp = 0;
for(int i = 0; i < 10 || i < rows; i++)
{
row = sheet.getRow(i);
if(row != null)
{
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp > cols)
cols = tmp;
}
}
for(int r = 0; r < rows; r++)
{
row = sheet.getRow(r);
if(row != null)
{
for(int c = 0; c < cols; c++)
{
int m=c;
cell = row.getCell((short)c);
if(cell != null)
{
if(m==0)
{
parea=cell.getStringCellValue();
}
if(m==1)
{
eegrp=cell.getNumericCellValue();
}
if(m==2)
{
egroup=cell.getStringCellValue();
}
if(m==3)
{
esgrp=cell.getStringCellValue();
}
if(m==4)
{
esubgroup=cell.getStringCellValue();
}
if(m==5)
{
bc=cell.getNumericCellValue();
}
if(m==6)
{
costctr=cell.getStringCellValue();
}
if(m==7)
{
psubarea=cell.getStringCellValue();
}
if(m==8)
{
personelsub=cell.getStringCellValue();
}
if(m==9)
{
tno=cell.getNumericCellValue();
}
if(m==10)
{
halfname=cell.getStringCellValue();
}
if(m==11)
{
designation=cell.getStringCellValue();
}
if(m==12)
{
position=cell.getStringCellValue();
}
if(m==13)
{
name=cell.getStringCellValue();
}
}//if
else
{
if(m==11)
{
designation="";
}
}
}//for
stmt=con.prepareStatement("insert into Axlemain values(?,?,?,?,?,?,?,?,?)");
stmt.setString(1,egroup);
stmt.setString(2,esubgroup);
stmt.setInt(3,bc);
stmt.setString(4,costctr);
stmt.setString(5,personelsub);
stmt.setInt(6,tno);
stmt.setString(7,designation);
stmt.setString(8,position);
stmt.setString(9,name);
int i6=stmt.executeUpdate();
if(i6>0)
{
flag=1;
}
}//if
}//for
if(flag==1)
{
%>
<script language="JavaScript">
alert("Successfully inserted");
</script>
<%
}
}
catch(Exception e)
{
%>
<script language="JavaScript">
alert("Error while inserting");
</script>
<%
out.println(e.getMessage());
}
%>
errors are:
package org.apache.poi.hssf.eventusermodel does not exist
import org.apache.poi.hssf.eventusermodel.HSSFSheet;
package org.apache.poi.hssf.usermodel does not exist
package org.apache.poi.poifs.filesystem does not exist
cannot find symbol
symbol : class POIFSFileSystem
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
^
cannot find symbol
symbol : class POIFSFileSystem
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(name1));
^
cannot find symbol
symbol : class HSSFWorkbook
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFWorkbook wb=new HSSFWorkbook(fs);
^
cannot find symbol
symbol : class HSSFWorkbook
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFWorkbook wb=new HSSFWorkbook(fs);
^
cannot find symbol
symbol : class HSSFSheet
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFSheet sheet = wb.getSheetAt(0);
^
cannot find symbol
symbol : class HSSFRow
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFRow row;
^
cannot find symbol
symbol : class HSSFCell
location: class org.apache.jsp.Deployment.upload_005ffile_005fmultipale_jsp
HSSFCell cell;
^
plz tell me how to remove these errors & to run successfully. is it is a case that i hav to download some POI.jar file for it.plz give me a reply for it. it is very important for me.im in a dangerous situation.plz send me response as fast as possible.
thanks in advance.