@GetMapping("getStyleSummaryExport")
@ApiOperation("款式汇总报表--导出")
@ApiImplicitParams({
@ApiImplicitParam(name = Constant.PAGE, value = "当前页码，从1开始", paramType = "query", dataType = "int"),
@ApiImplicitParam(name = Constant.LIMIT, value = "每页显示记录数", paramType = "query", dataType = "int"),
@ApiImplicitParam(name = Constant.ORDER_FIELD, value = "排序字段", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = Constant.ORDER, value = "排序方式，可选值(asc、desc)", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "season", value = "季节", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "batch", value = "批次", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "series", value = "系列", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "styleNo", value = "款号", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "brand", value = "品牌", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "stage", value = "阶段", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "materialNo", value = "主身面料编号", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "supplier", value = "供应商", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "shipmentDate", value = "上货日期", paramType = "query", dataType = "Date"),
@ApiImplicitParam(name = "ids", value = "勾选列表id,多个逗号隔开", paramType = "query", dataType = "String")
})
public void getStyleSummaryExport(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
if(!ParamUtil.paramsIsNotNull(params,"limit") || !ParamUtil.paramsIsNotNull(params,"page")){
params.put("limit", "1000");
params.put("page", "1");
}
if(ParamUtil.paramsIsNotNull(params, "ids")){
params.put("ids", Arrays.asList(params.get("ids").toString().split(",")));
}else {
params.remove("ids");
}
List<StyleSummaryDTO> list = styleSummaryService.getStyleSummaryReportPage(params).getList();
List<List<Object>> resList = new ArrayList<>();
//合并图片问题
List<Integer> imgCount = new ArrayList<>();
//合并的行
List<Integer> merge = new ArrayList<>();
AtomicInteger index = new AtomicInteger();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
if(CollectionUtils.isNotEmpty(list)) {
list.forEach(e ->{
if(CollectionUtils.isNotEmpty(e.getItemList())){
for (int i = 0; i < e.getItemList().size(); i++) {
index.addAndGet(1);
if(i > 0){
merge.add(index.intValue());
}
List<Object> itemList = new ArrayList<>();
if (i == 0 && CollectionUtils.isNotEmpty(e.getStyleUrlList())){
try {
itemList.add(new URL(e.getStyleUrlList().get(0).getUrl()));
imgCount.add(e.getItemList().size());
} catch (MalformedURLException malformedURLException) {
log.info(malformedURLException.getMessage());
}
}else {
itemList.add("");
}
itemList.add(e.getBrand());
itemList.add(e.getSeason());
itemList.add(e.getStage());
itemList.add(e.getYbType());
itemList.add(e.getBatch());
itemList.add(e.getSeries());
itemList.add(e.getStyleNo());
if(e.getShipmentDate() != null){
itemList.add(simpleDateFormat.format(e.getShipmentDate()));
}else {
itemList.add("");
}
itemList.add(e.getItemList().get(i).getMaterialNo());
itemList.add(e.getItemList().get(i).getSupplier());
resList.add(itemList);
}
}else {
index.addAndGet(1);
List<Object> itemList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(e.getStyleUrlList())){
try {
itemList.add(new URL(e.getStyleUrlList().get(0).getUrl()));
imgCount.add(1);
} catch (MalformedURLException malformedURLException) {
log.info(malformedURLException.getMessage());
}
}else {
itemList.add("");
}
itemList.add(e.getBrand());
itemList.add(e.getSeason());
itemList.add(e.getStage());
itemList.add(e.getYbType());
itemList.add(e.getBatch());
itemList.add(e.getSeries());
itemList.add(e.getStyleNo());
if(e.getShipmentDate() != null){
itemList.add(simpleDateFormat.format(e.getShipmentDate()));
}else {
itemList.add("");
}
resList.add(itemList);
}
});
}
response.setContentType("application/json;charset=utf-8");
response.setCharacterEncoding("UTF-8");
String fileName = URLEncoder.encode("开发款式汇总导出-"+ DateUtils.format(new Date()), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = null;
try{
excelWriter = EasyExcel.write(response.getOutputStream()).build();
//excelWriter = EasyExcel.write("D:\\2023\\test222111.xlsx").build();
if(null != excelWriter) {
//需要合并的列
int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8};
// 从那一列开始合并
int mergeRowIndex = 1;
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
List<List<String>> userHeadList = getHeader();
WriteSheet writeSheet = EasyExcel.writerSheet( "开发款式汇总").head(userHeadList)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new CustomCellWeightWeightForDevStyleUtil())
.registerWriteHandler(new MergeSameRowsStrategy(mergeRowIndex, mergeColumeIndex, merge))
.registerWriteHandler(new StyleImageCellWriteHandler(imgCount))
.build();
excelWriter.write(resList, writeSheet);
}
}finally {
//关闭流
if(excelWriter != null){
excelWriter.finish();
}
}
}
package com.jack.modules.dsr.common.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
@Data
public class MergeSameRowsStrategy implements CellWriteHandler{
private int[] mergeColumnIndex;
private int mergeRowIndex;
private List<Integer> merge;
public MergeSameRowsStrategy() {
}
public MergeSameRowsStrategy(int mergeRowIndex, int[] mergeColumnIndex, List<Integer> merge) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.merge = merge;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
if (CollectionUtils.isNotEmpty(merge)){
// 当前行
int curRowIndex = cell.getRowIndex();
if (merge.contains(curRowIndex)){
// 当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder sheet保持对象
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
// 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
if (cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex) == null){
return;
}
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData) || ("0.0".equals(preData.toString()) && "".equals(curData))) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergedRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
}
package com.jack.modules.dsr.common.handler;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.util.ArrayList;
import java.util.List;
/**
* EasyExcel图片填充拦截器
*
* @author piyc
*/
@Data
public class StyleImageCellWriteHandler implements CellWriteHandler {
private List<Integer> imgCount;
public StyleImageCellWriteHandler() {
}
public StyleImageCellWriteHandler(List<Integer> imgCount) {
this.imgCount = imgCount;
}
int zab = 0;
List<byte[]> imageList = new ArrayList<>();
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
// 在 数据转换成功后 ，修改第一列 当然这里也可以根据其他判断 然后不是头 就把类型设置成空 这样easyexcel 不会去处理该单元格
if (head.getColumnIndex() != 0 || aBoolean) {
return;
}
if (cellData.getImageValue() != null && cellData.getImageValue().length > 0){
cellData.setType(CellDataTypeEnum.EMPTY);
}
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
// 在 单元格写入完毕后 ，自己填充图片
if (head.getColumnIndex() != 0 || aBoolean || CollectionUtils.isEmpty(list) || list.get(0) == null) {
return;
}
if (list.get(0).getStringValue() != null && StringUtils.isNotEmpty(list.get(0).getStringValue())){
list.get(0).setType(CellDataTypeEnum.STRING);
return;
}
Sheet sheet = cell.getSheet();
// cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
if (list.get(0).getImageValue() != null && list.get(0).getImageValue().length > 0){
int index = sheet.getWorkbook().addPicture(list.get(0).getImageValue(), HSSFWorkbook.PICTURE_TYPE_PNG);
Drawing drawing = sheet.getDrawingPatriarch();
if (drawing == null) {
drawing = sheet.createDrawingPatriarch();
}
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
// 设置图片坐标
anchor.setDx1(0);
anchor.setDx2(0);
anchor.setDy1(0);
anchor.setDy2(0);
//设置图片位置
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() +1);
anchor.setRow1(cell.getRowIndex());
if (head.getColumnIndex() == 0 && CollectionUtils.isNotEmpty(imgCount)){
anchor.setRow2(cell.getRowIndex() + imgCount.get(zab));
zab++;
} else {
anchor.setRow2(cell.getRowIndex() + 1);
}
// 设置图片可以随着单元格移动
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
drawing.createPicture(anchor, index);
}
}
}
package com.jack.modules.dsr.common.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.nacos.common.utils.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
public class CustomCellWeightWeightForDevStyleUtil extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Sheet sheet = writeSheetHolder.getSheet();
int columnIndex = cell.getColumnIndex();
if (columnIndex == 0 || columnIndex == 6 || columnIndex == 7 || columnIndex == 8 || columnIndex == 9 || columnIndex == 10){
sheet.setColumnWidth(columnIndex,4576);
}
}
}
}