前言

在系统开发中最常用的功能就是列表数据的导出了，Java中常用的方法是用apache的POI进行操作的，现在我们就一起来学习下POI导出excel文件的基本用法。

需要的jar包

上代码

使用JDBC连接数据库获取导出的数据

MySQL数据库表结构

实体类代码

package com.cgp.excelexport.pojo;

public class Person {

    private Long id;

    private String name;

    private int age;

    private String phone;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", phone='" + phone + '\'' +
                '}';
    }
}

DBHelper连接MySQL数据库

package com.cgp.excelexport.utils;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBHelper {

    //MySQL数据库驱动
    private static final String driver = "com.mysql.jdbc.Driver";

    //数据库连接地址
    private static final String url = "jdbc:mysql://localhost:3306/test_1?useUnicode=true&characterEncoding=UTF-8";

    //数据库用户名
    private static final String username = "root";

    //数据库密码
    private static final String password = "root";

    //声明数据库连接对象
    private static Connection conn = null;

    //静态代码块先加载驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //创建数据库连接，供外部调用
    public static Connection getConnection() throws Exception
    {
        if(conn==null)
        {
            //连接数据库
            conn = DriverManager.getConnection(url, username, password);
            return conn;
        }
        return conn;
    }
}

这里有静态代码块，静态方法和静态的常量，小伙伴们可以思考下这三个在类中加载顺序，在下面的评论区互相讨论下。

数据操作Dao类

package com.cgp.excelexport.dao;

import com.cgp.excelexport.pojo.Person;
import com.cgp.excelexport.utils.DBHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class PersonDao {

    private static Connection conn = null;

    private static PreparedStatement pst = null;

    private static ResultSet rs = null;

    public List<Person> getPersonList() {
        List<Person> list = new ArrayList<>();
        try {
            conn = DBHelper.getConnection();
            String sql = "select * from person";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                Person person = new Person();
                person.setId(rs.getLong(1));
                person.setName(rs.getString(2));
                person.setAge(rs.getInt(3));
                person.setPhone(rs.getString(4));
                list.add(person);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

我这里使用最原始的JDBC来连接数据库，大家现实开发中会用到mybatis这种持久化框架，请以实际开发为准。

导出excel文件的工具类代码

package com.cgp.excelexport.utils;

import com.cgp.excelexport.pojo.Person;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExportExcelUtil {

    public static void ExportExcel(List<Person> list, String[] titles) {
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(0);
        //给单元格设置样式
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //设置字体加粗
        font.setBold(true);
        //给字体设置样式
        cellStyle.setFont(font);
        //设置单元格背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置单元格填充样式(使用纯色背景颜色填充)
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
            //设置列的宽度
            sheet.setColumnWidth(i, 200*50);
        }
        for (int j = 0; j < list.size(); j++) {
            Row rowData = sheet.createRow(j + 1);
            Person person = list.get(j);
            Cell cell = rowData.createCell(0);
            cell.setCellValue(person.getId());
            Cell cell2 = rowData.createCell(1);
            cell2.setCellValue(person.getName());
            Cell cell3 = rowData.createCell(2);
            cell3.setCellValue(person.getAge());
            Cell cell4 = rowData.createCell(3);
            cell4.setCellValue(person.getPhone());
        }
        String fileName = "D:/人员信息导出.xlsx";
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            wb.write(fileOutputStream);
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

运行测试代码

package com.cgp.excelexport.test;

import com.cgp.excelexport.dao.PersonDao;
import com.cgp.excelexport.pojo.Person;
import com.cgp.excelexport.utils.ExportExcelUtil;

import java.util.List;

public class ExportExcelTest {

    public static void main(String[] args) {
        PersonDao personDao = new PersonDao();
        List<Person> list = personDao.getPersonList();
        String[] titles = {"编号","姓名","年龄","手机号"};
        ExportExcelUtil.ExportExcel(list, titles);
        System.out.println("人员数据导出成功！");
    }
}

运行结果

可以看出excel文件成功的导出到了D盘目录下面

总结

以上就是Java中使用JDBC连接数据库获取表数据，使用POI导出excel文件的基本方法，本文仅供参考，请以实际开发业务为准！