旅游网站建设外现状,电影网站制作,linux版本的wordpress,酒店网站建设方案策划方案前些天发现了一个巨牛的人工智能学习网站#xff0c;通俗易懂#xff0c;风趣幽默#xff0c;忍不住分享一下给大家。点击跳转到教程。
实现的功能#xff1a;
Java实现Excel导入数据库#xff0c;如果存在就更新数据库中的数据导入到Excel1、添加jxl.jar mysql-connect…前些天发现了一个巨牛的人工智能学习网站通俗易懂风趣幽默忍不住分享一下给大家。点击跳转到教程。
实现的功能
Java实现Excel导入数据库如果存在就更新数据库中的数据导入到Excel1、添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下
2、Excel文件目录:D://book.xls
3、数据库名javenforexcel
4、表名:stu
5、编写类连接mysql的字符串方法、插入的方法、实体类
表结构如下 连接数据库的工具类 package com.javen.db;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class DBhepler {/*String driver com.microsoft.sqlserver.jdbc.SQLServerDriver;String url jdbc:sqlserver://127.0.0.1;DatabaseNamejavenforexcel;*/String driver com.mysql.jdbc.Driver;String url jdbc:mysql://127.0.0.1:3306/javenforexcel;Connection con null;ResultSet res null;public void DataBase() {try {Class.forName(driver);con DriverManager.getConnection(url, root, root);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockSystem.err.println(装载 JDBC/ODBC 驱动程序失败。 ); e.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blockSystem.err.println(无法连接数据库 ); e.printStackTrace();}}// 查询public ResultSet Search(String sql, String str[]) {DataBase();try {PreparedStatement pst con.prepareStatement(sql);if (str ! null) {for (int i 0; i str.length; i) {pst.setString(i 1, str[i]);}}res pst.executeQuery();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return res;}// 增删修改public int AddU(String sql, String str[]) {int a 0;DataBase();try {PreparedStatement pst con.prepareStatement(sql);if (str ! null) {for (int i 0; i str.length; i) {pst.setString(i 1, str[i]);}}a pst.executeUpdate();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return a;}} 表的实体如下 package com.javen.entity;/*** author Javen* Email zyw205gmail.com* */
public class StuEntity {private int id;private String name;private String sex;private int num;public StuEntity() {}public StuEntity(int id, String name, String sex, int num) {this.id id;this.name name;this.sex sex;this.num num;}Overridepublic String toString() {return StuEntity [id id , name name , sex sex , num num ];}public int getId() {return id;}public void setId(int id) {this.id id;}public String getName() {return name;}public void setName(String name) {this.name name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex sex;}public int getNum() {return num;}public void setNum(int num) {this.num num;}} 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 zyw205gmail.com* */
public class StuService {/*** 查询stu表中所有的数据* return */public static ListStuEntity getAllByDb(){ListStuEntity listnew ArrayListStuEntity();try {DBhepler dbnew DBhepler();String sqlselect * from stu;ResultSet rs db.Search(sql, null);while (rs.next()) {int idrs.getInt(id);String namers.getString(name);String sexrs.getString(sex);int numrs.getInt(num);//System.out.println(id name sex num);list.add(new StuEntity(id, name, sex, num));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return list;}/*** 查询指定目录中电子表格中所有的数据* param file 文件完整路径* return*/public static ListStuEntity getAllByExcel(String file){ListStuEntity listnew ArrayListStuEntity();try {Workbook rwbWorkbook.getWorkbook(new File(file));Sheet rsrwb.getSheet(Test Shee 1);//或者rwb.getSheet(0)int closrs.getColumns();//得到所有的列int rowsrs.getRows();//得到所有的行System.out.println(clos rows:rows);for (int i 1; i rows; i) {for (int j 0; j clos; j) {//第一个是列数第二个是行数String idrs.getCell(j, i).getContents();//默认最左边编号也算一列 所以这里得jString namers.getCell(j, i).getContents();String sexrs.getCell(j, i).getContents();String numrs.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 blocke.printStackTrace();} return list;}/*** 通过Id判断是否存在* param id* return*/public static boolean isExist(int id){try {DBhepler dbnew DBhepler();ResultSet rsdb.Search(select * from stu where id?, new String[]{id});if (rs.next()) {return true;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return false;}public static void main(String[] args) {/*ListStuEntity allgetAllByDb();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 filenew File(fileName);if (!file.exists()) {file.createNewFile();}//以fileName为文件名来创建一个Workbookwwb Workbook.createWorkbook(file);// 创建工作表WritableSheet ws wwb.createSheet(Test Shee 1, 0);//查询数据库中所有的数据ListStuEntity list 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, i1, list.get(i).getId());Label labelName_i new Label(1, i1, list.get(i).getName());Label labelSex_i new Label(2, i1, list.get(i).getSex());Label labelNum_i new Label(3, i1, 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 blocke.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 zyw205gmail.com* */
public class TestExcelToDb {public static void main(String[] args) {//得到表格中所有的数据ListStuEntity listExcelStuService.getAllByExcel(d://book.xls);/*//得到数据库表中所有的数据ListStuEntity listDbStuService.getAllByDb();*/DBhepler dbnew DBhepler();for (StuEntity stuEntity : listExcel) {int idstuEntity.getId();if (!StuService.isExist(id)) {//不存在就添加String sqlinsert into stu (name,sex,num) values(?,?,?);String[] strnew String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()};db.AddU(sql, str);}else {//存在就更新String sqlupdate stu set name?,sex?,num? where id?;String[] strnew String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum(),id};db.AddU(sql, str);}}}
} 源代码下载地址 http://download.csdn.net/detail/zyw_java/7430807
