import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class writeDataToExcel {
public static void main(String[] args) {
writerExcel(getListUser());
}
public static void writerExcel(List<User> listUser) {
// POI操作Excle 所有的类都以HSSF作为前缀
// 创建文档对象
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建工作单元
HSSFSheet sheet = workBook.createSheet("用户信息");
// 创建行
HSSFRow row = sheet.createRow(0);
// 创建单元格
HSSFCell cl0 = row.createCell(0);
HSSFCell cl1 = row.createCell(1);
HSSFCell cl2 = row.createCell(2);
// 往单元格中放数据
cl0.setCellValue("学号");
cl1.setCellValue("姓名");
cl2.setCellValue("密码");
// 循环users集合 给每个UserInfo创建新行 并赋值;
for (int i = 1; i <= listUser.size(); i++) {
HSSFRow newRow = sheet.createRow(i);
HSSFCell cl_1 = newRow.createCell(1);
User usInfo = listUser.get(i - 1);
cl_1.setCellValue(usInfo.getName());
}
FileOutputStream output = null;
// 输出流
try {
output = new FileOutputStream(new File("c:\\user.xls"));
// 将文档对象写入输出流
workBook.write(output);
// 刷新缓冲区
output.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != output) {
try {
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public static List<User> getListUser() {
Connection conn = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "system";
String password = "orcl";
String sql = "select * from scott.emp";
ResultSet rs = null;
Statement sm = null;
List<User> listUser = new ArrayList<User>();
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
sm = conn.createStatement();
rs = sm.executeQuery(sql);
while (rs.next()) {
User userClass = new User();
userClass.setName(rs.getString("ENAME"));
listUser.add(userClass);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return listUser;
}
}
class User {
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}