exceldb2.java

将DB2数据库里的数据导出为excel文件的java代码

/*********************************************************************/
/*(c) Copyright IBM Corp. 2004  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.ExcelDB2;

/*********************************************************************/
/* 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;
import org.apache.poi.hssf.usermodel.*;

/**
 *  Main class of the DB2Excel application.
 */
public class ExcelDB2 extends JFrame implements LoginService {

	/**
	*	Filters files of type XLS in the file chooser
	*/	
	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;
		}
	}

	/**
	*  provides a custom file filter to FileChooser for Excel spreadsheet files
	*/
	public class CustomFileFilter extends FileFilter {
		/**
		* @see javax.swing.filechooser.FileFilter#accept(File)
		*/
		public boolean accept (File f) {
			// Always allow the user to see directories in order to navigate
			if (f.isDirectory()) {
				return true;
			}
			Utils dummyUtils = new Utils();
			String extension = dummyUtils.getExtension(f);
   			if ((extension!=null) && (extension.equals(Utils.EXCEL))) return true;
			else return false;			
    	}
		/**
		* @see javax.swing.filechooser.FileFilter#getDescription()
		*/
		public String getDescription() {
			return ("Microsoft Excel Files");
		}
	}

	/**
	*  Flag indicating whether a spreadsheet has been loaded.
	*/
	private boolean spreadsheetLoaded = false;

	/**
	* name of the Spreadsheet being viewed
	*/
	private String spreadsheetName;

	/**
	* SpreadsheetModel for the JTable
	*/
	private SpreadsheetTableModel spreadsheetModel;

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

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

	/**
	*	ExcelFileGenerator to create Excel Spreadsheets
	*/
	private DBTableGenerator generator;

	/**
	* 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
	* @see java.lang.Object#Object()
	*/
    public ExcelDB2() {
    	// Create the application frame with the the application title
		super("Excel -> DB2");
		// Create file chooser
		final JFileChooser fc = new JFileChooser();
		// restrict the fileChooser to Excel files
		CustomFileFilter xlFilter = new CustomFileFilter();
		fc.setFileFilter(xlFilter);
		// build and show the login dialog
		JFrame loginFrame = new JFrame();
		LoginDialog ld = new LoginDialog(loginFrame, "Excel -> DB2", this);
		ld.show();
		// Exit if user did not login.
		if (ld.isCancelled()) System.exit(0);
		// Set up application window components
		JPanel topPanel = new JPanel();
		topPanel.setLayout(new BoxLayout(topPanel, BoxLayout.X_AXIS));
		topPanel.setBorder(BorderFactory.createEmptyBorder(0, 10, 10, 10));
		Object[] spreadsheetArray = null;
		// text field for entering a DB Table name
		final JTextField tableNameField = new JTextField("New_Table");
		ImageIcon openIcon = new ImageIcon("open.gif");
		JButton loadspreadsheet = new JButton("Load My Spreadsheet", openIcon);
		ImageIcon writeIcon = new ImageIcon("write.gif");
		JButton writeDBTable = new JButton("Write DB Table",writeIcon);
		// Add all applciation window components
		topPanel.add(new JLabel("Table to Create: "));
		topPanel.add(Box.createRigidArea(new Dimension(5, 0)));
		topPanel.add(tableNameField);
		topPanel.add(Box.createHorizontalGlue());
		topPanel.add(loadspreadsheet);
		topPanel.add(Box.createRigidArea(new Dimension(5, 0)));
		topPanel.add(writeDBTable);
		// load the (initially empty table
		spreadsheetModel = new SpreadsheetTableModel();
		spreadsheet = new JTable(spreadsheetModel);
		spreadsheet.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		JScrollPane scrollPane = new JScrollPane(spreadsheet);
		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);
		//listener for the "load my spreadsheet" button
		loadspreadsheet.addActionListener(new ActionListener() {
        	public void actionPerformed(ActionEvent e) {
        		try {
					// show the file open dialog
					int returnVal = fc.showOpenDialog(ExcelDB2.this);
					// load the spreadsheet & preview it to the user
					if (returnVal == JFileChooser.APPROVE_OPTION) {
						File sourceFile = fc.getSelectedFile();
						spreadsheetModel.reloadSpreadsheetModel(sourceFile.getPath());
						// Use the Worksheet name extracted from the Excel Spreadsheet for the default Table name
						tableNameField.setText(spreadsheetModel.getSpreadsheetName());
						// Apply custom renderer to display Excel data in string form
						for (int i = 1; i < spreadsheetModel.getColumnCount(); i++) {
							spreadsheet.getColumnModel().getColumn(i).setCellRenderer(new ExcelCellRenderer());
						}
						spreadsheetLoaded=true;
					}
				} catch(Exception ex) {
					MessageBox.showErrorMessageDialog(ex,"Unable to load selected file.");				
					ex.printStackTrace();
				}
			}
        });
		// listener for the "create database" Button
		writeDBTable.addActionListener(new ActionListener() {
			/**
			* When the write button is hit, the spreadsheet model is refreshed using the spreadsheetName that is selected in the text field.
			*/
			public void actionPerformed(ActionEvent e) {
				setCursor(new Cursor(Cursor.WAIT_CURSOR));
				try {
					// Extract the table name from the provided text field.
					spreadsheetName=tableNameField.getText();
					// make sure a source spreadsheet is specified
					if (!spreadsheetLoaded) {
						throw new Exception("A source spreadsheet must be loaded before creating a DB Table.");
					}
					// make sure a table name is chosen
					if ((spreadsheetName==null) || (spreadsheetName.equals("")) ) {
						throw new Exception("Please specify a Table Name.");
					}
					// Make sure a table of the same name doesn't already exist in the database
					if (sqlFacade.checkTableConflict(spreadsheetName)) {
						throw new Exception("A Table by that name already exists, please enter a different name.");
					}					
					// create the DB Table for the chosen spreadsheet
					generator = new DBTableGenerator(spreadsheetName, spreadsheetModel, sqlFacade);
				} catch (Exception exc) {
					MessageBox.showErrorMessageDialog(exc, "An error writing the spreadsheet to the DB has occured.");
				}
				setCursor(new Cursor(Cursor.DEFAULT_CURSOR));
			}
		});
	}

	/*
	* Method login.
	* 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
	* @see com.ibm.ExcelDB2.LoginService#login(String, String, String, String)
	*/
	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, "Class not Found");
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
 		
		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);
			}
			JLabel c = (JLabel)(super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column));
			c.setForeground(table.getForeground());
			return c;
		}
	}
		
	/**
	* Inner class used to render Excel Spreadsheet cells in JTable as strings.
	*/
	class ExcelCellRenderer extends CustomCellRenderer {
		public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
			super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
			JLabel c = (JLabel)(super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column));
			c.setText(((HSSFCell)value).getStringCellValue());
			return c;
		}
	}

	/**
	 * Method main.
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
	// instantiate an instance of the program
		final ExcelDB2 progInstance = new ExcelDB2();
		progInstance.addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent e) {
				System.exit(0);
			}
		});
		progInstance.pack();
		progInstance.centerDialog();
		progInstance.setSize(new Dimension(500, 200));
		progInstance.setVisible(true);
	}
}