Excel链接mysql语句查询_连接数据库查询 将查询结果写入exce文件中

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>list=new ArrayList>();

while(rs.next()){

Mapmap=new HashMap();

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> list2) {

Listlist=new ArrayList();

for(Mapmap: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

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文件

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.cn

peitest

0.0.1-SNAPSHOT

jar

peitest

http://maven.apache.org

UTF-8

junit

junit

3.8.1

test

org.apache.directory.studio

org.apache.commons.codec

1.8

net.sourceforge.jexcelapi

jxl

2.6.12

org.apache.poi

poi-ooxml

3.9

com.alibaba

fastjson

1.2.54