转 java实现读取excel
来源:互联网 发布:淘宝网页设计怎么设计 编辑:程序博客网 时间:2024/06/13 22:04
本文是读者转载的作品,向作者智慧云端日记致敬。
作者:爱读才能立世
出处:(http://www.cnblogs.com/zyw-205520/p/3762954.html)
版权声明:本文的版权归作者与博客园共有。转载时须注明本文的详细链接,否则作者将保留追究其法律责任。
实现的功能:
连接数据库的工具类:
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;DatabaseName=javenforexcel”;*/
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 block System.err.println("装载 JDBC/ODBC 驱动程序失败。" ); e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block System.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 block e.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 block e.printStackTrace(); } return a;}
}
表的实体如下:
package com.javen.entity;
/**
* @author Javen
* @Email zyw205@gmail.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 zyw205@gmail.com
*
*/
public class StuService {
/**
* 查询stu表中所有的数据
* @return
*/
public static List getAllByDb(){
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<StuEntity> getAllByExcel(String file){ List<StuEntity> list=new ArrayList<StuEntity>(); 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<StuEntity> 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); //查询数据库中所有的数据 List<StuEntity> 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, 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) {
//得到表格中所有的数据
List listExcel=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); } }}
}
感谢作者,转发收藏,大家交流,谢谢
- 转 java实现读取excel
- Java实现读取Excel
- JAVA读取Excel的实现[转]
- java读取excel实现 poi
- 【转】 Java 操作 Excel (读取Excel2007,Poi实现)
- Java 操作 Excel (读取Excel2007,Poi实现)
- Java 操作 Excel (读取Excel2007,Poi实现)
- PIO读取excel文档,java实现
- Java 操作 Excel (读取Excel2007,Poi实现)
- Java中读取Excel功能实现_POI
- PIO读取excel文档,java实现
- Java 操作 Excel (读取Excel2007,Poi实现)
- Java实现读取Excel(.xlsx)数据
- PIO读取excel文档,java实现
- java读取Excel读取
- python 实现Excel 读取
- JAVA读取Excel的实现(利用jxl.jar读取excel)
- java--Excel读取及转XML
- python 常库介绍及安装方法
- Windows API
- MyBatis的foreach语句详解
- Javascript、Jquery获取浏览器和屏幕各种高度宽度
- JVM介绍
- 转 java实现读取excel
- CSS 基础(006_图标)
- Transaction rolled back because it has been marked as rollback-only
- A Large Contextual Dataset for Classification, Detection and Counting of Cars with Deep Learning
- 工作笔记之图标切换
- js中变量和jsp中java代码中变量互相访问解决方案
- 黑客常用攻击命令大全
- 数组声明以及初始化
- 由于被jq radio坑了一次这次附带一个初始选中的状态的radio checked更改 初始空的自己不加了