JAVA把excel表格的資料匯入到資料庫

標籤：excel   匯入資料庫   

package com.insertdatebase;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Locale;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import com.dao.DKInfoDao;import com.dao.DaiKuanInfoIm;import com.entity.DaiKuanInfo;/* * 把excel表格的資料插入資料冷庫 * @auther guo-xqi */public class TestExcel {// 記錄類的輸出資訊static Log log = LogFactory.getLog(TestExcel.class);// 擷取Excel文檔的路徑­public static String filePath = "D://daizhanghexiao.xls";public static void main(String[] args) {try {// 建立對Excel活頁簿檔案的引用HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));// 在Excel文檔中，第一張工作表的預設索引是0// 其語句為：HSSFSheet sheet = workbook.getSheetAt(0);HSSFSheet sheet = wookbook.getSheet("Sheet1");// 擷取到Excel檔案中的所有行數int rows = sheet.getPhysicalNumberOfRows();// 遍曆行for (int i = 0; i < rows; i++) {//讀取左上端儲存格HSSFRow row = sheet.getRow(i);// 行不為空白if (row != null) {// 擷取到Excel檔案中的所有的列int cells = row.getPhysicalNumberOfCells();String value = "";// 遍曆列for (int j = 0; j < cells; j++) {// 擷取到列的值?HSSFCell cell = row.getCell((short) j);if (cell != null) {switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_FORMULA:break;case HSSFCell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {value += cell.getDateCellValue() + ",";}else {value += cell.getNumericCellValue() + ",";}break;case HSSFCell.CELL_TYPE_STRING:value += cell.getStringCellValue() + ",";break;default:value += "0";break;}}}// 將資料插入到mysql資料庫中//System.out.println(value);//String string="Tue Feb 04 00:00:00 CST 2014";// SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy",Locale.US);// try {//Date d=sdf.parse(string);//sdf=new SimpleDateFormat("yyyy-MM-dd");////System.out.println(sdf.format(d));//} catch (ParseException e) {//// TODO Auto-generated catch block//e.printStackTrace();//}//把得到的字串拆分String[] val = value.split(",");//給建立的對象設定值DaiKuanInfo dkinfo = new DaiKuanInfo();dkinfo.setDKPerson(val[0]);SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy",Locale.US);Date dkDate;try {dkDate = sdf.parse(val[1]);sdf=new SimpleDateFormat("yyyy-MM-dd");String dkdateString=sdf.format(dkDate);dkinfo.setDKDate(java.sql.Date.valueOf(dkdateString));dkinfo.setDkje(Double.parseDouble(val[2]));dkinfo.setDBPerson(val[3]);SimpleDateFormat simdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy",Locale.US);Date hkdate=simdf.parse(val[4]);simdf=new SimpleDateFormat("yyyy-MM-dd");dkinfo.setHKDate(java.sql.Date.valueOf(simdf.format(hkdate)));} catch (ParseException e1) {e1.printStackTrace();}dkinfo.setYhje(Double.parseDouble(val[5]));dkinfo.setQkje(Double.parseDouble(val[6]));//插入資料庫DKInfoDao dkdao = new DaiKuanInfoIm();int boo = dkdao.addDaiKuanInfo(dkinfo);if (boo == 0) {continue;} else {break;}}}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}

JAVA把excel表格的資料匯入到資料庫