将数据库数据导到excel(xls)中

最新推荐文章于 2022-07-20 11:35:59 发布

高进123

最新推荐文章于 2022-07-20 11:35:59 发布

阅读量369

点赞数

分类专栏：数据库文章标签：数据库数据 excel

本文链接：https://blog.csdn.net/lengjinghk/article/details/52204705

版权

数据库专栏收录该内容

13 篇文章 1 订阅

订阅专栏

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import cn.hncu.proxy.ConnsUtil;

public class DatabaseToExcelUtil {

    public static void main(String[] args) {
        try {
            Connection con = ConnsUtil.getConnection();
            DatabaseMetaData dmd = con.getMetaData();
            ResultSet rs = dmd.getCatalogs();
            List<String>dbNames = new ArrayList<String>();
            while(rs.next()){
                dbNames.add(rs.getString(1));
            }
            con.close();
            writeToExcel(dbNames);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static void writeToExcel(List<String> dbNames) throws Exception {
        Connection con = ConnsUtil.getConnection();
        for(String dbName:dbNames){
            con.createStatement().execute("use "+dbName);
            ResultSet rs = con.getMetaData().getTables(dbName, dbName, null,new String[]{"TABLE"});
            List<String>tbNames = new ArrayList<String>();
            while(rs.next()){
                tbNames.add(rs.getString("TABLE_NAME"));
            }
            if(tbNames.size()==0)
                continue;
            HSSFWorkbook book = new HSSFWorkbook();
            for(String tbName:tbNames){
                Sheet sheet = book.createSheet(tbName);
                String sql = "select * from "+dbName+"."+tbName;
                ResultSet res = con.createStatement().executeQuery(sql);
                ResultSetMetaData rsmd = res.getMetaData();
                int cols = rsmd.getColumnCount();
                Row row = sheet.createRow(0);
                for(int i=0;i<cols;i++){
                    row.createCell(i).setCellValue(rsmd.getCatalogName(i+1));
                }
                int index = 1;
                while(res.next()){
                    Row row2 = sheet.createRow(index++);
                    for(int i=0;i<cols;i++){
                        row2.createCell(i).setCellValue(res.getString(i+1));
                    }
                }   
            }       
            FileOutputStream fos = new FileOutputStream(dbName+".xls");
            book.write(fos);
        }

    }

}