@PostMapping(value = "/importExcel")
@ApiOperation(value = "导入excel", notes = "导入excel", httpMethod = "POST")
public R importExcel(@RequestPart("file") MultipartFile file) {
return R.ok(catalogSystemService.importExcel(file));
}
/**
* 系统梳理-导入
*
* @param file
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public R importExcel(MultipartFile file) {
try {
// 设置台头别名进行赋值映射
Map<String, String> firstRow = ExcelUtil.getFirstRow(ROW_NAME, ROW_NAME_EN, ExcelUtil.IN);
// 校验文件及表头并转换成数据
List<CatalogSystem> catalogSystemsPOIList = ExcelUtil.readFile(file, ROW_NAME, firstRow,
CatalogSystem.class);
if (ObjectUtils.isEmpty(catalogSystemsPOIList)) {
return R.failed("当前表格没有数据，请检查。");
}
// 获取当前权限
GovmadeUser user = SecurityUtils.getUser();
Boolean isAdmin = StrUtil.equals("admin", user.getUsername());
MatDeptVO userDept = null;
if (!isAdmin) {
userDept = remoteDeptService.getMatDeptVo(user.getDeptId());
}
// 判断必填
Set<String> mapDeptNames = new HashSet<>();
if (CollUtil.isNotEmpty(catalogSystemsPOIList)) {
int num = 1;
for (CatalogSystem catalogSystem : catalogSystemsPOIList) {
if (StrUtil.isBlank(catalogSystem.getName())) {
return R.failed("第" + num + "行存在错误，系统名称不许为空");
}
if (StrUtil.length(catalogSystem.getName()) > 245) {
return R.failed("第" + num + "行存在错误，系统名称长度不得大于245");
}
if (StrUtil.isBlank(catalogSystem.getDeptName())) {
return R.failed("第" + num + "行存在错误，系统所属部门不许为空");
}
if (!isAdmin && !StrUtil.equals(userDept.getDeptName(), catalogSystem.getDeptName())) {
return R.failed("第" + num + "行存在错误，系统所属部门不为本部门");
}
if (StrUtil.length(catalogSystem.getSystemIntroduction()) > 1000) {
return R.failed("第" + num + "行存在错误，系统简介长度不得大于1000");
}
if (StrUtil.length(catalogSystem.getProjectApprovalDepartment()) > 255) {
return R.failed("第" + num + "行存在错误，项目立项审批部门长度不得大于255");
}
if (StrUtil.length(catalogSystem.getProjectApprovalDate()) > 255) {
return R.failed("第" + num + "行存在错误，项目立项审批日期长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBudgetItemName()) > 255) {
return R.failed("第" + num + "行存在错误，预算项目名称长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBudgetBeginYear()) > 255) {
return R.failed("第" + num + "行存在错误，预算涉及的年度起长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBudgetEndYear()) > 255) {
return R.failed("第" + num + "行存在错误，预算涉及的年度止长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBusinessDepartmentListName()) > 255) {
return R.failed("第" + num + "行存在错误，内设机构长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBusinessDepartmentListContact()) > 255) {
return R.failed("第" + num + "行存在错误，内设机构联系人长度不得大于255");
}
if (StrUtil.length(catalogSystem.getBusinessDepartmentListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，内设机构联系方式长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSafetySupervisorListContact()) > 255) {
return R.failed("第" + num + "行存在错误，安全主管领导长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSafetySupervisorListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，主管领导联系方式长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSafetyResponsiblePersonListContact()) > 255) {
return R.failed("第" + num + "行存在错误，安全责任人长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSafetyResponsiblePersonListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，责任人联系方式长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSystemStartDate()) > 255) {
return R.failed("第" + num + "行存在错误，系统开始使用日期长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSystemEndDate()) > 255) {
return R.failed("第" + num + "行存在错误，系统停止使用日期长度不得大于255");
}
if (StrUtil.length(catalogSystem.getIpUrl()) > 396) {
return R.failed("第" + num + "行存在错误，IP地址长度不得大于396");
}
if (StrUtil.length(catalogSystem.getLoginAddress()) > 255) {
return R.failed("第" + num + "行存在错误，系统访问/登录地址长度不得大于255");
}
if (StrUtil.length(catalogSystem.getNotGoingReasons()) > 255) {
return R.failed("第" + num + "行存在错误，未上政务云原因长度不得大于255");
}
if (StrUtil.length(catalogSystem.getGoingReasonsTime()) > 255) {
return R.failed("第" + num + "行存在错误，上政务云时间计划长度不得大于255");
}
if (StrUtil.length(catalogSystem.getDataScale()) > 255) {
return R.failed("第" + num + "行存在错误，数据规模(G)长度不得大于255");
}
if (StrUtil.length(catalogSystem.getTotalData()) > 255) {
return R.failed("第" + num + "行存在错误，数据总量(条)长度不得大于255");
}
if (StrUtil.length(catalogSystem.getMonthlyGrowth()) > 255) {
return R.failed("第" + num + "行存在错误，数据月增长情况(M)长度不得大于255");
}
if (StrUtil.length(catalogSystem.getDataTimeRange()) > 255) {
return R.failed("第" + num + "行存在错误，存量数据时间范围长度不得大于255");
}
if (StrUtil.length(catalogSystem.getFilingAuthority()) > 255) {
return R.failed("第" + num + "行存在错误，等保备案机关长度不得大于255");
}
if (StrUtil.length(catalogSystem.getFilingAuthorityTime()) > 255) {
return R.failed("第" + num + "行存在错误，等保备案时间长度不得大于255");
}
if (StrUtil.length(catalogSystem.getFilingNo()) > 255) {
return R.failed("第" + num + "行存在错误，等保备案编号长度不得大于255");
}
if (StrUtil.length(catalogSystem.getEvaluationOrganization()) > 255) {
return R.failed("第" + num + "行存在错误，等保测评机构长度不得大于255");
}
if (StrUtil.length(catalogSystem.getFilingTime()) > 255) {
return R.failed("第" + num + "行存在错误，等保测评时间长度不得大于255");
}
if (StrUtil.length(catalogSystem.getEvaluationScore()) > 255) {
return R.failed("第" + num + "行存在错误，等保测评得分长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSystemDeveloperListName()) > 255) {
return R.failed("第" + num + "行存在错误，系统开发厂商长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSystemDeveloperListContact()) > 255) {
return R.failed("第" + num + "行存在错误，开发厂商联系人长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSystemDeveloperListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，开发厂商联系方式长度不得大于255");
}
if (StrUtil.length(catalogSystem.getMaintenanceManufacturerListName()) > 255) {
return R.failed("第" + num + "行存在错误，系统运维厂商长度不得大于255");
}
if (StrUtil.length(catalogSystem.getMaintenanceManufacturerListContact()) > 255) {
return R.failed("第" + num + "行存在错误，运维厂商联系人长度不得大于255");
}
if (StrUtil.length(catalogSystem.getMaintenanceManufacturerListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，运维厂商联系方式长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSecurityManufacturerListName()) > 255) {
return R.failed("第" + num + "行存在错误，安全开发厂商长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSecurityManufacturerListContact()) > 255) {
return R.failed("第" + num + "行存在错误，安全厂商联系人长度不得大于255");
}
if (StrUtil.length(catalogSystem.getSecurityManufacturerListContactInformation()) > 255) {
return R.failed("第" + num + "行存在错误，安全厂商联系方式长度不得大于255");
}
if (StrUtil.isNotBlank(catalogSystem.getProjectApprovalDate())
&& parseDate(catalogSystem.getProjectApprovalDate()) == null) {
return R.failed("第" + num + "行存在错误，项目立项审批日期不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getBudgetBeginYear())
&& parseDate(catalogSystem.getBudgetBeginYear()) == null) {
return R.failed("第" + num + "行存在错误，预算涉及的年度起不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getBudgetEndYear())
&& parseDate(catalogSystem.getBudgetEndYear()) == null) {
return R.failed("第" + num + "行存在错误，预算涉及的年度止不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getSystemStartDate())
&& parseDate(catalogSystem.getSystemStartDate()) == null) {
return R.failed("第" + num + "行存在错误，系统开始使用日期不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getSystemEndDate())
&& parseDate(catalogSystem.getSystemEndDate()) == null) {
return R.failed("第" + num + "行存在错误，系统停止使用日期不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getGoingReasonsTime())
&& parseDate(catalogSystem.getGoingReasonsTime()) == null) {
return R.failed("第" + num + "行存在错误，上政务云时间计划不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getFilingAuthorityTime())
&& parseDate(catalogSystem.getFilingAuthorityTime()) == null) {
return R.failed("第" + num + "行存在错误，等保备案时间不是日期格式");
}
if (StrUtil.isNotBlank(catalogSystem.getFilingTime())
&& parseDate(catalogSystem.getFilingTime()) == null) {
return R.failed("第" + num + "行存在错误，等保测评时间不是日期格式");
}
mapDeptNames.add(catalogSystem.getDeptName());
mapDeptNames.add(catalogSystem.getMakeDeptName());
num++;
}
}
Map<String, SysDept> deptMap = remoteDeptService.getDeptmap(mapDeptNames, SecurityConstants.FROM_IN);
// 根据系统名称进行分组-批量添加系统
List<CatalogSystem> clsList = new ArrayList<>();
Map<String, List<CatalogSystem>> map = catalogSystemsPOIList.stream()
.filter(item -> StrUtil.isNotBlank(item.getName()))
.collect(Collectors.groupingBy(CatalogSystem::getName));
// 基本信息批量添加
for (String key : map.keySet()) {
List<CatalogSystem> catalogSystemsPOILists = map.get(key);
catalogSystemsPOILists.stream().forEach(poi -> {
CatalogSystem cls = new CatalogSystem();
BeanUtils.copyProperties(poi, cls);
// 是否有子系统
if (StrUtil.isBlank(poi.getIsSon())) {
cls.setIsSon("0");
}
// 是否编目
if (StrUtil.isBlank(poi.getState())) {
cls.setState("0");
}
// 编目梳理
if (StrUtil.isBlank(poi.getState())) {
cls.setCataNum(0);
}
// 项目立项审批日期，如果不是日期格式 不导入
if (parseDate(poi.getProjectApprovalDate()) == null) {
cls.setProjectApprovalDate(null);
}
// '预算涉及的年度起'，如果不是日期格式 不导入
if (parseDate(poi.getBudgetBeginYear()) == null) {
cls.setBudgetBeginYear(null);
}
// 预算涉及的年度止，如果不是日期格式 不导入
if (parseDate(poi.getBudgetEndYear()) == null) {
cls.setBudgetEndYear(null);
}
// '系统开始使用日期'，如果不是日期格式 不导入
if (parseDate(poi.getSystemStartDate()) == null) {
cls.setSystemStartDate(null);
}
// '系统停止使用日期'，如果不是日期格式 不导入
if (parseDate(poi.getSystemEndDate()) == null) {
cls.setSystemEndDate(null);
}
// '上政务云时间计划'，如果不是日期格式 不导入
if (parseDate(poi.getGoingReasonsTime()) == null) {
cls.setGoingReasonsTime(null);
}
// '等保备案时间'，如果不是日期格式 不导入
if (parseDate(poi.getFilingAuthorityTime()) == null) {
cls.setFilingAuthorityTime(null);
}
// '等保测评时间'，如果不是日期格式 不导入
if (parseDate(poi.getFilingTime()) == null) {
cls.setFilingTime(null);
}
String generateSysCode = generateSysCode();
cls.setCode(generateSysCode);
String isVertical = poi.getIsVertical();
if (StringUtil.isNotBlank(isVertical) && StringUtil.equals(isVertical, "是")) {
cls.setVerticalCode(generateSysCode);
}
// ip地址
if (StrUtil.isNotBlank(poi.getIpUrl())) {
JSONArray jsonArray = new JSONArray();
jsonArray.add(poi.getIpUrl());
cls.setIpUrl(JSONUtil.toJsonStr(jsonArray));
} else {
JSONArray jsonArray = new JSONArray();
jsonArray.add("");
cls.setIpUrl(JSONUtil.toJsonStr(jsonArray));
}
// 信息系统类型
cls.setSystemTypeInformation(
EnumUtil.INFORMATION_SYSTEM_TYPE.getName(poi.getSystemTypeInformation()));
// 建设依据
cls.setMakeDesc(EnumUtil.CONSTRUCTION_BASIS.getName(poi.getMakeDesc()));
// 垂管范围
cls.setVerticalType(EnumUtil.SCOPE_VERTICAL.getName(poi.getVerticalType()));
// 建设资金来源
cls.setSourceConstructionFunds(EnumUtil.SOURCES_FUNDING.getName(poi.getSourceConstructionFunds()));
// 使用范围-区域
cls.setRegion(EnumUtil.SCOPE_AREA.getName(poi.getRegion()));
// 使用范围-用户
cls.setSubscriber(EnumUtil.SCOPE_USERS.getName(poi.getSubscriber()));
// 建设层级
cls.setConstructionLevel(EnumUtil.CONSTRUCTION_LEVEL.getName(poi.getConstructionLevel()));
// 安全服务类型
cls.setSecurityServiceType(EnumUtil.SECURITY_SERVICE_TYPE.getName(poi.getSecurityServiceType()));
// 系统部署情况
cls.setSystemDeployment(EnumUtil.SYSTEM_DEPLOYMENT.getName(poi.getSystemDeployment()));
// cls.setPhysicalServer("");
// cls.setStorageName("");
// cls.setDatabaseName("");
// cls.setPhysicalOther("");
// cls.setGongyouyun("");
// cls.setProprietaryCloud("");
// 网络环境
cls.setNetworkEnvironment(EnumUtil.NETWORK_ENV.getName(poi.getNetworkEnvironment()));
// 数据库格式
cls.setDatabaseFormat(EnumUtil.DATABASE_FORMAT.getName(poi.getDatabaseFormat()));
// 等级保护
cls.setGradeProtection(EnumUtil.LEVEL_PROTECTION.getName(poi.getGradeProtection()));
// 系统应用领域
cls.setAreaName(EnumUtil.SYSTEM_FIELD.getName(poi.getAreaName()));
// 机构部门
SysDept d = deptMap.get(poi.getDeptName());
if (d != null) {
cls.setDeptId(d.getDeptId());
} else {
cls.setDeptId(user.getDeptId());
}
// 所属城市
SysCity sysCity = remoteCityService.findByDeptId(cls.getDeptId());
if (sysCity != null) {
cls.setRangeName(sysCity.getRegionName());
cls.setRangeCityId(sysCity.getId());
}
// 是否垂管系统
cls.setIsUnified(EnumUtil.TeaseIsLicenseEnum.getName(poi.getIsUnified()));
cls.setIsVertical(EnumUtil.TeaseIsLicenseEnum.getName(poi.getIsVertical()));
// 建设部门
SysDept makeDept = deptMap.get(poi.getMakeDeptName());
if (makeDept != null) {
cls.setMakeDeptId(makeDept.getDeptId());
}
if (StrUtil.isNotBlank(poi.getIsSynergy())) {
// 是否协同
cls.setIsSynergy(EnumUtil.TeaseIsLicenseEnum.getName(poi.getIsSynergy()));
if (poi.getIsSynergy().equals("是")) {
String synergyDeptName = poi.getSynergyDeptName();
if (StringUtils.isNotBlank(synergyDeptName)) {
String[] deptNames = synergyDeptName.split(",");
String synergyDeptId = "";
for (String deptName : deptNames) {
synergyDeptId += remoteDeptService.getFeignDeptId(deptName,
SecurityConstants.FROM_IN);
}
cls.setSynergyDeptId(synergyDeptId);
cls.setSynergyDeptName(synergyDeptName);
} else {
cls.setSynergyDeptId("");
cls.setSynergyDeptName("");
}
}
}
// 是否在用其它部门数据
cls.setDepartmentsBeingUsed(EnumUtil.TeaseIsLicenseEnum.getName(poi.getDepartmentsBeingUsed()));
// 是否有独立app
cls.setIsIndependent(EnumUtil.TeaseIsLicenseEnum.getName(poi.getIsIndependent()));
// 是否为僵尸系统
cls.setIsCorpse(EnumUtil.TeaseIsLicenseEnum.getName(poi.getIsCorpse()));
// 初始化基本变量
cls.setCreateTime(LocalDateTime.now());
cls.setUpdateTime(LocalDateTime.now());
cls.setDelFlag("0");
cls.setTenantId(0);
// cls.setDeptId(SecurityUtils.getUser().getDeptId());
cls.setSysOrg(SecurityUtils.getUser().getDeptId().longValue());
cls.setApplicationAudit("0");
// 系统来源为excel导入
cls.setSourceType("2");
List<CatalogSystemInformation> informations = new ArrayList<>();
// 归口业务处室
if (StringUtils.isNotBlank(poi.getBusinessDepartmentListName())) {
CatalogSystemInformation coif = new CatalogSystemInformation();
coif.setName(poi.getBusinessDepartmentListName());
coif.setContact(poi.getBusinessDepartmentListContact());
coif.setContactInformation(poi.getBusinessDepartmentListContactInformation());
coif.setDeptId(SecurityUtils.getUser().getDeptId().longValue());
coif.setInformationType("1");
informations.add(coif);
}
// 安全主管领导
if (StringUtils.isNotBlank(poi.getSafetySupervisorListContact())) {
CatalogSystemInformation coifSafety = new CatalogSystemInformation();
coifSafety.setContact(poi.getSafetySupervisorListContact());
coifSafety.setContactInformation(poi.getSafetySupervisorListContactInformation());
coifSafety.setDeptId(SecurityUtils.getUser().getDeptId().longValue());
coifSafety.setInformationType("2");
informations.add(coifSafety);
}
// 安全责任人
if (StringUtils.isNotBlank(poi.getSafetyResponsiblePersonListContact())) {
CatalogSystemInformation coifSafetyResponsible = new CatalogSystemInformation();
coifSafetyResponsible.setContact(poi.getSafetyResponsiblePersonListContact());
coifSafetyResponsible
.setContactInformation(poi.getSafetyResponsiblePersonListContactInformation());
coifSafetyResponsible.setInformationType("3");
informations.add(coifSafetyResponsible);
}
// 系统开发厂商
if (StringUtils.isNotBlank(poi.getSystemDeveloperListName())) {
CatalogSystemInformation coifSystemDeveloper = new CatalogSystemInformation();
coifSystemDeveloper.setName(poi.getSystemDeveloperListName());
coifSystemDeveloper.setContact(poi.getSystemDeveloperListContact());
coifSystemDeveloper.setContactInformation(poi.getSystemDeveloperListContactInformation());
coifSystemDeveloper.setDeptId(SecurityUtils.getUser().getDeptId().longValue());
coifSystemDeveloper.setInformationType("4");
informations.add(coifSystemDeveloper);
}
// 系统运维厂商
if (StringUtils.isNotBlank(poi.getMaintenanceManufacturerListName())) {
CatalogSystemInformation coifMaintenanceManu = new CatalogSystemInformation();
coifMaintenanceManu.setName(poi.getMaintenanceManufacturerListName());
coifMaintenanceManu.setContact(poi.getMaintenanceManufacturerListContact());
coifMaintenanceManu
.setContactInformation(poi.getMaintenanceManufacturerListContactInformation());
coifMaintenanceManu.setDeptId(SecurityUtils.getUser().getDeptId().longValue());
coifMaintenanceManu.setInformationType("5");
informations.add(coifMaintenanceManu);
}
// 安全开发厂商
if (StringUtils.isNotBlank(poi.getSecurityManufacturerListName())) {
CatalogSystemInformation coifSecurityManufacturer = new CatalogSystemInformation();
coifSecurityManufacturer.setName(poi.getSecurityManufacturerListName());
coifSecurityManufacturer.setContact(poi.getSecurityManufacturerListContact());
coifSecurityManufacturer
.setContactInformation(poi.getSecurityManufacturerListContactInformation());
coifSecurityManufacturer.setDeptId(SecurityUtils.getUser().getDeptId().longValue());
coifSecurityManufacturer.setInformationType("6");
informations.add(coifSecurityManufacturer);
}
cls.setInformations(informations);
clsList.add(cls);
});
}
baseMapper.insertBatchSomeColumn(clsList);
// 处理子项数据
for (CatalogSystem catalogSystem : clsList) {
List<CatalogSystemInformation> informations = catalogSystem.getInformations();
for (CatalogSystemInformation catalogSystemInformation : informations) {
catalogSystemInformation.setSystemId(catalogSystem.getId());
}
catalogSystemInformationMapper.insertBatchList(informations);
}
} catch (Exception e) {
e.printStackTrace();
return R.failed(e.getMessage());
}
return R.ok();
}
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();
}
}
}
}
