Java测试数据库查询,数据导出到excel文件

Java测试数据库查询,数据导出到excel文件

1. 结果截图

2.所使用的jar包

3.实现代码以及逻辑解析(一个测试类，一个用户实体类)

package com.Java.Demo;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 
 * @author Admin 
 * 测试数据库查询,数据导出到excel文件
 */
public class DBexcelTest {

	private static final String URL = "jdbc:mysql://localhost:3306/goods";
	private static final String NAME = "1234";
	private static final String PASSWORD = "Admin@123";

	public static void main(String[] args) throws RowsExceededException, WriteException, IOException {
		// TODO Auto-generated method stub
		String filePath = "export.xls";
		WritableWorkbook wwb = null;
		try {
			wwb = Workbook.createWorkbook(new File(filePath));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("获取excel文件失败");
		}

		// 创建Excel文件数据
		WritableSheet sheet = wwb.createSheet("用户信息", 0);
		// 设置title信息
		String[] titles = { "id", "用户名", "密码" };
		// 单元格
		Label label = null;
		
		for (int i = 0; i < titles.length; i++) {
			label = new Label(i, 0, titles[i]);
			// 添加到单元格
			sheet.addCell(label);
			System.out.println("000999");
		}
		// 数据库连接
		try {
			Connection con = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql = "select * from user";
			con = DriverManager.getConnection(URL, NAME, PASSWORD);
			System.out.println("连接数据库成功了~");
			ps = con.prepareStatement(sql);// SQL预处理
			rs = ps.executeQuery();
			
			// ResultSet是数据库中的数据，将其转换为List类型
			List<User> list = new ArrayList();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPwd(rs.getString("pwd"));
				list.add(user);
			}
			ps.close();
			con.close();
			for (int i = 0; i < list.size(); i++) {
				// Number对应数据库的int类型数据
				sheet.addCell(new jxl.write.Number(0, i, list.get(i).getId()));
				// Label对应数据库String类型数据
				sheet.addCell(new Label(1, i, list.get(i).getName()));
				sheet.addCell(new Label(2, i, list.get(i).getPwd()));
			}
			wwb.write();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			wwb.close();
		}

	}

}

package com.Java.Demo;

import java.io.Serializable;

/**
 * 
 * @author Admin
 * 用户的实体类
 */
public class User implements Serializable{
	private static final long serialVersionUID = 1L;
	
	private int id;
	private String name;
	private String pwd;
	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 getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public User(int id, String name, String pwd) {
		super();
		this.id = id;
		this.name = name;
		this.pwd = pwd;
	}
	

}