Jump to content


Import Data From Access Or Excel Tables


  • Please log in to reply
4 replies to this topic

#1 Free Will

Free Will

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 31 December 2008 - 05:02 PM

Hi all and happy New Year biggrin.gif

I am a student and I have problems creating a GUI app that reads data  from MS Access or Excel table and displays it in a JTable component.

I have no prior experience with Swing or JDBC, but I have created simple apps in C++ and Java.

With the help of Sun's  tutorials I managed to make the simple GUI , but I need your help with the reading from DB file part.
I am using JDK 6.0 Update 11.

Thanks all who join this thread and help me complete the task happy.gif

So far i have done this:
CODE
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.AbstractTableModel;
    
public class NewFrame extends JFrame {
            
    private JButton exitButton = new JButton("Exit");    
    private JTable table = null;
        
    public NewFrame() {
        
        table = new JTable( new MyTableModel() );        
        table.setAutoCreateRowSorter(true);
                                                                          
        JScrollPane scrollPane = new JScrollPane(table);
        scrollPane.setPreferredSize(new Dimension(400, 100));
        add(scrollPane);
        
        exitButton.addActionListener
        (
            new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                    System.exit(0);
                }
            }
        );
            
        add(exitButton, BorderLayout.PAGE_END);
              
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);            
        pack();          
        setLocationRelativeTo(null);
     }
        
        
  class MyTableModel extends AbstractTableModel {
            
        String[] columnNames = { "First Name", "Last Name", "Sport", "# of Years", "Vegetarian" };
        
        Object[][] data = {
            { "Mary", "Campione", "Snowboarding", new Integer(5), new Boolean(false) },
            { "Alison", "Huml", "Rowing", new Integer(3), new Boolean(true) },
            { "Kathy", "Walrath", "Knitting", new Integer(2), new Boolean(false) },
            { "Sharon", "Zakhour", "Speed reading", new Integer(20), new Boolean(true) },
            { "Philip", "Milne", "Pool", new Integer(10), new Boolean(false) }
        };

        public int getColumnCount() {
            return columnNames.length;
        }

        public int getRowCount() {
            return data.length;
        }

        public String getColumnName(int col) {
            return columnNames[col];
        }

        public Object getValueAt(int row, int col) {
            return data[row][col];
        }
  
        public Class getColumnClass(int c) {
            return getValueAt(0, c).getClass();
        }

       public boolean isCellEditable(int row, int col) {
            return true;  
        }
        
       public void setValueAt(Object value, int row, int col) {
           
           data[row][col] = value;
           fireTableCellUpdated(row, col);
       }
    }
                                            
    public static void main(String[] args) {      
        NewFrame frame = new NewFrame();
        frame.setVisible(true);
    }
}


#2 Free Will

Free Will

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 20 January 2009 - 01:38 PM

Hi all smile.gif

I completed the task with the help of POI.

With this java interface you can read and modify data in MS Excel XLS files wink.gif

Here is the source code of the simple GUI app that reads from XLS files:
CODE
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


public class NewFrame extends JFrame {                  
            
public NewFrame() throws IOException {
            
       setTitle("Your Title");
            
       FileInputStream in = new FileInputStream("C:\\Your File.xls");
      
       POIFSFileSystem fs = new POIFSFileSystem(in);
      
       HSSFWorkbook wb = new HSSFWorkbook(fs);
      
       HSSFSheet sheet = wb.getSheetAt(0);
      
       int nCols = 0, nRows = sheet.getPhysicalNumberOfRows();
      
       HSSFRow row = sheet.getRow(0);
      
       if(row != null)
             nCols = row.getPhysicalNumberOfCells();
            
       else {
             in.close();
             System.exit(0);
       }
            
            
       String[] columnNames = new String[nCols];
      
       HSSFCell cell;
    
       for (int i = 0; i < nCols; i++) {
            
             cell = row.getCell(i);
            
             columnNames[i] = cell.toString();

       }
      
            
       Object[][] data = new Object[nRows-1][nCols];
      
       for (int i = 1; i < nRows; i++) {
            
             row = sheet.getRow(i);
            
             for (int j = 0; j < nCols; j++) {
                  
                   cell = row.getCell(j);
                  
                   data[i-1][j] = cell.toString();
             }
       }
                                                            
       in.close();  
                                                                                      
            
            JTable table = new JTable(data, columnNames);
            
            table.setAutoCreateRowSorter(true);
            
                                                                                        
          JScrollPane scrollPane = new JScrollPane(table);
        
          scrollPane.setPreferredSize(new Dimension(700, 150));
          
          add(scrollPane);
          
          
            JButton exitButton = new JButton("Изход");
            
            exitButton.addActionListener
       (
           new ActionListener() {
               public void actionPerformed(ActionEvent e) {
                   System.exit(0);
               }
           }
       );  
                
       add(exitButton, BorderLayout.PAGE_END);
      
              
       setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
              
       pack();    
      
       setLocationRelativeTo(null);
      
      }
      
                                                                    
   public static void main(String[] args) {
        
         NewFrame frame = null;
        
         try {
               frame = new NewFrame();
         }
      
         catch (Exception e) {
               e.printStackTrace();
       }
  
       frame.setVisible(true);
   }
}


Hope it's helpful, greetings smile.gif

#3 runbrett01

runbrett01

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 28 December 2009 - 06:03 PM

QUOTE (Free Will @ Jan 20 2009, 07:38 AM) <{POST_SNAPBACK}>
Hi all smile.gif

I completed the task with the help of POI.

With this java interface you can read and modify data in MS Excel XLS files wink.gif

Here is the source code of the simple GUI app that reads from XLS files:
CODE
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


public class NewFrame extends JFrame {                  
            
public NewFrame() throws IOException {
            
       setTitle("Your Title");
            
       FileInputStream in = new FileInputStream("C:\\Your File.xls");
      
       POIFSFileSystem fs = new POIFSFileSystem(in);
      
       HSSFWorkbook wb = new HSSFWorkbook(fs);
      
       HSSFSheet sheet = wb.getSheetAt(0);
      
       int nCols = 0, nRows = sheet.getPhysicalNumberOfRows();
      
       HSSFRow row = sheet.getRow(0);
      
       if(row != null)
             nCols = row.getPhysicalNumberOfCells();
            
       else {
             in.close();
             System.exit(0);
       }
            
            
       String[] columnNames = new String[nCols];
      
       HSSFCell cell;
    
       for (int i = 0; i < nCols; i++) {
            
             cell = row.getCell(i);
            
             columnNames[i] = cell.toString();

       }
      
            
       Object[][] data = new Object[nRows-1][nCols];
      
       for (int i = 1; i < nRows; i++) {
            
             row = sheet.getRow(i);
            
             for (int j = 0; j < nCols; j++) {
                  
                   cell = row.getCell(j);
                  
                   data[i-1][j] = cell.toString();
             }
       }
                                                            
       in.close();  
                                                                                      
            
            JTable table = new JTable(data, columnNames);
            
            table.setAutoCreateRowSorter(true);
            
                                                                                        
          JScrollPane scrollPane = new JScrollPane(table);
        
          scrollPane.setPreferredSize(new Dimension(700, 150));
          
          add(scrollPane);
          
          
            JButton exitButton = new JButton("Изход");
            
            exitButton.addActionListener
       (
           new ActionListener() {
               public void actionPerformed(ActionEvent e) {
                   System.exit(0);
               }
           }
       );  
                
       add(exitButton, BorderLayout.PAGE_END);
      
              
       setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
              
       pack();    
      
       setLocationRelativeTo(null);
      
      }
      
                                                                    
   public static void main(String[] args) {
        
         NewFrame frame = null;
        
         try {
               frame = new NewFrame();
         }
      
         catch (Exception e) {
               e.printStackTrace();
       }
  
       frame.setVisible(true);
   }
}


Hope it's helpful, greetings smile.gif



#4 runbrett01

runbrett01

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 28 December 2009 - 06:07 PM

Hi,

Thanks for the information.  I am trying something similar, but I'm having trouble with setting up the POI files in JCreator.  Which of the files need to be included under the Configure>Options>JDK Profiles?  

Do I add them to the JDK file or create them separately?

Do I need my program to be in a Project?

Thanks,

Matt

#5 Captain Pierce

Captain Pierce

    Advanced Member

  • Moderator
  • PipPipPip
  • 877 posts
  • Gender:Male
  • Location:Georgia

Posted 28 December 2009 - 09:20 PM

Yes, your program should be in a project, which that project should be in a workspace.

I don't have access to an install of Jcreator at the moment, so the exact steps you need to take escape me, but you need to do something like Project Settings or Runtime Config, click default then an edit button, and look for "add library". You'll need to make a new library, point it to the POI jar file(s), then make sure that library's actually selected and it will be included in the compile/run paths.

edit: Sorry I lied. I forgot JCreator survived the "clean" install of Windows 7 lol

Anyways goto "Project Settings" -> "Required Libraries" tab. Click "New", a dialog will appear. Enter a name, e.g. POI. Select "Add Archive" and find the jar file(s) for the POI system. Click "Ok". Make sure to check the library you just added in the "Required Libraries" tab and you should be good to go.