@ApiOperation(value = "系统导出")
@PostMapping(value = "/vertical_exportData")
public void verticalExportData(@RequestBody CatalogSystem columns, HttpServletRequest request,
HttpServletResponse response) {
catalogSystemService.verticalExportData(columns, request, response);
}
@Override
public void verticalExportData(CatalogSystem o, HttpServletRequest request, HttpServletResponse response) {
String[] rowNamesEn = o.getColumns();
if (ObjectUtil.isNull(rowNamesEn)) {
rowNamesEn = new String[1];
}
String[] rowNames = new String[rowNamesEn.length];
List<String> ens = Arrays.asList(ROW_NAME_EN_EXPORT);
for (int i = 0; i < rowNamesEn.length; i++) {
if (ens.indexOf(rowNamesEn[i]) == -1) {
continue;
}
rowNames[i] = ROW_NAME_EXPORT[ens.indexOf(rowNamesEn[i])];
}
// 自定义标题别名,换成链表表头顺序会固定
Map<String, String> headerAlias = ExcelUtil.getFirstRow(rowNames, rowNamesEn, ExcelUtil.OUT);
GovmadeUser user = SecurityUtils.getUser();
Integer userDeptId = user.getDeptId();
QueryWrapper<CatalogSystem> ew = new QueryWrapper<>();
Set<Long> searchIds = CollUtil.newHashSet();
// 标记成当前部门为使用部门的系统,只获取垂管引用
Set<Long> catalogIdsByDeptId = catalogSystemRelationService.getCatalogIdsByDeptId(userDeptId, 1);
if (!CollUtil.isEmpty(catalogIdsByDeptId)) {
searchIds.addAll(catalogIdsByDeptId);
}
// 标记成来源为引用统建的当前部门的系统
Set<Long> quoteSystemIds = this.getCatalogIdsByDeptId(userDeptId);
if (!CollUtil.isEmpty(quoteSystemIds)) {
searchIds.addAll(quoteSystemIds);
}
if (CollUtil.isEmpty(searchIds)) {
searchIds.add(0L);
}
ew.in("id", searchIds);
// 系统名称
if (!ObjectUtils.isEmpty(o.getName())) {
ew.like("name", o.getName().replaceAll("%", "/%"));
}
// 建设部门名称
if (!ObjectUtils.isEmpty(o.getDeptId())) {
ew.eq("dept_id", o.getDeptId());
}
// 状态
if (!ObjectUtils.isEmpty(o.getApplicationAudit())) {
ew.eq("application_audit", o.getApplicationAudit());
}
if (!ObjectUtils.isEmpty(o.getSystemStatus())) {
ew.eq("system_status", o.getSystemStatus());
}
// 是否编目
if (!ObjectUtils.isEmpty(o.getState())) {
if (StringUtils.equals(o.getState(), "1")) {
ew.ne("cata_num", 0);
} else {
ew.eq("cata_num", 0);
}
}
// 应用领域名称
if (!ObjectUtils.isEmpty(o.getAreaName())) {
ew.eq("area_name", o.getAreaName());
}
// 系统建设层级
if (!ObjectUtils.isEmpty(o.getConstructionLevel())) {
ew.eq("construction_level", o.getConstructionLevel());
}
if (!ObjectUtils.isEmpty(o.getOrderNo())) {
if (!ObjectUtils.isEmpty(o.getOrderDesc()) && "1".equals(o.getOrderDesc().toString())) {
Arrays.stream(o.getOrderNo().split(",")).forEach(p -> {
if ("1".equals(p)) {
ew.orderByDesc("visit_count");
} else if ("2".equals(p)) {
ew.orderByDesc("update_time");
}
});
} else {
Arrays.stream(o.getOrderNo().split(",")).forEach(p -> {
if ("1".equals(p)) {
ew.orderByAsc("visit_count");
} else if ("2".equals(p)) {
ew.orderByAsc("update_time");
}
});
}
}
ew.orderByDesc("update_time");
ew.eq("del_flag", "0");
Page page = new Page(o.getCurrent(), o.getSize());
Page<CatalogSystem> resultPage = baseMapper.selectPage(page, ew);
List<CatalogSystem> catalogSystemList = resultPage.getRecords();
// 获取字典项
List<String> listDictType = Arrays.asList("INFORMATION_SYSTEM_TYPE", "CONSTRUCTION_BASIS", "SCOPE_VERTICAL",
"CONSTRUCTION_LEVEL", "SOURCES_FUNDING", "SCOPE_AREA", "SCOPE_USERS", "SECURITY_SERVICE_TYPE",
"SYSTEM_DEPLOYMENT", "SYSTEN_STATE", "NETWORK_ENV", "DATABASE_FORMAT", "LEVEL_PROTECTION",
"SYSTEM_FIELD", "YES_NO", "CENSUS_SYSTEM_STATUS", "CATALOG_SYSTEM_SOURCETYPE");
Map<String, Map<String, String>> mapDictItemLabels = remoteUpmsService.getDictItemLabels(listDictType);
catalogSystemList.forEach(cls -> {
cls.setSystemTypeInformation(mapDictItemLabels.getOrDefault("INFORMATION_SYSTEM_TYPE", new HashMap<>(1))
.getOrDefault(cls.getSystemTypeInformation(), ""));
cls.setMakeDesc(mapDictItemLabels.getOrDefault("CONSTRUCTION_BASIS", new HashMap<>(1))
.getOrDefault(cls.getMakeDesc(), ""));
cls.setVerticalType(mapDictItemLabels.getOrDefault("SCOPE_VERTICAL", new HashMap<>(1))
.getOrDefault(cls.getVerticalType(), ""));
cls.setSourceConstructionFunds(mapDictItemLabels.getOrDefault("SOURCES_FUNDING", new HashMap<>(1))
.getOrDefault(cls.getSourceConstructionFunds(), ""));
cls.setSourceType(mapDictItemLabels.getOrDefault("CATALOG_SYSTEM_SOURCETYPE", new HashMap<>(1))
.getOrDefault(cls.getSourceType(), ""));
cls.setIsUnified(
mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getIsUnified(), ""));
// 设置使用范围-区域
String[] regions = StrUtil.split(cls.getRegion(), ",");
String region = "";
for (String df : regions) {
region += mapDictItemLabels.getOrDefault("SCOPE_AREA", new HashMap<>(1)).getOrDefault(df, "") + "，";
}
region = StrUtil.sub(region, 0, region.length() - 1);
cls.setRegion(region);
// 设置使用范围-用户
String[] subscribers = StrUtil.split(cls.getSubscriber(), ",");
String subscriber = "";
for (String df : subscribers) {
subscriber += mapDictItemLabels.getOrDefault("SCOPE_USERS", new HashMap<>(1)).getOrDefault(df, "")
+ "，";
}
subscriber = StrUtil.sub(subscriber, 0, subscriber.length() - 1);
cls.setSubscriber(subscriber);
cls.setConstructionLevel(mapDictItemLabels.getOrDefault("CONSTRUCTION_LEVEL", new HashMap<>(1))
.getOrDefault(cls.getConstructionLevel(), ""));
cls.setSecurityServiceType(mapDictItemLabels.getOrDefault("SECURITY_SERVICE_TYPE", new HashMap<>(1))
.getOrDefault(cls.getSecurityServiceType(), ""));
String resourceList = "";
if (StrUtil.contains("1,2", cls.getSystemDeployment())) {
resourceList += "物理服务器：" + cls.getPhysicalServer() + "，存储：" + cls.getStorageName() + "，数据库："
+ cls.getDatabaseName() + "，其他：" + cls.getPhysicalOther();
} else if (StrUtil.contains("3,4", cls.getSystemDeployment())) {
QueryWrapper<CatalogSystemResources> qw = new QueryWrapper<>();
qw.lambda().eq(CatalogSystemResources::getSystemId, cls.getId());
List<CatalogSystemResources> resources = catalogSystemResourcesMapper.selectList(qw);
if (CollUtil.isNotEmpty(resources)) {
for (CatalogSystemResources r : resources) {
if (StrUtil.isNotBlank(r.getEcs())) {
resourceList += "块存储（ECS云盘）：" + r.getEcs() + "，";
}
if (StrUtil.isNotBlank(r.getOss())) {
resourceList += "对象存储（OSS）：" + r.getOss() + "，";
}
if (StrUtil.isNotBlank(r.getNas())) {
resourceList += "文件存储（NAS）：" + r.getNas() + "，";
}
if (StrUtil.isNotBlank(r.getVpc())) {
resourceList += "专有网络（VPC）：" + r.getVpc() + "，";
}
if (StrUtil.isNotBlank(r.getEip())) {
resourceList += "弹性公网IP（EIP）：" + r.getEip() + "，";
}
if (StrUtil.isNotBlank(r.getNat())) {
resourceList += "NAT网关：" + r.getNat() + "，";
}
if (StrUtil.isNotBlank(r.getSlb())) {
resourceList += "负载均衡（SLB）：" + r.getSlb() + "，";
}
if (StrUtil.isNotBlank(r.getRds())) {
resourceList += "云数据库RDS：" + r.getRds() + "，";
}
if (StrUtil.isNotBlank(r.getRedis())) {
resourceList += "云数据库Redis版：" + r.getRedis() + "，";
}
if (StrUtil.isNotBlank(r.getMongodb())) {
resourceList += "云数据库MongoDB版（MongoDB）：" + r.getMongodb() + "，";
}
if (StrUtil.isNotBlank(r.getGpdb())) {
resourceList += "AnalyticDB for PostgreSQL（原gpdb）：" + r.getGpdb() + "，";
}
if (StrUtil.isNotBlank(r.getPetadata())) {
resourceList += "HybridDB for MySQL（原PetaData）：" + r.getPetadata() + "，";
}
if (StrUtil.isNotBlank(r.getEcsyun())) {
resourceList += "云服务器（ECS）：" + r.getEcsyun() + "，";
}
if (StrUtil.isNotBlank(r.getDts())) {
resourceList += "数据传输服务（DTS）：" + r.getDts() + "，";
}
if (StrUtil.isNotBlank(r.getDrds())) {
resourceList += "分布式关系型数据库（DRDS）：" + r.getDrds() + "，";
}
if (StrUtil.isNotBlank(r.getMq())) {
resourceList += "消息队列（MQ）态势感知服务：" + r.getMq() + "，";
}
if (StrUtil.isNotBlank(r.getDtgz())) {
resourceList += "消息队列（MQ）态势感知服务：" + r.getDtgz() + "，";
}
if (StrUtil.isNotBlank(r.getZjaq())) {
resourceList += "主机安全服务：" + r.getZjaq() + "，";
}
if (StrUtil.isNotBlank(r.getEdas())) {
resourceList += "企业级分布式应用服务（EDAS）：" + r.getEdas() + "，";
}
}
resourceList = StrUtil.sub(resourceList, 0, resourceList.length() - 1);
}
}
cls.setResourceList(resourceList);
// 系统部署情况
cls.setSystemDeployment(mapDictItemLabels.getOrDefault("SYSTEM_DEPLOYMENT", new HashMap<>(1))
.getOrDefault(cls.getSystemDeployment(), ""));
// 设置系统状态
cls.setSystemStatus(mapDictItemLabels.getOrDefault("SYSTEN_STATE", new HashMap<>(1))
.getOrDefault(cls.getNetworkEnvironment(), ""));
cls.setNetworkEnvironment(mapDictItemLabels.getOrDefault("NETWORK_ENV", new HashMap<>(1))
.getOrDefault(cls.getNetworkEnvironment(), ""));
// 重新设置数据库格式
String[] databaseFormats = StrUtil.split(cls.getDatabaseFormat(), ",");
String databaseFormat = "";
for (String df : databaseFormats) {
databaseFormat += mapDictItemLabels.getOrDefault("DATABASE_FORMAT", new HashMap<>(1)).getOrDefault(df,
"") + "，";
}
databaseFormat = StrUtil.sub(databaseFormat, 0, databaseFormat.length() - 1);
cls.setDatabaseFormat(databaseFormat);
cls.setGradeProtection(mapDictItemLabels.getOrDefault("LEVEL_PROTECTION", new HashMap<>(1))
.getOrDefault(cls.getGradeProtection(), ""));
cls.setAreaName(mapDictItemLabels.getOrDefault("SYSTEM_FIELD", new HashMap<>(1))
.getOrDefault(cls.getAreaName(), ""));
cls.setIsVertical(
mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getIsVertical(), ""));
cls.setIsSynergy(
mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getIsSynergy(), ""));
cls.setIsCorpse(
mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getIsCorpse(), ""));
cls.setDepartmentsBeingUsed(mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1))
.getOrDefault(cls.getDepartmentsBeingUsed(), ""));
cls.setIsIndependent(mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1))
.getOrDefault(cls.getIsIndependent(), ""));
cls.setApplicationAudit(mapDictItemLabels.getOrDefault("CENSUS_SYSTEM_STATUS", new HashMap<>(1))
.getOrDefault(cls.getApplicationAudit(), ""));
cls.setState(mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getState(), ""));
cls.setIsSon(mapDictItemLabels.getOrDefault("YES_NO", new HashMap<>(1)).getOrDefault(cls.getIsSon(), ""));
if (!ObjectUtils.isEmpty(cls.getDeptId())) {
cls.setDeptName(remoteDeptService.getMatDeptVo(cls.getDeptId()).getDeptName());
}
if (!ObjectUtils.isEmpty(cls.getMakeDeptId())) {
cls.setMakeDeptName(remoteDeptService.getMatDeptVo(cls.getMakeDeptId()).getDeptName());
}
// 处理子集数据
List<CatalogSystemInformation> clsItemList = catalogSystemInformationMapper
.list(new QueryWrapper<CatalogSystemInformation>().eq("system_id", cls.getId()));
cls.setBusinessDepartmentList(
clsItemList.stream().filter(p -> p.getInformationType().equals("1")).collect(Collectors.toList()));
cls.setSafetySupervisorList(
clsItemList.stream().filter(p -> p.getInformationType().equals("2")).collect(Collectors.toList()));
cls.setSafetyResponsiblePersonList(
clsItemList.stream().filter(p -> p.getInformationType().equals("3")).collect(Collectors.toList()));
cls.setSystemDeveloperList(
clsItemList.stream().filter(p -> p.getInformationType().equals("4")).collect(Collectors.toList()));
cls.setMaintenanceManufacturerList(
clsItemList.stream().filter(p -> p.getInformationType().equals("5")).collect(Collectors.toList()));
cls.setSecurityManufacturerList(
clsItemList.stream().filter(p -> p.getInformationType().equals("6")).collect(Collectors.toList()));
// 归口业务处室
cls.getBusinessDepartmentList().stream().forEach(p -> {
cls.setBusinessDepartmentListName(p.getName());
cls.setBusinessDepartmentListContact(p.getContact());
cls.setBusinessDepartmentListContactInformation(p.getContactInformation());
});
// 安全主管领导
cls.getSafetySupervisorList().stream().forEach(p -> {
cls.setSafetySupervisorListContact(p.getContact());
cls.setSafetySupervisorListContactInformation(p.getContactInformation());
});
// 安全责任人
cls.getSafetyResponsiblePersonList().stream().forEach(p -> {
cls.setSafetyResponsiblePersonListContact(p.getContact());
cls.setSafetyResponsiblePersonListContactInformation(p.getContactInformation());
});
// 系统开发厂商
cls.getSystemDeveloperList().stream().forEach(p -> {
cls.setSystemDeveloperListName(p.getName());
cls.setSystemDeveloperListContact(p.getContact());
cls.setSystemDeveloperListContactInformation(p.getContactInformation());
});
// 系统运维厂商
cls.getMaintenanceManufacturerList().stream().forEach(p -> {
cls.setMaintenanceManufacturerListName(p.getName());
cls.setMaintenanceManufacturerListContact(p.getContact());
cls.setMaintenanceManufacturerListContactInformation(p.getContactInformation());
});
// 安全开发厂商
cls.getSecurityManufacturerList().stream().forEach(p -> {
cls.setSecurityManufacturerListName(p.getName());
cls.setSecurityManufacturerListContact(p.getContact());
cls.setSecurityManufacturerListContactInformation(p.getContactInformation());
});
});
// 多sheet的导出，关闭流变成了专门一个方法,后续考虑整合到common
ExcelWriter writer = null;
OutputStream out = null;
try {
writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
List<ExcelStyle> excelStyleList = new ArrayList<>(2);
excelStyleList.add(new ExcelStyle(writer, headerAlias, catalogSystemList, "系统梳理", 1));
ImportTemplateUtils.setStyle(excelStyleList);
// 设置返回头
ImportTemplateUtils.setResponse(response, ImportTemplateUtils.getNameWithRequest(request, "系统梳理-导出.xlsx"));
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭writer，释放内存
IoUtil.close(writer);
// 此处记得关闭输出Servlet流
IoUtil.close(out);
}
}
/**
* 表头别名设置
*
* @param ROW_NAMES 中文别名
* @param ROW_NAMES_EN 英文别名
* @param type 导入导出类型in导入 out导出
* @return map
*/
public static Map<String, String> getFirstRow(final String[] ROW_NAMES, final String[] ROW_NAMES_EN, String type) {
if (ROW_NAMES.length == 0 || ROW_NAMES.length != ROW_NAMES_EN.length) {
throw new IllegalArgumentException("表头中英文长度不一致");
}
Map<String, String> map;
switch (type) {
case IN:
map = Maps.newHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES[i], ROW_NAMES_EN[i]);
}
break;
case OUT:
map = Maps.newLinkedHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES_EN[i], ROW_NAMES[i]);
}
break;
default:
throw new IllegalArgumentException("表头转换类型错误");
}
return map;
}
package com.govmade.govmade.common.core.util;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.WorkbookUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import com.google.common.collect.Maps;
import com.govmade.govmade.common.core.exception.CheckedException;
import io.micrometer.core.instrument.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author dbs
*/
@SuppressWarnings("rawtypes")
public class ExcelUtil {
private ExcelAdapter adapter;
public ExcelUtil(ExcelAdapter adapter) {
super();
this.adapter = adapter;
}
private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public final static String IN = "in";
public final static String OUT = "out";
/**
* 表头别名设置
*
* @param ROW_NAMES 中文别名
* @param ROW_NAMES_EN 英文别名
* @param type 导入导出类型in导入 out导出
* @return map
*/
public static Map<String, String> getFirstRow(final String[] ROW_NAMES, final String[] ROW_NAMES_EN, String type) {
if (ROW_NAMES.length == 0 || ROW_NAMES.length != ROW_NAMES_EN.length) {
throw new IllegalArgumentException("表头中英文长度不一致");
}
Map<String, String> map;
switch (type) {
case IN:
map = Maps.newHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES[i], ROW_NAMES_EN[i]);
}
break;
case OUT:
map = Maps.newLinkedHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES_EN[i], ROW_NAMES[i]);
}
break;
default:
throw new IllegalArgumentException("表头转换类型错误");
}
return map;
}
/**
* 通过hutool读取文件内容
*
* @param file 文件
* @param rowNames 表头信息
* @param firstRow 文件头别名信息
* @param beanType 转换的类型
* @return list
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
public static <T> List<T> readFile(MultipartFile file, String[] rowNames, Map<String, String> firstRow, Class<T> beanType) throws IOException, InvalidFormatException {
//校验文件格式及表头并获取数据
checkFile(file);
checkFirstRowWithRowName(file, rowNames, 0);
ExcelReader excelReader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream());
excelReader.setHeaderAlias(firstRow);
return excelReader.readAll(beanType);
}
/**
* 通过hutool读取文件内容
*
* @param fullPath 路径
* @param page sheet页
* @throws IOException
* @throws InvalidFormatException
*/
public void readResrcFile(String fullPath, Integer page) throws IOException, InvalidFormatException {
// 解析excel
Workbook workbook = null;
// 将Excel的各行记录放入ImpExcelBean的list里面
try {
workbook = WorkbookFactory.create(new FileInputStream(fullPath));
Sheet sheet = workbook.getSheetAt(page);// 统计excel的行数
int rowLen = sheet.getPhysicalNumberOfRows();// excel总行数，记录数=行数-1
int cellNum = sheet.getRow(0).getLastCellNum();
int startRow = adapter.getStartRow();
if (startRow >= rowLen) {
adapter.setError(true);
adapter.appendMsg("模板为空！");
return;
}
for (int i = adapter.getStartRow(); i < rowLen; i++) {
Row row = sheet.getRow(i);
// 将Excel中各行记录依次导入到ImpExcelBean的list中
if (row != null) {
String[] str = new String[cellNum];
boolean isEmpty = true;
for (int j = 0; j < cellNum; j++) {
Cell c = row.getCell(j);
if (c == null) {
str[j] = "";
continue;
}
boolean isMerge = PoiUtil.isMergedRegion(sheet, i, c.getColumnIndex());//判断是否具有合并单元格
if (isMerge) {
str[j] = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
} else {
str[j] = PoiUtil.getValue(c);
}
if (StrUtil.isNotEmpty(str[j].trim())) {
isEmpty = false;
}
}
if (!isEmpty) {
adapter.buildList(str, i);
}
}
}
} catch (Exception e) {
adapter.setError(true);
if (StringUtils.isBlank(adapter.getErrorMsg() + "")) {
adapter.appendMsg("模板错误");
}
e.printStackTrace();
}
}
/**
* 通过hutool读取文件内容
*
* @param file 文件
* @param rowNames 表头信息
* @param firstRow 文件头别名信息
* @param beanType 转换的类型
* @param sheetIndex shheet序号
* @return list
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
public static <T> List<T> readFileWithSheetIndex(MultipartFile file, String[] rowNames, Map<String, String> firstRow, Class<T> beanType, Integer sheetIndex) throws IOException, InvalidFormatException {
//校验文件格式及表头并获取数据
checkFile(file);
checkFirstRowWithRowName(file, rowNames, sheetIndex);
ExcelReader excelReader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream(), sheetIndex);
excelReader.setHeaderAlias(firstRow);
return excelReader.readAll(beanType);
}
/**
* 通过hutool导出数据
*
* @param fileName 文件名称
* @param sheetName sheet名称
* @param headerAlias 标题别名
* @param list 导出数据
* @param title 标题
* @param response 返回体
*/
public static void writeFile(String fileName, String sheetName, Map<String, String> headerAlias, Iterable<?> list, String title, HttpServletResponse response) {
ExcelWriter writer = null;
OutputStream out = null;
try {
// 通过工具类创建writer
writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
//设置样式
setStyle(writer, headerAlias, list, sheetName, title);
//设置返回头
setResponse(response, fileName);
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
} finally {
// 关闭writer，释放内存
IoUtil.close(writer);
//此处记得关闭输出Servlet流
IoUtil.close(out);
}
}
/**
* 设置返回头
*
* @param response response
* @param fileName 文件名
*/
private static void setResponse(HttpServletResponse response, String fileName) {
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//fileName是弹出下载对话框的文件名，不能为中文，中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
}
/**
* 设置样式
*
* @param writer hutool的导出类
* @param headerAlias 标题别名
* @param list 导出数据
* @param sheetName sheet名称
* @param title 标题
*/
private static void setStyle(ExcelWriter writer, Map<String, String> headerAlias, Iterable<?> list, String sheetName, String title) {
//自定义标题别名
writer.setHeaderAlias(headerAlias);
//只保留设置了别名的字段
writer.setOnlyAlias(true);
// 一次性写出内容，使用默认样式，强制输出标题,这里要放在设置宽行属性之前
writer.write(list, true);
//ignoreHead表示是否忽略头部样式
writer.getStyleSet().setFont((short) -1, (short) 11, "Courier New", true);
//设置表头和单元格格式
Font font = StyleUtil.createFont(writer.getWorkbook(), (short) -1, (short) 13, "Courier New");
//头部样式独立调整
writer.getHeadCellStyle().setFont(font);
//表头设置换行
writer.getHeadCellStyle().setWrapText(true);
//自动宽度
writer.autoSizeColumnAll();
//自动宽度有点问题，设置个默认值
int columnCount = writer.getColumnCount();
for (int i = 0; i < columnCount; i++) {
int width = writer.getSheet().getColumnWidth(i);
if (width < 1990) {
writer.setColumnWidth(i, 15);
}
if (width > 19900) {
writer.setColumnWidth(i, 30);
}
}
//设置换行
writer.getStyleSet().setWrapText();
//设置行高
//writer.setDefaultRowHeight(25);
//更改sheet名称
writer.renameSheet(sheetName);
// 合并单元格后的标题行，使用默认标题样式
if (StrUtil.isNotBlank(title)) {
writer.merge(headerAlias.size(), title);
}
}
/**
* 校验文件是否存在或合法
*
* @param file 文件
* @throws IOException io异常
*/
private static void checkFile(MultipartFile file) throws IOException {
if (null == file) {
throw new FileNotFoundException("文件不存在！");
}
String fileName = file.getOriginalFilename();
if (StrUtil.isBlank(fileName) || (!fileName.endsWith(EXCEL2003) && !fileName.endsWith(EXCEL2007))) {
throw new IOException(fileName + "不是excel文件");
}
}
/**
* 校验表头是否正确
*
* @param file 文件
* @param rowNames 表头
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
private static void checkFirstRowWithRowName(MultipartFile file, String[] rowNames, Integer sheetIndex) throws IOException, InvalidFormatException {
//使用hutool的类，内部其实还是调用WorkbookFactory.create方法，只不过多了个关闭流的操作
Workbook workbook = WorkbookUtil.createBook(file.getInputStream(), true);
Sheet sheet = workbook.getSheetAt(sheetIndex);
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
Row row = sheet.getRow(firstRowNum);
String s;
for (int i = 0; i < rowNames.length; i++) {
s = row.getCell(i).getStringCellValue();
if (StrUtil.isBlank(s)) {
throw new CheckedException("警告！模板为空或有误！");
}
checkNameWithRowName(s, i, rowNames[i]);
}
}
/**
* 文件中的表头与正确表头对比
*
* @param name 文件表头名称
* @param i 表头序列
* @param rowName 标准表头名称
*/
private static void checkNameWithRowName(String name, int i, String rowName) {
String error = "警告！模板为空或有误！第 " + (i + 1) + " 列名错误";
if (!rowName.equals(name)) {
throw new CheckedException(error);
}
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return PoiUtil.getValue(fCell);
}
}
}
return null;
}
/**
* 导出excel
*
* @param title 导出表的标题
* @param rowsName 导出表的列名
* @param dataList 需要导出的数据
* @param fileName 生成excel文件的文件名
* @param response
*/
public static void exportExcel(String title, String[] rowsName, List<String> bigRowName, List<String> smallRowName, List<Object[]> dataList, String fileName, HttpServletResponse response) throws Exception {
OutputStream output = response.getOutputStream();
response.reset();
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + fileName);
export(title, rowsName, bigRowName, smallRowName, dataList, output);
close(output);
}
/**
* 导出数据
*
* @param title
* @param rowName
* @param dataList
* @param out
*/
private static void export(String title, String[] rowName, List<String> bigRowName, List<String> smallRowName, List<Object[]> dataList, OutputStream out) {
try {
// 创建工作簿对象
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 创建工作表
SXSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
//HSSFRow rowm = sheet.createRow(0);
//创建表格标题列
//HSSFCell cellTiltle = rowm.createCell(0);
// sheet样式定义; getColumnTopStyle(); getStyle()均为自定义方法 --在下面,可扩展
// 获取列头样式对象
CellStyle columnTopStyle = ExcelUtil.getColumnTopStyle(workbook);
// 获取单元格样式对象
CellStyle style = ExcelUtil.getStyle(workbook);
//合并表格标题行，合并列数为列名的长度,第一个0为起始行号，第二个1为终止行号，第三个0为起始列好，第四个参数为终止列号
//sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
//设置标题行样式
//cellTiltle.setCellStyle(columnTopStyle);
//设置标题行值
//cellTiltle.setCellValue(title);
int x = 0;
if (bigRowName != null && !bigRowName.isEmpty()) {
int columnNum = bigRowName.size();
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n + 4);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(bigRowName.get(n));
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
x += 1;
}
if (smallRowName != null && !smallRowName.isEmpty()) {
int columnNum = smallRowName.size();
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n + 4);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(smallRowName.get(n));
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
x += 1;
}
if (x >= 1) {
SXSSFRow row = sheet.getRow(0);
int cellNum = row.getLastCellNum();
SXSSFCell tempCell = row.createCell(cellNum);
tempCell.setCellValue("%1");
String temp = row.getCell(4).getStringCellValue();
List<Integer> mergeList = new ArrayList<>();
for (int i = 4; i < cellNum + 1; i++) {
String stringCellValue = row.getCell(i).getStringCellValue();
if (!temp.equals(stringCellValue)) {
temp = stringCellValue;
mergeList.add(i - 1);
}
}
for (int i = 0; i < mergeList.size(); i++) {
Integer merge = mergeList.get(i);
if (0 == i) {
if (merge != 4) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, merge));
}
} else {
if (merge != mergeList.get(i - 1) + 1) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, mergeList.get(i - 1) + 1, merge));
}
}
}
row.removeCell(tempCell);
}
if (x == 2) {
SXSSFRow row = sheet.getRow(1);
int cellNum = row.getLastCellNum();
SXSSFCell tempCell = row.createCell(cellNum);
tempCell.setCellValue("%1");
String temp = row.getCell(4).getStringCellValue();
List<Integer> mergeList = new ArrayList<>();
for (int i = 4; i < cellNum + 1; i++) {
String stringCellValue = row.getCell(i).getStringCellValue();
if (!temp.equals(stringCellValue)) {
temp = stringCellValue;
mergeList.add(i - 1);
}
}
for (int i = 0; i < mergeList.size(); i++) {
Integer merge = mergeList.get(i);
if (0 == i) {
if (merge != 4) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, merge));
}
} else {
if (merge != mergeList.get(i - 1) + 1) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, mergeList.get(i - 1) + 1, merge));
}
}
}
row.removeCell(tempCell);
}
int columnNum = rowName.length;
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
if (0 != x) {
SXSSFCell cell = sheet.getRow(0).createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString("基本信息");
cell.setCellValue(text);
cell.setCellStyle(columnTopStyle);
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 3);
sheet.addMergedRegion(region);
}
// 将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
// 遍历每个对象
Object[] obj = dataList.get(i);
// 创建所需的行数
SXSSFRow row = sheet.createRow(i + 3);
for (int j = 0; j < obj.length; j++) {
// 设置单元格的数据类型
SXSSFCell cell = null;
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
// 设置单元格的值
cell.setCellValue(obj[j].toString());
} else {
cell.setCellValue("无");
}
// 设置单元格样式
cell.setCellStyle(style);
}
}
// 让列宽随着导出的列长自动适应
/* for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue()
.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}*/
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 列头单元格样式
*
* @param workbook
* @return
*/
private static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 列数据信息单元格样式
*
* @param workbook
* @return
*/
private static CellStyle getStyle(SXSSFWorkbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(true);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 关闭输出流
*
* @param os
*/
private static void close(OutputStream os) {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
