package com.cn.peitest.connectDatabase;
import java.io.File;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.alibaba.fastjson.JSONObject;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* 连接数据库查询 将查询结果写入exce文件中
* @author Pei
*
*/
public class WritterToExcel {
public static void main(String[] args) {
//连接数据库
//声明
Connection con;
//驱动程序名称
String driver="com.mysql.jdbc.Driver";
//路径
String url="jdbc:mysql://localhost:3306/peixian?serverTimezone=UTC";
//用户名
String name="root";
//密码
String password="123456";
try {
//加载驱动程序
Class.forName(driver);
con=DriverManager.getConnection(url,name,password);
if(!con.isClosed()) {
System.out.println("\n\t\t成功以 " + name + " 身份连接到数据库！！！");
}
// 2.创建statement类对象，用来执行SQL语句！！
Statement st=con.createStatement();
String sql="select * from pei_user_baseinf";
// 3.ResultSet类，用来存放获取的结果集！！
ResultSet rs=st.executeQuery(sql);
ResultSetMetaData rsd=rs.getMetaData();
int count=rsd.getColumnCount();
System.out.println(rs);
List<Map<String,Object>>list=new ArrayList<Map<String,Object>>();
while(rs.next()){
Map<String,Object>map=new HashMap<String,Object>();
for(int i=1;i<=count;i++) {
map.put(rsd.getColumnName(i), rs.getObject(i));
}
list.add(map);
}
System.out.println("list======"+list);
excelOut(list);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//将数据导出到Excel
public static void excelOut(List<Map<String, Object>> list2) {
List<UserBaseinf>list=new ArrayList<UserBaseinf>();
for(Map<String,Object>map:list2) {
UserBaseinf userBaseinf=JSONObject.parseObject(JSONObject.toJSONString(map), UserBaseinf.class);
list.add(userBaseinf);
}
WritableWorkbook bWorkbook = null;
try {
// 创建Excel对象
bWorkbook = Workbook.createWorkbook(new File("D:/book.xls"));
// 通过Excel对象创建一个选项卡对象
WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
//使用循环将数据读出
for (int i =0; i < list.size(); i++) {
UserBaseinf book=list.get(i);
Class cls = book.getClass();
Field[] fields = cls.getDeclaredFields();
for(int j=0; j<fields.length; j++){
Field f = fields[j];
f.setAccessible(true);
System.out.println("属性名:" + f.getName() + " 属性值:" + f.get(book));
Label label=new Label(j,i,String.valueOf(f.get(book)));//j控制行，i控制列
sheet.addCell(label);
}
}
// 创建一个单元格对象，第一个为列，第二个为行，第三个为值
Label label = new Label(0, 2, "test");
// 将创建好的单元格放入选项卡中
//sheet.addCell(label);
// 写如目标路径
bWorkbook.write();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
bWorkbook.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
//实体类
package com.cn.peitest.connectDatabase;
import java.io.Serializable;
public class UserBaseinf implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private String phone_no;
private String pass_word;
private String company_name;
private String user_addres;
private String sex;
private String user_name;
private String register_time;
private String approval_status;
public String getPhone_no() {
return phone_no;
}
public void setPhone_no(String phone_no) {
this.phone_no = phone_no;
}
public String getPass_word() {
return pass_word;
}
public void setPass_word(String pass_word) {
this.pass_word = pass_word;
}
public String getCompany_name() {
return company_name;
}
public void setCompany_name(String company_name) {
this.company_name = company_name;
}
public String getUser_addres() {
return user_addres;
}
public void setUser_addres(String user_addres) {
this.user_addres = user_addres;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getRegister_time() {
return register_time;
}
public void setRegister_time(String register_time) {
this.register_time = register_time;
}
public String getApproval_status() {
return approval_status;
}
public void setApproval_status(String approval_status) {
this.approval_status = approval_status;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
@Override
public String toString() {
return "UserBaseinf [phone_no=" + phone_no + ", pass_word=" + pass_word + ", company_name=" + company_name
+ ", user_addres=" + user_addres + ", sex=" + sex + ", user_name=" + user_name + ", register_time="
+ register_time + ", approval_status=" + approval_status + "]";
}
}
//pom.xml文件
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.cn</groupId>
<artifactId>peitest</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>peitest</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.directory.studio</groupId>
<artifactId>org.apache.commons.codec</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.54</version>
</dependency>
</dependencies>
</project>
