jave导出mysql_java导出mysql数据到excel（poi）

其实从数据库中导出数据到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();

}

}

}