DB2Excel

 /*********************************************************************/
 /*(c) Copyright IBM Corp. 2003  All rights reserved.                 */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program.                            */
 /*                                                                   */
 /*The sample program is provided to you on an "AS IS" basis, without */
 /*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
 /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
 /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
 /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
 /*limitation of implied warranties, so the above limitations or      */
 /*exclusions may not apply to you.  IBM shall not be liable for any  */
 /*damages you suffer as a result of using, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include a the above copyright notice and disclaimer of */
 /*warranty.                                                          */
 /*                                                                   */
 /*********************************************************************/

package com.ibm.DB2Excel;

  /******************************************************************/
  /* Main class of the DB2Excel application                      */
  /******************************************************************/


import javax.swing.*;
import javax.swing.filechooser.*;
import java.io.File;
import java.awt.*;
import java.awt.event.*;
import java.sql.SQLException;


/**
 *  Main class of the DB2Excel application.
 */

public class DB2Excel extends JFrame implements LoginService {


    /**
     * name of the table being viewed
     */
    private String tableName;


    /**
     * TableModel for the JTable
     */
    private ResultSetTableModel tableModel;


    /**
     * JTable that is used to represent a database table.
     */
    private JTable table;


    /**
     *   Simple facade that hides the details of executing JDBC commands
     */
    private SQLFacade sqlFacade;


    /**
     *    ExcelFileGenerator to create Excel Spreadsheets
     */

    private ExcelFileGenerator generator;
    /**
    *    Filters files of type XLS in the file chooser
    */
   
    private boolean tableLoaded = false;

    public class Utils
    {

        // Extension for Excel files
        public final static String EXCEL = "xls";


        /*
         * Get the extension of a file.
         */ 
        public String getExtension(File f)
        {
             String ext = null;
            String s = f.getName();
            int i = s.lastIndexOf('.');


            if (i > 0 &&  i < s.length() - 1)
            {
                ext = s.substring(i+1).toLowerCase();
            }
            return ext;
        }
    }




    public class CustomFileFilter extends FileFilter
    {
        public boolean accept (File f)
        {
            Utils dummyUtils = new Utils();
            String extension = dummyUtils.getExtension(f);
               if ((extension!=null) && (extension.equals(Utils.EXCEL))) return true;
            else return false;
           
        }


        public String getDescription()
        {
            return ("Microsoft Excel Files");
        }
    }

    /**
     * Centers the dialog box to appear in the middle of the screen
     */
      private void centerDialog() {
        Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();


        int x = ((int) screenSize.getWidth() - (int) (screenSize.getWidth() / 2)) - (int) (getWidth() / 2);
        int y = ((int) screenSize.getHeight() - (int) (screenSize.getHeight() / 2)) - (int) (getHeight() / 2);


        setLocation(x, y);
    }

    // class constructor  - sets up the application
    public DB2Excel() {
        super("DB2 -> Excel");

        // file chooser, restricted to Excel files
        final JFileChooser fc = new JFileChooser();
        CustomFileFilter xlFilter = new CustomFileFilter();
        fc.setFileFilter(xlFilter);


        // build and show the login dialog
        JFrame loginFrame = new JFrame();
        LoginDialog ld = new LoginDialog(loginFrame, "DB2 -> Excel", this);
        ld.show();
        if (ld.isCancelled())
            System.exit(0);


        JPanel topPanel = new JPanel();
        topPanel.setLayout(new BoxLayout(topPanel, BoxLayout.X_AXIS));
        topPanel.setBorder(BorderFactory.createEmptyBorder(0, 10, 10, 10));


        Object[] tableArray = null;
        try {
            // grab the tables of the current database
            tableArray = sqlFacade.getTablesFromDb();
        } catch (SQLException se) {
            MessageBox.showErrorMessageDialog(se, "Error accessing tables in database");
            se.printStackTrace();
        }
        // combo box for choosing a table
        JComboBox tables = new JComboBox(tableArray);
        tables.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                JComboBox cb = (JComboBox) e.getSource();
                tableName = (String) cb.getSelectedItem();
            }
        });

        ImageIcon saveIcon = new ImageIcon("c:\\save.gif");
        JButton create = new JButton("Create My Spreadsheet", saveIcon);
        //listener for the Create button
        create.addActionListener(new ActionListener()
        {
            public void actionPerformed(ActionEvent e)
            {
                try
                {
                    // System.out.println("Call to Create Spreadsheet of " + tableName);
                    // show the file save dialog
                    File destFile;           
                    Exception exc = null;
                     // make sure a source table is specified
                       if ((tableName==null) ||    (tableName.equals(SQLFacade.NO_TABLE)) )
                    {
                        exc = new Exception("A source table must be specified and loaded.");
                        throw exc;
                    }               
                    if (!tableLoaded)
                    {
                        exc = new Exception("A source table must be loaded before creating a spreadsheet.");
                        throw exc;
                    }
                    int returnVal = fc.showSaveDialog(DB2Excel.this);
                    if (returnVal == JFileChooser.APPROVE_OPTION)
                    {
                            destFile = fc.getSelectedFile();
                       
                           // create the excel file for the chosen table
                          
                           generator = new ExcelFileGenerator(destFile.getPath(),tableName,tableModel);
                       
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.showErrorMessageDialog(ex,"An Error Has Occurred.");               
                    ex.printStackTrace();
                }
            }
        });

        ImageIcon loadIcon = new ImageIcon("c:\\open.gif");
        JButton load = new JButton("Load Table",loadIcon);
        // listener for the Go Button
        load.addActionListener(new ActionListener() {
            /**
             * When the load button is hit, the table model is refreshed using the tableName that is selected
             * in the drop down menu.
             */
            public void actionPerformed(ActionEvent e)
            {
                setCursor(Cursor.WAIT_CURSOR);
                try
                {
                    // make sure a table is chosen
                    if ((tableName==null) ||    (tableName.equals(SQLFacade.NO_TABLE)) )
                   {
                        Exception excep = new Exception("Please specify a table.");
                        throw excep;
                   }
                   // load the table & preview it to the user
                   tableModel.reloadTableModel(tableName);
                   for (int i = 0; i < tableModel.getColumnCount(); i++)
                   {
                           table.getColumnModel().getColumn(i).setCellRenderer(new CustomCellRenderer());
                   }
                   tableLoaded=true;
                }
                catch (Exception exc)
                {
                    MessageBox.showErrorMessageDialog(exc, "An Error Has Occured");
                }
                setCursor(Cursor.DEFAULT_CURSOR);
            }
        });


        topPanel.add(new JLabel("Current Table: "));
        topPanel.add(Box.createRigidArea(new Dimension(5, 0)));
        topPanel.add(tables);
        topPanel.add(Box.createRigidArea(new Dimension(5, 0)));
        topPanel.add(load);
        topPanel.add(Box.createHorizontalGlue());
        topPanel.add(create);

        // load the table
        tableModel = new ResultSetTableModel(sqlFacade);
        table = new JTable(tableModel);
        table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);

        JScrollPane scrollPane = new JScrollPane(table);
        getContentPane().add(topPanel, BorderLayout.NORTH);
        getContentPane().add(scrollPane, BorderLayout.CENTER);
           Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
        int x = ((int) screenSize.getWidth() - (int) (screenSize.getWidth() / 2)) - (int) (getWidth() / 2);
        int y = ((int) screenSize.getHeight() - (int) (screenSize.getHeight() / 2)) - (int) (getHeight() / 2);
        getContentPane().setLocation(x, y);
    }




    /**
     * Implementation of the LoginService interface.
     * Connects to a database with the given username and password
     *
     * @return true if connection was made to the database. false if connection failed
     */
    public boolean login(String username, String password, String database, String port) {
        try {
            sqlFacade = new SQLFacade(username, password, database, port);
        } catch (SQLException se) {
            MessageBox.showErrorMessageDialog(se, "Error connecting to database");
            return false;
        } catch (ClassNotFoundException ce) {
            MessageBox.showErrorMessageDialog(ce, "Error connecting to database");
            return false;
        }
        return true;
    }




    static {
        try {
            UIManager.setLookAndFeel(
                    // set the look and feel to metal
                    UIManager.getCrossPlatformLookAndFeelClassName());
        } catch (Exception e) {
            MessageBox.showErrorMessageDialog(e, "Error intializing application");
        }
    }


    /**
     * Inner class used to render edited cells in JTable in a different color.
     */
    class CustomCellRenderer extends javax.swing.table.DefaultTableCellRenderer {




        public Component getTableCellRendererComponent(JTable table, Object value,
                                                       boolean isSelected, boolean hasFocus, int row, int column) {
            if (isSelected)
            {
                return super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
            }
            Component c = super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
            c.setForeground(table.getForeground());
            return c;
        }
    }


    public static void main(String[] args) throws Exception {

        // instantiate an instance of the program
        final DB2Excel progInstance = new DB2Excel();
        progInstance.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });
        progInstance.pack();
        progInstance.centerDialog();
        progInstance.setVisible(true);
    }




}