本文以mysql数据为例：
public class exportExcel{
public static void main(String[] args) throws Exception {
CreateExcelDemo(connectDb());//根据数据库查询结果，导出到excel中
}
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/guizi?autoreconnect=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&autoReconnect=true&allowMultiQueries=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username,password);
return conn;
} catch (Exception e) {
throw new IllegalArgumentException(e);
}
}
public static List> connectDb(){
List> list=new ArrayList>();
Connection conn = getConnection();
Statement stmt =null;
ResultSet rs =null;
try {
String sql="select city,phone from socialize_user_main where DEL_FLAG=0 limit 120000";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
Map map=new HashMap();
String city=rs.getString("CITY");
String phone=rs.getString("PHONE");
map.put("CITY", city);
map.put("PHONE", phone);
list.add(map);
}
rs.close();
stmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
return list;
}
public static void CreateExcelDemo(List> list) throws Exception {
String fileName="";
// 第一步，创建一个webbook，对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet("用户信息");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short XSSFRow row = sheet.createRow((int) 0);
// 第四步，创建单元格，并设置值表头 设置表头居中
XSSFCellStyle style = wb.createCellStyle(); //style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 创建一个居中格式
XSSFCell cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell = row.createCell((short) 1);
cell.setCellValue("城市");
cell = row.createCell((short) 2);
cell.setCellValue("手机号");
// 第五步，写入实体数据 实际应用中这些数据从数据库得到
for(int i=0;i<list.size();i++){
HashMap map =(HashMap)list.get(i);
// 第四步，创建单元格，并设置值
row = sheet.createRow((int) i+1);
XSSFCell celli = row.createCell((short) 0);
row.createCell((short) 0).setCellValue(i);
if(map.get("CITY")!=null)
row.createCell((short) 1).setCellValue((String) map.get("CITY")); if(map.get("PHONE")!=null)
row.createCell((short) 2).setCellValue((String) map.get("PHONE"));
}
// 第六步，将文件存到指定位置
try {
fileName = "H:/export.xlsx";
FileOutputStream fout = new FileOutputStream(fileName);
wb.write(fout); fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
网友评论