copy:excle文件进行java数据导入库:https://www.cnblogs.com/zyw-205520/p/3762954.html
Java实现Excel导入数据核心类 读取Excel表中所有的数据、操作数据(查询、更新)
package com.javen.service; import java.io.File; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import com.javen.db.DBhepler; import com.javen.entity.StuEntity; /** * @author Javen * @Email zyw205@gmail.com * */ public class StuService { /** * 查询stu表中所有的数据 * @return */ public static ListgetAllByDb(){ List list=new ArrayList (); try { DBhepler db=new DBhepler(); String sql="select * from stu"; ResultSet rs= db.Search(sql, null); while (rs.next()) { int id=rs.getInt("id"); String name=rs.getString("name"); String sex=rs.getString("sex"); int num=rs.getInt("num"); //System.out.println(id+" "+name+" "+sex+ " "+num); list.add(new StuEntity(id, name, sex, num)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } /** * 查询指定目录中电子表格中所有的数据 * @param file 文件完整路径 * @return */ public static List getAllByExcel(String file){ List list=new ArrayList (); try { Workbook rwb=Workbook.getWorkbook(new File(file)); Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0) int clos=rs.getColumns();//得到所有的列 int rows=rs.getRows();//得到所有的行 System.out.println(clos+" rows:"+rows); for (int i = 1; i < rows; i++) { for (int j = 0; j < clos; j++) { //第一个是列数,第二个是行数 String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String name=rs.getCell(j++, i).getContents(); String sex=rs.getCell(j++, i).getContents(); String num=rs.getCell(j++, i).getContents(); System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num); list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num))); } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } /** * 通过Id判断是否存在 * @param id * @return */ public static boolean isExist(int id){ try { DBhepler db=new DBhepler(); ResultSet rs=db.Search("select * from stu where id=?", new String[]{id+""}); if (rs.next()) { return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } public static void main(String[] args) { /*List all=getAllByDb(); for (StuEntity stuEntity : all) { System.out.println(stuEntity.toString()); }*/ System.out.println(isExist(1)); } }
数据的数据导入到Excel表
package com.javen.excel; import java.io.File; import java.util.List; import com.javen.entity.StuEntity; import com.javen.service.StuService; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class TestDbToExcel { public static void main(String[] args) { try { WritableWorkbook wwb = null; // 创建可写入的Excel工作簿 String fileName = "D://book.xls"; File file=new File(fileName); if (!file.exists()) { file.createNewFile(); } //以fileName为文件名来创建一个Workbook wwb = Workbook.createWorkbook(file); // 创建工作表 WritableSheet ws = wwb.createSheet("Test Shee 1", 0); //查询数据库中所有的数据 Listlist= StuService.getAllByDb(); //要插入到的Excel表格的行号,默认从0开始 Label labelId= new Label(0, 0, "编号(id)");//表示第 Label labelName= new Label(1, 0, "姓名(name)"); Label labelSex= new Label(2, 0, "性别(sex)"); Label labelNum= new Label(3, 0, "薪水(num)"); ws.addCell(labelId); ws.addCell(labelName); ws.addCell(labelSex); ws.addCell(labelNum); for (int i = 0; i < list.size(); i++) { Label labelId_i= new Label(0, i+1, list.get(i).getId()+""); Label labelName_i= new Label(1, i+1, list.get(i).getName()); Label labelSex_i= new Label(2, i+1, list.get(i).getSex()); Label labelNum_i= new Label(3, i+1, list.get(i).getNum()+""); ws.addCell(labelId_i); ws.addCell(labelName_i); ws.addCell(labelSex_i); ws.addCell(labelNum_i); } //写进文档 wwb.write(); // 关闭Excel工作簿对象 wwb.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
Excel表中的数据导入到MySql数据库
package com.javen.excel; import java.util.List; import com.javen.db.DBhepler; import com.javen.entity.StuEntity; import com.javen.service.StuService; /** * @author Javen * @Email zyw205@gmail.com * */ public class TestExcelToDb { public static void main(String[] args) { //得到表格中所有的数据 ListlistExcel=StuService.getAllByExcel("d://book.xls"); /*//得到数据库表中所有的数据 List listDb=StuService.getAllByDb();*/ DBhepler db=new DBhepler(); for (StuEntity stuEntity : listExcel) { int id=stuEntity.getId(); if (!StuService.isExist(id)) { //不存在就添加 String sql="insert into stu (name,sex,num) values(?,?,?)"; String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""}; db.AddU(sql, str); }else { //存在就更新 String sql="update stu set name=?,sex=?,num=? where id=?"; String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""}; db.AddU(sql, str); } } } }