I have to read an Excel file and transfer the contents to the database using Java swing.The excel file contains questions for the surveys with options for answers or responses.The database should have a table say Question with columns "Question No., Question, Option 1, Option 2 and so on".I have this code snippet with which I am able to read the excel file and display its contents on the console but I am unable to transfer the content to the database using SQL.Could you please help me in doing what I need.Provide a code for doing that.
package pck1;
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.awt.*;
import java.io.FileInputStream;
import java.io.IOException;
import javax.swing.*;
import java.awt.event.*;
//import javax.swing.filechooser.*;
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;
@SuppressWarnings("serial")
public class SelectFile extends JFrame{
public static void main(String args[]){
JFrame frame = new JFrame();
frame.setLayout(null);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setTitle("Select File for Linking");
frame.setSize(600, 300);
Container container = frame.getContentPane();
container.setLayout(new GridBagLayout());
final JTextField text=new JTextField(20);
JButton b=new JButton("Select File");
text.setBounds(20,20,120,20);
b.setBounds(150,20,80,20);
b.setHorizontalAlignment(SwingConstants.LEFT);
//b.setBorderPainted(false);
//b.setOpaque(false);
// b.setBackground(Color.lightGray);
b.addActionListener(new ActionListener() {
@SuppressWarnings("unchecked")
public void actionPerformed(ActionEvent e){
JFileChooser fc = new JFileChooser();
fc.addChoosableFileFilter(new OnlyExt());
int returnval = fc.showOpenDialog(null);
if (returnval == JFileChooser.APPROVE_OPTION) {
File file = fc.getSelectedFile();
text.setText(file.getPath());
//Read1 fl=new Read1(file);
//fl.setVisible(true);
List sheetData = new ArrayList();
FileInputStream fis = null;
try {
// Create a FileInputStream that will be use to read the excel file
fis = new FileInputStream(file);
// Create an excel workbook from the file system.
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e1) {
e1.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
showExelData(sheetData);
}
}});
container.add(text);
container.add(b);
frame.setVisible(true);
}
@SuppressWarnings("unchecked")
private static void showExelData(List sheetData) {
// print data out to the console.
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
HSSFCell cell = (HSSFCell) list.get(j);
System.out.print(
cell.getStringCellValue().toString());
//System.out.print("\t");
if (j < list.size() - 1) {
System.out.print(" ");
}
}
System.out.println(" ");
}
}
}
class OnlyExt extends javax.swing.filechooser.FileFilter{
public boolean accept(File file) {
if (file.isDirectory()) return false;
String name = file.getName().toLowerCase();
return (name.endsWith(".xls"));
}
public String getDescription() { return "Excel ( *.xls)"; }
}