前台代码
html：
<button class="btn greenButton" style="auto;padding: 0 12px;" ng-click="exportXML()">导出</button>
controller：
//导出
$scope.exportXML = function () {
//debugger;
let param = {
crashRepairExcelDto:$scope.crashRepairFeeAnalysisInfoList,
// totalNameList:Object.keys($scope.operatPointObj)
};
function download(data) {
if(data.state){
angular.alert(data.msg);
}else{
const blob = new Blob([data]);
const blobUrl = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.download = getXMLName('维修费用统计');
a.href = blobUrl;
a.click();
}
}
crashRepairFeeStatisticsServ.exportXML(param,download);
};
let getXMLName = function(title){
let now = new Date();
function setDateFormat(num) {
return num > 9 ? num : '0'+num;
}
return title + '_' + now.getFullYear() + '_' + setDateFormat(now.getMonth() + 1) + '_' + setDateFormat(now.getDate()) + '.xls';
};
service：
// 导出 XMl
exportXML:function(param,callback){
const req = new XMLHttpRequest();
req.open('POST', ApiPath.api.CrashRepairExport, true);
req.responseType = 'blob';
req.setRequestHeader('Content-Type', 'application/json');
req.onload = function() {
if(callback){
/*const data = req.response;
callback(data)*/
var reader = new FileReader();
let data = req.response;
reader.readAsText(data, 'utf-8');
let msg = '';
reader.onload = function () {
msg = JSON.parse(reader.result.toString());
if(callback){
callback(msg);
}
};
}
if(req.response.type == 'application/vnd.ms-excel'){
if(callback){
callback(req.response);
}
}
};
req.send(JSON.stringify(param));
},
// 调用接口：CrashRepairExport:app.bd+"/crashRepairFeeStatistics/crashRepairExport",//维修费用统计 导出
后台代码：
入参：
/**
* @ClassName：CrashCostExcelDto
* @Description：TODO 维修费用统计数据导出集合
* @author：mapCode
* @date：2020/4/21 11:25
* @Version V1.0
**/
public class CrashCostExcelDto implements Serializable {
//维修费用统计表格标题名
private List<String> totalNameList;
//装载维修费用统计数据
private List<CostCountDto> crashRepairExcelDto;
public List<String> getTotalNameList() {
return totalNameList;
}
public void setTotalNameList(List<String> totalNameList) {
this.totalNameList = totalNameList;
}
public List<CostCountDto> getCrashRepairExcelDto() {
return crashRepairExcelDto;
}
public void setCrashRepairExcelDto(List<CostCountDto> crashRepairExcelDto) {
this.crashRepairExcelDto = crashRepairExcelDto;
}
@Override
public String toString() {
return "CrashCostExcelDto{" +
"totalNameList=" + totalNameList +
", crashRepairExcelDto=" + crashRepairExcelDto +
'}';
}
Api接口层：
/**
* @Author mapCode
* @Description //TODO 维修费用统计
* @Date 9:25 2020/4/22
* @Param [crashCostExcelDto, response]
* @return java.util.Map
**/
@PostMapping("crashRepairExport")
Map crashRepairExport(@RequestBody CrashCostExcelDto crashCostExcelDto, HttpServletResponse response);
Controller实现类：
@Override
public Map crashRepairExport(@RequestBody CrashCostExcelDto crashCostExcelDto, HttpServletResponse response) {
Map data = new HashMap();
try {
//调用Service接口对应方法处理
crashRepairFeeStatisticsService.crashRepairExport(crashCostExcelDto, response);
data.put("state", ResponseInfoTypeEnum.REQUEST_SUCCESS.getCode());
data.put("msg", ResponseInfoTypeEnum.REQUEST_SUCCESS.getName());
} catch (Exception e) {
logger.error("CrashRepairFeeStatisticsController.CrashRepairExport，异常信息:{}", e.toString());
data.put("state", ResponseInfoTypeEnum.REQUEST_EXCEPTION.getCode());
data.put("msg", ResponseInfoTypeEnum.REQUEST_EXCEPTION.getName());
}
return data;
}
Service接口层：
/**
* @Author mapCode
* @Description //TODO 维修费用统计打印功能
* @Date 15:19 2020/4/21
* @Param [crashCostExcelDto, response]
* @return void
**/
void crashRepairExport(CrashCostExcelDto crashCostExcelDto, HttpServletResponse response) throws Exception;
Service实现类：
@Override
public void crashRepairExport(CrashCostExcelDto crashCostExcelDto, HttpServletResponse response) throws Exception {
logger.info("维修费用统计导出入参:{}",crashCostExcelDto.getCrashRepairExcelDto());
//1.获取Excel工作簿对象（相当于创建一个了Excel）
HSSFWorkbook wb = new HSSFWorkbook();
//2.获取sheet对象（相当于创建了一行）
// HSSFSheet sheet = wb.createSheet("sheet1");
//2.创建单元格样式对象
HSSFCellStyle cellStyle = wb.createCellStyle();
//3.添加常用样式
cellStyle.setWrapText(true);//设置自动换行
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中显示
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//4.设置单元格背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//填充单元格
cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);//设置单元格背景色
HSSFCellStyle cellStyle2 = wb.createCellStyle();
//3.添加常用样式
cellStyle2.setWrapText(true);//设置自动换行
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中显示
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//主要格式格式
//主要格式格式
HSSFCellStyle mainStyle = wb.createCellStyle();
mainStyle.setWrapText(true);//设置自动换行
mainStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
mainStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
mainStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
mainStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
mainStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
mainStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
mainStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//主要格式字体
HSSFFont mainFont =wb.createFont();
mainFont.setFontName("宋体");
mainFont.setFontHeightInPoints((short) 10);//字体大小
mainStyle.setFont(mainFont);
//首行格式（创建首行样式）
HSSFCellStyle preHeadStyle = wb.createCellStyle();
preHeadStyle.setWrapText(true);//设置自动换行
preHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
preHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
preHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
preHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
preHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
preHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
preHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//首行字体格式
HSSFFont preHeadFont =wb.createFont();
preHeadFont.setFontName("宋体");
preHeadFont.setFontHeightInPoints((short) 10);//字体大小
preHeadStyle.setFont(preHeadFont);
//首行填充颜色
preHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//填充单元格
preHeadStyle.setFillForegroundColor(HSSFColor.AQUA.index);//设置单元格背景色
//标题格式
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setWrapText(true);//设置自动换行
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//标题字体格式
HSSFFont headFont =wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 11);//字体大小
headStyle.setFont(headFont);
headStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//标题
//合并单元格
//序号
CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//任务编号
CellRangeAddress region2 = new CellRangeAddress(0, 1, (short) 1, (short) 1); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//品牌
CellRangeAddress region3 = new CellRangeAddress(0, 1, (short) 2, (short) 2); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//车系
CellRangeAddress region4 = new CellRangeAddress(0, 1, (short) 3, (short) 3); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//车型
CellRangeAddress region5 = new CellRangeAddress(0, 1, (short) 4, (short) 4); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//中保研
CellRangeAddress region6 = new CellRangeAddress(0, 0, (short) 5, (short) 7); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//维修费用统计数据
List<CostCountDto> crashRepairExcelDto = crashCostExcelDto.getCrashRepairExcelDto();
/** 保险公司 */
List<InsuranceFeeDto> insuranceCompanyFeeDtoList = new ArrayList<InsuranceFeeDto>();
if(crashRepairExcelDto!=null && crashRepairExcelDto.size()>0) {
insuranceCompanyFeeDtoList = crashRepairExcelDto.get(0).getInsuranceCompanyFeeDto();
}
HSSFSheet partsFeeSheet = wb.createSheet("维修费用统计");
HSSFRow partsFeeRow1;
HSSFCell partsFeeCellHeader1;
HSSFRow partsFeeRow;
HSSFCell partsFeeCellHeader;
HSSFCell partsFeeCell;
//首行（把上面合并好的添加到Excel第一行中）
partsFeeRow1 = partsFeeSheet.createRow( 0);
partsFeeSheet.addMergedRegion(region1);
partsFeeSheet.addMergedRegion(region2);
partsFeeSheet.addMergedRegion(region3);
partsFeeSheet.addMergedRegion(region4);
partsFeeSheet.addMergedRegion(region5);
//partsFeeSheet.addMergedRegion(region6);
//首行值
partsFeeCellHeader1 = partsFeeRow1.createCell(0);
partsFeeCellHeader1.setCellValue("序号");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeCellHeader1 = partsFeeRow1.createCell(1);
partsFeeCellHeader1.setCellValue("任务编号");
/*自动设置列宽*/
partsFeeSheet.setColumnWidth(1, partsFeeSheet.getColumnWidth(1) * 17 / 10);
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeCellHeader1 = partsFeeRow1.createCell(2);
partsFeeCellHeader1.setCellValue("品牌");
partsFeeCellHeader1.setCellStyle(headStyle);
// partsFeeSheet.setColumnWidth(2, partsFeeSheet.getColumnWidth(1) * 17 / 10);
partsFeeCellHeader1 = partsFeeRow1.createCell(3);
partsFeeCellHeader1.setCellValue("车系");
partsFeeCellHeader1.setCellStyle(headStyle);
// partsFeeSheet.setColumnWidth(3, partsFeeSheet.getColumnWidth(1) * 17 / 10);
partsFeeCellHeader1 = partsFeeRow1.createCell(4);
partsFeeCellHeader1.setCellValue("车型");
partsFeeCellHeader1.setCellStyle(headStyle);
// partsFeeSheet.setColumnWidth(4, partsFeeSheet.getColumnWidth(1) * 17 / 10);
//第二行
partsFeeRow = partsFeeSheet.createRow( 1);
partsFeeCellHeader = partsFeeRow.createCell(0);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(1);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(2);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(3);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(4);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
if(insuranceCompanyFeeDtoList!=null && insuranceCompanyFeeDtoList.size()>0){
int j = 5;
for(int i = 0;i<insuranceCompanyFeeDtoList.size();i++){
//合并单元格【05~07】
partsFeeSheet.addMergedRegion(new CellRangeAddress(0, 0, (short) (j+i*2), (short) (j+(i+1)*2)));
//单元格第一个填充公司名称
partsFeeCellHeader1 = partsFeeRow1.createCell(j+i*2); //创建一列
partsFeeCellHeader1.setCellValue(new HSSFRichTextString(insuranceCompanyFeeDtoList.get(i).getFactoryName()));
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeCellHeader1 = partsFeeRow1.createCell((j+1)+i*2);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeCellHeader1 = partsFeeRow1.createCell((j+2)+i*2);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(headStyle);
//单元格第二列填充公司属性
partsFeeCellHeader = partsFeeRow.createCell(j+i*2);
partsFeeCellHeader.setCellValue("约束系统费用(元)");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth(j+i*2, "约束系统费用(元)".getBytes().length*256);
partsFeeCellHeader = partsFeeRow.createCell(j+i*2+1);
partsFeeCellHeader.setCellValue("前碰费用(元)");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth(j+i*2+1, "前碰费用(元)".getBytes().length*256);
partsFeeCellHeader = partsFeeRow.createCell(j+i*2+2);
partsFeeCellHeader.setCellValue("后碰费用(元)");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth(j+i*2+2, "后碰费用(元)".getBytes().length*256);
//每次走完+1；
j++;
}
}
//判断保险公司不等于空
if(crashRepairExcelDto!=null && crashRepairExcelDto.size()>0) {
//表格行
int dataIndex = 1;
//单元格
int dataIndexCell = 0;
//序号及宽度
int excelId = 0;
int brandNameWidth = 0;
int vehicleSystemNameWidth = 0;
int vehicleTypeNameWidth = 0;
//设置单元格的值
for(CostCountDto obj : crashRepairExcelDto) {
dataIndex++;
excelId++;
partsFeeRow = partsFeeSheet.createRow(dataIndex);
partsFeeRow.setHeightInPoints((float)13.5);
//序号
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(excelId);
partsFeeCell.setCellStyle(mainStyle);
//任务编号
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getTaskSheetNo());
partsFeeCell.setCellStyle(mainStyle);
//品牌
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getBrandName());
partsFeeCell.setCellStyle(mainStyle);
brandNameWidth = ExcelUtils.getMaxWidth(brandNameWidth, obj.getBrandName().getBytes().length*256);
partsFeeSheet.setColumnWidth(2, brandNameWidth);
//车系
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getVehicleSystemName());
partsFeeCell.setCellStyle(mainStyle);
vehicleSystemNameWidth = ExcelUtils.getMaxWidth(vehicleSystemNameWidth, obj.getVehicleSystemName().getBytes().length*256);
partsFeeSheet.setColumnWidth(3, vehicleSystemNameWidth);
//车型
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getVehicleTypeName());
partsFeeCell.setCellStyle(mainStyle);
vehicleTypeNameWidth = ExcelUtils.getMaxWidth(vehicleTypeNameWidth, obj.getVehicleTypeName().getBytes().length*256);
partsFeeSheet.setColumnWidth(4, vehicleTypeNameWidth);
for(InsuranceFeeDto OBJ: obj.getInsuranceCompanyFeeDto()) {
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(OBJ.getRestraintSystemsFee());
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(OBJ.getFrontCollisionFee());
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(OBJ.getBehindCollisionFee());
partsFeeCell.setCellStyle(mainStyle);
}
dataIndexCell = 0;
}
}
//导出
// 设置Excel文件名，并以中文进行编码
String codedFileName = new String("D:\维修费用统计".getBytes("gbk"), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
// 响应类型,编码
response.setContentType("application/vnd.ms-excel");
OutputStream osOut = response.getOutputStream();
//写入流
wb.write(osOut);
// 刷新此输出流并强制将所有缓冲的输出字节被写出
osOut.flush();
// 关闭流
osOut.close();
}
/**
* BigDecimal非空校验
*/
private BigDecimal notEmptyCheck(BigDecimal value) throws Exception {
if (value == null) {
return BigDecimal.ZERO;
} else {
return value;
}
}
// 自适应宽度(中文支持)
private void setSizeColumn(XSSFSheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
// 自适应宽度(中文支持)
private void setSizeColumn(HSSFSheet sheet) {
for (int columnNum = 0; columnNum <= 8; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 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(columnNum) != null) {
HSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
- 效果图
变形量统计（导出）
- 前台代码
<div class="toRight">
<button class="btn greenButton" style=" auto;padding: 0 12px;margin-top: 7px" ng-click="exportXML()">
导出
</button>
</div>
- JS代码（controller）
//导出
$scope.exportXML = function () {
let param = {
upBxg:$scope.returnupBxg,
afterJg:$scope.returnupafterJg,
afterBxg:$scope.returnupafterBxg,
upJg:$scope.returnupJg,
};
function download(data) {
if(data.state){
angular.alert(data.msg);
}else{
const blob = new Blob([data]);
const blobUrl = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.download = getXMLName('变形量统计');
a.href = blobUrl;
a.click();
}
}
deformationStatisticsServ.exportXML(param,download);
};
let getXMLName = function(title){
let now = new Date();
function setDateFormat(num) {
return num > 9 ? num : '0'+num;
}
return title + '_' + now.getFullYear() + '_' + setDateFormat(now.getMonth() + 1) + '_' + setDateFormat(now.getDate()) + '.xls';
};
- JS代码（service控制器代码）
// 导出 XMl
exportXML:function(param,callback){
const req = new XMLHttpRequest();
req.open('POST', ApiPath.api.exportTheDeformationData, true);
req.responseType = 'blob';
req.setRequestHeader('Content-Type', 'application/json');
req.onload = function() {
if(callback){
/*const data = req.response;
callback(data)*/
var reader = new FileReader();
let data = req.response;
reader.readAsText(data, 'utf-8');
let msg = '';
reader.onload = function () {
/*if(reader.result){
msg = JSON.parse(reader.result.toString());
}
if(callback){
callback(msg);
}*/
};
}
if(req.response.type == 'application/vnd.ms-excel'){
if(callback){
callback(req.response);
}
}
};
req.send(JSON.stringify(param));
},
- 前后端映射 ApiPathConstant.js
// 变形量统计 导出
exportTheDeformationData:app.bd+"/DeformationStatistics/exportTheDeformationData",
- 前端参数传递
- 后台控制层接口代码
@Api(description = "外覆件结构件变形量表结构", value = "外覆件结构件变形量表结构")
@FeignClient(name = BusinessSystemConstant.BD_SERVICE_NAME, path = DeformationStatisticsApi.PATH)
public interface DeformationStatisticsApi {
String PATH = "bd/DeformationStatistics";
/**
* @Author mapCode
* @Description //TODO 变形量统计导出
* @Date 11:24 2020/5/7
* @Param [taskSheetNo, response]
* @return java.util.Map
**/
@RequestMapping(value = "/exportTheDeformationData", method = RequestMethod.POST)
@ResponseBody
Map exportTheDeformationData(@RequestBody TheDeformationDto theDeformationDto, HttpServletResponse response);
}
- 后台参数接受
//保险杠前碰 11
List<TaskCarClosuresInfoDto> upBxg;
//结构前碰 21
List<TaskLowCarBodyDto> upJg;
//保险杠后碰 12
List<TaskCarClosuresInfoDto> afterBxg ;
//结构后碰 22
List<TaskLowCarBodyDto> afterJg;
TaskCarClosuresInfoDto：
//试验任务单号
private String taskSheetNo;
//试验任务号
private String taskNo;
//车型编码
private String vehicleTypeCode;
//车型名称
@Excel(name = "车型名称")
private String vehicleTypeName;
//数据集合
@ExcelCollection(name = "dataList")
private List<TaskCarClosuresInfoDataDto> dataList;
//保险杠安全指数
private String SafetyRate;
private String testItem;
//车系名称
private String vehicleSystemCode;
//车系名称
private String vehicleSystemName;
//品牌编码
private String brandCode;
//品牌编码
private String brandName;
TaskLowCarBodyDto：
//试验任务单号
private String TaskSheetNo;
//任务号
private String TaskNo;
//车型编码
private String vehicleTypeCode;
//车型名称
private String vehicleTypeName;
//结构数据集合
private List<TaskLowCarBodyChlDto> dataList;
//结构安全指数得分
private String SafetyRate;
private String testItem;
//车系名称
private String vehicleSystemCode;
//车系名称
private String vehicleSystemName;
//品牌编码
private String brandCode;
//品牌编码
private String brandName;
- Controller层是显示类
/**
* @PackageName:com.ciri.businessSystem.rest.dataAnalysis
* @ClassName:DeformationStatisticsController
* @Description:
* @author:yang-li
* @date:2019/9/1 0001 下午 9:02
*/
@RestController
@RequestMapping(value = DeformationStatisticsApi.PATH)
public class DeformationStatisticsController implements DeformationStatisticsApi {
private static final Logger LOGGER = LoggerFactory.getLogger(DeformationStatisticsController.class);
@Autowired
private DeformationStatisticsService deformationStatisticsService;
@Override
public Map exportTheDeformationData(@RequestBody TheDeformationDto theDeformationDto, HttpServletResponse response) {
Map data = new HashMap();
try {
//调用Service实现
deformationStatisticsService.exportTheDeformationData(theDeformationDto, response);
data.put("state", ResponseInfoTypeEnum.REQUEST_SUCCESS.getCode());
data.put("msg", ResponseInfoTypeEnum.REQUEST_SUCCESS.getName());
} catch (Exception e) {
LOGGER.error("DeformationStatisticsController.exportTheDeformationData，异常信息:{}", e);
data.put("state", ResponseInfoTypeEnum.REQUEST_EXCEPTION.getCode());
data.put("msg", ResponseInfoTypeEnum.REQUEST_EXCEPTION.getName());
}
return data;
}
}
- DeformationStatisticsService层接口
void exportTheDeformationData(TheDeformationDto theDeformationDto, HttpServletResponse response) throws Exception;
- DeformationStatisticsServiceImpl解耦实现类
@Override
public void exportTheDeformationData(TheDeformationDto theDeformationDto, HttpServletResponse response) throws Exception {
LOGGER.info("变形量统计导出入参:{}",theDeformationDto.getUpBxg());
//1.获取Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook();
//2.获取sheet对象
//HSSFSheet sheet = wb.createSheet("sheet1");
//2.创建单元格样式对象
HSSFCellStyle cellStyle = wb.createCellStyle();
//3.添加常用样式
cellStyle.setWrapText(true);//设置自动换行
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中显示
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//4.设置单元格背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//填充单元格
cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);//设置单元格背景色
HSSFCellStyle cellStyle2 = wb.createCellStyle();
//3.添加常用样式
cellStyle2.setWrapText(true);//设置自动换行
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中显示
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//主要格式格式
//主要格式格式
HSSFCellStyle mainStyle = wb.createCellStyle();
mainStyle.setWrapText(true);//设置自动换行
mainStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
mainStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
mainStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
mainStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
mainStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
mainStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
mainStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//主要格式字体
HSSFFont mainFont =wb.createFont();
mainFont.setFontName("宋体");
mainFont.setFontHeightInPoints((short) 10);//字体大小
mainStyle.setFont(mainFont);
//首行格式
HSSFCellStyle preHeadStyle = wb.createCellStyle();
preHeadStyle.setWrapText(true);//设置自动换行
preHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
preHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
preHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
preHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
preHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
preHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
preHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//首行字体格式
HSSFFont preHeadFont =wb.createFont();
preHeadFont.setFontName("宋体");
preHeadFont.setFontHeightInPoints((short) 10);//字体大小
preHeadStyle.setFont(preHeadFont);
//首行填充颜色
preHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//填充单元格
preHeadStyle.setFillForegroundColor(HSSFColor.AQUA.index);//设置单元格背景色
//标题格式
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setWrapText(true);//设置自动换行
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//标题字体格式
HSSFFont headFont =wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 11);//字体大小
headStyle.setFont(headFont);
headStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
exportTheDeformationData_Bxg(headStyle, mainStyle, wb, theDeformationDto, "upBxg");
exportTheDeformationData_Jg(headStyle, mainStyle, wb, theDeformationDto, "upJg");
exportTheDeformationData_Bxg(headStyle, mainStyle, wb, theDeformationDto, "afterBxg");
exportTheDeformationData_Jg(headStyle, mainStyle, wb, theDeformationDto, "afterJg");
//导出
// 设置Excel文件名，并以中文进行编码
String codedFileName = new String("D:\变形量统计".getBytes("gbk"), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
// 响应类型,编码
response.setContentType("application/vnd.ms-excel");
OutputStream osOut = response.getOutputStream();
//写入流
wb.write(osOut);
// 刷新此输出流并强制将所有缓冲的输出字节被写出
osOut.flush();
// 关闭流
osOut.close();
}
//前后碰结构件变形量
public void exportTheDeformationData_Jg(HSSFCellStyle headStyle,HSSFCellStyle mainStyle,HSSFWorkbook wb,TheDeformationDto theDeformationDto,String nodecode)throws Exception {
//标题
//合并单元格
//测量项目
CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//测量点
CellRangeAddress region2 = new CellRangeAddress(0, 1, (short) 1, (short) 1); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
List<TaskLowCarBodyDto> upJg = new ArrayList<TaskLowCarBodyDto>();
HSSFSheet partsFeeSheet;
if ("upJg".equals(nodecode)) {
partsFeeSheet = wb.createSheet("前碰结构件变形量");
upJg = theDeformationDto.getUpJg();
} else{
partsFeeSheet = wb.createSheet("后碰结构件变形量");
upJg = theDeformationDto.getAfterJg();
}
HSSFRow partsFeeRow1;
HSSFCell partsFeeCellHeader1;
HSSFRow partsFeeRow;
HSSFCell partsFeeCellHeader;
HSSFCell partsFeeCell;
//首行
partsFeeRow1 = partsFeeSheet.createRow(0);
partsFeeSheet.addMergedRegion(region1);
partsFeeSheet.addMergedRegion(region2);
//首行值
partsFeeCellHeader1 = partsFeeRow1.createCell(0);
partsFeeCellHeader1.setCellValue("测量项目");
partsFeeCellHeader1.setCellStyle(headStyle);
//设置宽度
partsFeeSheet.setColumnWidth(0, "测量项目".getBytes().length * 256);
partsFeeCellHeader1 = partsFeeRow1.createCell(1);
partsFeeCellHeader1.setCellValue("测量点");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth(1, "测量点".getBytes().length * 256);
//第二行
partsFeeRow = partsFeeSheet.createRow(1);
partsFeeCellHeader = partsFeeRow.createCell(0);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(1);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
Map<String,TaskLowCarBodyChlDto> nodeNameAndNodeMap = new HashMap<String, TaskLowCarBodyChlDto>();
if (upJg != null && upJg.size() > 0) {
int j = 2;
for (int i = 0; i < upJg.size(); i++) {
String taskSheetNo = upJg.get(i).getTaskSheetNo();
String vehicleSystemName = upJg.get(i).getVehicleSystemName();
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
partsFeeSheet.addMergedRegion(new CellRangeAddress(0, 0, (short) ((j + i) * 2 - 2), (short) ((j + i) * 2 + 1)));
partsFeeCellHeader1 = partsFeeRow1.createCell((j + i) * 2 - 2);
partsFeeCellHeader1.setCellValue(new HSSFRichTextString(taskSheetNo + "-" + vehicleSystemName + "-" + upJg.get(i).getVehicleTypeName()));
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth((j + i) * 2 - 2, (taskSheetNo + "-" + vehicleSystemName + "-" + upJg.get(i).getVehicleTypeName()).getBytes().length * 256 / 3);
partsFeeCellHeader1 = partsFeeRow1.createCell((j + i) * 2 - 1);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth((j + i) * 2 - 1, (taskSheetNo + "-" + vehicleSystemName + "-" + upJg.get(i).getVehicleTypeName()).getBytes().length * 256 / 3);
partsFeeCellHeader1 = partsFeeRow1.createCell((j + i) * 2);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth((j + i) * 2, (taskSheetNo + "-" + vehicleSystemName + "-" + upJg.get(i).getVehicleTypeName()).getBytes().length * 256 / 3);
partsFeeCellHeader1 = partsFeeRow1.createCell((j + i) * 2 + 1);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(headStyle);
partsFeeSheet.setColumnWidth((j + i) * 2 + 1, (taskSheetNo + "-" + vehicleSystemName + "-" + upJg.get(i).getVehicleTypeName()).getBytes().length * 256 / 3);
partsFeeCellHeader = partsFeeRow.createCell((j + i) * 2 - 2);
partsFeeCellHeader.setCellValue("△X");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell((j + i) * 2 - 1);
partsFeeCellHeader.setCellValue("△Y");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell((j + i) * 2);
partsFeeCellHeader.setCellValue("△Z");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell((j + i) * 2 + 1);
partsFeeCellHeader.setCellValue("空间距离");
partsFeeCellHeader.setCellStyle(headStyle);
j++;
if(upJg.get(i)!=null && upJg.get(i).getDataList()!= null) {
for(TaskLowCarBodyChlDto obj : upJg.get(i).getDataList()) {
nodeNameAndNodeMap.put(upJg.get(i).getTaskNo()+obj.getNodeName()+obj.getNode(), obj);
}
}
}
String[] jgjStr = new String[]{"右纵梁", "左纵梁"};
if (upJg.get(0) != null && upJg.get(0).getDataList() != null && upJg.get(0).getDataList().size() > 0) {
List<TaskLowCarBodyChlDto> dataList = upJg.get(0).getDataList();
int rightRow = dataList.stream().filter(obj -> (jgjStr[0]).equals(obj.getNodeName()))
.collect(Collectors.toList()).size();
int leftRow = dataList.stream().filter(obj -> (jgjStr[1]).equals(obj.getNodeName()))
.collect(Collectors.toList()).size();
int dataIndex = 1;
// 单元格
int dataIndexCell = 1;
// 单元格
boolean tabLeft = true;
boolean tabRight = true;
for (TaskLowCarBodyChlDto obj : dataList) {
dataIndex++;
partsFeeRow = partsFeeSheet.createRow(dataIndex);
// 表格行
// 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
if (jgjStr[0].equals(obj.getNodeName()) && tabLeft) {
tabLeft = false;
partsFeeSheet.addMergedRegion(new CellRangeAddress(2, rightRow + 1, 0, 0));
} else if (jgjStr[1].equals(obj.getNodeName()) && tabRight) {
tabRight = false;
partsFeeSheet.addMergedRegion(new CellRangeAddress(rightRow + 2, rightRow + 1 + leftRow, 0, 0));
}
partsFeeRow.setHeightInPoints((float) 13.5);
partsFeeCell = partsFeeRow.createCell(0);
partsFeeCell.setCellValue(obj.getNodeName());
partsFeeCell.setCellStyle(mainStyle);
// 测量点
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getNode());
partsFeeCell.setCellStyle(mainStyle);
String nodeNameAndNode = obj.getNodeName()+obj.getNode();
for(TaskLowCarBodyDto oBj :upJg) {
String string = oBj.getTaskNo()+nodeNameAndNode;
TaskLowCarBodyChlDto dto = nodeNameAndNodeMap.get(string);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getxDiff()))?dto.getxDiff():"--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getYDiff()))?dto.getYDiff():"--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getZDiff()))?dto.getZDiff():"--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null&& StringUtils.isNotBlank(dto.getSpatialDistance()))?dto.getSpatialDistance():"--");
partsFeeCell.setCellStyle(mainStyle);
}
dataIndexCell = 1;
}
HSSFRow scoreRow = partsFeeSheet.createRow(dataIndex+ 1);
partsFeeCellHeader1 = scoreRow.createCell(0);
partsFeeCellHeader1.setCellValue("安全指数得分");
partsFeeCellHeader1.setCellStyle(mainStyle);
partsFeeSheet.setColumnWidth(0, "安全指数得分".getBytes().length*256);
partsFeeCellHeader1 = scoreRow.createCell(1);
partsFeeCellHeader1.setCellValue("");
partsFeeCellHeader1.setCellStyle(mainStyle);
int index = 2;
for(TaskLowCarBodyDto oBj :upJg) {
partsFeeCellHeader1 = scoreRow.createCell(index++);//创建列
partsFeeCellHeader1.setCellValue("--");
partsFeeCellHeader1.setCellStyle(mainStyle);
partsFeeCellHeader1 = scoreRow.createCell(index++);
partsFeeCellHeader1.setCellValue("--");
partsFeeCellHeader1.setCellStyle(mainStyle);
partsFeeCellHeader1 = scoreRow.createCell(index++);
partsFeeCellHeader1.setCellValue("--");
partsFeeCellHeader1.setCellStyle(mainStyle);
partsFeeCellHeader1 = scoreRow.createCell(index++);
partsFeeCellHeader1.setCellValue(oBj.getSafetyRate());
partsFeeCellHeader1.setCellStyle(mainStyle);
}
}
}
}
//前碰外覆件距离
public void exportTheDeformationData_Bxg(HSSFCellStyle headStyle,HSSFCellStyle mainStyle,HSSFWorkbook wb,TheDeformationDto theDeformationDto,String nodecode){
//合并单元格
//测量项目
CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
//测量点
CellRangeAddress region2 = new CellRangeAddress(0, 1, (short) 1, (short) 1); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
boolean upOrAfterbRet = "upBxg".equals(nodecode);
HSSFSheet sheet ;
if(upOrAfterbRet) {
sheet = wb.createSheet("前碰外覆件距离");
}else{
sheet = wb.createSheet("后碰外覆件距离");
}
HSSFRow partsFeeRowOne;
HSSFCell partsFeeCellHeaderOne;
HSSFRow partsFeeRow;
HSSFCell partsFeeCellHeader;
HSSFCell partsFeeCell;
//首行
partsFeeRowOne = sheet.createRow(0);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
//首行值
partsFeeCellHeaderOne = partsFeeRowOne.createCell(0);
partsFeeCellHeaderOne.setCellValue("测量项目");
partsFeeCellHeaderOne.setCellStyle(headStyle);
sheet.setColumnWidth(0, "测量项目".getBytes().length*256);
partsFeeCellHeaderOne = partsFeeRowOne.createCell(1);
partsFeeCellHeaderOne.setCellValue("测量点");
partsFeeCellHeaderOne.setCellStyle(headStyle);
sheet.setColumnWidth(1, "测量项目".getBytes().length*256);
//第二行
partsFeeRow = sheet.createRow(1);
partsFeeCellHeader = partsFeeRow.createCell(0);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(1);
partsFeeCellHeader.setCellValue("");
partsFeeCellHeader.setCellStyle(headStyle);
List<TaskCarClosuresInfoDataDto> dataList = new ArrayList<TaskCarClosuresInfoDataDto>();
Map<String, TaskCarClosuresInfoDataDto> nodeNameAndNodeMap = new HashMap<String, TaskCarClosuresInfoDataDto>();
List<TaskCarClosuresInfoDto> upBxg = new ArrayList<TaskCarClosuresInfoDto>();
if(theDeformationDto != null && theDeformationDto.getUpBxg()!= null) {
if(upOrAfterbRet) {
upBxg = theDeformationDto.getUpBxg();
}else{
upBxg = theDeformationDto.getAfterBxg();
}
}
int j = 2;
//List<TaskCarClosuresInfoDto> upBxg = theDeformationDto.getUpBxg();
for(int i = 0; i < upBxg.size(); i++) {
String title = upBxg.get(i).getTaskSheetNo() + "-" + upBxg.get(i).getBrandName() + "-"
+ upBxg.get(i).getVehicleSystemName() + "-" + upBxg.get(i).getVehicleTypeName();
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) (j + i * 2), (short) (j + (i + 1) * 2)));
partsFeeCellHeaderOne = partsFeeRowOne.createCell(j + i * 2);
partsFeeCellHeaderOne.setCellValue(new HSSFRichTextString(title));
partsFeeCellHeaderOne.setCellStyle(headStyle);
sheet.setColumnWidth(j + i * 2, title.getBytes().length * 256 / 3);
partsFeeCellHeaderOne = partsFeeRowOne.createCell((j + 1) + i * 2);
partsFeeCellHeaderOne.setCellValue("");
partsFeeCellHeaderOne.setCellStyle(headStyle);
sheet.setColumnWidth(j + i * 2 + 1, title.getBytes().length * 256 / 3);
partsFeeCellHeaderOne = partsFeeRowOne.createCell((j + 2) + i * 2);
partsFeeCellHeaderOne.setCellValue("");
partsFeeCellHeaderOne.setCellStyle(headStyle);
sheet.setColumnWidth(j + i * 2 + 2, title.getBytes().length * 256 / 3);
partsFeeCellHeader = partsFeeRow.createCell(j + i * 2);
partsFeeCellHeader.setCellValue("试验前");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(j + i * 2 + 1);
partsFeeCellHeader.setCellValue("试验后");
partsFeeCellHeader.setCellStyle(headStyle);
partsFeeCellHeader = partsFeeRow.createCell(j + i * 2 + 2);
partsFeeCellHeader.setCellValue("差值");
partsFeeCellHeader.setCellStyle(headStyle);
if(upBxg.get(i)!= null && upBxg.get(i).getDataList()!=null && upBxg.get(i).getDataList().size()>0) {
List<TaskCarClosuresInfoDataDto> list = upBxg.get(i).getDataList();
for(TaskCarClosuresInfoDataDto oBJ :list) {
nodeNameAndNodeMap.put(upBxg.get(i).getTaskNo()+oBJ.getNodeName()+oBJ.getNode(), oBJ);
}
}
j++;
}
if (upBxg != null && upBxg.size()>0 && upBxg.get(0).getDataList() != null && upBxg.get(0).getDataList().size() > 0) {
dataList = upBxg.get(0).getDataList();
}
String enginehatchcover = OpenClosemoduleEnum.ENGINEHATCHCOVER.getName();
String skylight = OpenClosemoduleEnum.SKYLIGHT.getName();
String leftfrontdoor = OpenClosemoduleEnum.LEFTFRONTDOOR.getName();
String rightfrontdoor = OpenClosemoduleEnum.RIGHTFRONTDOOR.getName();
String leftreardoor = OpenClosemoduleEnum.LEFTREARDOOR.getName();
String rightreardoor = OpenClosemoduleEnum.RIGHTREARDOOR.getName();
String trunk = OpenClosemoduleEnum.TRUNK.getName();
int enginehatchcoverSize = dataList.stream().filter(obj->enginehatchcover.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int skylightSize = dataList.stream().filter(obj->skylight.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int leftfrontdoorSize = dataList.stream().filter(obj->leftfrontdoor.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int rightfrontdoorSize = dataList.stream().filter(obj->rightfrontdoor.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int leftreardoorSize = dataList.stream().filter(obj->leftreardoor.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int rightreardoorSize = dataList.stream().filter(obj->rightreardoor.equals(obj.getNodeName())).collect(Collectors.toList()).size();
int trunkSize = dataList.stream().filter(obj->trunk.equals(obj.getNodeName())).collect(Collectors.toList()).size();
//表格行
int dataIndex = 1;
//单元格
int dataIndexCell = 0;
int excelId = 1;
int maxWidth = 0;
boolean enginehatchcoverbRet = true;
boolean skylightbRet = true;
boolean leftfrontdoorbRet = true;
boolean rightfrontdoorbRet = true;
boolean leftreardoorbRet = true;
boolean rightreardoorbRet = true;
boolean trunkbRet = true;
int enginehatchcoverStart =2;
int enginehatchcoverEnd = enginehatchcoverStart+enginehatchcoverSize - 1;
int skylightStart =enginehatchcoverEnd +1;
int skylightEnd =skylightStart+skylightSize-1;
int leftfrontdoorStart =skylightEnd+1;
int leftfrontdoorEnd =leftfrontdoorStart +leftfrontdoorSize-1;
int rightfrontdoorStart =leftfrontdoorEnd+1;
int rightfrontdoorEnd =rightfrontdoorStart+rightfrontdoorSize-1;
int leftreardoorStart =rightfrontdoorEnd+1;
int leftreardoorEnd =leftreardoorStart +leftreardoorSize -1;
int rightrearStart =leftreardoorEnd +1;
int rightrearEnd =rightrearStart + rightreardoorSize -1;
int trunkStart =rightrearEnd +1;
int trunkEnd =trunkStart + trunkSize -1;
//设置单元格的值
for(TaskCarClosuresInfoDataDto obj : dataList) {
dataIndex++;
if(enginehatchcover.equals(obj.getNodeName()) && enginehatchcoverbRet && upOrAfterbRet) {
enginehatchcoverbRet = false;
sheet.addMergedRegion(new CellRangeAddress(2, enginehatchcoverSize + 1, 0, 0));
} else if (skylight.equals(obj.getNodeName()) && skylightbRet ) {
skylightbRet = false;
sheet.addMergedRegion(new CellRangeAddress(enginehatchcoverSize + 2,
enginehatchcoverSize + 2 + skylightSize - 1, 0, 0));// 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
} else if (leftfrontdoor.equals(obj.getNodeName()) && leftfrontdoorbRet) {
leftfrontdoorbRet = false;
sheet.addMergedRegion(new CellRangeAddress(enginehatchcoverSize + 2 + skylightSize,
enginehatchcoverSize + 2 + skylightSize + leftfrontdoorSize - 1, 0, 0));// 参数1：起始行 参数2：终止行
// 参数3：起始列 参数4：终止列
} else if (rightfrontdoor.equals(obj.getNodeName()) && rightfrontdoorbRet) {
rightfrontdoorbRet = false;
sheet.addMergedRegion(new CellRangeAddress(enginehatchcoverSize + 2 + skylightSize + leftfrontdoorSize,
enginehatchcoverSize + 2 + skylightSize + leftfrontdoorSize + rightfrontdoorSize-1, 0, 0));// 参数1：起始行
}else if(leftreardoor.equals(obj.getNodeName()) && leftreardoorbRet && !upOrAfterbRet) {
leftreardoorbRet = false;
sheet.addMergedRegion(new CellRangeAddress(leftreardoorStart,
leftreardoorEnd, 0, 0));// 参数1：起始行
}else if(rightreardoor.equals(obj.getNodeName()) && rightreardoorbRet&& !upOrAfterbRet) {
rightreardoorbRet = false;
sheet.addMergedRegion(new CellRangeAddress(rightrearStart,
rightrearEnd, 0, 0));// 参数1：起始行
}else if(trunk.equals(obj.getNodeName()) && trunkbRet&& !upOrAfterbRet) {
trunkbRet = false;
sheet.addMergedRegion(new CellRangeAddress(trunkStart,
trunkEnd, 0, 0));// 参数1：起始行
}
partsFeeRow = sheet.createRow(dataIndex);
partsFeeRow.setHeightInPoints((float)13.5);
//测量项目
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getNodeName());
partsFeeCell.setCellStyle(mainStyle);
//测量点
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue(obj.getNode());
partsFeeCell.setCellStyle(mainStyle);
String nodeNameAndNode = obj.getNodeName()+obj.getNode();
for(int i = 0; i < upBxg.size(); i++) {
TaskCarClosuresInfoDto DTO = upBxg.get(i);
String string = DTO.getTaskNo()+nodeNameAndNode;
TaskCarClosuresInfoDataDto dto = nodeNameAndNodeMap.get(string);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getBeforeCenterValue()))?dto.getBeforeCenterValue():"--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getAfterCenterValue()))?dto.getAfterCenterValue():"--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(dataIndexCell++);
partsFeeCell.setCellValue((dto!=null && StringUtils.isNotBlank(dto.getCenterDifferenceValue()))?dto.getCenterDifferenceValue():"--");
partsFeeCell.setCellStyle(mainStyle);
}
dataIndexCell = 0;
}
if (upBxg.size()>0) {
partsFeeRow = sheet.createRow(dataIndex + 1);
partsFeeCell = partsFeeRow.createCell(0);
partsFeeCell.setCellValue("安全指数得分");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(1);
partsFeeCell.setCellValue("");
partsFeeCell.setCellStyle(mainStyle);
int index = 2;
for (TaskCarClosuresInfoDto obj : upBxg) {
partsFeeCell = partsFeeRow.createCell(index++);
partsFeeCell.setCellValue("--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(index++);
partsFeeCell.setCellValue("--");
partsFeeCell.setCellStyle(mainStyle);
partsFeeCell = partsFeeRow.createCell(index++);
partsFeeCell.setCellValue(obj.getSafetyRate());
partsFeeCell.setCellStyle(mainStyle);
}
}
}
- 导出效果
