JAVA把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(); } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。