JAVA&JXL实现Excel导入数据库

package com.excel.test;
/*
 * @author KaysonYao
 */
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Sheet;
import jxl.Workbook;

public class ReadExcel {
    Connection conn = null;

    public ReadExcel() {
        try {
            // 构建Workbook对象, 只读Workbook对象
            // 直接从本地文件创建Workbook
            // 从输入流创建Workbook
            InputStream is = new FileInputStream("C:/Users/ASUS/Desktop/account_12-12-12.xls");
            Workbook rwb = Workbook.getWorkbook(is);
            // 获得工作薄（Workbook）中工作表（Sheet）的个数
            int sheetNum = rwb.getNumberOfSheets();
            // String sheetName = rwb.getSheet(0);.getName();

            // 获取第一张Sheet表
            Sheet rs = rwb.getSheet(0);
            // 获取第一行，第一列的值
            // Cell c00 = rs.getCell(0, 0); //getCell(列,行)
            // String strc00 = c00.getContents();

            System.out.println("工作薄中Sheet的个数: " + sheetNum);

            // 输出sheet名
            System.out.print("sheet :");
            for (int i = 0; i < sheetNum; i++) {
                System.out.print(rwb.getSheet(i).getName() + " ; ");
            }
            System.out.println();

            // 列总数
            int rsColumns = rs.getColumns();
            // 行总数
            int rsRows = rs.getRows();

            // 输出表
            for (int i = 0; i < rsRows; i++) {
                for (int j = 0; j < rsColumns; j++) {
                    System.out.print(rs.getCell(j, i).getContents() + "  ");
                }
                System.out.println();
            }

            // 生成SQL
            String colName = "";
            for (int i = 0; i < rsColumns; i++) {
                if (i == rsColumns - 1) {
                    colName = colName + rs.getCell(i, 0).getContents();
                } else {
                    colName = colName + rs.getCell(i, 0).getContents() + ",";
                }
            }

            System.out.println("colName :" + colName);

            // String colValue[] = null;
            String colValue[] = new String[rsRows - 1];
            // 获取第2行的值,第1行列名
            for (int i = 0; i < rsRows - 1; i++) {
                colValue[i] = "";
                for (int j = 0; j < rsColumns; j++) {
                    if (j == rsColumns - 1) {
                        colValue[i] = colValue[i] + "\'" + rs.getCell(j, i + 1).getContents() + "\'";
                    } else {
                        colValue[i] = colValue[i] + "\'" + rs.getCell(j, i + 1).getContents() + "\'" + ",";
                    }
                }
            }
            // System.out.println(colValue);
            String SQL[] = new String[rsRows - 1];
            for (int i = 0; i < rsRows - 1; i++) {
                SQL[i] = "insert into account" + "(" + colName + ") values (" + colValue[i] + ")";
                System.out.println("SQL  : " + SQL[i]);
            }

            // 获取到链接
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433", "sa", "123456");
                System.out.println("Connectedmysql Successfully");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            Statement st = (Statement) conn.createStatement();
            st.execute("use test");
            for (int i = 0; i < rsRows - 1; i++) {
                st.execute(SQL[i]);
                System.out.println("导入成功");
            }
            conn.close();
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        @SuppressWarnings("unused")
        ReadExcel re = new ReadExcel();// 测试
    }
}