jxl读取excel文件

package inportexcel;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
/**
 *
 * @author zhangel
 * jxl操作excel，读取excel中的数据，导入到数据库中
 *
 */

public class ImportExcelAction extends Action {

    private Vector sumExcelList;
    private Cell cell;
    private int rows;
    private int cols;
    private String sql;

    public ActionForward execute(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) {
        String fileContext = null;
        File file =null;
        try {
            fileContext = new String(request.getParameter("fileContext")
                    .getBytes("iso-8859-1"), "UTF-8");
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        if(fileContext!=null){
            file = new File(fileContext);
        }
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(file);
        } catch (BiffException e1) {
            e1.printStackTrace();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        Sheet sheet = workbook.getSheet(0);
        rows = sheet.getRows();
        // System.out.print(rows);
        cols = sheet.getColumns();
        // System.out.print(cols);

        sumExcelList = getSumExcelList(rows, cols, sheet);

        // System.out.print(sheet.getCell(1, 0).getContents());
        // System.out.print(sumExcelList);

        sql = buildSQL(cols);
        // System.out.print(sql1);
       

//        mysql测试
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUser = "root";
        String dbPasswor = "root";
        String dbURL = "jdbc:mysql://127.0.0.1:3306/lindb";
        try {
            Class.forName(dbDriver);
            Connection conn = DriverManager.getConnection(dbURL, dbUser,
                    dbPasswor);
            PreparedStatement ps = conn.prepareStatement(sql);
            importDB(sumExcelList, ps);
            conn.close();
            ps.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return mapping.findForward("success");
    }

//    构建sql语句
    public String buildSQL(int cols) {
        String sql = "insert into worker(";
        String rowFirst = (String) sumExcelList.get(0);
        sql = sql + rowFirst.substring(0, rowFirst.length() - 1) + ") values(";
        for (int i = 0; i < cols; i++) {
            sql += "?,";
        }
        sql = sql.subSequence(0, sql.length() - 1) + ")";
        return sql;
    }


    public Vector getSumExcelList(int rows, int cols, Sheet sheet) {
        sumExcelList = new Vector();
        for (int i = 0; i < rows; i++) {
            String rowList = "";
            for (int j = 0; j < cols; j++) {
                cell = sheet.getCell(j, i);
                rowList += cell.getContents() + ",";
            }
            sumExcelList.add(rowList);
        }
        return sumExcelList;
    }

//    导入数据库中
    public void importDB(Vector sumExcelList, PreparedStatement ps)
            throws SQLException {
        for (int i = 1; i < sumExcelList.size(); i++) {
            String rowContext = (String) sumExcelList.get(i);
            String[] strs = rowContext.split(",");
            for (int j = 0; j < strs.length; j++) {
                ps.setString(j + 1, strs[j]);
            }
            ps.addBatch();
        }
        ps.executeBatch();
    }
}