MS-Excel Sheets Reading and Uploading Using Java

This Java Program processing (reads) an excel sheet and inserts the cell values in database tables. It makes use of the JXL API which can be used for both reading and writing on excelsheets. In essence, it makes available a host of methods which may be utilized in conjunction with Java.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;


public class ExcelConnect {
    private HttpServletRequest request = null;
    
  private String[] rowNames = null;
    
    private String tableName = "Sample_table";
    
    static Connection conn = null;
    
    static PreparedStatement pst = null;
    
  public void setRowNames(String[] rowNames) {
        this.rowNames = rowNames;
    }

    // import data from excel to database
    public boolean importdata() {
        boolean result = false;
        try {
                
      File file = new File("C:\sample_workbook.xls");
            Workbook workbook = Workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet(0); 
            Cell cell = null;
            
            // prepare the insert sql statement
            StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append("(");
            StringBuffer params = new StringBuffer("VALUES(");
      int rows = rowNames.length;
  
      for (int i = 0; i < rows; i++) {
                sql.append(rowNames[i]).append(",");
                params.append("?,");
            }
            sql = sql.deleteCharAt(sql.length() - 1).append(")").append(params.deleteCharAt(params.length() - 1)).append(")");
            
            // get database connection
      conn = DBConnection.loadProps();
      System.out.println("Connection:" + conn);
      conn.setAutoCommit(false);
       pst = conn.prepareStatement(sql.toString()); 
      SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
       // Read data from the excel spreadsheet
       // Assumption is that the data begins in row 2 [A2]

   int columns = sheet.getColumns();

    for (int i = 1; i < columns; i++) {
       for (int j = 0; j < rows - 1; j++) {
           cell = sheet.getCell(j, i);
           if (cell.getType() == CellType.DATE) {
                    DateCell datecell = (DateCell)cell;
                    pst.setString(j + 1, df.format(datecell.getDate()));
           } else {
                    pst.setString(j + 1, cell.getContents());
           }       
       }
       pst.executeUpdate();
   }
   conn.commit();
   conn.setAutoCommit(true);
   result = true;
   // close the workbook and free up memory 
            workbook.close();
        } catch (Exception e) {
             try {
                conn.rollback();
            } catch (SQLException ex) {
            }
            result = false;
        } finally {
            closeDB();
        }
        return result;
     }
    
    private static void closeDB() {
     try {
         if (pst != null) {
             pst.close();
             pst = null;
         }
         if (conn != null) {
          conn.close();
          conn = null;
         }
     } catch (Exception e) {}
 }
}