This feed shows how to import excel contents to JTable. To start, this application has a provision for users to select excel workbook (JFileChooser). Once selected excel file is imported, all available worksheets are listed to user(JOptionPane). Now based upon the opted excel sheet corresponding contents are loaded in Tabular format (JTable)
To Note,
- Accepts only .xls format
- Only one excel sheet can be imported at a time
- Empty cells are respected.
- Very first row of excel sheet is considered as header for the JTable
- HSSF is used to access excel workbook through Java
import java.awt.*;
import java.awt.event.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class excelTojTable extends JFrame {
static JTable table;
static JScrollPane scroll;
// header is Vector contains table Column
static Vector headers = new Vector();
static Vector data = new Vector();
// Model is used to construct
DefaultTableModel model = null;
// data is Vector contains Data from Excel File
static Vector data = new Vector();
static JButton jbClick;
static JFileChooser jChooser;
static int tableWidth = 0;
static int tableHeight = 0;
public excelTojTable()
{
super("Import Excel To JTable");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JPanel buttonPanel = new JPanel();
buttonPanel.setBackground(Color.white);
jChooser = new JFileChooser();
jbClick = new JButton("Select Excel File");
buttonPanel.add(jbClick, BorderLayout.CENTER);
// Show Button Click Event
jbClick.addActionListener(new ActionListener()
{
@Override public void actionPerformed(ActionEvent arg0)
{
jChooser.showOpenDialog(null);
jChooser.setDialogTitle("Select only Excel workbooks");
File file = jChooser.getSelectedFile();
if(file==null)
{
JOptionPane.showMessageDialog(
null, "Please select any Excel file",
"Help",
JOptionPane.INFORMATION_MESSAGE);
return;
}
else if(!file.getName().endsWith("xls"))
{
JOptionPane.showMessageDialog(
null, "Please select only Excel file.",
"Error",JOptionPane.ERROR_MESSAGE);
}
else
{
fillData(file);
model = new DefaultTableModel(data, headers);
tableWidth = model.getColumnCount() * 150;
tableHeight = model.getRowCount() * 25;
table.setPreferredSize(new Dimension( tableWidth, tableHeight));
table.setModel(model);
}
}
});
table = new JTable();
table.setAutoCreateRowSorter(true);
model = new DefaultTableModel(data, headers);
table.setModel(model);
table.setBackground(Color.pink);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setEnabled(false);
table.setRowHeight(25);
table.setRowMargin(4);
tableWidth = model.getColumnCount() * 150;
tableHeight = model.getRowCount() * 25;
table.setPreferredSize(new Dimension( tableWidth, tableHeight));
scroll = new JScrollPane(table);
scroll.setBackground(Color.pink);
scroll.setPreferredSize(new Dimension(300, 300));
scroll.setHorizontalScrollBarPolicy( JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy( JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
getContentPane().add(buttonPanel, BorderLayout.NORTH);
getContentPane().add(scroll, BorderLayout.CENTER);
setSize(600, 600);
setResizable(true);
setVisible(true);
}
// Fill JTable with Excel file data. * * @param file * file :contains xls file to display in jTable
void fillData(File file)
{
int index=-1;
HSSFWorkbook workbook = null;
try {
try {
FileInputStream inputStream = new FileInputStream (file);
workbook = new HSSFWorkbook(inputStream);
}
catch (IOException ex)
{
Logger.getLogger(excelTojTable.class. getName()).log(Level.SEVERE, null, ex);
}
String[] strs=new String[workbook.getNumberOfSheets()];
//get all sheet names from selected workbook
for (int i = 0; i < strs.length; i++) {
strs[i]= workbook.getSheetName(i); }
JFrame frame = new JFrame("Input Dialog");
String selectedsheet = (String) JOptionPane.showInputDialog(
frame, "Which worksheet you want to import ?", "Select Worksheet",
JOptionPane.QUESTION_MESSAGE, null, strs, strs[0]);
if (selectedsheet!=null) {
for (int i = 0; i < strs.length; i++)
{
if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
index=i; }
HSSFSheet sheet = workbook.getSheetAt(index);
HSSFRow row=sheet.getRow(0);
headers.clear();
for (int i = 0; i < row.getLastCellNum(); i++)
{
HSSFCell cell1 = row.getCell(i);
headers.add(cell1.toString());
}
data.clear();
for (int j = 1; j < sheet.getLastRowNum() + 1; j++)
{
Vector d = new Vector();
row=sheet.getRow(j);
int noofrows=row.getLastCellNum();
for (int i = 0; i < noofrows; i++)
{ //To handle empty excel cells
HSSFCell cell=row.getCell(i,
org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
d.add(cell.toString());
}
d.add("\n");
data.add(d);
}
}
else { return; }
}
catch (Exception e) { e.printStackTrace(); } }
public static void main(String[] args)
{ new excelTojTable(); }
}
import java.awt.event.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class excelTojTable extends JFrame {
static JTable table;
static JScrollPane scroll;
// header is Vector contains table Column
static Vector headers = new Vector();
static Vector data = new Vector();
// Model is used to construct
DefaultTableModel model = null;
// data is Vector contains Data from Excel File
static Vector data = new Vector();
static JButton jbClick;
static JFileChooser jChooser;
static int tableWidth = 0;
static int tableHeight = 0;
public excelTojTable()
{
super("Import Excel To JTable");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JPanel buttonPanel = new JPanel();
buttonPanel.setBackground(Color.white);
jChooser = new JFileChooser();
jbClick = new JButton("Select Excel File");
buttonPanel.add(jbClick, BorderLayout.CENTER);
// Show Button Click Event
jbClick.addActionListener(new ActionListener()
{
@Override public void actionPerformed(ActionEvent arg0)
{
jChooser.showOpenDialog(null);
jChooser.setDialogTitle("Select only Excel workbooks");
File file = jChooser.getSelectedFile();
if(file==null)
{
JOptionPane.showMessageDialog(
null, "Please select any Excel file",
"Help",
JOptionPane.INFORMATION_MESSAGE);
return;
}
else if(!file.getName().endsWith("xls"))
{
JOptionPane.showMessageDialog(
null, "Please select only Excel file.",
"Error",JOptionPane.ERROR_MESSAGE);
}
else
{
fillData(file);
model = new DefaultTableModel(data, headers);
tableWidth = model.getColumnCount() * 150;
tableHeight = model.getRowCount() * 25;
table.setPreferredSize(new Dimension( tableWidth, tableHeight));
table.setModel(model);
}
}
});
table = new JTable();
table.setAutoCreateRowSorter(true);
model = new DefaultTableModel(data, headers);
table.setModel(model);
table.setBackground(Color.pink);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setEnabled(false);
table.setRowHeight(25);
table.setRowMargin(4);
tableWidth = model.getColumnCount() * 150;
tableHeight = model.getRowCount() * 25;
table.setPreferredSize(new Dimension( tableWidth, tableHeight));
scroll = new JScrollPane(table);
scroll.setBackground(Color.pink);
scroll.setPreferredSize(new Dimension(300, 300));
scroll.setHorizontalScrollBarPolicy( JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy( JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
getContentPane().add(buttonPanel, BorderLayout.NORTH);
getContentPane().add(scroll, BorderLayout.CENTER);
setSize(600, 600);
setResizable(true);
setVisible(true);
}
// Fill JTable with Excel file data. * * @param file * file :contains xls file to display in jTable
void fillData(File file)
{
int index=-1;
HSSFWorkbook workbook = null;
try {
try {
FileInputStream inputStream = new FileInputStream (file);
workbook = new HSSFWorkbook(inputStream);
}
catch (IOException ex)
{
Logger.getLogger(excelTojTable.class. getName()).log(Level.SEVERE, null, ex);
}
String[] strs=new String[workbook.getNumberOfSheets()];
//get all sheet names from selected workbook
for (int i = 0; i < strs.length; i++) {
strs[i]= workbook.getSheetName(i); }
JFrame frame = new JFrame("Input Dialog");
String selectedsheet = (String) JOptionPane.showInputDialog(
frame, "Which worksheet you want to import ?", "Select Worksheet",
JOptionPane.QUESTION_MESSAGE, null, strs, strs[0]);
if (selectedsheet!=null) {
for (int i = 0; i < strs.length; i++)
{
if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
index=i; }
HSSFSheet sheet = workbook.getSheetAt(index);
HSSFRow row=sheet.getRow(0);
headers.clear();
for (int i = 0; i < row.getLastCellNum(); i++)
{
HSSFCell cell1 = row.getCell(i);
headers.add(cell1.toString());
}
data.clear();
for (int j = 1; j < sheet.getLastRowNum() + 1; j++)
{
Vector d = new Vector();
row=sheet.getRow(j);
int noofrows=row.getLastCellNum();
for (int i = 0; i < noofrows; i++)
{ //To handle empty excel cells
HSSFCell cell=row.getCell(i,
org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
d.add(cell.toString());
}
d.add("\n");
data.add(d);
}
}
else { return; }
}
catch (Exception e) { e.printStackTrace(); } }
public static void main(String[] args)
{ new excelTojTable(); }
}
Here is the flow
Select Excel file(.xls) | Select any available sheet |
---|---|
Imported contents from selected Excel sheet |
---|
Want to download above code ?? click this link excelToJTable. Below cited, is used/sample excel data.
Preview of the excel sheet used as input |
Let me know if you encounter any issues with above code. As usual, share your comments any time!!
No comments:
Post a Comment