java导出mysql数据到excel(poi)

2016-11-06  邓智群 

其实从数据库中导出数据到excel很简单,无非就是获取数据库的结果集

然后创建workbook,sheet,循环结果集写入对应的行和列中

下面的例子中表table有一条数据,三列:

import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ResultToExcel {
public static void main(String[]args){
String url = "jdbc:mysql://localhost:3306/test";
String filename = "D:\\eclipsework\\SelenTest\\data\\test.xlsx";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url,"root","xx");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from student");
ResultSetMetaData colName = rs.getMetaData();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("stuDB");
Row row = sheet.createRow((short)0);
Cell cell = null;
cell = row.createCell((short)0);
cell.setCellValue(colName.getColumnName(1));
cell = row.createCell((short)1);
cell.setCellValue(colName.getColumnName(2));
cell = row.createCell((short)2);
cell.setCellValue(colName.getColumnName(3));
int i =1;
while(rs.next()){
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(rs.getInt("number"));
cell = row.createCell(1);
cell.setCellValue(rs.getString("name"));
cell = row.createCell(2);
cell.setCellValue(rs.getInt("age"));
i++;
}
FileOutputStream FOut = new FileOutputStream(filename);
workbook.write(FOut);
FOut.flush();
FOut.close();
rs.close();
stmt.close();
conn.close();
System.out.println("Success");
}catch(Exception e){
e.printStackTrace();
}
}
}

588°/5883 人阅读/0 条评论 发表评论

登录 后发表评论