Thursday, April 14, 2011

MySQL DB Table DESC to Excel Sheet Export

java code :
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tabledescription;

import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import java.io.IOException;
import java.io.File;
import jxl.Range;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 *
 * @author yogi
 */
public class Main {

    public static void main(String[] args) throws IOException {
        Connection connection = null;

        WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/my.xls"));
        WritableSheet sheet = workbook.createSheet("mySheet", 0);

        WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.SINGLE, jxl.format.Colour.BROWN);
        WritableCellFormat arial10format = new WritableCellFormat(arial10font);

        WritableFont detailfont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat detailformat = new WritableCellFormat(detailfont);

        WritableFont Headerfont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.DOUBLE_ACCOUNTING, jxl.format.Colour.BLUE_GREY);
        WritableCellFormat Headerformat = new WritableCellFormat(Headerfont);



        FileWriter fw = new FileWriter(new File("D:/my.csv"));
        fw.write("asdf");
        fw.close();

        try {
// Load the JDBC driver
            String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
            Class.forName(driverName);

// Create a connection to the database
            String serverName = "localhost";
            String mydatabase = "mysql";
            String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
            String username = "root";
            String password = "";
            connection = DriverManager.getConnection(url, username, password);

            Statement statment = connection.createStatement();

            ResultSet resultSet = statment.executeQuery("show tables");

            resultSet.next();
            int j = 0;

            while (resultSet.isLast() != true) {

                Range range = sheet.mergeCells(0, j, 5, 0);

                Label label = new Label(0, j, resultSet.getString(1), arial10format);
                sheet.addCell(label);

                System.out.println("Tables : " + resultSet.getString(1));

                Statement statment1 = connection.createStatement();
                ResultSet resultSet1 = statment1.executeQuery("desc " + resultSet.getString(1));

                ResultSetMetaData RSM1 = resultSet1.getMetaData();

                /*                Label label8 = new Label(1, j + 1, "Field", Headerformat);
                sheet.addCell(label8);

                label8 = new Label(2, j + 1, "Type", Headerformat);
                sheet.addCell(label8);

                label8 = new Label(3, j + 1, "Null", Headerformat);
                sheet.addCell(label8);
                 */
                resultSet1.next();

                while (resultSet1.isLast() != true) {
                    int k = j++;
                    for (int i = 1; i <= RSM1.getColumnCount(); i++) {
                        Label label1 = new Label(i, k++, resultSet1.getString(i), detailformat);
                        sheet.addCell(label1);
                        System.out.print(resultSet1.getString(i) + " - ");
                    }
                    System.out.println();
                    resultSet1.next();
                }
                resultSet.next();
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            System.out.println("Error : " + e);
        }
    }
}


No comments:

Post a Comment

Disqus for yogi's talk

comments powered by Disqus