Excel相同列数据合并工具类

1 在业务中,常常对Excel导出进行操作，但是对于数据的聚合在每一个不同的数据编写逻辑，我发现大多数对Excel的数据合并工具类做不到灵活配置合并制定的哪列相同的数据，所以我自己编写的对Excel相同列的数据进行合并的工具类，
比如在这个我的和这个例子中，对数据的聚合成的Sheet对象并不是一个在数据库查出来的平常List集合，而是对三个数据来源的聚合，我只能在生成Excel表单后对sheet文件进行兑奖的数据操作，对比，思维参考冒泡排序，

最后进行数据导出的结果

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.*;

/**
 * @Created by LXY
 * @Data: 2020/9/22 17:15
 */
public class OfficeUtil {
	/**
	 *
	 * @param sheet 作为那个文本
	 * @param fontStyle 样式的选择
	 * @param beginSite 从第几列开始
	 * @param collSite	合并哪一列
	 */
	public static void toMerge(Sheet sheet, CellStyle fontStyle,Integer beginSite , Integer collSite) {
		HashMap<String, String> map = new HashMap<>();
		HashMap<String, String> map2 = new HashMap<>();
		HashMap<String, String> result = new HashMap<>();

		int lastRowNum = sheet.getLastRowNum();
		for (int i = 0; i<lastRowNum-beginSite; i++){
			StringBuffer stringBuffer = new StringBuffer();
			String vaule1 = sheet.getRow(i+beginSite).getCell(beginSite ).getStringCellValue();
			String vaule2 = sheet.getRow(i+beginSite+1).getCell(beginSite ).getStringCellValue();
			//进行两两判断,是的话就为一组数据
			if (vaule1.equals(vaule2)){
				String vaule3 = vaule2+","+String.valueOf(i);
				stringBuffer.append(i+beginSite).append(",").append(i+beginSite+1);
				map.put(vaule3,stringBuffer.toString());
				map2.put(vaule3,stringBuffer.toString());
			}
		}

		for (Map.Entry<String, String> stringStringEntry : map.entrySet()) {
			String key1 = stringStringEntry.getKey();
			List<String> keys1 = Arrays.asList(key1.split(","));
			List<String> Rows1 = Arrays.asList(stringStringEntry.getValue().split(","));

			for (Map.Entry<String, String> stringEntry : map2.entrySet()) {
				ArrayList<Integer> objects = new ArrayList<>();
				String key2 = stringEntry.getKey();
				List<String> keys2 = Arrays.asList(key2.split(","));
				List<String> Rows2 = Arrays.asList(stringEntry.getValue().split(","));

				objects.add(Integer.valueOf(Rows1.get(0)));
				objects.add(Integer.valueOf(Rows1.get(1)));
				objects.add(Integer.valueOf(Rows2.get(0)));
				objects.add(Integer.valueOf(Rows2.get(1)));
				Collections.sort(objects, new Comparator<Integer>() {
					@Override
					public int compare(Integer o1, Integer o2) {
						return o1-o2;
					}
				});
				if (keys1.get(0).equals(keys2.get(0)) && objects.get(0)==objects.get(1)){
					//查看是否存有
					String value = result.get(keys1.get(0));
					if (value!=null){
						ArrayList<Integer> objects2 = new ArrayList<>();

						List<String> rows = Arrays.asList(value.split(","));
						objects2.add(Integer.valueOf(rows.get(0)));
						objects2.add(Integer.valueOf(rows.get(1)));
						objects2.add(objects.get(0));
						objects2.add(objects.get(3));
						Collections.sort(objects2, new Comparator<Integer>() {
							@Override
							public int compare(Integer o1, Integer o2) {
								return o1-o2;
							}
						});
						StringBuffer stringBuffer = new StringBuffer();
						stringBuffer.append(objects2.get(0)).append(",").append(objects2.get(3));
						result.put(keys1.get(0),stringBuffer.toString());

					}else {

						StringBuffer stringBuffer = new StringBuffer();
						stringBuffer.append(objects.get(0)).append(",").append(objects.get(3));
						result.put(keys1.get(0),stringBuffer.toString());
					}
				}
			}

		}


		Iterator<Map.Entry<String, String>> iterator3 = result.entrySet().iterator();
		while (iterator3.hasNext()){
			Map.Entry<String, String> next = iterator3.next();
			List<String> rows = Arrays.asList(next.getValue().split(","));
			Integer value1 = Integer.valueOf(rows.get(0));
			Integer value2 = Integer.valueOf(rows.get(1));
			CheckExcelUtils.mergeRegion(value1,value2,collSite,collSite,sheet);
			sheet.getRow(value1).getCell(collSite).setCellStyle(fontStyle);

		}
	}
}

对应的设置Excel 的表头，文字工具类也给你们

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.util.Date;

public class ExcelUtils {

    public static void setCellValue(Row row, Object value, CellStyle cellStyle, int cellIndex){
        Cell cell = row.createCell(cellIndex);

        if(value instanceof HSSFRichTextString){
            cell.setCellValue((HSSFRichTextString)value);
        }else if(value instanceof String){
            cell.setCellValue((String)value);
        }else if(value instanceof Boolean){
            cell.setCellValue((Boolean)value);
        }else if(value instanceof Double){
            cell.setCellValue((Double)value);
        }else if(value instanceof Date){
            cell.setCellValue((Date)value);
        }
        cell.setCellStyle(cellStyle);
    }

    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;
    }
}

OfficeUtil.toMerge(sheet,fontStyle,3,1);  //第一列

比如我这个代码，需要在第三行开始进行第二列的数据进行合并，传入数据意义：
第一个是需要操作的Excel表单
第二个是样式
第三个是第几行开始比，如我在这里需要对第三列的数据开始进行聚合，调过前三列，输入3，对比第三列上下两个值后进行合并
第四个是对接列进行操作，我这是是对第2行，就输入1

此项工具类仅对列数据集进行处理，有什么可以联系我企鹅 954248544 嘻嘻