any
business applications have been developed using Java and
more are in development. Many of these applications display
data in a spreadsheet-like format using Swing's JTable
component. It would be convenient if these business
applications could import and export data to and from
Microsoft Excel so that users could have access to the power
of that omnipresent spreadsheet program. This Java Tip will
help you understand the system clipboard and enable your
JTable to interact and interoperate with Excel. As you'll
see, adding this valuable functionality can be accomplished
by adding just one extra line of code to your existing
applications.
All you need to do to achieve this goal is copy the file
ExcelAdapter.java represented here, compile it,
and make sure your applications can find the
ExcelAdapter.class file; once you've done that, your
JTable is ready to talk with Excel! We'll show you how, with
this single line of code, to enable copy (control-C) and
paste (control-V) to and from Excel. A sample application
using the ExcelAdapter is also provided.
The code
Here is the adapter code, called ExcelAdapter.java ,
that actually accomplishes this task:
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.awt.datatransfer.*;
import java.util.*;
/**
* ExcelAdapter enables Copy-Paste Clipboard functionality on
JTables.
* The clipboard data format used by the adapter is
compatible with
* the clipboard format used by Excel. This provides for
clipboard
* interoperability between enabled JTables and Excel.
*/
public class ExcelAdapter implements ActionListener
{
private String rowstring,value;
private Clipboard system;
private StringSelection stsel;
private JTable jTable1 ;
/**
* The Excel Adapter is constructed with a
* JTable on which it enables Copy-Paste and acts
* as a Clipboard listener.
*/
public ExcelAdapter(JTable myJTable)
{
jTable1 = myJTable;
KeyStroke copy =
KeyStroke.getKeyStroke(KeyEvent.VK_C,ActionEvent.CTRL_MASK,false);
// Identifying the copy KeyStroke user can modify this
// to copy on some other Key combination.
KeyStroke paste =
KeyStroke.getKeyStroke(KeyEvent.VK_V,ActionEvent.CTRL_MASK,false);
// Identifying the Paste KeyStroke user can modify
this
//to copy on some other Key combination.
jTable1.registerKeyboardAction(this,"Copy",copy,JComponent.WHEN_FOCUSED);
jTable1.registerKeyboardAction(this,"Paste",paste,JComponent.WHEN_FOCUSED);
system =
Toolkit.getDefaultToolkit().getSystemClipboard();
}
/**
* Public Accessor methods for the Table on which this
adapter acts.
*/
public JTable getJTable() {return jTable1;}
public void setJTable(JTable jTable1)
{this.jTable1=jTable1;}
/**
* This method is activated on the Keystrokes we are
listening to
* in this implementation. Here it listens for Copy and
Paste ActionCommands.
* Selections comprising non-adjacent cells result in
invalid selection and
* then copy action cannot be performed.
* Paste is done by aligning the upper left corner of the
selection with the
* 1st element in the current selection of the JTable.
*/
public void actionPerformed(ActionEvent e)
{
if (e.getActionCommand().compareTo("Copy")==0)
{
StringBuffer sbf=new StringBuffer();
// Check to ensure we have selected only a
contiguous block of
// cells
int numcols=jTable1.getSelectedColumnCount();
int numrows=jTable1.getSelectedRowCount();
int[] rowsselected=jTable1.getSelectedRows();
int[] colsselected=jTable1.getSelectedColumns();
if
(!((numrows-1==rowsselected[rowsselected.length-1]-rowsselected[0]
&&
numrows==rowsselected.length)
&&
(numcols-1==colsselected[colsselected.length-1]-colsselected[0]
&&
numcols==colsselected.length)))
{
JOptionPane.showMessageDialog(null, "Invalid
Copy Selection",
"Invalid
Copy Selection",
JOptionPane.ERROR_MESSAGE);
return;
}
for (int i=0;i<numrows;i++)
{
for (int j=0;j<numcols;j++)
{
sbf.append(jTable1.getValueAt(rowsselected[i],colsselected[j]));
if (j<numcols-1) sbf.append("\t");
}
sbf.append("\n");
}
stsel = new StringSelection(sbf.toString());
system =
Toolkit.getDefaultToolkit().getSystemClipboard();
system.setContents(stsel,stsel);
}
if (e.getActionCommand().compareTo("Paste")==0)
{
System.out.println("Trying to Paste");
int startRow=(jTable1.getSelectedRows())[0];
int startCol=(jTable1.getSelectedColumns())[0];
try
{
String trstring=
(String)(system.getContents(this).getTransferData(DataFlavor.stringFlavor));
System.out.println("String is:"+trstring);
StringTokenizer st1=new
StringTokenizer(trstring,"\n");
for(int i=0;st1.hasMoreTokens();i++)
{
rowstring=st1.nextToken();
StringTokenizer st2=new
StringTokenizer(rowstring,"\t");
for(int
j=0;st2.hasMoreTokens();j++)
{
value=(String)st2.nextToken();
if (startRow+i< jTable1.getRowCount() &&
startCol+j< jTable1.getColumnCount())
jTable1.setValueAt(value,startRow+i,startCol+j);
System.out.println("Putting "+ value+"at
row="+startRow+i+"column="+startCol+j);
}
}
}
catch(Exception ex){ex.printStackTrace();}
}
}
}
A sample application
Here is a sample application, Frame1.java , that
uses the ExcelAdapter to make a JTable
Excel-compatible.
import java.awt.*;
import javax.swing.*;
public class Frame1 extends Frame
{
BorderLayout borderLayout1 = new BorderLayout();
JTable jTable1 ;
Object[][] data=new Object[4][4];
Object header[]= {"Jan","Feb","Mar","Apr"};
public static void main(String args[])
{
Frame1 myframe=new Frame1();
myframe.setSize(new Dimension(250,250));
myframe.setVisible(true);
}
public Frame1()
{
super();
try
{
jbInit();
}
catch (Exception e)
{
e.printStackTrace();
}
}
private void jbInit() throws Exception
{
for (int i=0;i<4;i++)
for (int j=0;j<4;j++)
data[i][j]=new Integer(i*10+j);
System.out.println("Header length="+header[1]);
jTable1=new JTable(data,header);
jTable1.setCellSelectionEnabled(true);
this.setTitle("Excel Lent JTABLE");
jTable1.setBackground(Color.pink);
this.setLayout(borderLayout1);
this.setSize(new Dimension(400, 300));
this.setBackground(Color.white);
this.add(jTable1, BorderLayout.CENTER);
// This is the line that does all the magic!
ExcelAdapter myAd = new ExcelAdapter(jTable1);
}
}
Excel's clipboard format
Excel has a very simple clipboard format. It separates
elements on the same row with tabs and separates rows with
newline characters. So, when you copy a set of contiguous
and/or adjacent cells, Excel simply tokenizes the
spreadsheet data into one long string, with the individual
cell values separated by tabs and newlines within that
string. What if the cells selected are not adjacent? Simple:
Excel will not let you copy your selection to the clipboard.
This behavior is mimicked by the adapter described here,
which will also not let you copy data if the cells selected
are not adjacent. In Excel, a dialog box pops up to tell us
that you have not been permitted to copy; again, this
behavior is mimicked by the adapter.
A brief explanation of the code
To use this feature, you need to download the
ExcelAdapter.java file, compile it, and add the last
line from the sample application to your code somewhere in
order to activate the adapter on the JTable.
Within the adapter, the activating keystrokes for both
copy and paste functions are registered. Thereafter, any
time the activating key is typed, the actionPerformed
method is called. If it is the copy action, then the
selected cells' data are tokenized appropriately for Excel
and written to the system clipboard. If the action is paste,
then the data within the system clipboard are converted to
its string flavor and parsed to populate the JTable cells,
depending on which cell is selected.
Conclusion
Users of tables written in Java often want to be able to do
something familiar in Excel with the data from JTables.
Instead of writing code to enable copy-paste functionality
on each JTable, it is much simpler to write a generic
adapter that brings the desired functionality to every
JTable with just one line of additional code. The adapter
presented in this Java Tip does just that.
About the author
Ashok Banerjee and Jignesh Mehta are both currently working
with Oracle Corporation on manufacturing applications. They
have been using Java for around two years.
|