Hi, i want to write more than 65536 rows in single excel file but more than 1 sheets.. now i can write only less than 65536 rows.. but in my office db having more than 1 crore data.. pls help me... here is my code..
public class ExcelInterface extends JFrame implements ActionListener{
public ExcelInterface() {
JCheckBox checkBox = new JCheckBox("");
getContentPane().add(checkBox, BorderLayout.CENTER);
}
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);
}
}
});
dblist.addItemListener(new ItemListener(){
public void itemStateChanged(ItemEvent ie){
/* 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"});
while (ctlgs.next()) {
//table.addItem(ctlgs.getString(3));
//@SuppressWarnings("unused")
//tableNames.add(ctlgs.getString(3));
String tableNames = ctlgs.getString(3);
System.out.println(tableNames);
}
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");
//System.out.println("I'm here");
}*/
}
});
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();
}
}
It is database connection code...
import java.sql.*;
import javax.swing.JOptionPane;
public class ExcelDB {
public static Connection con;
public Connection con1;
PreparedStatement stmt=null;
ExcelDB(){
}
public static void connection(String conString,String user,String pass)throws Exception{
try{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection(conString,user,pass);
System.out.println("connected With DB");
}catch(ClassNotFoundException cnfe){
System.out.println("Error..."+cnfe);
JOptionPane.showMessageDialog(null, "Class Not Found");
}
catch(SQLException sqle){
System.out.println("SQL Error..."+sqle);
JOptionPane.showMessageDialog(null, "Connection is Empty");
}
}
public void closeDB()throws Exception{
if(!con.isClosed()){
con.close();}
}
}
kindly pls rep me... thanks...
Ads
