1.前一段时间使用Eaexcel做了一个下载EXCEL的功能，发现网上有很多的分享不是很全面，我是查询后结合了很多的案例和博客，开发了这个功能，现将其分享给大家，
（1）废话不多说，上代码：先看EsbGovInterfaceInfoController类 ，
@RestController
@RequestMapping("/gov/offline")
public class EsbGovInterfaceInfoController {/**
* 接口下载excel
*/
@PostMapping("/interface/file/download")
public void download(HttpServletResponse response, @RequestBody JSONObject cosuAppIds) {
List<String> appIds =(List)cosuAppIds.get("cosuAppIds");
List<EsbGovInterfaceInfo> esbGovInterfaceInfos =new ArrayList<>();
List<EsbGovInterfaceInfo> esbGovInterfaceInfosAll =new ArrayList<>();
for (String appId:appIds) {
EsbGovInterfaceInfo esbGovInterfaceInfo=new EsbGovInterfaceInfo();
esbGovInterfaceInfo.setAppId(appId);
esbGovInterfaceInfos=esbGovInterfaceInfoService.queryList(esbGovInterfaceInfo);
esbGovInterfaceInfosAll.addAll(esbGovInterfaceInfos);
}
esbGovInterfaceInfoService.downLoadInterface(response,esbGovInterfaceInfosAll);
}
}
（2）下面看下实现类EsbGovInterfaceInfoService的方法downLoadInterface（）
public interface EsbGovInterfaceInfoService{/**
* 导出excel
* @param response
* @param
* @return
*/
void downLoadInterface(HttpServletResponse response,List<EsbGovInterfaceInfo> esbGovInterfaceInfos);}
public class EsbGovInterfaceInfoServiceImpl implements EsbGovInterfaceInfoService {@Override
public void downModel(HttpServletResponse response,List<EsbGovInterfaceInfo> esbGovInterfaceInfos) {
String fileName="接口信息模板表";
try {
//数据写入excel对象
createNoInterfaceInfoSheet(esbGovInterfaceInfos,fileName,response);
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public void downLoadInterface(HttpServletResponse response, List<EsbGovInterfaceInfo> esbGovInterfaceInfos) {
String fileName=new SimpleDateFormat("yyyy-MM-dd").format(new Date())+"~接口信息表";
try {
//数据写入excel对象
if (esbGovInterfaceInfos.size() > 0) {
createNoInterfaceInfoSheet(esbGovInterfaceInfos,fileName,response);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void createNoInterfaceInfoSheet(List<EsbGovInterfaceInfo> esbGovInterfaceInfos,String fileName,HttpServletResponse response) throws IOException {
ExcelWriter excelWriter = EasyExcel.write(ExcelUtils.getExcelWriter(fileName,response))
.autoCloseStream(true).excelType(ExcelTypeEnum.XLS).build();
try {
for (int i = 0; i < 5; i++) {
createNoInterfaceInfoSheetHead(excelWriter,esbGovInterfaceInfos,i);
}
for (int i = 0; i < esbGovInterfaceInfos.size(); i++) {
createNoInterfaceInfoSheetDetail(excelWriter,esbGovInterfaceInfos,i);
}
excelWriter.finish();
}catch (Exception e){
e.printStackTrace();
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
public void createNoInterfaceInfoSheetDetail(ExcelWriter excelWriter,List<EsbGovInterfaceInfo> esbGovInterfaceInfos,int i) throws IOException {
String prod=messageFormat(esbBaseProtocolManageService.getObjectById(esbGovInterfaceInfos.get(i).getProtocolId()).getBodyType());
if("json".equals(prod)){
createNoInterfaceInfoSheetDetailJson(excelWriter,esbGovInterfaceInfos,i);
}else if("xml".equals(prod)){
createNoInterfaceInfoSheetDetailXml(excelWriter,esbGovInterfaceInfos,i);
}
}
public void createNoInterfaceInfoSheetDetailJson(ExcelWriter excelWriter,List<EsbGovInterfaceInfo> esbGovInterfaceInfos,int i) throws IOException {
int seqIn=1;
int seqOut=1;
List<InterfaceFieldModel> esbGovInterfaceFieldList = fieldService.getFieldModelsByInterfaceId(esbGovInterfaceInfos.get(i).getId());
List<InterfaceDownLoadExcelInterfaceInfo> interfaceDownLoadExcelInterfaceInfosIn = new ArrayList<>();
List<InterfaceDownLoadExcelInterfaceInfo> interfaceDownLoadExcelInterfaceInfosOut= new ArrayList<>();
List<InterfaceFieldModel> interfaceFieldModelList= new ArrayList<>();
for (InterfaceFieldModel interfaceFieldModel:esbGovInterfaceFieldList) {
if("input".equals(fileTypeFormat(interfaceFieldModel.getFieldType()))){
interfaceDownLoadExcelInterfaceInfosIn.add(getDownloadExcelDataInfoJson(interfaceFieldModel,seqIn));
seqIn +=1;
interfaceFieldModelList.add(interfaceFieldModel);
}else if("output".equals(fileTypeFormat(interfaceFieldModel.getFieldType()))){
interfaceDownLoadExcelInterfaceInfosOut.add(getDownloadExcelDataInfoJson(interfaceFieldModel,seqOut));
seqOut +=1;
}
}
Map<String, List<? extends Object>> listMap= new LinkedHashMap<>();
listMap.put("input",interfaceDownLoadExcelInterfaceInfosIn);
listMap.put("output",interfaceDownLoadExcelInterfaceInfosOut);
ExcelUtils.writeInterfaceExcelInfo(excelWriter, listMap, i,
esbGovInterfaceInfos.get(i).getTradeName(),esbGovInterfaceInfos.get(i).getTradeCode(),
InterfaceDownLoadExcelInterfaceInfo.class,interfaceFieldModelList);
}
public void createNoInterfaceInfoSheetDetailXml(ExcelWriter excelWriter,List<EsbGovInterfaceInfo> esbGovInterfaceInfos,int i) throws IOException {
int seqIn=1;
int seqOut=1;
List<InterfaceFieldModel> esbGovInterfaceFieldList = fieldService.getFieldModelsByInterfaceId(esbGovInterfaceInfos.get(i).getId());
List<InterfaceDownLoadExcelInterfaceInfo> interfaceDownLoadExcelInterfaceInfosIn = new ArrayList<>();
List<InterfaceDownLoadExcelInterfaceInfo> interfaceDownLoadExcelInterfaceInfosOut= new ArrayList<>();
List<InterfaceFieldModel> interfaceFieldModelList= new ArrayList<>();
for (InterfaceFieldModel interfaceFieldModel:esbGovInterfaceFieldList) {
if("input".equals(fileTypeFormat(interfaceFieldModel.getFieldType()))){
interfaceDownLoadExcelInterfaceInfosIn.add(getDownloadExcelDataInfoXml(interfaceFieldModel,seqIn));
seqIn +=1;
interfaceFieldModelList.add(interfaceFieldModel);
}else if("output".equals(fileTypeFormat(interfaceFieldModel.getFieldType()))){
interfaceDownLoadExcelInterfaceInfosOut.add(getDownloadExcelDataInfoXml(interfaceFieldModel,seqOut));
seqOut +=1;
}
}
Map<String, List<? extends Object>> listMap= new LinkedHashMap<>();
listMap.put("input",interfaceDownLoadExcelInterfaceInfosIn);
listMap.put("output",interfaceDownLoadExcelInterfaceInfosOut);
ExcelUtils.writeInterfaceExcelInfo(excelWriter, listMap, i,
esbGovInterfaceInfos.get(i).getTradeName(),esbGovInterfaceInfos.get(i).getTradeCode(),
InterfaceDownLoadExcelInterfaceInfo.class,interfaceFieldModelList);
}
public void createNoInterfaceInfoSheetHead(ExcelWriter excelWriter,List<EsbGovInterfaceInfo> esbGovInterfaceInfos,int i) throws IOException {
/**
* 优化后版本
*/
if (i == 2) {
ExcelUtils.writeInterfaceExcel(excelWriter,getDownloadExcelData(esbGovInterfaceInfos), i, "接口列表", InterfaceDownLoadExcelInterfaceDetail.class);
}else if (i == 0) {
ExcelUtils.writeInterfaceExcel(excelWriter, new ArrayList<InterfaceDownLoadExcelWriteReport>(), i, "填写说明（必填）", InterfaceDownLoadExcelWriteReport.class);
}else if (i == 1) {
ExcelUtils.writeInterfaceExcel(excelWriter, new ArrayList<InterfaceDownLoadExcelApplication>(), i, "服务方系统", InterfaceDownLoadExcelApplication.class);
}else if (i == 3) {
ExcelUtils.writeInterfaceExcel(excelWriter, new ArrayList<InterfaceDownLoadExcelSvcAndSysHead>(), i, "公共报文头", InterfaceDownLoadExcelApplication.class);
}else if (i==4) {
ExcelUtils.writeInterfaceExcel(excelWriter, new ArrayList<InterfaceDownLoadExcelSvcAndSysHead>(), i, "业务头", InterfaceDownLoadExcelApplication.class);
}
}
public InterfaceDownLoadExcelInterfaceInfo getDownloadExcelDataInfoXml(InterfaceFieldModel interfaceFieldModel,int seq){
InterfaceDownLoadExcelInterfaceInfo interfaceDownLoadExcelInterfaceInfo = new InterfaceDownLoadExcelInterfaceInfo();
interfaceDownLoadExcelInterfaceInfo.setSeq(String.valueOf(seq));
interfaceDownLoadExcelInterfaceInfo.setReq("");
interfaceDownLoadExcelInterfaceInfo.setChineseName("");
interfaceDownLoadExcelInterfaceInfo.setEnglishName("");
interfaceDownLoadExcelInterfaceInfo.setLength("");
interfaceDownLoadExcelInterfaceInfo.setValueRange("");
interfaceDownLoadExcelInterfaceInfo.setIsNoInput("");
interfaceDownLoadExcelInterfaceInfo.setDateType("");
interfaceDownLoadExcelInterfaceInfo.setReport("");
interfaceDownLoadExcelInterfaceInfo.setDetail("如服务提供系统无法按照标准规范改制则经过架构决策后提供此右侧表格");
interfaceDownLoadExcelInterfaceInfo.setReqOrg(fileTypeFormat(interfaceFieldModel.getFieldType()));
interfaceDownLoadExcelInterfaceInfo.setChineseNameOrg(interfaceFieldModel.getDescription());
interfaceDownLoadExcelInterfaceInfo.setEnglishNameOrg(interfaceFieldModel.getFieldKey());
putValuesForDataTypeXml(interfaceDownLoadExcelInterfaceInfo,interfaceFieldModel);
interfaceDownLoadExcelInterfaceInfo.setDateTypeOrg("");
interfaceDownLoadExcelInterfaceInfo.setReportOrg("");
return interfaceDownLoadExcelInterfaceInfo;
}
public InterfaceDownLoadExcelInterfaceInfo getDownloadExcelDataInfoJson(InterfaceFieldModel interfaceFieldModel,int seq){
InterfaceDownLoadExcelInterfaceInfo interfaceDownLoadExcelInterfaceInfo = new InterfaceDownLoadExcelInterfaceInfo();
interfaceDownLoadExcelInterfaceInfo.setSeq(String.valueOf(seq));
interfaceDownLoadExcelInterfaceInfo.setReq(fileTypeFormat(interfaceFieldModel.getFieldType()));
interfaceDownLoadExcelInterfaceInfo.setChineseName(interfaceFieldModel.getDescription());
interfaceDownLoadExcelInterfaceInfo.setEnglishName(interfaceFieldModel.getFieldKey());
putValuesForDataTypeJson(interfaceDownLoadExcelInterfaceInfo,interfaceFieldModel);
interfaceDownLoadExcelInterfaceInfo.setDateType(putDataTypeFormat(interfaceFieldModel.getMetadataType()));
interfaceDownLoadExcelInterfaceInfo.setReport(interfaceFieldModel.getDescription());
interfaceDownLoadExcelInterfaceInfo.setDetail("如服务提供系统无法按照标准规范改制则经过架构决策后提供此右侧表格");
interfaceDownLoadExcelInterfaceInfo.setReqOrg("");
interfaceDownLoadExcelInterfaceInfo.setChineseNameOrg("");
interfaceDownLoadExcelInterfaceInfo.setEnglishNameOrg("");
interfaceDownLoadExcelInterfaceInfo.setLengthOrg("");
interfaceDownLoadExcelInterfaceInfo.setValueRangeOrg("");
interfaceDownLoadExcelInterfaceInfo.setIsNoInputOrg("");
interfaceDownLoadExcelInterfaceInfo.setDateTypeOrg("");
interfaceDownLoadExcelInterfaceInfo.setReportOrg("");
return interfaceDownLoadExcelInterfaceInfo;
}
public void putValuesForDataTypeXml(InterfaceDownLoadExcelInterfaceInfo interfaceDownLoadExcelInterfaceInfo,InterfaceFieldModel interfaceFieldModel){
if("7".equals(interfaceFieldModel.getMetadataType())||"8".equals(interfaceFieldModel.getMetadataType()) || "9".equals(interfaceFieldModel.getMetadataType())
|| "99".equals(interfaceFieldModel.getMetadataType())||"88".equals(interfaceFieldModel.getMetadataType())){
interfaceDownLoadExcelInterfaceInfo.setLengthOrg("");
interfaceDownLoadExcelInterfaceInfo.setValueRangeOrg("");
interfaceDownLoadExcelInterfaceInfo.setIsNoInputOrg("");
}else{
interfaceDownLoadExcelInterfaceInfo.setLength(interfaceFieldModel.getMetadataLength());
if("3".equals(interfaceFieldModel.getMetadataType()) ||"5".equals(interfaceFieldModel.getMetadataType())){
interfaceDownLoadExcelInterfaceInfo.setValueRangeOrg("["+String.valueOf(Integer.valueOf(interfaceFieldModel.getMetadataType())-1)+",2]");
}else{
interfaceDownLoadExcelInterfaceInfo.setValueRangeOrg("");
}
interfaceDownLoadExcelInterfaceInfo.setIsNoInputOrg(isOrNoInput(interfaceFieldModel.getRequired()));
}
}
public void putValuesForDataTypeJson(InterfaceDownLoadExcelInterfaceInfo interfaceDownLoadExcelInterfaceInfo,InterfaceFieldModel interfaceFieldModel){
if("7".equals(interfaceFieldModel.getMetadataType())||"8".equals(interfaceFieldModel.getMetadataType()) || "9".equals(interfaceFieldModel.getMetadataType())
|| "99".equals(interfaceFieldModel.getMetadataType())||"88".equals(interfaceFieldModel.getMetadataType())){
interfaceDownLoadExcelInterfaceInfo.setLength("");
interfaceDownLoadExcelInterfaceInfo.setValueRange("");
interfaceDownLoadExcelInterfaceInfo.setIsNoInput("");
}else{
interfaceDownLoadExcelInterfaceInfo.setLength(interfaceFieldModel.getMetadataLength());
if("3".equals(interfaceFieldModel.getMetadataType()) ||"5".equals(interfaceFieldModel.getMetadataType())){
interfaceDownLoadExcelInterfaceInfo.setValueRange("["+String.valueOf(Integer.valueOf(interfaceFieldModel.getMetadataType())-1)+",2]");
}else{
interfaceDownLoadExcelInterfaceInfo.setValueRange("");
}
interfaceDownLoadExcelInterfaceInfo.setIsNoInput(isOrNoInput(interfaceFieldModel.getRequired()));
}
}
public List<InterfaceDownLoadExcelInterfaceDetail> getDownloadExcelData(List<EsbGovInterfaceInfo> esbGovInterfaceInfos){
List<InterfaceDownLoadExcelInterfaceDetail> interfaceDownLoadExcelInterfaceDetails =new ArrayList<>();
Integer seq= 1;
for (EsbGovInterfaceInfo esbGovInterfaceInfo:esbGovInterfaceInfos){
InterfaceDownLoadExcelInterfaceDetail interfaceDownLoadExcelInterfaceDetail = new InterfaceDownLoadExcelInterfaceDetail();
interfaceDownLoadExcelInterfaceDetail.setSeq(String.valueOf(seq));
interfaceDownLoadExcelInterfaceDetail.setAppCode(appService.getObjectById(esbGovInterfaceInfo.getAppId()).getAppAbbr());
interfaceDownLoadExcelInterfaceDetail.setAppName(appService.getObjectById(esbGovInterfaceInfo.getAppId()).getAppName());
interfaceDownLoadExcelInterfaceDetail.setServiceCode(esbGovInterfaceInfo.getServiceCode());
interfaceDownLoadExcelInterfaceDetail.setTradeName(esbGovInterfaceInfo.getTradeName());
interfaceDownLoadExcelInterfaceDetail.setTradeCode(esbGovInterfaceInfo.getTradeCode());
interfaceDownLoadExcelInterfaceDetail.setInterfaceDesc(esbGovInterfaceInfo.getInterfaceDesc());
interfaceDownLoadExcelInterfaceDetail.setCommProtocol(commProtocolFormat(esbBaseProtocolManageService.getObjectById(esbGovInterfaceInfo.getProtocolId()).getVisitType()));
interfaceDownLoadExcelInterfaceDetail.setMassageFormat(messageFormat(esbBaseProtocolManageService.getObjectById(esbGovInterfaceInfo.getProtocolId()).getBodyType()));
interfaceDownLoadExcelInterfaceDetail.setTakeApp("");
interfaceDownLoadExcelInterfaceDetail.setOthPro("");
interfaceDownLoadExcelInterfaceDetail.setZrPeople("");
interfaceDownLoadExcelInterfaceDetails.add(interfaceDownLoadExcelInterfaceDetail);
seq += 1;
}
return interfaceDownLoadExcelInterfaceDetails;
}
public String isOrNoInput(String required){
if ("1".equals(required)) {
required = "Y";
} else if ("0".equals(required)) {
required = "N";
}
return required;
}
public String putDataTypeFormat(String dataType){
if (dataType.equals("1")) {
dataType = "string";
} else if (dataType.equals("2")) {
dataType = "integer";
} else if (dataType.equals("3")) {
dataType = "double";
} else if (dataType.equals("4")) {
dataType = "lang";
} else if (dataType.equals("5")) {
dataType = "bigdecimal";
} else if (dataType.equals("6")) {
dataType = "boolean";
} else if (dataType.equals("7")) {
dataType = "date";
} else if (dataType.equals("8")) {
dataType = "Array";
} else if (dataType.equals("9")) {
dataType = "Object";
} else if (dataType.equals("88")) {
dataType = "Array_end";
} else if (dataType.equals("99")) {
dataType = "Object_end";
}
return dataType;
}
public String fileTypeFormat(String fileType){
if (fileType.equals("1")) {
fileType = "input";
} else if (fileType.equals("2")) {
fileType = "output";
}
return fileType;
}
public String commProtocolFormat(String reqWay){
if (reqWay.equals("0")) {
reqWay = "https";
} else if (reqWay.equals("1")) {
reqWay = "http";
} else if (reqWay.equals("2")) {
reqWay = "tcp";
} else if (reqWay.equals("3")) {
reqWay = "dubbo";
} else if (reqWay.equals("4")) {
reqWay = "rest";
} else {
reqWay = "";
}
return reqWay;
}
public String messageFormat(String formatMessage){
if (formatMessage.equals("1")) {
formatMessage = "json";
} else if (formatMessage.equals("2")) {
formatMessage = "xml";
} else if (formatMessage.equals("3")) {
formatMessage = "fixed";
} else if (formatMessage.equals("4")) {
formatMessage = "key_value";
} else {
formatMessage="";
}
return formatMessage;
}(3)实体类 EsbGovInterfaceInfo
public class EsbGovInterfaceInfo {
private static final long serialVersionUID = -84694823324557796L;
/**
* 交易编码
*/
private String tradeCode;
/**
* 交易名称
*/
private String tradeName;
/**
* 应用编码
*/
private String appId;
/**
* 接口编码
*/
private String interfaceCode;
/**
* 接口地址
*/
private String interfaceAddress;
/**
* 接口版本
*/
private String interfaceVersion;
/**
* 协议id
*/
private String protocolId;
/**
* 协议参数
*/
private String protocolParam;
/**
* 接口描述
*/
private String interfaceDesc;
/**
* 下发状态
*/
private String issue;
/**
* 服务编码
*/
private String serviceCode;
/**
* 启用限流，0：禁用，1：启用
*/
private String enableLimit;
/**
* 负载模式，0：默认（轮询），1：自定义
*/
private String balanceModel;
//是否启用超时时间
private String enableTimeout;
//超时时间
private String timeout;
//是否启用失败重试
private String enableRetry;
//失败重试次数
private String retryCount;
//失败重试间隔时间
private String retryIntervalTime;
//p端拆组包
private String pUnpackPack;
public String getpUnpackPack() {
return pUnpackPack;
}
public void setpUnpackPack(String pUnpackPack) {
this.pUnpackPack = pUnpackPack;
}
public String getEnableTimeout() {
return enableTimeout;
}
public void setEnableTimeout(String enableTimeout) {
this.enableTimeout = enableTimeout;
}
public String getTimeout() {
return timeout;
}
public void setTimeout(String timeout) {
this.timeout = timeout;
}
public String getEnableRetry() {
return enableRetry;
}
public void setEnableRetry(String enableRetry) {
this.enableRetry = enableRetry;
}
public String getRetryCount() {
return retryCount;
}
public void setRetryCount(String retryCount) {
this.retryCount = retryCount;
}
public String getRetryIntervalTime() {
return retryIntervalTime;
}
public void setRetryIntervalTime(String retryIntervalTime) {
this.retryIntervalTime = retryIntervalTime;
}
public String getServiceCode() {
return serviceCode;
}
public void setServiceCode(String serviceCode) {
this.serviceCode = serviceCode;
}
public String getEnableLimit() {
return enableLimit;
}
public void setEnableLimit(String enableLimit) {
this.enableLimit = enableLimit;
}
public String getBalanceModel() {
return balanceModel;
}
public void setBalanceModel(String balanceModel) {
this.balanceModel = balanceModel;
}
public String getIssue() {
return issue;
}
public void setIssue(String issue) {
this.issue = issue;
}
public String getTradeCode() {
return tradeCode;
}
public void setTradeCode(String tradeCode) {
this.tradeCode = tradeCode;
}
public String getTradeName() {
return tradeName;
}
public void setTradeName(String tradeName) {
this.tradeName = tradeName;
}
public String getAppId() {
return appId;
}
public void setAppId(String appId) {
this.appId = appId;
}
public String getInterfaceCode() {
return interfaceCode;
}
public void setInterfaceCode(String interfaceCode) {
this.interfaceCode = interfaceCode;
}
public String getInterfaceAddress() {
return interfaceAddress;
}
public void setInterfaceAddress(String interfaceAddress) {
this.interfaceAddress = interfaceAddress;
}
public String getInterfaceVersion() {
return interfaceVersion;
}
public void setInterfaceVersion(String interfaceVersion) {
this.interfaceVersion = interfaceVersion;
}
public String getProtocolId() {
return protocolId;
}
public void setProtocolId(String protocolId) {
this.protocolId = protocolId;
}
public String getProtocolParam() {
return protocolParam;
}
public void setProtocolParam(String protocolParam) {
this.protocolParam = protocolParam;
}
public String getInterfaceDesc() {
return interfaceDesc;
}
public void setInterfaceDesc(String interfaceDesc) {
this.interfaceDesc = interfaceDesc;
}
}
（4）操作EasyExcel主要类
public class ExcelUtils {
public static void writeExcel(
HttpServletResponse response,
List<? extends Object> data,
String fileName,
String sheetName,
Class clazz) throws Exception {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
response.setContentType("application/vnd.ms-excel");
//response.setContentType("application/force-download");// 设置强制下载不打开
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
public static void writeInterfaceExcel(ExcelWriter excelWriter, List<? extends Object> data, Integer i, String sheetName,Class clazz) throws IOException {
/**
* .registerWriteHandler() 为自定义构造器
* new LongestMatchColumnWidthStyleStrategy() 自动列宽
*/
WriteSheet writeSheet =EasyExcel.writerSheet(i,sheetName).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(DetectionCellStyleStrategy.getStyleStrategy((short)11,(short)9)).build();
excelWriter.write(data,writeSheet);
}
public static void writeInterfaceExcelInfo(ExcelWriter excelWriter, Map<String, List<? extends Object>> listMap, Integer j, String sheetName, String tradeCode, Class clazz, List<InterfaceFieldModel> lists) throws IOException {
/**
* .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) 为自定义构造器
* new LongestMatchColumnWidthStyleStrategy()自动列宽
* .registerWriteHandler(new ExcelMerchStrategyCells())
* new ExcelMerchStrategyCells() 合并单元格
* // 行合并策略map
* Map<String, List<RowRangeDto>> strategyMap = addMerStrategy((List<InterfaceDownLoadExcelInterfaceInfo>) list);
* // 列合并策略
* int[] totalLength= totalLength(lists);
* WriteSheet writeSheet=EasyExcel.writerSheet(i,sheetName +i).head(clazz)
* .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
* .useDefaultStyle(true).relativeHeadRowIndex(5)
* .registerWriteHandler(DetectionCellStyleStrategy.getStyleStrategy((short)15,(short)9))
* .registerWriteHandler(new ExcelMerchStrategyCells("sheetName","tradeCode",i,totalLength,strategyMap))
* .build();
* excelWriter.write(list,writeSheet);
*/
Integer i=j+5;//前4个为头
for (Map.Entry<String,List<? extends Object>> mapObjects : listMap.entrySet()) {
WriteSheet writeSheet=EasyExcel.writerSheet(i,sheetName).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(DetectionCellStyleStrategy.getStyleStrategy((short)11,(short)9))
.registerWriteHandler(new ExcelMerchStrategyCells(tradeCode,sheetName,i, ExcelMerchStrategyCells.totalLength(lists),ExcelMerchStrategyCells.addMerStrategy((List<InterfaceDownLoadExcelInterfaceInfo>) mapObjects.getValue())))
.needHead(false).build();
if("input".equals(mapObjects.getKey())){
WriteTable writeTable0= EasyExcel.writerTable(0).needHead(true).useDefaultStyle(true)
.useDefaultStyle(true).relativeHeadRowIndex(5).build();
excelWriter.write(mapObjects.getValue(),writeSheet,writeTable0);
}else if("output".equals(mapObjects.getKey())){
WriteTable writeTable1=EasyExcel.writerTable(1).needHead(true).build();
excelWriter.write(mapObjects.getValue(),writeSheet,writeTable1);
}
}
}
public static OutputStream getExcelWriter(String fileName, HttpServletResponse response){
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
OutputStream outputStream=null;
try {
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
outputStream = response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return outputStream;
}
/**
* @param fileName
* @param response
* @return
* @throws Exception
*/
public static void writeExcels(
HttpServletResponse response,
List<? extends Object> data,
String fileName,
String sheetName,
Class clazz,
Map<Integer, String[]> map) throws Exception {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
response.setContentType("application/vnd.ms-excel");
//response.setContentType("application/force-download");// 设置强制下载不打开
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new CustomSheetWriteHandler(map)).build();
WriteSheet sheet = EasyExcel.writerSheet(0, sheetName).build();
excelWriter.write(data, sheet);
excelWriter.finish();
}
}（5）实现的策略
1>.样式策略
public class DetectionCellStyleStrategy {
/**
* 导出excel时的样式配置
*
* @param headFont
* contentFont字体大小
* @return
*/
public static HorizontalCellStyleStrategy getStyleStrategy(short headFont, short contentFont) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints(headFont);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(true);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
headWriteCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
// FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints(contentFont);
// 字体样式
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 自动换行
contentWriteCellStyle.setWrapped(true);
// 水平对齐方式
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
2>.合并策略
public class ExcelMerchStrategyCells extends AbstractMergeStrategy implements SheetWriteHandler {
//合并坐标集合
private List<CellRangeAddress> cellRangeAddress;
private int[] totalLength;//检测方数据条数小计行数
private Map<String, List<RowRangeDto>> strategyMap; //需要合并行的坐标
private String tradeName;
private String tradeCode;
private Integer i;
/**
* //合并列
* CellRangeAddress item1 = new CellRangeAddress(0, 0, 2, 5);
* //合并行
* LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
*/
public ExcelMerchStrategyCells(String tradeCode,String tradeName,Integer integer,int[] totalLength1, Map<String, List<RowRangeDto>> strategyMap) {
this.totalLength = totalLength1;
this.strategyMap = strategyMap;
this.tradeCode=tradeCode;
this.tradeName=tradeName;
this.i=integer;
//合并单元格，起始行,结束行,起始列,结束列
CellRangeAddress item1 = new CellRangeAddress(0, 0, 1, 3);
CellRangeAddress item2 = new CellRangeAddress(0, 0, 5, 7);
CellRangeAddress item3 = new CellRangeAddress(0, 0, 10, 12);
CellRangeAddress item4 = new CellRangeAddress(0, 0, 14, 16);
CellRangeAddress item5 =new CellRangeAddress(1, 1, 1, 8);
CellRangeAddress item6=new CellRangeAddress(1, 1, 10, 17);
CellRangeAddress item7=new CellRangeAddress(2, 2, 1, 8);
CellRangeAddress item8=new CellRangeAddress(2, 2, 10, 17);
CellRangeAddress item9=new CellRangeAddress(3, 3, 1, 8);
CellRangeAddress item10=new CellRangeAddress(3, 3, 10, 17);
CellRangeAddress item11=new CellRangeAddress(4, 4, 1, 8);
CellRangeAddress item12=new CellRangeAddress(4, 4, 10, 17);
List<CellRangeAddress> cellRangeAddress=new ArrayList<>();
cellRangeAddress.add(item1);
cellRangeAddress.add(item2);
cellRangeAddress.add(item3);
cellRangeAddress.add(item4);
cellRangeAddress.add(item5);
cellRangeAddress.add(item6);
cellRangeAddress.add(item7);
cellRangeAddress.add(item8);
cellRangeAddress.add(item9);
cellRangeAddress.add(item10);
cellRangeAddress.add(item11);
cellRangeAddress.add(item12);
this.cellRangeAddress=cellRangeAddress;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
// 当前行
int curRowIndex = cell.getRowIndex();
// 当前列
int curColIndex = cell.getColumnIndex();
//合并单元格
/**
* ****加个判断:if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {}****
* 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
* 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
* 但此时A2,A3已经是合并的单元格了
* cell.getRowIndex() == 6 表头占据了5行
*/
if (curRowIndex == 6 && curColIndex == 0) {
for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
List<RowRangeDto> rowRangeDtoList = entry.getValue();
for (RowRangeDto rowRangeDto : rowRangeDtoList) {
// 添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(curRowIndex + rowRangeDto.getStart() - 1,
curRowIndex + rowRangeDto.getEnd() - 1, columnIndex, columnIndex));
}
}
}
/**if (curColIndex == 0 || curColIndex == 3) {// 列合并
int index = -1;
for (int i=0; i< totalLength.length; i++) {
int entry = totalLength[i];
index = index + entry+1;//加1是因为合并的费用小计行
if (integer == index) {// || (relativeRowIndex - first) % TFByLength == 0
sheet.addMergedRegionUnsafe(
new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + 1));
}
}
}*/
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(i);
if (CollectionUtils.isNotEmpty(cellRangeAddress)) {
for (CellRangeAddress item : cellRangeAddress) {
sheet.addMergedRegionUnsafe(item);
}
}
CellStyle row1Cell0Style = workbook.createCellStyle();
row1Cell0Style.setVerticalAlignment(VerticalAlignment.CENTER);
row1Cell0Style.setAlignment(HorizontalAlignment.LEFT);
row1Cell0Style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
row1Cell0Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font row1Cell0Font = workbook.createFont();
row1Cell0Font.setBold(true);
row1Cell0Font.setFontName("宋体");
row1Cell0Font.setFontHeightInPoints((short) 10);
row1Cell0Style.setFont(row1Cell0Font);
row1Cell0Style.setBorderLeft(BorderStyle.THIN);// 左边框
row1Cell0Style.setBorderTop(BorderStyle.THIN);// 上边框
row1Cell0Style.setBorderRight(BorderStyle.THIN);// 右边框
row1Cell0Style.setBorderBottom(BorderStyle.THIN);// 下边框
CellStyle row1Cell1Style = workbook.createCellStyle();
row1Cell1Style.setVerticalAlignment(VerticalAlignment.CENTER);
row1Cell1Style.setAlignment(HorizontalAlignment.CENTER);
row1Cell1Style.setFillForegroundColor(IndexedColors.BLACK.getIndex());
row1Cell1Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font row1Cell1Font = workbook.createFont();
row1Cell1Font.setBold(true);
row1Cell1Font.setFontName("宋体");
row1Cell1Font.setFontHeightInPoints((short) 13);
row1Cell1Font.setColor(IndexedColors.WHITE.getIndex());
row1Cell1Style.setFont(row1Cell1Font);
row1Cell1Style.setBorderLeft(BorderStyle.THIN);// 左边框
row1Cell1Style.setBorderTop(BorderStyle.THIN);// 上边框
row1Cell1Style.setBorderRight(BorderStyle.THIN);// 右边框
row1Cell1Style.setBorderBottom(BorderStyle.THIN);// 下边框
CellStyle row2CellStyle = workbook.createCellStyle();
row2CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row2CellStyle.setAlignment(HorizontalAlignment.LEFT);
/*
* 单元格背景色设置
*/
row2CellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
row2CellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font row2Font = workbook.createFont();
row1Cell0Font.setBold(true);
row2Font.setFontName("宋体");
row2Font.setFontHeightInPoints((short) 10);
row2CellStyle.setFont(row2Font);
row2CellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
row2CellStyle.setBorderTop(BorderStyle.THIN);// 上边框
row2CellStyle.setBorderRight(BorderStyle.THIN);// 右边框
row2CellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
//设置第一行标题
Row row1 = sheet.createRow(0);
Cell row1Cell0 = row1.createCell(0);
row1Cell0.setCellValue("接口名称");
row1Cell0.setCellStyle(row1Cell0Style);
Cell row1Cell1 = row1.createCell(1);
row1Cell1.setCellValue(tradeName);
row1Cell1.setCellStyle(row1Cell1Style);
Cell row1Cell2 = row1.createCell(2);
row1Cell2.setCellValue("");
row1Cell2.setCellStyle(row1Cell1Style);
row1Cell2.setCellStyle(row1Cell1Style);
Cell row1Cell3 = row1.createCell(3);
row1Cell3.setCellValue("");
row1Cell3.setCellStyle(row1Cell1Style);
Cell row1Cell4 = row1.createCell(4);
row1Cell4.setCellValue("交易码");
row1Cell4.setCellStyle(row1Cell0Style);
Cell row1Cell5 = row1.createCell(5);
row1Cell5.setCellValue(tradeCode);
row1Cell5.setCellStyle(row1Cell1Style);
Cell row1Cell6 = row1.createCell(6);
row1Cell6.setCellValue("");
row1Cell6.setCellStyle(row1Cell1Style);
Cell row1Cell7 = row1.createCell(7);
row1Cell7.setCellValue("");
row1Cell7.setCellStyle(row1Cell1Style);
Cell row1Cell8 = row1.createCell(8);
row1Cell8.setCellValue("返回");
row1Cell8.setCellStyle(row1Cell0Style);
Cell row1Cell9 = row1.createCell(9);
row1Cell9.setCellValue("接口名称");
row1Cell9.setCellStyle(row1Cell0Style);
Cell row1Cell10 = row1.createCell(10);
row1Cell10.setCellValue(tradeName);
row1Cell10.setCellStyle(row1Cell1Style);
Cell row1Cell11 = row1.createCell(11);
row1Cell11.setCellValue("");
row1Cell11.setCellStyle(row1Cell1Style);
Cell row1Cell12 = row1.createCell(12);
row1Cell12.setCellValue("");
row1Cell12.setCellStyle(row1Cell1Style);
Cell row1Cell13 = row1.createCell(13);
row1Cell13.setCellValue("交易码");
row1Cell13.setCellStyle(row1Cell0Style);
Cell row1Cell14 = row1.createCell(14);
row1Cell14.setCellValue(tradeCode);
row1Cell14.setCellStyle(row1Cell1Style);
Cell row1Cell15 = row1.createCell(15);
row1Cell15.setCellValue("");
row1Cell15.setCellStyle(row1Cell1Style);
Cell row1Cell16 = row1.createCell(16);
row1Cell16.setCellValue("");
row1Cell16.setCellStyle(row1Cell1Style);
Cell row1Cell17 = row1.createCell(17);
row1Cell17.setCellValue("返回");
row1Cell17.setCellStyle(row1Cell0Style);
// 设置第二行标题
Row row2 = sheet.createRow(1);
Cell row2Cell0 = row2.createCell(0);
row2Cell0.setCellValue("格式");
row2Cell0.setCellStyle(row1Cell0Style);
Cell row2Cell1 = row2.createCell(1);
row2Cell1.setCellValue("json");
row2Cell1.setCellStyle(row2CellStyle);
Cell row2Cell2 = row2.createCell(2);
row2Cell2.setCellValue("");
row2Cell2.setCellStyle(row2CellStyle);
Cell row2Cell3 = row2.createCell(3);
row2Cell3.setCellValue("");
row2Cell3.setCellStyle(row2CellStyle);
Cell row2Cell4 = row2.createCell(4);
row2Cell4.setCellValue("");
row2Cell4.setCellStyle(row2CellStyle);
Cell row2Cell5= row2.createCell(5);
row2Cell5.setCellValue("");
row2Cell5.setCellStyle(row2CellStyle);
Cell row2Cell6 = row2.createCell(6);
row2Cell6.setCellValue("");
row2Cell6.setCellStyle(row2CellStyle);
Cell row2Cell7 = row2.createCell(7);
row2Cell7.setCellValue("");
row2Cell7.setCellStyle(row2CellStyle);
Cell row2Cell8 = row2.createCell(8);
row2Cell8.setCellValue("");
row2Cell8.setCellStyle(row2CellStyle);
Cell row2Cell9 = row2.createCell(9);
row2Cell9.setCellValue("原格式");
row2Cell9.setCellStyle(row1Cell0Style);
Cell row2Cell10 = row2.createCell(10);
row2Cell10.setCellValue("xml");
row2Cell10.setCellStyle(row2CellStyle);
Cell row2Cell11 = row2.createCell(11);
row2Cell11.setCellValue("");
row2Cell11.setCellStyle(row2CellStyle);
Cell row2Cell12 = row2.createCell(12);
row2Cell12.setCellValue("");
row2Cell12.setCellStyle(row2CellStyle);
Cell row2Cell13 = row2.createCell(13);
row2Cell13.setCellValue("");
row2Cell13.setCellStyle(row2CellStyle);
Cell row2Cell14 = row2.createCell(14);
row2Cell14.setCellValue("");
row2Cell14.setCellStyle(row2CellStyle);
Cell row2Cell15 = row2.createCell(15);
row2Cell15.setCellValue("");
row2Cell15.setCellStyle(row2CellStyle);
Cell row2Cell16 = row2.createCell(16);
row2Cell16.setCellValue("");
row2Cell16.setCellStyle(row2CellStyle);
Cell row2Cell17 = row2.createCell(17);
row2Cell17.setCellValue("");
row2Cell17.setCellStyle(row2CellStyle);
// 设置第三行标题
Row row3 = sheet.createRow(2);
row3.setHeight((short) 400);
Cell row3Cell0 = row3.createCell(0);
row3Cell0.setCellValue("请求方式");
row3Cell0.setCellStyle(row1Cell0Style);
Cell row3Cell1 = row3.createCell(1);
row3Cell1.setCellValue("http");
row3Cell1.setCellStyle(row2CellStyle);
Cell row3Cell2 = row3.createCell(2);
row3Cell2.setCellValue("");
row3Cell2.setCellStyle(row2CellStyle);
Cell row3Cell3 = row3.createCell(3);
row3Cell3.setCellValue("");
row3Cell3.setCellStyle(row2CellStyle);
Cell row3Cell4 = row3.createCell(4);
row3Cell4.setCellValue("");
row3Cell4.setCellStyle(row2CellStyle);
Cell row3Cell5= row3.createCell(5);
row3Cell5.setCellValue("");
row3Cell5.setCellStyle(row2CellStyle);
Cell row3Cell6 = row3.createCell(6);
row3Cell6.setCellValue("");
row3Cell6.setCellStyle(row2CellStyle);
Cell row3Cell7 = row3.createCell(7);
row3Cell7.setCellValue("");
row3Cell7.setCellStyle(row2CellStyle);
Cell row3Cell8 = row3.createCell(8);
row3Cell8.setCellValue("");
row3Cell8.setCellStyle(row2CellStyle);
Cell row3Cell9 = row3.createCell(9);
row3Cell9.setCellValue("原请求方式");
row3Cell9.setCellStyle(row1Cell0Style);
Cell row3Cell10 = row3.createCell(10);
row3Cell10.setCellValue("tcp");
row3Cell10.setCellStyle(row2CellStyle);
Cell row3Cell11 = row3.createCell(11);
row3Cell11.setCellValue("");
row3Cell11.setCellStyle(row2CellStyle);
Cell row3Cell12 = row3.createCell(12);
row3Cell12.setCellValue("");
row3Cell12.setCellStyle(row2CellStyle);
Cell row3Cell13 = row3.createCell(13);
row3Cell13.setCellValue("");
row3Cell13.setCellStyle(row2CellStyle);
Cell row3Cell14 = row3.createCell(14);
row3Cell14.setCellValue("");
row3Cell14.setCellStyle(row2CellStyle);
Cell row3Cell15 = row3.createCell(15);
row3Cell15.setCellValue("");
row3Cell15.setCellStyle(row2CellStyle);
Cell row3Cell16 = row3.createCell(16);
row3Cell16.setCellValue("");
row3Cell16.setCellStyle(row2CellStyle);
Cell row3Cell17 = row3.createCell(17);
row3Cell17.setCellValue("");
row3Cell17.setCellStyle(row2CellStyle);
// 设置第四行标题
Row row4 = sheet.createRow(3);
row3.setHeight((short) 400);
Cell row4Cell0 = row4.createCell(0);
row4Cell0.setCellValue("接口描述");
row4Cell0.setCellStyle(row1Cell0Style);
Cell row4Cell1 = row4.createCell(1);
row4Cell1.setCellValue(tradeName);
row4Cell1.setCellStyle(row2CellStyle);
Cell row4Cell2 = row4.createCell(2);
row4Cell2.setCellValue("");
row4Cell2.setCellStyle(row2CellStyle);
Cell row4Cell3 = row4.createCell(3);
row4Cell3.setCellValue("");
row4Cell3.setCellStyle(row2CellStyle);
Cell row4Cell4 = row4.createCell(4);
row4Cell4.setCellValue("");
row4Cell4.setCellStyle(row2CellStyle);
Cell row4Cell5= row4.createCell(5);
row4Cell5.setCellValue("");
row4Cell5.setCellStyle(row2CellStyle);
Cell row4Cell6 = row4.createCell(6);
row4Cell6.setCellValue("");
row4Cell6.setCellStyle(row2CellStyle);
Cell row4Cell7 = row4.createCell(7);
row4Cell7.setCellValue("");
row4Cell7.setCellStyle(row2CellStyle);
Cell row4Cell8 = row4.createCell(8);
row4Cell8.setCellValue("");
row4Cell8.setCellStyle(row2CellStyle);
Cell row4Cell9 = row4.createCell(9);
row4Cell9.setCellValue("原接口描述");
row4Cell9.setCellStyle(row1Cell0Style);
Cell row4Cell10 = row4.createCell(10);
row4Cell10.setCellValue(tradeName);
row4Cell10.setCellStyle(row2CellStyle);
Cell row4Cell11 = row4.createCell(11);
row4Cell11.setCellValue("");
row4Cell11.setCellStyle(row2CellStyle);
Cell row4Cell12 = row4.createCell(12);
row4Cell12.setCellValue("");
row4Cell12.setCellStyle(row2CellStyle);
Cell row4Cell13 = row4.createCell(13);
row4Cell13.setCellValue("");
row4Cell13.setCellStyle(row2CellStyle);
Cell row4Cell14 = row4.createCell(14);
row4Cell14.setCellValue("");
row4Cell14.setCellStyle(row2CellStyle);
Cell row4Cell15 = row4.createCell(15);
row4Cell15.setCellValue("");
row4Cell15.setCellStyle(row2CellStyle);
Cell row4Cell16 = row4.createCell(16);
row4Cell16.setCellValue("");
row4Cell16.setCellStyle(row2CellStyle);
Cell row4Cell17 = row4.createCell(17);
row4Cell17.setCellValue("");
row4Cell17.setCellStyle(row2CellStyle);
// 设置第五行标题
Row row5 = sheet.createRow(4);
row3.setHeight((short) 400);
Cell row5Cell0 = row5.createCell(0);
row5Cell0.setCellValue("调用服务");
row5Cell0.setCellStyle(row1Cell0Style);
Cell row5Cell1 = row5.createCell(1);
row5Cell1.setCellValue("");
row5Cell1.setCellStyle(row2CellStyle);
Cell row5Cell2 = row5.createCell(2);
row5Cell2.setCellValue("");
row5Cell2.setCellStyle(row2CellStyle);
Cell row5Cell3 = row5.createCell(3);
row5Cell3.setCellValue("");
row5Cell3.setCellStyle(row2CellStyle);
Cell row5Cell4 = row5.createCell(4);
row5Cell4.setCellValue("");
row5Cell4.setCellStyle(row2CellStyle);
Cell row5Cell5= row5.createCell(5);
row5Cell5.setCellValue("");
row5Cell5.setCellStyle(row2CellStyle);
Cell row5Cell6 = row5.createCell(6);
row5Cell6.setCellValue("");
row5Cell6.setCellStyle(row2CellStyle);
Cell row5Cell7 = row5.createCell(7);
row5Cell7.setCellValue("");
row5Cell7.setCellStyle(row2CellStyle);
Cell row5Cell8 = row5.createCell(8);
row5Cell8.setCellValue("");
row5Cell8.setCellStyle(row2CellStyle);
Cell row5Cell9 = row5.createCell(9);
row5Cell9.setCellValue("原调用服务");
row5Cell9.setCellStyle(row1Cell0Style);
Cell row5Cell10 = row5.createCell(10);
row5Cell10.setCellValue("");
row5Cell10.setCellStyle(row2CellStyle);
Cell row5Cell11 = row5.createCell(11);
row5Cell11.setCellValue("");
row5Cell11.setCellStyle(row2CellStyle);
Cell row5Cell12 = row5.createCell(12);
row5Cell12.setCellValue("");
row5Cell12.setCellStyle(row2CellStyle);
Cell row5Cell13 = row5.createCell(13);
row5Cell13.setCellValue("");
row5Cell13.setCellStyle(row2CellStyle);
Cell row5Cell14 = row5.createCell(14);
row5Cell14.setCellValue("");
row5Cell14.setCellStyle(row2CellStyle);
Cell row5Cell15 = row5.createCell(15);
row5Cell15.setCellValue("");
row5Cell15.setCellStyle(row2CellStyle);
Cell row5Cell16 = row5.createCell(16);
row5Cell16.setCellValue("");
row5Cell16.setCellStyle(row2CellStyle);
Cell row5Cell17 = row5.createCell(17);
row5Cell17.setCellValue("");
row5Cell17.setCellStyle(row2CellStyle);
}
/**
* 算出每个数据类型的条数
*
* @param dataList
* @return
*/
public static int[] totalLength(List<InterfaceFieldModel> dataList) {
int[] totalLength;
final int[] i = {0};
// 简单地按数据类型排个序，再分组，方便组装数据
Map<String, List<InterfaceFieldModel>> clazzMap = dataList.stream()
.sorted(Comparator.comparing(InterfaceFieldModel::getMetadataType))
.collect(Collectors.groupingBy(InterfaceFieldModel::getMetadataType, TreeMap::new, Collectors.toList()));
totalLength = new int[clazzMap.size() + 1];
clazzMap.forEach((clazz, clazzes) -> {
totalLength[i[0]] = clazzes.size();
i[0] = i[0] + 1;// 每个数据类型后都跟一条小计行
});
return totalLength;
}
/**
* 计算需要合并的行坐标
*
* @param excelDtoList
* @return
*/
public static Map<String, List<RowRangeDto>> addMerStrategy(@NotNull List<InterfaceDownLoadExcelInterfaceInfo> excelDtoList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
InterfaceDownLoadExcelInterfaceInfo preExcelDto = null;
for (int i = 0; i < excelDtoList.size(); i++) {
InterfaceDownLoadExcelInterfaceInfo currDto = excelDtoList.get(i);
if (preExcelDto != null) {
if (currDto.getDetail().trim().equals(preExcelDto.getDetail().trim())) {
fillStrategyMap(strategyMap, "9", i);//明细标题
}
}
preExcelDto = currDto;
}
return strategyMap;
}
/**
* @Author: TheBigBlue
* @Description: 新增或修改合并策略map
* @Date: 2020/3/16
* @Param:
* @return:
**/
private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
boolean flag = false;
for (RowRangeDto dto : rowRangeDtoList) {
// 分段list中是否有end索引是上一行索引的，如果有，则索引+1
if (dto.getEnd() == index) {
dto.setEnd(index + 1);
flag = true;
}
}
// 如果没有，则新增分段
if (!flag) {
rowRangeDtoList.add(new RowRangeDto(index, index + 1));
}
strategyMap.put(key, rowRangeDtoList);
}
}
完，待后续更多作品.............
}
4399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
