Excel数据批量导入到数据库

* * */ 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 = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { student = new Student(); HSSFCell no = hssfRow.getCell(0); HSSFCell name = hssfRow.getCell(1); HSSFCell age = hssfRow.getCell(2); HSSFCell score = hssfRow.getCell(3); 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()); } } }

3.SaveData2DB.ava保存数据

/**
 * 
 */
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!");
			}
		}
	}
}

4.源程序http://files.cnblogs.com/files/feifeishi/ExcelTest.rar

Excel数据批量导入到数据库

标签：put   path   exist   void   读取数据   set   org   类型   alt