JSP import Excel

Package com. LSF;

 

Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfcell;

Import java. SQL .*;
Import java. Io .*;
Import java. util. arraylist;
Import java. SQL. connection;

Public class readexceltest
{
Private connection con;
String url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = test ";
String dbdriver = "com. Microsoft. JDBC. sqlserver. sqlserverdriver ";
String user = "sa ";
String Pwd = "sa ";

Private arraylist listfirstcolum = new arraylist ();
Private arraylist listsecondcolum = new arraylist ();

// Connect to the database
Public readexceltest () throws exception
{
Try
{
Class. forname (dbdriver );
Con = drivermanager. getconnection (URL, user, PWD );
}
Catch (exception E)
{
E. printstacktrace ();
Throw (new exception ("sorry, database connecting failed! "));
}
}

// Read data from Excel and save it to arraylist
Public void readexcel (string sfilename)
{
Try
{
Hssfworkbook workbook = new hssfworkbook (New fileinputstream (sfilename); // read an Excel file
Hssfsheet sheet = Workbook. getsheetat (0); // read the first sheet
For (INT I = 0; I <= sheet. getlastrownum (); I ++) // loop to get all rows
{
If (sheet. getrow (I )! = NULL)
{
Hssfrow Arow = sheet. getrow (I); // number of rows
Hssfcell cellfirstcolum = Arow. getcell (short) 0); // The first column
Hssfcell cellsecondcolum = Arow. getcell (short) 1); // The second column
// Store the data in the list for backup
Listfirstcolum. Add (INT) cellfirstcolum. getnumericcellvalue (); // assume that the first column is Numeric.
Listsecondcolum. Add (cellsecondcolum. getstringcellvalue (); // assume that the second column is of string type.
}
}
System. Out. println ("Excel read successful! ");
} Catch (exception E)
{
E. printstacktrace ();
}
}

// Write data from arraylist to the database
Public void insertdata (string sfilename)
{
// Read
Readexcel (sfilename );

String sqlinsert = "insert into impexcel"
+ "(First_colum, second_colum) values (?,?) ";
// Insert
Try
{
For (INT I = 0; I <listfirstcolum. Size (); I ++ ){
Preparedstatement pstmt = con. preparestatement (sqlinsert );
Pstmt. setint (1, integer. parseint (listfirstcolum. Get (I). tostring ()));
Pstmt. setstring (2, listsecondcolum. Get (I). tostring ());
Pstmt.exe cute ();
System. Out. println ("importing" + (I + 1) + "record ");
}
System. Out. println ("imported! ");
} Catch (exception E)
{
E. printstacktrace ();
}
}
}