工具类:
一行多列合并工具类:
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.AllArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
* Author: xy
* Date: 2022/3/1 11:04
* FileName: ExcelFilesRowsMergeUtil
* Description: 一行多列合并
*/
@AllArgsConstructor
public class ExcelFilesRowsMergeUtil implements CellWriteHandler {
// 第几行开始合并,默认为1,因为第0行为标题
private int mergeRowIndex;
// 需要合并的列
private int colIndex;
@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) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
// mergeRowIndex = 1
if (curRowIndex >=mergeRowIndex) {
// 前五列需要合并 所以需要读取到第五列
if (curColIndex == colIndex) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
}
}
}
/**
* @description TODO
* @title mergeWithPrevRow
* @param writeSheetHolder
* @param cell
* @param curRowIndex 当前行
* @param curColIndex 当前列
* @return void
* @throws
* @author xiecy
* @date 2022/3/1 17:49
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
// startCol 行,startRow列
int startCol=curRowIndex ,endCol = curRowIndex,startRow = 0,endRow = 0;
boolean isStart = true;
boolean isEnd = true;
// 比较当前行的单元格与上一行是否相同,相同合并当前单元格与上一行
for (int i = 1; i<=curColIndex; i++){
if (cell.getSheet().getRow(curRowIndex).getCell(i).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex).getCell(i-1).getStringCellValue())&&isStart){
startRow = i-1;
isStart = false;
continue;
}
if ((!cell.getSheet().getRow(curRowIndex).getCell(i).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex).getCell(i-1).getStringCellValue()))&&isEnd&&!isStart){
endRow = i-1;
isEnd = false;
}
}
Sheet sheet = writeSheetHolder.getSheet();
//参数:起始行号,终止行号, 起始列号,终止列号
if (!isStart){
CellRangeAddress region = new CellRangeAddress(startCol, endCol, startRow, endRow);
sheet.addMergedRegionUnsafe(region);
}
}
}
一列多行合并工具类:
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.RequiredArgsConstructor;
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;
/**
* Author: xy
* Date: 2022/2/28 10:39
* FileName: ExcelFileCellsMergeUtil
* Description: 一列多行合并
*/
@RequiredArgsConstructor
public class ExcelFileCellsMergeUtil implements CellWriteHandler {
private final int[] mergeColumnIndex;
private final int mergeRowIndex;
private final int standardColumnIndex;
@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) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
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;
}
}
/* // 前五列需要合并 所以需要读取到第五列
if (curColIndex == colIndex) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex,curColIndex);
}*/
}
}
/**
* @param writeSheetHolder 合并每行相同单元格数据
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
* @return void
* @throws
* @description TODO
* @title mergeWithPrevRow
* @author xiecy
* @date 2022/3/1 9:44
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 获取上一行第0列单元格和本行第0列单元格
Cell preRowFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(standardColumnIndex);
Object preRowFirstData = preRowFirstCell.getCellTypeEnum() == CellType.STRING ? preRowFirstCell.getStringCellValue() : preRowFirstCell.getNumericCellValue();
Cell curRowFirstCell = cell.getSheet().getRow(curRowIndex).getCell(standardColumnIndex);
Object curRowFirstData = curRowFirstCell.getCellTypeEnum() == CellType.STRING ? curRowFirstCell.getStringCellValue() : curRowFirstCell.getNumericCellValue();
// 当前列不为标杆列,根据标杆列上下两个单元格值是否一致来合并
// 当前列为标杆列并且上下两个单元格一致则合并
if ((curColIndex == standardColumnIndex && preData.equals(curData)) || (curColIndex != standardColumnIndex && preRowFirstData.equals(curRowFirstData) && preData.equals(curData))) {
Sheet sheet = writeSheetHolder.getSheet();
List mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = (CellRangeAddress) mergeRegions.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);
}
}
}
}
拦截器,水印:
/**
* Author: xy
* Date: 2022/2/15 14:35
* FileName: MonthSheetWriteHandler
* Description: 拦截器*/
@RequiredArgsConstructor
public class SheetWriteFilter implements SheetWriteHandler {
private final String WATER_MARK;
private final int[] FROZEN;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@SneakyThrows
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
ByteArrayOutputStream waterMark = WaterMarkUtil.createWaterMark(WATER_MARK);
// XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();
// sheet.createFreezePane(0,2); 冻结表头
sheet.createFreezePane(FROZEN[0],FROZEN[1] );
// sheet.setAutoFilter(CellRangeAddress.valueOf("1:1"));
WaterMarkUtil.putWaterRemarkToExcel( (XSSFSheet)sheet, waterMark.toByteArray());
}
}
水印工具类:
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.awt.Font;
import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
/**
* Author: xy
* Date: 2022/2/15 16:49
* FileName: WaterMarkUtil
* Description: 水印*/
public class WaterMarkUtil {
//设置水印样式
public static ByteArrayOutputStream createWaterMark(String content) throws IOException {
int width = 700;
int height = 500;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
String fontType = "微软雅黑";
int fontStyle = Font.BOLD;
int fontSize = 30;
Font font = new Font(fontType, fontStyle, fontSize);
Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
g2d.setColor(new Color(0, 0, 0, 20)); //设置字体颜色和透明度,最后一个参数为透明度
g2d.setStroke(new BasicStroke(1)); // 设置字体
g2d.setFont(font); // 设置字体类型 加粗 大小
g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(content, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小,所以累计写水印,增加高度
g2d.drawString(content, (int) x, (int) baseY);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write(image, "png", os);
return os;
}
/**
* 为Excel打上水印工具函数
* @param sheet excel sheet
* @param bytes 水印图片字节数组*/
public static void putWaterRemarkToExcel(XSSFSheet sheet, byte[] bytes) {
//add relation from sheet to the picture data
XSSFWorkbook workbook = sheet.getWorkbook();
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
String rID = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx))
.getRelationship().getId();
//set background picture to sheet
sheet.getCTWorksheet().addNewPicture().setId(rID);
}
public static Map setExcelStyle(HttpServletResponse response, String fileName) {
Map map = new HashMap();
try {
response.setContentType("application/json;charset=UTF-8");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition", "attachment;filename= " + fileName + ".xlsx");
// 内容样式
WriteCellStyle cellStyle = new WriteCellStyle();
// 垂直居中,水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
// 设置自动换行
cellStyle.setWrapped(true);
// 字体
WriteFont writeFont = new WriteFont();
// 字体大小
writeFont.setFontHeightInPoints((short)13);
cellStyle.setWriteFont(writeFont);
// 头策略样式设置
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
map.put("cellStyle",cellStyle);
map.put("headWriteCellStyle",headWriteCellStyle);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return map;
}
}
导出实体类:
/**
* 能力认证实体类/能力认证页面导出实体类
*/
@Data
@NoArgsConstructor
@Accessors(chain = true)
@HeadStyle(fillForegroundColor = 9) // 设置背景色
@ContentRowHeight(20) // 设置Cell 高度45, 不包含表头高度
@HeadRowHeight(30) // 设置表头高度
public class AbilityCertificationEntity {
/**
* 专业线
*/
@ExcelProperty(value = {"专业线"})
@ColumnWidth(10)
private String professionalLine;
/**
* 数字化方向
*/
@ExcelProperty(value = {"数字化方向"}) // 标题名称
@ColumnWidth(10)
private String digitizationDirection;
/**
* 数字化岗位族
*/
@ExcelProperty(value = {"数字化岗位族"})
@ColumnWidth(10)
private String digitizationPost;
/**
* 统计口径
*/
@ExcelProperty(value = {"统计口径"})
@ColumnWidth(10)
private String caliber;
/**
* 全省
*/
@ExcelProperty(value = {"全省"})
@ColumnWidth(10)
private String qs;
/**
* 省本部
*/
@ExcelProperty(value = {"省本部"})
@ColumnWidth(10)
private String sbb;
/**
* 杭州
*/
@ExcelProperty(value = {"杭州"})
@ColumnWidth(10)
private String hz;
/**
* 宁波
*/
@ExcelProperty(value = {"宁波"})
@ColumnWidth(10)
private String nb;
/**
* 温州
*/
@ExcelProperty(value = {"温州"})
@ColumnWidth(10)
private String wz;
/**
* 台州
*/
@ExcelProperty(value = {"台州"})
@ColumnWidth(10)
private String tz;
/**
* 金华
*/
@ExcelProperty(value = {"金华"})
@ColumnWidth(10)
private String jh;
/**
* 嘉兴
*/
@ExcelProperty(value = {"嘉兴"})
@ColumnWidth(10)
private String jx;
/**
* 绍兴
*/
@ExcelProperty(value = {"绍兴"})
@ColumnWidth(10)
private String sx;
/**
* 湖州
*/
@ExcelProperty(value = {"湖州"})
@ColumnWidth(10)
private String huz;
/**
* 丽水
*/
@ExcelProperty(value = {"丽水"})
@ColumnWidth(10)
private String ls;
/**
* 衢州
*/
@ExcelProperty(value = {"衢州"})
@ColumnWidth(10)
private String qz;
/**
* 舟山
*/
@ExcelProperty(value = {"舟山"})
@ColumnWidth(10)
private String zs;
/**
* 产互
*/
@ExcelProperty(value = {"产互"})
@ColumnWidth(10)
private String ch;
}
controller:
@AutoLog("用工-用工分布")
@ApiOperation(value = "用工分布-页面导出")
@GetMapping(value = "getAbilityCertificationListPageDown" ,produces ={ "application/json;charset=UTF-8"})
public void getAbilityCertificationListPageDown(HttpServletResponse response, String lineId,String monthId, String userId) {
abilityCertificationService.getAbilityCertificationListPageDown(response, lineId, monthId, userId);
}
service:
/**
* 用工分布-页面导出
*
* @param response response
* @param lineId lineId
* @param userId userId
*/
void getAbilityCertificationListPageDown(HttpServletResponse response, String lineId,String monthId, String userId);
serviceImpl:
/**
* 能力认证表数据导出
*
* @param response HttpServletResponse
* @param lineId lineId
* @param userId userId
*/
@Override
public void getAbilityCertificationListPageDown(HttpServletResponse response, String lineId, String monthId, String userId) {
if ("e9ca23d68d884d4ebb19d07889727dae".equals(userId)) {
userId = "admin";
}
// 默认为空获取该用户所拥有的专业线
if (StringUtils.isBlank(lineId)) {
lineId = systemService.getLineTypetoString();
}
// 获取能力认证页面信息
List<AbilityCertificationEntity> infoList = abilityCertificationMapper.getAbilityCertificationList(lineId, monthId);
this.returnList(infoList);
int[] index = {0, 1};
// 设置0, 1, 4为需要合并的列
int[] mergeColumnIndex = {0, 1, 2};
// 从第一行开始合并
int mergeRowIndex = 1;
// 设置第0列为标杆列
int standardColumnIndex = 0;
try {
response.setContentType("application/octet-stream; charset=utf-8");
// response.setHeader("Content-disposition","attachment;filename=AAAA.xlsx" );
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("数字化能力认证图谱.xlsx", "UTF-8"));
EasyExcel.write(response.getOutputStream(), AbilityCertificationEntity.class)
.inMemory(true)
// 横向合并
.registerWriteHandler(new ExcelFilesRowsMergeUtil(1, 4))
// 纵向合并
.registerWriteHandler(new ExcelFileCellsMergeUtil(mergeColumnIndex, mergeRowIndex, standardColumnIndex))
.registerWriteHandler(new SheetWriteFilter(userId + "-" + new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()), index))
.sheet("清单")
// 设置默认样式及写入头信息开始的行数
.useDefaultStyle(false).relativeHeadRowIndex(0)
.doWrite(infoList);
} catch (IOException e) {
e.printStackTrace();
}
}
public List returnList(List<AbilityCertificationEntity> list){
int size = list.size();
AbilityCertificationEntity abilityCertificationEntity = new AbilityCertificationEntity();
abilityCertificationEntity.setProfessionalLine("合计");
abilityCertificationEntity.setDigitizationDirection("认证率");
abilityCertificationEntity.setDigitizationPost("认证率");
abilityCertificationEntity.setCaliber("认证率");
abilityCertificationEntity.setQs( (list.get(size-2).getQs().equals("--") || list.get(size-1).getQs() .equals("--"))? "--" : (list.get(size-2).getQs().equals("0") || list.get(size-1).getQs() .equals("0")) ? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getQs())/Float.parseFloat(list.get(size-2).getQs())*100 )+"%"));
abilityCertificationEntity.setSbb( (list.get(size-2).getSbb().equals("--") || list.get(size-1).getSbb() .equals("--"))? "--" : (list.get(size-2).getSbb().equals("0") || list.get(size-1).getSbb() .equals("%")) ? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getSbb())/Float.parseFloat(list.get(size-2).getSbb())*100 )+"%"));
abilityCertificationEntity.setCh( (list.get(size-2).getCh().equals("--") || list.get(size-1).getCh() .equals("--"))? "--" : (list.get(size-2).getCh().equals("0") || list.get(size-1).getCh() .equals("0")) ? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getCh())/Float.parseFloat(list.get(size-2).getCh()) *100 )+"%"));
abilityCertificationEntity.setHz( (list.get(size-2).getHz().equals("--") || list.get(size-1).getHz() .equals("--"))? "--" : (list.get(size-2).getHz().equals("0") || list.get(size-1).getHz() .equals("0")) ? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getHz())/Float.parseFloat(list.get(size-2).getHz())*100 )+"%"));
abilityCertificationEntity.setNb( (list.get(size-2).getNb().equals("--") || list.get(size-1).getNb() .equals("--"))? "--" : (list.get(size-2).getNb().equals("0") || list.get(size-1).getNb() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getNb())/Float.parseFloat(list.get(size-2).getNb()) *100 )+"%"));
abilityCertificationEntity.setWz( (list.get(size-2).getWz().equals("--") || list.get(size-1).getWz() .equals("--"))? "--" : (list.get(size-2).getWz().equals("0") || list.get(size-1).getWz() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getWz())/Float.parseFloat(list.get(size-2).getWz())*100 )+"%"));
abilityCertificationEntity.setTz( (list.get(size-2).getTz().equals("--") || list.get(size-1).getTz() .equals("--"))? "--" : (list.get(size-2).getTz().equals("0") || list.get(size-1).getTz() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getTz())/Float.parseFloat(list.get(size-2).getTz()) *100)+"%"));
abilityCertificationEntity.setJh( (list.get(size-2).getJh().equals("--") || list.get(size-1).getJh() .equals("--"))? "--" : (list.get(size-2).getJh().equals("0") || list.get(size-1).getJh() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getJh())/Float.parseFloat(list.get(size-2).getJh()) *100)+"%"));
abilityCertificationEntity.setJx( (list.get(size-2).getJx().equals("--") || list.get(size-1).getJx() .equals("--"))? "--" : (list.get(size-2).getJx().equals("0") || list.get(size-1).getJx() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getJx())/Float.parseFloat(list.get(size-2).getJx()) *100)+"%"));
abilityCertificationEntity.setSx( (list.get(size-2).getSx().equals("--") || list.get(size-1).getSx() .equals("--"))? "--" : (list.get(size-2).getSx().equals("0") || list.get(size-1).getSx() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getSx())/Float.parseFloat(list.get(size-2).getSx()) *100)+"%"));
abilityCertificationEntity.setHuz( (list.get(size-2).getHuz().equals("--") || list.get(size-1).getHuz() .equals("--"))? "--" : (list.get(size-2).getHuz().equals("0") || list.get(size-1).getHuz() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getHuz())/Float.parseFloat(list.get(size-2).getHuz()) *100)+"%"));
abilityCertificationEntity.setLs( (list.get(size-2).getLs().equals("--") || list.get(size-1).getLs() .equals("--"))? "--" : (list.get(size-2).getLs().equals("0") || list.get(size-1).getLs() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getLs())/Float.parseFloat(list.get(size-2).getLs()) *100)+"%"));
abilityCertificationEntity.setQz( (list.get(size-2).getQz().equals("--") || list.get(size-1).getQz() .equals("--"))? "--" : (list.get(size-2).getQz().equals("0") || list.get(size-1).getQz() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getQz())/Float.parseFloat(list.get(size-2).getQz()) *100)+"%"));
abilityCertificationEntity.setZs( (list.get(size-2).getZs().equals("--") || list.get(size-1).getZs() .equals("--"))? "--" : (list.get(size-2).getZs().equals("0") || list.get(size-1).getZs() .equals("0"))? "0" : (String.format("%.2f", Float.parseFloat(list.get(size-1).getZs())/Float.parseFloat(list.get(size-2).getZs()) *100)+"%"));
list.add(abilityCertificationEntity);
return list;
}