-----------------------

for comments mail me to

-----------------------
ravikumar17jan@[Link]

ravibecks2300@[Link]

-------------
Preconditions
-------------

The name of each individual excel sheet should be the same as the name
of the mysql table to which you are converting

Number of columns in the excel sheet should be the same as the number of
columns in the mysql table to which you are converting

----------------------------------------------------
How to Run this application
----------------------------------------------------

[Link] Java Excel API from [Link]

[Link] [Link] file from the downloaded zip or [Link] to your classpath

[Link] all the three files given below in the same location

[Link] the following in the [Link] and save it

yourhost with hostip or host name in which your mysql server is running
yourdb with mysql server DB name
yourname with your mysql user account name having previlage to modify above DB
yourpass with the password for above user account

[Link] javac [Link]

[Link] java ExToMy

7. if everything is done correctly then window loaded with all the tables
in the DB is displayed

8. browse and load the xls file

9. select the sheet name and corresponding mysql table name and click convert

10. if all the preconditions are satisfied then conversion is done successfully

----------------------------------------------------
Save this code as [Link]
----------------------------------------------------
import [Link].*;
import [Link].*;

public class DBConnection {

public Connection con;

FileInputStream fi;
BufferedReader buff;
 String line="";
String[] lines=new String[30];
int i=0;
public DBConnection() {

try {

fi=new FileInputStream("[Link]");
buff=new BufferedReader(new InputStreamReader(fi));
i=0;
while((line=[Link]())!=null){
lines[i]=line;
i++;
}
String host=lines[1].trim();
String database =lines[3].trim();
String user =lines[5].trim();
String password = lines[7].trim();
[Link]("[Link]");
con = [Link]("jdbc:mysql://"+host+"/" + database,
user, password);

} catch (Exception exe) {

[Link]("fof " + exe);
}
}
}
----------------------------------------------------------------------------------
--------------------
----------------------------------------------------
Save this code as [Link]
----------------------------------------------------
#hostname or IP address
yourhost
#Database name
yourdb
#Database server User name
yourname
#Database server password(leave next line blank if no password)
yourpass
#end
----------------------------------------------------------------------------------
--------------------
----------------------------------------------------
Save this code as [Link]
----------------------------------------------------

import jxl.*;
import [Link].*;
import [Link].*;
import [Link].*;
import [Link].*;
import [Link].*;
import [Link].*;
public class ExToMy extends JFrame implements ActionListener{
/*swing components*/
private JLabel l_xlfile,l_xlsheets,l_mytables;
private JTextField xlfile;
private JComboBox xlsheets,mytables;
private JButton browse,convert,reload_file;
private JFileChooser filechoose;
private GridBagLayout gbl;
private GridBagConstraints gbc;
/*swing components*/
[Link] my_fields_type;
/*DB*/
DBConnection db;
Statement stat;
ResultSet rs;
/*DB*/
/*excel*/
Workbook workbook;
private String[] sheet_names;
/*excel*/
public ExToMy(){
initComponents();
/*DB*/
try{
db=new DBConnection();
stat=[Link]();
}
catch(Exception exe){}
initDB();
/*DB*/

/*Layout settings*/

gbl=new GridBagLayout();
gbc=new GridBagConstraints();
[Link]=1;
[Link]=1;
[Link]=[Link];
posComponent(l_xlfile,gbl,gbc,1,1);
posComponent(l_xlsheets,gbl,gbc,1,3);
posComponent(l_mytables,gbl,gbc,1,4);
posComponent(xlfile,gbl,gbc,2,1);
[Link]=[Link];
[Link]=[Link];
posComponent(reload_file,gbl,gbc,1,2);
posComponent(browse,gbl,gbc,2,2);
[Link]=[Link];
[Link]=[Link];
posComponent(xlsheets,gbl,gbc,2,3);
posComponent(mytables,gbl,gbc,2,4);
[Link]=[Link];
posComponent(convert,gbl,gbc,2,5);
[Link]=[Link];
setLayout(gbl);
setTitle("Excel to mysql Converter");
setSize(500,500);
setVisible(true);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
/*Layout settings*/
}

public void initComponents(){

 l_xlfile=new JLabel("Select a ms excel file to open");
l_xlsheets=new JLabel("Worksheets in workbook");
l_mytables=new JLabel("MySQL tables");

xlfile=new JTextField();

xlsheets=new JComboBox();
mytables=new JComboBox();

browse=new JButton("Browse");
[Link](this);
reload_file=new JButton("Reload File");
reload_file.addActionListener(this);
convert=new JButton("Convert");
[Link](this);

filechoose=new JFileChooser();
my_fields_type=new ArrayList();
}

public void initDB(){

try{
[Link]();
rs=[Link]("SHOW TABLES");
while([Link]()){
[Link](""+[Link](1));
}

}
catch(Exception exe){[Link](""+exe);}
}

public void posComponent(Component comp,GridBagLayout gbl,GridBagConstraints

gbc,int posx,int posy){
[Link]=posx;
[Link]=posy;
[Link](comp,gbc);
getContentPane().add(comp);
}

public void openXls(){

int val=[Link](this);
if(val == JFileChooser.APPROVE_OPTION) {
/*excel*/
try{
workbook=
[Link]([Link]());

[Link]([Link]().getPath());
sheet_names=[Link](); //A string
array of sheet names is returned
[Link]();
for(int i=0;i<sheet_names.length;i++){
[Link](""+sheet_names[i]);
}
}
catch(Exception
exe){[Link](this,(String)"Select a valid excel(.xls)
file");}
}
}

public void actionPerformed(ActionEvent ev){

Object source=[Link]();
if(source==browse){
openXls();

}
if(source==reload_file){
try{
workbook=
[Link]([Link]());
sheet_names=[Link](); //A string array of
sheet names is returned
[Link]();
for(int i=0;i<sheet_names.length;i++){
[Link](""+sheet_names[i]);
}
}
catch(Exception exe){}

}
if(source==convert){
if([Link]()<=0){
[Link](this,(String)"Select a valid
excel(.xls) file to select a worksheet");
}
else{
try{
int xl_fields=0,my_fields=0,xl_row_count=0;
String insert_query="";
String
sel_sheet_name=""+[Link]();
String
sel_table_name=""+[Link]();
if(sel_sheet_name.equals(sel_table_name)){
Sheet
sheet=[Link]([Link]());
xl_fields=[Link]();
my_fields_type.clear();
rs=[Link]("DESCRIBE
"+sel_table_name);
while([Link]()){
my_fields++;

my_fields_type.add([Link](2));
}
if(xl_fields==my_fields){

//[Link](this,(String)"Number of Fields are equal");

xl_row_count=[Link]();
[Link](xl_row_count);//
for(int j=0;j<xl_row_count;j++){
insert_query="INSERT INTO
"+sel_table_name+" VALUES(";
 for(int i=0;i<xl_fields;i++){
String
field_type=""+my_fields_type.get(i);

if(field_type.indexOf("int")>=0||field_type.indexOf("float")>=0||
field_type.indexOf("double")>=0){

insert_query+=[Link](i,j).getContents().toString()+",";
}
else{

insert_query+="\'"+[Link](i,j).getContents()+"\',";
}
}

insert_query=insert_query.substring(0,insert_query.length()-1)+")";

[Link](insert_query);
try{

[Link](insert_query);
}
catch(Exception
exe){[Link](this,(String)"Row No "+j+"
"+exe);[Link](""+exe);}
}

[Link](this,(String)"All rows Successfully copied");

}
else

[Link](this,(String)"Number of Fields are not

equal");
}
else

[Link](this,(String)"Select same worksheet name as

mysql table");
}
catch(Exception exe){[Link](""+exe);}
}
}
}

public static void main(String[] args){

ExToMy exm=new ExToMy();

}
----------------------------------------------------------------------------------
--------------------