学步园

package com.lsf;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

import java.sql.*;
import java.io.*;
import java.util.ArrayList;
import java.sql.Connection;

public class ReadExcelTest
{
private Connection con;
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test";
String DBDriver="com.microsoft.jdbc.sqlserver.SQLServerDriver";
String USER="sa";
String PWD="sa";

private ArrayList listFirstColum = new ArrayList();
private ArrayList listSecondColum = new ArrayList();

  // 连接数据库
  public ReadExcelTest() throws Exception
  {
   try
   {
    Class.forName(DBDriver);
    con=DriverManager.getConnection(url,USER,PWD);
   }
   catch(Exception e)
   {
     e.printStackTrace();
     throw(new Exception("Sorry,database connecting failed!"));
    }
 }

  // 从excel中读取数据存入ArrayList中
  public void readExcel(String sFileName)
   {
  try
  {
   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(sFileName));// 读取excel文件
   HSSFSheet sheet = workbook.getSheetAt(0);// 读取第一个sheet
   for(int i=0;i<=sheet.getLastRowNum();i++)// 循环取得所有行
   {
    if(sheet.getRow(i)!=null)
    {
     HSSFRow aRow = sheet.getRow(i);// 行数
     HSSFCell cellFirstColum = aRow.getCell((short)0);// 第一列
     HSSFCell cellSecondColum = aRow.getCell((short)1);// 第二列
     // 把数据存入list里备用
     listFirstColum.add((int)cellFirstColum.getNumericCellValue());// 假定第一列为数字型
     listSecondColum.add(cellSecondColum.getStringCellValue());// 假定第二列为字符串型
    }
  }
   System.out.println("Excel读取成功！");
  }catch(Exception e)
  {
   e.printStackTrace();
  }
  }
  
  // 从ArrayList中把数据写入数据库
  public void insertData(String sFileName)
  {
   // 读取
   readExcel(sFileName);
  
   String sqlInsert = "insert into impExcel"
   +"(first_colum,second_colum)values(?,?)";
   // 插入
  try
  {
   for (int i=0;i<listFirstColum.size();i++){
   PreparedStatement pstmt=con.prepareStatement(sqlInsert);
   pstmt.setInt(1, Integer.parseInt(listFirstColum.get(i).toString()));
   pstmt.setString(2, listSecondColum.get(i).toString());
   pstmt.execute();
   System.out.println("正在导入第 "+(i+1)+" 条记录");
  }
   System.out.println("导入完毕！");
  }catch(Exception e)
  {
   e.printStackTrace();
  }
  }
  }