[POI]数据库导出Excel

环境：eclipse luna、MySql、jdk1.7.0_79

java

package com.bhu.db;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

public class textATable {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost/test";
 
    static final String USER = "root";
    static final String PASS = "root";
	
	public static void createExcel() throws IOException, ClassNotFoundException, SQLException{
//		创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
//      创建工作表
        HSSFSheet sheet=wb.createSheet("成绩");
//		设置列宽
        sheet.setColumnWidth(0, 10*256);
        sheet.setColumnWidth(1, 10*256);
        sheet.setColumnWidth(2, 10*256);
        sheet.setColumnWidth(3, 10*256);
        sheet.setColumnWidth(4, 20*256);
        sheet.setColumnWidth(5, 10*256);
        sheet.setColumnWidth(6, 10*256);
        sheet.setColumnWidth(7, 10*256);
        sheet.setColumnWidth(8, 10*256);
        sheet.setColumnWidth(9, 10*256);
        
        HSSFFont font = wb.createFont();  
        font.setFontHeightInPoints((short) 10);  
        font.setFontName("宋体"); 
        
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font); 
        style.setWrapText(true);  
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        
        Connection conn = null;
	     Statement stmt = null;
	     Class.forName("com.mysql.jdbc.Driver");
	        
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
    
        stmt = conn.createStatement();
        String sql;
        sql = "SELECT * FROM score";
        ResultSet rs = stmt.executeQuery(sql);
        
        
    
        int rowNo = 1;
        int currRowNo = 1;
        
//		创建一个行
        HSSFRow row0=sheet.createRow(0);
//      创建单元格
        HSSFCell cellhead0=row0.createCell(0);
        HSSFCell cellhead1=row0.createCell(1);
        HSSFCell cellhead2=row0.createCell(2);
        HSSFCell cellhead3=row0.createCell(3);
        HSSFCell cellhead4=row0.createCell(4);
        HSSFCell cellhead5=row0.createCell(5);
        HSSFCell cellhead6=row0.createCell(6);
        HSSFCell cellhead7=row0.createCell(7);
        HSSFCell cellhead8=row0.createCell(8);
        HSSFCell cellhead9=row0.createCell(9);
//      写入表头
        cellhead0.setCellValue("序号");
        cellhead1.setCellValue("学号");
        cellhead2.setCellValue("姓名");
        cellhead3.setCellValue("学校");
        cellhead4.setCellValue("院系");
        cellhead5.setCellValue("操作系统");
        cellhead6.setCellValue("计算机网络");
        cellhead7.setCellValue("数据结构");
        cellhead8.setCellValue("总分");
        cellhead9.setCellValue("平均分");
        
        boolean flag = true;
        while(rs.next()){
        	if(flag){
        		currRowNo = rowNo;
            	String no = rs.getString("no");
                String name = rs.getString("name");
                String school = rs.getString("school");
                String college = rs.getString("college");
                int operating = rs.getInt("operating");
                int net = rs.getInt("net");
                int data = rs.getInt("data");
                int sum= operating + net + data;
                int average = sum/3;
                
//				创建一个行
                HSSFRow row1=sheet.createRow(rowNo);
//              创建一个单元格
                HSSFCell cell0=row1.createCell(0);
                HSSFCell cell1=row1.createCell(1);
                HSSFCell cell2=row1.createCell(2);
                HSSFCell cell3=row1.createCell(3);
                HSSFCell cell4=row1.createCell(4);
                HSSFCell cell5=row1.createCell(5);
                HSSFCell cell6=row1.createCell(6);
                HSSFCell cell7=row1.createCell(7);
                HSSFCell cell8=row1.createCell(8);
                HSSFCell cell9=row1.createCell(9);
                
                
//              写入数据
                cell0.setCellValue(rowNo);
                cell1.setCellValue(no);
                cell2.setCellValue(name);
                cell3.setCellValue(school);
                cell4.setCellValue(college);
                cell5.setCellValue(operating);
                cell6.setCellValue(net);
                cell7.setCellValue(data);
                cell8.setCellValue(sum);
                cell9.setCellValue(average);             
                               
                
                cell0.setCellStyle(style);
                cell1.setCellStyle(style);
                cell2.setCellStyle(style);
                cell3.setCellStyle(style);
                cell4.setCellStyle(style);
                cell5.setCellStyle(style);
                cell6.setCellStyle(style);
                cell7.setCellStyle(style);
                cell8.setCellStyle(style);
                cell9.setCellStyle(style);
                rowNo++;
                
                
                
        	}else{
            	String no = rs.getString("no");
                String name = rs.getString("name");
                String college = rs.getString("college");
                String data = rs.getString("data");

                HSSFRow row1=sheet.getRow(currRowNo);
                HSSFCell cell0=row1.createCell(2);
                cell0.setCellValue(name);
                cell0.setCellStyle(style);
                currRowNo++;
                                
        	}
        	flag = !flag;
        }
        rs.close();
        stmt.close();
        conn.close();
        
//        03版本是XLS结尾
        File f= new File("D:" + File.separator + "textaTableXIII.xls") ;
//        生成一张表    IO 流
        OutputStream output = new FileOutputStream(f) ;

        wb.write(output);
        output.close();
		
	}

	public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
		createExcel();
	}

}

数据库

结果

处于隐私保护，用表情包遮住真实的信息，没错这些都是真实的同学。