TableInfo

create VIEW [dbo].[TableInfo]
AS
SELECT DISTINCT
                      obj.name AS TableName, col.colid AS ColumnIndex, col.name AS ColumnName, scol.DATA_TYPE, col.length AS ColumnLength, CONVERT(varchar(500),
                      ISNULL(prop.value, N' ')) AS ColumnDesc
FROM         sys.sysobjects AS obj INNER JOIN
                      sys.syscolumns AS col ON col.id = obj.id INNER JOIN
                      INFORMATION_SCHEMA.COLUMNS AS scol ON scol.TABLE_NAME = obj.name AND scol.COLUMN_NAME = col.name LEFT OUTER JOIN
                      sys.extended_properties AS prop ON prop.major_id = obj.id AND prop.minor_id = col.colid AND prop.name = 'MS_Description'


GO

优化这段代码public static void readExcel(String fileName) { List<Version> versions = EasyExcel.read(fileName, Version.class, null).sheet("版本号").doReadSync(); List<TableInfo> tableInfoList = EasyExcel.read(fileName, TableInfo.class, null).sheet("数据库&数据表").doReadSync(); List<TableField> tableFieldList = EasyExcel.read(fileName, TableField.class, null).sheet("字段").doReadSync(); List<HopIndex> hopIndexList = EasyExcel.read(fileName, HopIndex.class, null).sheet("跳数索引").doReadSync(); Map<String, List<TableInfo>> tableInfoMap = tableInfoList.stream().collect(Collectors.groupingBy(TableInfo::getEnDataBaseName)); Map<String, List<TableField>> tableFieldMap = tableFieldList.stream().collect(Collectors.groupingBy(TableField::getEnTableName)); Map<String, List<HopIndex>> hopIndexMap = hopIndexList.stream().collect(Collectors.groupingBy(HopIndex::getEnTableName)); List<TableInfo> tableInfos1 = new ArrayList<>(); List<TableInfo> tableInfos2 = new ArrayList<>(); tableInfoMap.forEach((dbName, tableList) -> { if (tableList.size() < 200) { tableInfos1.addAll(tableList); } else { tableInfos2.addAll(tableList); } }); List<TableField> tableFields = new ArrayList<>(); List<HopIndex> hopIndex = new ArrayList<>(); for (TableInfo tableInfo : tableInfos1) { String enTableName = tableInfo.getEnTableName(); tableFields.addAll(tableFieldMap.get(enTableName)); List<HopIndex> hops = hopIndexMap.get(enTableName); if (hops !=null){ hopIndex.addAll(hops); } } String name = filepath + File.separator + "明细层_V1.0_" + DateUtil.date().toString(DatePattern.PURE_DATE_PATTERN) + "1.xls"; ExcelWriter excelWriter = EasyExcel.write(name).build(); ExcelUtil.writerExcel(excelWriter, "版本号", Version.class, versions); ExcelUtil.writerExcel(excelWriter, "数据库&数据表", TableInfo.class, tableInfos1); ExcelUtil.writerExcel(excelWriter, "字段", TableField.class, tableFields); ExcelUtil.writerExcel(excelWriter, "跳数索引", HopIndex.class, hopIndex); ExcelUtil.close(excelWriter); tableFields.clear(); hopIndex.clear(); for (TableInfo tableInfo : tableInfos2) { String enTableName = tableInfo.getEnTableName(); tableFields.addAll(tableFieldMap.get(enTableName)); List<HopIndex> hops = hopIndexMap.get(enTableName); if (hops !=null){ hopIndex.addAll(hops); } } name = filepath + File.separator + "明细层_V1.0" + DateUtil.date().toString(DatePattern.PURE_DATE_PATTERN) + "_2.xls"; excelWriter = EasyExcel.write(name).build(); ExcelUtil.writerExcel(excelWriter, "版本号", Version.class, versions); ExcelUtil.writerExcel(excelWriter, "数据库&数据表", TableInfo.class, tableInfos2); ExcelUtil.writerExcel(excelWriter, "字段", TableField.class, tableFields); ExcelUtil.writerExcel(excelWriter, "跳数索引", HopIndex.class, hopIndex); ExcelUtil.close(excelWriter); }
05-30
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值