/**

 *

 */

package com.b510.excel;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

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;

import com.b510.common.Common;

import com.b510.excel.vo.Student;

/**

 * @author Hongten

 * @created 2014-5-18

 */

public class ReadExcel {

    public List<Student> readXls() throws IOException {

        InputStream is = new FileInputStream(Common.EXCEL_PATH);

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        Student student = null;

        List<Student> list = new ArrayList<Student>();

        // 循环工作表Sheet

        for (int numSheet = ; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);

            if (hssfSheet == null) {

                continue;

            }

            // 循环行Row

            for (int rowNum = ; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

                HSSFRow hssfRow = hssfSheet.getRow(rowNum);

                if (hssfRow != null) {

                    student = new Student();

                    HSSFCell no = hssfRow.getCell();

                    HSSFCell name = hssfRow.getCell();

                    HSSFCell age = hssfRow.getCell();

                    HSSFCell score = hssfRow.getCell();

                    student.setNo(getValue(no));

                    student.setName(getValue(name));

                    student.setAge(getValue(age));

                    student.setScore(Float.valueOf(getValue(score)));

                    list.add(student);

                }

            }

        }

        return list;

    }

     @SuppressWarnings("static-access")

    private String getValue(HSSFCell hssfCell) {

            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {

                // 返回布尔类型的值

                return String.valueOf(hssfCell.getBooleanCellValue());

            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {

                // 返回数值类型的值

                return String.valueOf(hssfCell.getNumericCellValue());

            } else {

                // 返回字符串类型的值

                return String.valueOf(hssfCell.getStringCellValue());

            }

        }

}

/**

 *

 */

package com.b510.excel;

import java.io.IOException;

import java.sql.SQLException;

import java.util.List;

import com.b510.common.Common;

import com.b510.excel.util.DbUtil;

import com.b510.excel.vo.Student;

/**

 * @author Hongten

 * @created 2014-5-18

 */

public class SaveData2DB {

	@SuppressWarnings({ "rawtypes" })

	public void save() throws IOException, SQLException {

		ReadExcel xlsMain = new ReadExcel();

		Student student = null;

		List<Student> list = xlsMain.readXls();

		for (int i = 0; i < list.size(); i++) {

			student = list.get(i);

			List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student);

			if (!l.contains(1)) {

				DbUtil.insert(Common.INSERT_STUDENT_SQL, student);

			} else {

				System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + ", and has been throw away!");

			}

		}

	}

}