利用对比取值来进行Excel的横向与横向单元格的合并
1 在开发过程中,需要对层级的数据进行合并处理,解决思路,每一行都是单独的一个List 数据进行填充后,进行合并操作
2 效果如图
上面是合并后,下面是合并前,对数据的横向聚合
对数据的纵向聚合;
最终效果
/**
* @Created by LXY
* @Data: 2020/9/22 17:15
*/
public class OfficeUtil {
/**
*
* @param sheet 作为那个文本
* @param fontStyle 样式的选择
* @param beginSite 从第几列开始
* @param collSite 合并哪一列
* OfficeUtil.toMerge(sheet, fontStyle, 3, 2); //第二列
*/
public static HashMap<String, String> toMergeTheStand(Sheet sheet, CellStyle fontStyle, Integer beginSite , Integer collSite) {
List<Integer> numbers = new ArrayList<>();
List<Integer> cutPoint = new ArrayList<>();
cutPoint.add(beginSite);
HashMap<String, String> map = new HashMap<>();
HashMap<String, String> result = new HashMap<>();
int lastCellNum = sheet.getLastRowNum();
for (int i = 0; i<lastCellNum-beginSite; i++){
String vaule1 = sheet.getRow(i+beginSite).getCell(collSite).getStringCellValue();
String vaule2 = sheet.getRow(i+beginSite+1).getCell(collSite).getStringCellValue();
//进行两两判断,是的话就为一组数据
if (vaule1.equals(vaule2)){
numbers.add(i+beginSite);
numbers.add(i+beginSite+1);
}else {
//找出他们的不相等的点 进行记录 进行切割
cutPoint.add(i+beginSite+1);
}
}
//末尾
cutPoint.add(lastCellNum+1);
for (int i = 0; i < cutPoint.size() - 1; i++) {
//得到填充的字段名
String value = sheet.getRow(cutPoint.get(i)).getCell(collSite).getStringCellValue();
Integer vo1 = cutPoint.get(i);
Integer vo2 = cutPoint.get(i + 1) - 1;
StringBuffer cut1 = new StringBuffer();
cut1.append(value).append("%@").append(vo1);
StringBuffer cut2 = new StringBuffer();
cut2.append(vo1).append(",").append(vo2);
map.put(cut1.toString(), cut2.toString());
}
for (Map.Entry<String, String> entry : map.entrySet()) {
List<String> values = Arrays.asList(entry.getKey().split("%@"));
String value = values.get(0);
List<String> cutPointNumber = Arrays.asList(entry.getValue().split(","));
int begin = Integer.valueOf(cutPointNumber.get(0));
int end = Integer.valueOf(cutPointNumber.get(1));
if (begin!=end) {
ExcelUtils.mergeRegion(begin, end, collSite, collSite, sheet);
sheet.getRow(begin).getCell(collSite).setCellStyle(fontStyle);
result.put(entry.getKey(),entry.getValue());
}
}
return result;
}
/**
*
* @param sheet 对哪个sheet进行处理
* @param acrossNumber 从第几行开始
*/
public static void toMergeTheAcross(Sheet sheet,Integer acrossNumber) {
HashMap<Integer, String> cutPointMap = new HashMap<>();
List<Integer> cutPoint = new ArrayList<>();
cutPoint.add(0);
Row row = sheet.getRow(acrossNumber);
int lastCellNum = sheet.getRow(acrossNumber).getLastCellNum();
String value1 = "";
String value2 = "";
for (int i = 0; i<lastCellNum-1; i++){
if (row.getCell(i) !=null ){
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
value1 = row.getCell(i).getStringCellValue();
}
if (row.getCell(i+1) !=null ){
row.getCell(i+1).setCellType(Cell.CELL_TYPE_STRING);
value2 = row.getCell(i+1).getStringCellValue();
}
//进行两两判断,是的话就为一组数据
if (!value1.equals(value2)){
//获得下一个末尾和这个不想当
cutPoint.add(i+1);
}
}
//末尾
cutPoint.add(lastCellNum);
for (int i = 0; i < cutPoint.size() -1; i++) {
if (cutPoint.get(i) + 1 != cutPoint.get(i+1)){
StringBuffer str = new StringBuffer();
str.append(cutPoint.get(i)).append(",").append(cutPoint.get(i+1)-1);
cutPointMap.put(cutPoint.get(i),str.toString());
}
}
for (Map.Entry<Integer, String> entry : cutPointMap.entrySet()) {
List<String> cutPointNumber = Arrays.asList(entry.getValue().split(","));
int begin = Integer.valueOf(cutPointNumber.get(0));
int end = Integer.valueOf(cutPointNumber.get(1));
try {
OfficeUtil.mergeRegion(acrossNumber,acrossNumber,begin,end,sheet);
}catch (Exception e){
System.err.println(e.getMessage());
}
}
}
public static void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet) {
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(region);
setBorderStyle(BorderStyle.THIN,region,sheet);
}
public static void setBorderStyle(BorderStyle border, CellRangeAddress region, Sheet sheet){
RegionUtil.setBorderBottom(border, region, sheet); //下边框
RegionUtil.setBorderLeft(border, region, sheet); //左边框
RegionUtil.setBorderRight(border, region, sheet); //右边框
RegionUtil.setBorderTop(border, region, sheet); //上边框
}
/**
* 创建单元格居中对齐样式
* @param wb
* @return
*/
public static CellStyle createStyleForCell(Workbook wb) {
//设置字体
Font headFont = wb.createFont();
headFont.setFontName("黑体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true);
//设置sheet样式
CellStyle style = wb.createCellStyle();
//加边框
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
style.setWrapText(true);//设置自动换行
style.setAlignment(HorizontalAlignment.CENTER);//左右居中
//style.setFont(headFont);
return style;
}
}