将数据库数据导入excel

所用技术:poi,记得导入相关依赖

 用的是原生的jdbc,只需要把数据库配置一下就行了

jdbc类

package com.enter.swing;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;
public class JDBC {

	 /**
     * 打开连接
     * @return
     */
    public static Connection getConn(){
        Connection conn = null;
        try {
            Class.forName("数据库驱动");
            conn = DriverManager.getConnection("数据库地址","账号","密码");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭连接
     */
    public static void closeDB(ResultSet rs, PreparedStatement pstmt,Connection conn){
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if(pstmt != null){
                pstmt.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if(conn != null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    
    public static void main(String[] args) {
		JDBC.getConn();
	}
}

测试类

package com.enter.swing;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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 Test7 {

	
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	/**
	 * 
	 * @param data 要打印的数据集合
	 * @param path 打印的路径
	 * @param title 标题(可以为空)
	 */
	@SuppressWarnings("rawtypes")
	public static void PrintExcel(ArrayList data,String path,String[] title){
	        // 1.创建工作簿对象
	        HSSFWorkbook workbook = new HSSFWorkbook();

	        // 2.创建工作表对象
	        HSSFSheet sheet = workbook.createSheet();

	        // 3.创建第一行
	        HSSFRow row = sheet.createRow(0);
	        HSSFCell cell = null;

	        if(title!=null){
	        	// 4.添加标题栏
		        for (int i = 0; i < title.length; i++) {
		            cell = row.createCell(i);
		            cell.setCellValue(title[i]);

		        }

		        // 5.向表格中插入数据
		        for (int i = 0; i <data.size(); i++) {
		            // 6.创建行对象
		            HSSFRow nextrow = sheet.createRow(i+1);
		            ArrayList als=(ArrayList)data.get(i);
		            HSSFCell cell2 = null;
		            for (int j = 0; j < als.size(); j++) {
		            	cell2 = nextrow.createCell(j);
		            	cell2.setCellValue(als.get(j)==null?"":als.get(j).toString());
					}
		        }
	        }else{
		        // 5.向表格中插入数据
		        for (int i = 0; i <data.size(); i++) {
		            // 6.创建行对象
		            HSSFRow nextrow = sheet.createRow(i);
		            ArrayList als=(ArrayList)data.get(i);
		            HSSFCell cell2 = null;
		            for (int j = 0; j < als.size(); j++) {
		            	cell2 = nextrow.createCell(j);
		            	cell2.setCellValue(als.get(j)==null?"":als.get(i).toString());
					}
		        }
	        }

	        File f = new File(path);
	        try {
	            f.createNewFile();
	            FileOutputStream stream=new FileOutputStream(f);
	            workbook.write(stream);
	            //workbook.close();
	            System.out.println("成生成Excel表格！");
	        } catch (Exception e) {

	            e.printStackTrace();
	        }
	}
	
	
	
	/**
     * 查询自定义SQL
     * @return
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public ArrayList getSQLdata(String sql){
    	ArrayList ls=new ArrayList();
    	try {
    		conn = JDBC.getConn();
    		pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
    		ResultSetMetaData metaData = rs.getMetaData();
    		int columnCount = rs.getMetaData().getColumnCount();//获取列数
    		while(rs.next()){
    			ArrayList lsv=new ArrayList();
    			for (int i = 0; i < columnCount; i++) { 
					String cname=metaData.getColumnName(i+1);//根据下标拿到列名
					Object object = rs.getObject(cname);//根据列名拿到数据
					if(object==null){
						lsv.add(object);
					}else{
						lsv.add(object.toString());
					}
				}
    			ls.add(lsv);
    		}
    			
    	} catch(Exception e){
    		
		}finally{
	       try{
	    	   JDBC.closeDB(rs, pstmt, conn);
 	       }catch(Exception ex){
	    	   
	       } 
		 }
    	return ls;
    } 
	
    public static void main(String[] args) {
    	Test7 t=new Test7();
    	ArrayList sqLdata = t.getSQLdata("select id,name,class_id from student");
    	//传入的数据格式是ArrayList<ArrayList> 
    	Test7.PrintExcel(sqLdata, "D:\\poi_test1.xls", new String[]{ "编号", "姓名", "性别" });
    }

}