Java连接SqlServer数据库,数据导出为xls格式
主要代码:
package com.java.test;
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.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.excel.entity.User;
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 Administrator
* @date 2022年5月18日
*/
public class DataOutExcelTest {
private static final String URL = "jdbc:sqlserver://192.23.124.8;database=db_jkgd";
private static final String NAME = "sa";
private static final String PASSWORD = "sa@0123456";
public static void main(String[] args) throws SQLException, RowsExceededException, WriteException, IOException {
// 1. 导出Excel的路径
String filePath = "exportTest.xls";
WritableWorkbook wwb = null;
Connection connection = DriverManager.getConnection(URL, NAME, PASSWORD);
List<User> excelTests = new ArrayList<User>();
PreparedStatement preparedStatement = null;
String sql = "SELECT * FROM user";
ResultSet resultSet = null;
User exce = null;
try {
wwb = Workbook.createWorkbook(new File(filePath));
} catch (Exception e) {
e.printStackTrace();
}
// 创建Excel表的"用户信息"区域的数据
WritableSheet sheet = wwb.createSheet("用户信息", 0);// 或者rwb.getSheet(0)获取第一个区域
try {
// 2. 连接数据库的几行代码
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
exce = new User();
exce.set姓名(resultSet.getString("姓名"));
exce.set地(resultSet.getString("地"));
exce.set县(resultSet.getString("县"));
excelTests.add(exce);
}
// 向单元格添加表头信息
sheet.addCell(new Label(0, 0, "姓名"));
sheet.addCell(new Label(1, 0, "地"));
sheet.addCell(new Label(2, 0, "县"));
for (int i = 0; i < excelTests.size(); i++) {
// Number对应数据库的int类型数据
sheet.addCell(new Label(0, i + 1, excelTests.get(i).get姓名()));
// Label对应数据库String类型数据
sheet.addCell(new Label(1, i + 1, excelTests.get(i).get地()));
sheet.addCell(new Label(2, i + 1, excelTests.get(i).get县()));
}
wwb.write();
} catch (SQLException e) {
e.printStackTrace();
} finally {
wwb.close();
}
System.out.println("excel文件输出成功");
}
}
数据库对应的实体类对象
package com.excel.entity;
/**
* @author Administrator
* @date 2022年5月18日
*/
public class User {
private String 姓名;
private String 省;
private String 地;
private String 县;
private String 籍贯;
public String get姓名() {
return 姓名;
}
public void set姓名(String 姓名) {
this.姓名 = 姓名;
}
public String get省() {
return 省;
}
public void set省(String 省) {
this.省 = 省;
}
public String get地() {
return 地;
}
public void set地(String 地) {
this.地 = 地;
}
public String get县() {
return 县;
}
public void set县(String 县) {
this.县 = 县;
}
public String get籍贯() {
return 籍贯;
}
public void set籍贯(String 籍贯) {
this.籍贯 = 籍贯;
}
public User() {
super();
}
public User(String 姓名, String 省, String 地, String 县, String 籍贯) {
super();
this.姓名 = 姓名;
this.省 = 省;
this.地 = 地;
this.县 = 县;
this.籍贯 = 籍贯;
}
@Override
public String toString() {
return "User [姓名=" + 姓名 + ", 省=" + 省 + ", 地=" + 地 + ", 县=" + 县 + ", 籍贯=" + 籍贯 + "]";
}
}