导出数据库表信息到Excel

导出数据库表信息到excel

当涉及到数据导出以及 Excel 文件处理时，经常会涉及到对数据进行整理、处理，并将其导出到 Excel 文件中。

在 Java 开发中，使用 Apache POI 库可以方便地处理 Excel 文件的读写操作。

今天我将向大家介绍如何通过 Java 代码实现从数据库表中提取数据，整理输出到 Excel 文件的过程。我们将使用 Spring 框架和 Apache POI 库来实现这个过程。

步骤一:

首先,我们需要创建一个实体类 TableSch，用于映射数据库表结构信息。

import com.baomidou.mybatisplus.annotation.TableField;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

@Data
public class TableSch implements Serializable{

    @ApiModelProperty(value = "源表名")
    @TableField("TABLE_NAME")
    private String tableName;

    @ApiModelProperty(value = "源表中文名")
    @TableField("TABLE_COMMENT")
    private String tableComment;

    @ApiModelProperty(value = "字段序号")
    @TableField("ORDINAL_POSITION")
    private Integer ordinalPosition;

    @ApiModelProperty(value = "字段中文名称")
    @TableField("COLUMN_COMMENT")
    private String columnComment;

    @ApiModelProperty(value = "字段英文名称")
    @TableField("COLUMN_NAME")
    private String columnName;

    @ApiModelProperty(value = "字段类型")
    @TableField("COLUMN_TYPE")
    private String columnType;

    @ApiModelProperty(value = "是否允许为空")
    @TableField("IS_NULLABLE")
    private String isNullable;

    @ApiModelProperty(value = "是否主键")
    @TableField("COLUMN_KEY")
    private String columnKey;
}

步骤二:

然后,再编写一个数据访问层接口 TableSchMapper，用于数据库操作。

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.myspace.entity.TableSch;

import java.util.List;

public interface TableSchMapper extends BaseMapper<TableSch> {

    List<TableSch> findTableSch();
}

步骤三:

在mapper.xml中编写sql:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myspace.mapper.TableSchMapper">

    <select id="findTableSch" resultType="com.myspace.entity.TableSch">
        SELECT
            t1.table_name,
            t2.table_comment,
            t1.ordinal_position,
            t1.column_comment,
            t1.column_name,
            t1.column_type,
        IF
            ( t1.IS_NULLABLE = 'NO', 'N', 'Y' ) IS_NULLABLE,
        IF
            ( t1.COLUMN_KEY = 'PRI', 'Y', 'N' ) COLUMN_KEY
        FROM
            (
            SELECT
                *
            FROM
                information_schema.COLUMNS
            WHERE
                table_schema = '数据库实例名'
        		and table_name in ('sys_user', 'sys_role', 'sys_region')
            ) t1
            INNER JOIN ( SELECT * FROM information_schema.TABLES WHERE table_schema = '数据库实例名' AND table_type = 'BASE TABLE' ) t2
                ON t1.table_name = t2.table_name
    </select>
</mapper>

步骤四:

接着，编写一个测试类 ExcelRW。

方式一: 使用 Apache POI 库的方式。

引入需要的依赖包。

<properties>
	<poi.version>3.17</poi.version>
	<poi.ooxml.version>3.17</poi.ooxml.version>
	<poi.schemas.version>3.17</poi.schemas.version>
</properties>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.ooxml.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.schemas.version}</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>${poi.version}</version>
</dependency>

创建一个 XSSFWorkbook，遍历数据库中的数据并按照表名进行分组。然后，创建 Sheet，设置表头，逐行写入数据，并最终将数据输出到 Excel 文件中。

import com.myspace.entity.TableSch;
import com.myspace.mapper.TableSchMapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@SpringBootTest(classes = TestApplication.class)
@RunWith(SpringRunner.class)
public class ExcelRW {

    @Autowired
    private TableSchMapper tableSchMapper;

    @Test
    public void exportTableStructureOld() throws IOException {
        //创建一个输出工作簿
        Workbook wb = new XSSFWorkbook();
        //设置表头列表
        String[] titles = {"源表名", "源表中文名", "字段序号", "字段中文名称", "字段英文名称", "字段类型", "是否允许为空", "是否主键"};

        //获取输出流
        List<TableSch> tableSchList = tableSchMapper.findTableSch();
        Map<String, List<TableSch>> collectMap = tableSchList.parallelStream().collect(Collectors.groupingBy(TableSch::getTableName));
        int s = 1;
        for (Map.Entry<String, List<TableSch>> entry : collectMap.entrySet()) {
            Sheet sheet;
            Cell cell;
            //获取行内容
            List<TableSch> lists = entry.getValue();

            //创建单元格
            if (!StringUtils.isEmpty(entry.getValue().get(0).getTableComment()) && entry.getValue().get(0).getTableComment().length()<30) {
                sheet = wb.createSheet(entry.getValue().get(0).getTableComment());
            } else {
                sheet = wb.createSheet("table" + s);
            }
            s++;

            //创建表头行
            Row row = sheet.createRow(0);
            row.setHeightInPoints(26);
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i]);
            }

            for (int k = 0; k < lists.size(); k++) {
                row = sheet.createRow(k + 1);
                row.setHeightInPoints(20);
                cell = row.createCell(0);
                cell.setCellValue(lists.get(k).getTableName());

                cell = row.createCell(1);
                cell.setCellValue(lists.get(k).getTableComment());

                cell = row.createCell(2);
                cell.setCellValue(lists.get(k).getOrdinalPosition());

                cell = row.createCell(3);
                cell.setCellValue(lists.get(k).getColumnComment());

                cell = row.createCell(4);
                cell.setCellValue(lists.get(k).getColumnName());

                cell = row.createCell(5);
                cell.setCellValue(lists.get(k).getColumnType());

                cell = row.createCell(6);
                cell.setCellValue(lists.get(k).getIsNullable());

                cell = row.createCell(7);
                cell.setCellValue(lists.get(k).getColumnKey());
            }
        }

        //这里使用时间戳生成文件名，将导出的 Excel 文件保存到指定的目录中。(方便测试的时候区分哪个文件为最新导出的文件)
        SimpleDateFormat format = new SimpleDateFormat("MMdd_HH时mm分ss秒");
        String fileName = format.format(new Date());

        //输出内容
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Gedi_qin\\Desktop\\" + fileName + ".xlsx");
        wb.write(fileOutputStream);

    }

}

导出效果:

方式二:使用hutool中 ExcelUtil 工具类来简化代码(建议使用)

该方式将数据直接写入到 Excel 文件中，并按照表名分组输出到不同的 Sheet 中。通过设置表头别名和排序规则，可以更加灵活地处理数据输出。

引入hutool工具包依赖:

<properties>
    <hutool.version>5.6.3</hutool.version>
    <hutool-poi.version>4.6.17</hutool-poi.version>
</properties>  

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-core</artifactId>
    <version>${hutool.version}</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-poi</artifactId>
    <version>${hutool-poi.version}</version>
</dependency>

编写测试代码:

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.myspace.entity.TableSch;
import com.myspace.mapper.TableSchMapper;
import io.swagger.annotations.ApiModelProperty;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@SpringBootTest(classes = TestApplication.class)
@RunWith(SpringRunner.class)
public class ExcelRW {

    @Autowired
    private TableSchMapper tableSchMapper;

    @Test
    public void exportTableStructure(){
        List<TableSch> tableSchList = tableSchMapper.findTableSch();
        Map<String, List<TableSch>> collectMap = tableSchList.parallelStream().collect(Collectors.groupingBy(TableSch::getTableName));

        SimpleDateFormat format = new SimpleDateFormat("MMdd_HH时mm分ss秒");
        String fileName = format.format(new Date());
        ExcelWriter excelWriter = ExcelUtil.getWriter("C:\\Users\\Gedi_qin\\Desktop\\数据字典_" + fileName + ".xlsx");
        int indexSheet= 0;
        for (Map.Entry<String, List<TableSch>> entry : collectMap.entrySet()) {
            //获取行内容
            List<TableSch> lists = entry.getValue();
            //按序号排下序
            List<TableSch> resList = lists.stream().sorted(Comparator.comparing(TableSch::getOrdinalPosition)).collect(Collectors.toList());
            Map<String, String> headerAlias = getStringMap(resList);
            if (indexSheet == 0) {
                //重命名第一个sheet页, 不然导出后会多出一个空白的sheet1页
                excelWriter.renameSheet(0, resList.get(0).getTableComment());
            } else {
                excelWriter.setSheet(resList.get(0).getTableComment());
            }
            if ( ! headerAlias.isEmpty()) {
                excelWriter.setHeaderAlias(headerAlias);
                excelWriter.setOnlyAlias(true);
            }
            excelWriter.write(resList);
            indexSheet++;
        }

        // 关闭写入对象，释放资源
        excelWriter.close();
    }

    /**
     * 获取字段名
     * @param excelList 导出数据集
     * @return 表头字段名称(表头)
     */
    private static <T> Map<String, String> getStringMap(List<T> excelList) {
        T portraitBean = excelList.get(0);
        Class<?> aClass = portraitBean.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        Map<String, String> headerAlias = new LinkedHashMap<>();
        for (Field field : declaredFields) {
            ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class);
            if (annotation != null) {
                headerAlias.put(field.getName(), annotation.value());
            }
        }
        return headerAlias;
    }
}

测试效果:

使用hutool工具这项操作旨在帮助开发人员更加方便地从数据库中提取结构化数据，并导出到 Excel 文件中进行数据分析和共享。通过整理数据并以 Excel 文件形式输出，可以提高数据可视化和共享的效率。
希望这篇文章对您有所帮助!!!