利用POI合并Excel制定列上下行之间指定单元格内容的Util编写

最新推荐文章于 2022-06-20 16:28:32 发布

Lxy_CCr

最新推荐文章于 2022-06-20 16:28:32 发布

阅读量528

点赞数 1

文章标签： excel poi

本文链接：https://blog.csdn.net/Lxy_CCr/article/details/111773429

版权

在利用POI的API对Excel导出进行合并制定的列上下之间相同内容的单元格工具类处理
1 具体业务需求，在项目中，对于导出层级关系的Excel表单数据，进行操作合并，在这里插入图片描述
自己对数据内容进行填写操作后，采用工具类对数据进行合并操作
代码如下
1 设置表格的格式工具类

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

2 下面是对表哥单元合并内容的处理

在这里插入代码片/**
 * @Created by LXY
 * @Data: 2020/9/22 17:15
 */
public class OfficeUtil {
	/**
	 *
	 * @param sheet 作为那个文本
	 * @param fontStyle 样式的选择
	 * @param beginSite 从第几列开始
	 * @param collSite	合并哪一列
	 */
	
	public static void toMergeV3(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<>();


		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) {
				CheckExcelUtils.mergeRegion(begin, end, collSite, collSite, sheet);
				sheet.getRow(begin).getCell(collSite).setCellStyle(fontStyle);
			}
		}

	}

	public static HashMap<String, String> toMergeV4(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(","));
			Integer begin = Integer.valueOf(cutPointNumber.get(0));
			Integer end = Integer.valueOf(cutPointNumber.get(1));
			if (begin!=end) {
				CheckExcelUtils.mergeRegion(begin, end, collSite, collSite, sheet);
				sheet.getRow(begin).getCell(collSite).setCellStyle(fontStyle);
				result.put(entry.getKey(),entry.getValue());
			}
		}
		return result;
	}
	public static HashMap<String, String> toMergeV5(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++){
			Cell cell1 = sheet.getRow(i + beginSite).getCell(collSite);
			cell1.setCellType(Cell.CELL_TYPE_STRING);
			String vaule1 = cell1.getStringCellValue();
			Cell cell2 = sheet.getRow(i + beginSite+1).getCell(collSite);
			cell2.setCellType(Cell.CELL_TYPE_STRING);
			String vaule2 = cell2.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(","));
			Integer begin = Integer.valueOf(cutPointNumber.get(0));
			Integer end = Integer.valueOf(cutPointNumber.get(1));
			if (begin!=end) {
				CheckExcelUtils.mergeRegion(begin, end, collSite, collSite, sheet);
				sheet.getRow(begin).getCell(collSite).setCellStyle(fontStyle);
				result.put(entry.getKey(),entry.getValue());
			}
		}
		return result;
	}

}

程序的调用

比如：从第三行开始（第四行，第一行计算机是0），第一列（第二列，计算机第一列是0）

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

返回的数据map是哪个内容的合并单元格坐标的返回值。

Lxy_CCr

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
利用POI合并Excel制定列上下行之间指定单元格内容的Util编写

在利用POI的API对Excel导出进行合并制定的列上下之间相同内容的单元格工具类处理1 具体业务需求，在项目中，对于导出层级关系的Excel表单数据，进行操作合并，自己对数据内容进行填写操作后，采用工具类对数据进行合并操作代码如下1 设置表格的格式工具类public class ExcelUtils { public static void setCellValue(Row row, Object value, CellStyle cellStyle, int cellIndex){
复制链接

扫一扫