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表格的数据导入到数据库