java脚本之数据表生成excel

根据数据表生成excel，
用途：若配置繁杂，业务人员根据模板填写配置数据，开发人员可直接导入使用

第三方包：

<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-spring-boot-starter</artifactId>
	<version>4.2.0</version>
</dependency>

脚本内容

import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.hutool.core.collection.CollectionUtil;
import ef.accounting_core.mapper.ISqlMapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
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.File;
import java.io.FileOutputStream;
import java.util.*;

/**
 * Title: TableExcelSheetJobs
 *
 * @author jason
 * 表名+字段名+注释+数据
 * 生成excel用途：
 * 备份
 * 初期，若配置繁杂，业务人员根据模板填写配置数据，开发人员可直接导入使用
 * 可直接使用工具导入至数据库，如Navicat等
 */
@SpringBootTest
@RunWith(SpringRunner.class)
@SuppressWarnings("unchecked")
public class TableExcelSheetJobs {

	private static final String DATABASE_NAME = "CF_DB";// 数据库名
	private static final Boolean IS_TABLE_DATA = false;// 是否需要数据

	@Autowired
	private ISqlMapper iSqlMapper;

	// 需要做模板的表
	private static String[] tableList = new String[]{
			"ORG_CONF",
			"ORG_CON_REL",
			"ORG_CON_CONF",
			"ORG_RATE_CONF",
			"APL_BUSI_MON_AMT",
			"DEDUCT_MERID_CONF",
			"TFEE_RATE_CONF",
			"ORG_ACC_CONF",
	};

	@Test
	public void test() throws Exception {
		// 多sheet导出
		List<Map<String, Object>> sheetList = new ArrayList<>();

		for (String table : tableList) {
			// 获取表字段+说明
			String tableSql = "SELECT\n" +
					"COLUMN_NAME 字段名称,\n" +
					"COLUMN_TYPE 数据类型,\n" +
					"IF(IS_NULLABLE='NO','是','否') AS '必填',\n" +
					"COLUMN_COMMENT 注释\n" +
					"FROM\n" +
					"INFORMATION_SCHEMA.COLUMNS\n" +
					"where\n" +
					"-- Finance为数据库名称，到时候只需要修改成你要导出表结构的数据库即可\n" +
					"table_schema ='" + DATABASE_NAME + "'\n" +
					"AND\n" +
					"-- user为表名，到时候换成你要导出的表的名称\n" +
					"-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了\n" +
					"table_name = '" + table + "'";
			List<Map<String, Object>> tableList = iSqlMapper.selectList(tableSql);

			List<Map<String, Object>> tableDataList = CollectionUtil.newArrayList();
			// 判断是否需要获取表数据
			if (IS_TABLE_DATA) {
				String tableDataSql = "SELECT * FROM " + table;
				tableDataList = iSqlMapper.selectList(tableDataSql);
			}

			// 表头(表头和数据做映射用)
			List<ExcelExportEntity> entityList = new ArrayList<>();
			// 英文字段放第二行
			Map<String, Object> tableFieldMap = new HashMap<>();

			for (Map<String, Object> map : tableList) {
				String key = String.valueOf(map.get("字段名称"));// 英文
				String value = String.valueOf(map.get("注释"));// 中文
				entityList.add(new ExcelExportEntity(value, key));
				tableFieldMap.put(key, key);
			}
			tableDataList.add(0, tableFieldMap);
			// sheet
			Map<String, Object> sheetExportMap = new HashMap<>();
			sheetExportMap.put(NormalExcelConstants.CLASS, ExcelExportEntity.class);
			sheetExportMap.put(NormalExcelConstants.DATA_LIST, tableDataList);
			ExportParams params = new ExportParams();
			params.setSheetName(table);
			sheetExportMap.put(NormalExcelConstants.PARAMS, params);
			sheetExportMap.put(NormalExcelConstants.MAP_LIST, entityList);

			sheetList.add(sheetExportMap);
		}
		// 导出
		Workbook workbook = new HSSFWorkbook();
		for (Map<String, Object> map : sheetList) {
			ExcelExportService server = new ExcelExportService();
			ExportParams param = (ExportParams) map.get(NormalExcelConstants.PARAMS);
			List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get(NormalExcelConstants.MAP_LIST);
			Collection<?> data = (Collection<?>) map.get(NormalExcelConstants.DATA_LIST);
			server.createSheetForMap(workbook, param, entity, data);
		}
		File saveFilePath = new File("D:/temp/excel");
		if (!saveFilePath.exists()) {
			saveFilePath.mkdirs();
		}
		FileOutputStream fos = new FileOutputStream(saveFilePath.getPath() + "/表结构+注释+数据.xls");
		workbook.write(fos);
		fos.close();
	}

}

mybatis直接写sql的Mapper

import java.util.List;
import java.util.Map;

/**
 * Title: ISqlMapper
 *
 * @author jason
 */
public interface ISqlMapper {

	Integer insert(String statement);

	Integer delete(String statement);

	Integer update(String statement);

	List<Map<String, Object>> selectList(String statement);

	Map<String, Object> selectOne(String statement);

}

xml

<?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="ef.accounting_core.mapper.ISqlMapper">

    <insert id="insert" parameterType="java.lang.String">
        ${value}
    </insert>

    <select id="selectList" parameterType="java.lang.String" resultType="java.util.Map" useCache="false">
        ${value}
    </select>

    <select id="selectOne" parameterType="java.lang.String" resultType="java.util.Map" useCache="false">
        ${value}
    </select>

    <delete id="delete" parameterType="java.lang.String">
        ${value}
    </delete>

    <update id="update" parameterType="java.lang.String">
        ${value}
    </update>

</mapper>

生成的excel