
i can write only one sheet in a excel file.. but i want to write more than one sheets.. For example: first sheet have complete mean automatically go to second sheet... pls help me.. here my code..
import java.awt.BorderLayout;
public class ExcelInterface extends JFrame implements ActionListener{
public ExcelInterface() {
JCheckBox checkBox = new JCheckBox("");
getContentPane().add(checkBox, BorderLayout.CENTER);
JCheckBox checkBox_1 = new JCheckBox("New check box");
getContentPane().add(checkBox_1, BorderLayout.WEST);
}
javax.swing.JPanel panel;
static javax.swing.JComboBox dblist;
static javax.swing.JTextField ip_add;
static javax.swing.JButton jButton1;
static javax.swing.JButton jButton2;
static javax.swing.JButton jButton3;
static javax.swing.JButton jButton4;
static javax.swing.JButton jButton5;
static javax.swing.JFileChooser jFileChooser1;
static javax.swing.JLabel jLabel1;
static javax.swing.JLabel jLabel2;
static javax.swing.JLabel jLabel3;
static javax.swing.JLabel jLabel7;
static javax.swing.JLabel jLabel4;
static javax.swing.JLabel jLabel5;
static javax.swing.JLabel jLabel6;
static javax.swing.JTextField outputpath;
static javax.swing.JTextField output;
static javax.swing.JPasswordField password;
static javax.swing.JComboBox table;
static javax.swing.JTextField u_name;
public ExcelDB db = null;
public String conString;
String filePath;
DatabaseMetaData dbmd;
String tableName;
static File FilePath;
public void component(){
JFrame f = new JFrame("Excel Interface");
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JPanel panel=new JPanel();
panel.setLayout(null);
Font ff=new Font("SERIF",Font.BOLD,13);
jLabel1= new JLabel("IP Address");
jLabel1.setFont(ff);
jLabel1.setBounds(20,20, 100, 25);
panel.add(jLabel1);
jLabel2= new JLabel("UserName");
jLabel2.setFont(ff);
jLabel2.setBounds(20, 55, 100, 25);
panel.add(jLabel2);
jLabel3= new JLabel("Password");
jLabel3.setFont(ff);
jLabel3.setBounds(20,85, 100, 25);
panel.add(jLabel3);
jLabel4= new JLabel("DBList");
jLabel4.setFont(ff);
jLabel4.setBounds(20,115, 100, 25);
panel.add(jLabel4);
//jLabel5= new JLabel("Table");
// jLabel5.setFont(ff);
//jLabel5.setBounds(20,145, 100, 25);
//panel. add(jLabel5);
jLabel6= new JLabel("Select Folder");
jLabel6.setFont(ff);
jLabel6.setBounds(20,175,175, 25);
panel.add(jLabel6);
// jLabel7= new JLabel("Output File");
//jLabel7.setFont(ff);
//jLabel7.setBounds(20,205, 100, 25);
//panel.add(jLabel7);
ip_add=new JTextField(20);
//ip_add.
ip_add.setFont(ff);
ip_add.setBounds(130,25, 110, 25);
panel.add(ip_add);
u_name =new JTextField(20);
u_name.setFont(ff);
u_name.setBounds(130,55, 110, 25);
panel.add(u_name);
password= new JPasswordField(20);
password.setFont(ff);
password.setBounds(130,85, 110, 25);
panel.add(password);
dblist=new JComboBox();
dblist.setFont(ff);
dblist.setBounds(130,115, 110, 25);
panel.add(dblist);
table=new JComboBox();
table.setFont(ff);
table.setBounds(130,145, 110, 25);
// panel.add(table);
output=new JTextField(20);
output.setFont(ff);
output.setBounds(130,175, 110, 25);
panel.add(output);
//output=new JTextField(20);
//output.setFont(ff);
//output.setBounds(130,205, 110, 25);
//panel.add(output);
jButton1=new JButton("CONNECT");
jButton1.setFont(ff);
jButton1.setBounds(270,115, 110, 35);
jButton1.setEnabled(false);
panel.add(jButton1);
//jButton2=new JButton("LIST");
// jButton2.setFont(ff);
// jButton2.setBounds(270,145, 110, 25);
//panel.add(jButton2);
jButton3=new JButton("BROWSE");
jButton3.setFont(ff);
jButton3.setBounds(270,165, 110, 35);
panel.add(jButton3);
jButton4=new JButton("PROCESS");
jButton4.setFont(ff);
jButton4.setBounds(135,215, 110, 35);
panel.add(jButton4);
jButton5=new JButton("CANCEL");
jButton5.setFont(ff);
jButton5.setBounds(270,215, 110, 35);
panel.add(jButton5);
jFileChooser1=new JFileChooser();
jFileChooser1.setFont(ff);
jFileChooser1.setBounds(135,230,10,20);
panel.add(jFileChooser1);
jButton1.addActionListener(this);
//jButton2.addActionListener(this);
jButton3.addActionListener(this);
jButton4.addActionListener(this);
jButton5.addActionListener(this);
password.addKeyListener(new java.awt.event.KeyAdapter() {
public void keyReleased(java.awt.event.KeyEvent evt) {
if (!ip_add.getText().isEmpty() && !u_name.getText().isEmpty()) {
jButton1.setEnabled(true);
} else {
jButton1.setEnabled(false);
}
}
});
f.getContentPane().add(panel);
f.setSize(450, 300);
f.setVisible(true);
}
@SuppressWarnings({ "deprecation" })
public void actionPerformed(ActionEvent ae) {
String s= ae.getActionCommand();
if(s.equals("CONNECT"))
{
dblist.removeAllItems();
if (!ip_add.getText().isEmpty() && !u_name.getText().isEmpty()) {
// jButton1.setEnabled(true);
//System.out.println("Inside if");
try {
db = new ExcelDB();
System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/", u_name.getText(), password.getText());
dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
ResultSet ctlgs = dbmd.getCatalogs();
while (ctlgs.next()) {
dblist.addItem(ctlgs.getString(1));
}
ExcelDB.con.close();
ctlgs.close(); }
catch (Exception ex) {
System.out.println("Error at focusEvent:" + ex.toString());
JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
jButton1.setEnabled(false);
ip_add.setText(null);
u_name.setText(null);
password.setText(null);
}
} else {
JOptionPane.showMessageDialog(null, "Enter connection Details");
}
}
/* if(s.equals("LIST")){
if (dblist.getItemCount() != 0) {
table.removeAllItems();
try {
System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/" + dblist.getSelectedItem().toString(), u_name.getText(), password.getText());
dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
ResultSet ctlgs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
while (ctlgs.next()) {
table.addItem(ctlgs.getString(3));
}
ctlgs.close();
} catch (Exception ex) {
System.out.println("Error at focusEvent:" + ex.toString());
JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
}
} else {
JOptionPane.showMessageDialog(null, "Choose Your DB");
}
} */
if(s.equals("BROWSE")){
jFileChooser1.setVisible(true);
// FolderChooser chooser = new FolderChooser();
JFileChooser chooser = new JFileChooser();
chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
int o = chooser.showOpenDialog(this.panel);
// chooser.setCurrentDirectory(new java.io.File("."));
chooser.setDialogTitle("choosertitle");
// chooser.setAcceptAllFileFilterUsed(false);
if (o == JFileChooser.APPROVE_OPTION) {
FilePath = chooser.getSelectedFile();
{
output.setText(FilePath.toString());
}
}
}
/*jFileChooser1.setVisible(true);
int o = jFileChooser1.showOpenDialog(this.panel);
if (o == JFileChooser.APPROVE_OPTION) {
filePath = jFileChooser1.getSelectedFile().toString();
{
output.setText(filePath);
}
}
}*/
if(s.equals("PROCESS")){
//table.removeAllItems();
if (output.getText().isEmpty()) {
JOptionPane.showMessageDialog(null, "Select Folder");
} else {
if (dblist.getItemCount() != 0) {
table.removeAllItems();
//System.out.println("printing tableNames");
try {
// System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/" + dblist.getSelectedItem().toString(), u_name.getText(), password.getText());
dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
ResultSet ctlgs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
/* int coun=0;
while(ctlgs.next()) {
coun++;
}
System.out.println("Coun - " + coun);
for(int i=1; i<=coun;i++){ */
String tableNames = "";
while (ctlgs.next()) {
try{
tableNames = ctlgs.getString(3);
convertExcel(tableNames);
// System.out.println(tableNames);
} catch (Exception e) {
JOptionPane.showMessageDialog(null,"Error in table name: "+tableNames);
//e.printStackTrace();
//System.exit(0);
} }
//}
ctlgs.close();
JOptionPane.showMessageDialog(null, "Process Completed");
} catch (Exception ex) {
System.out.println("Error at focusEvent:" + ex.toString());
JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
}
} else {
JOptionPane.showMessageDialog(null, "Choose Your DB");
//System.out.println("I'm here");
}
} }
if(s.equals("CANCEL")){
System.exit(0);
}
}
@SuppressWarnings("deprecation")
public static void convertExcel(String tableName) throws SQLException
{
System.out.println("Processing "+tableName);
int rowCnt = 0;
ResultSet rsCount = null;
PreparedStatement stmt1 =ExcelDB.con.prepareStatement("select count(*) from "+tableName+"");
rsCount = stmt1.executeQuery();
rsCount.next();
rowCnt = rsCount.getInt(1);
System.out.println("rowcount:"+rowCnt);
if(rowCnt<65536)
{
try{
HSSFWorkbook exbook=new HSSFWorkbook();
HSSFSheet exsheet=exbook.createSheet();
int rowIndex=0;
ResultSet rs = null;
try{
//PreparedStatement stmt =ExcelDB.con.prepareStatement("select * from " +table.getSelectedItem() );
/*PreparedStatement stmt1 =ExcelDB.con.prepareStatement("select count (*) from "+tableName+"");
ResultSet rs1 = stmt1.executeQuery();
if(rs1 != null){*/
PreparedStatement stmt =ExcelDB.con.prepareStatement("select * from "+tableName+"");
//stmt.setFetchSize(1000);
rs = stmt.executeQuery();
//System.out.println("select * from" +table.getSelectedItem());
ResultSetMetaData metadata = rs.getMetaData();
List<String> colNames = new ArrayList<String>();
HSSFRow titleRow = exsheet.createRow(rowIndex++);
for (int i = 1; i <= metadata.getColumnCount(); i++) {
colNames.add(metadata.getColumnName(i));
titleRow.createCell((short) (i-1)).setCellValue(
new HSSFRichTextString(metadata.getColumnName(i)));
exsheet.setColumnWidth((short) (i-1), (short) 4000);
}
while (rs.next()) {
HSSFRow dataRow = exsheet.createRow(rowIndex++);
// System.out.println(rowIndex);
short colIndex = 0;
for (String colName : colNames) {
dataRow.createCell(colIndex++).setCellValue(
new HSSFRichTextString(rs.getString(colName)));
//System.out.println(rs.getString(colName));
}
}
String FilePathName = FilePath.toString();
FilePathName = FilePathName+"/"+tableName+".xls";
exbook.write(new FileOutputStream(FilePathName));
System.out.println("Completed "+tableName);
}catch(Exception es){
JOptionPane.showMessageDialog(null, es);
//System.exit(0);
//es.printStackTrace();
}
}
catch (Exception e1) {
JOptionPane.showMessageDialog(null,"error");
//JOptionPane.showMessageDialog(null,tableName);
}
}//rowcount
else{
JOptionPane.showMessageDialog(null,"Table "+tableName+" having more than 65536 rows");
System.out.println("Completed "+tableName);
}
}
public static void main(String args[]){
/*try {
System.setOut(new java.io.PrintStream(new java.io.FileOutputStream("Outputpart.txt")));
System.setErr(new java.io.PrintStream(new java.io.FileOutputStream("Errors.txt")));
} catch (FileNotFoundException e) {
e.printStackTrace();
}*/
ExcelInterface ei=new ExcelInterface();
ei.component();
}
}
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.