关闭

jdbc导出数据库数据到Excel表格

1019人阅读 评论(2) 收藏 举报
分类:
package com.jiepu.docbuilder.poi;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.*;

public class ExcelDatabase
{
    public static void main(String[] args) throws Exception
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connect = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mysql" ,
                "root" ,
                "mysql0774mysql"
        );
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement
                .executeQuery("select * from help_topic");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadsheet = workbook
                .createSheet("help_topic");
        XSSFRow row=spreadsheet.createRow(0);
        XSSFCell cell;

        // 获取列名 resultSet数据下标从1开始
        ResultSetMetaData metaData = resultSet.getMetaData();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            int index=i+1;
            String columnName = metaData.getColumnName(index);
            //System.out.println(columnName + "\t");
            cell=row.createCell(i);
            cell.getCellStyle().setWrapText(true);
            cell.setCellValue(columnName);
        }

        int i=1;
        while(resultSet.next())
        {
            row=spreadsheet.createRow(i);
            for (int j = 0; j< metaData.getColumnCount(); j++) {
                int index=j+1;
                cell=row.createCell(j);
                /*
                XSSFCellStyle cellStyle=workbook.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                cellStyle.setWrapText(true);
                cell.setCellStyle(cellStyle);
                */
                cell.setCellValue(resultSet.getString(index));
            }
            i++;
        }

        FileOutputStream out = new FileOutputStream(new File("excel_database.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("excel_database.xlsx written successfully");
    }
}

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1701429次
    • 积分:24566
    • 等级:
    • 排名:第247名
    • 原创:780篇
    • 转载:383篇
    • 译文:0篇
    • 评论:215条
    我的技术博客
    最新评论