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>