导出excel合并单元格

最新推荐文章于 2024-09-23 11:18:33 发布

玉离骚

最新推荐文章于 2024-09-23 11:18:33 发布

阅读量1.4k

点赞数 1

分类专栏： java 工具类

本文链接：https://blog.csdn.net/qq_29539827/article/details/110240226

版权

java 同时被 2 个专栏收录

53 篇文章 2 订阅

订阅专栏

工具类

26 篇文章 0 订阅

订阅专栏

以前做了很多导出excel 的功能，最近做了一个业务报表导出，要求合并单元格。今天花了一点时间整理成工具类，并测试后分享一下。

测试主类：
主要通过mergeCellUtil.mergeCell(fileName, sheetName, rowName, content, mergeHear, mergeFoot, img);调用。传参含义如下：

fileName：excel完整输出路径
sheetName： excel文件名和sheet表格名
rowName：表头（标题行）
content：数据行（可选，数据正文）
mergeHear：表头上面的行（可选，统计日期之类的信息）
mergeFoot：数据行下面的行（可选，备注说明之类的信息）
img：插入图片（可选，统计图表，印章之类）

下面测试示例：

1.完整的测试主类

package ExcelTest;

import java.util.List;
import java.util.Map;

/**
 * 横跨单元格主测试
 * 
 * @author yulisao
 * @createDate 2020年11月16日
 */
public class mergeCellTest{

	public static void main(String[] args) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

		String sheetName = "学生成绩信息统计表";// sheet名
		String fileName = "C:\\Users\\Administrator\\Desktop\\excel\\"+sheetName+".xls";// excel输出路径
		String[] rowName = { "学校", "姓名", "学号", "手机", "课程", "分数", "是否及格" };// excel标题

		String[][] content = new String[5][rowName.length];
		for (int i = 0; i < 5; i++) {
			int score = (int) (Math.random() * 101);
			content[i][0] = "某某学校";
			content[i][1] = "姓名-" + i;
			content[i][2] = "学号-" + i;
			content[i][3] = "手机-" + i;
			content[i][4] = "课程-" + i;
			content[i][5] = score + "";
			content[i][6] = score > 60 ? "及格" : "不及格";
		}

		String[][] mergeHear = new String[3][2];
		mergeHear[0][0] = "0,0,0,7";
		mergeHear[0][1] = "合并测试统计表";
		mergeHear[1][0] = "1,1,0,3";
		mergeHear[1][1] = "统计日期：2020-11-16";
		mergeHear[2][0] = "1,1,5,7";
		mergeHear[2][1] = "统计人：yulisao";

		String[][] mergeFoot = new String[9][2];
		mergeFoot[0][0] = "8,8,0,0";
		mergeFoot[0][1] = "班级";
		mergeFoot[1][0] = "8,8,1,1";
		mergeFoot[1][1] = "1201班";
		mergeFoot[2][0] = "8,8,3,3";
		mergeFoot[2][1] = "负责老师";
		mergeFoot[3][0] = "8,8,4,4";
		mergeFoot[3][1] = "张三";
		mergeFoot[4][0] = "8,8,6,6";
		mergeFoot[4][1] = "院长";
		mergeFoot[5][0] = "8,8,7,7";
		mergeFoot[5][1] = "李四";
		mergeFoot[6][0] = "9,9,0,7";
		mergeFoot[6][1] = "注意：";
		mergeFoot[7][0] = "10,10,0,7";
		mergeFoot[7][1] = "1.本统计表是随机生产的分数";
		mergeFoot[8][0] = "11,11,0,7";
		mergeFoot[8][1] = "2.样式可以自行设计";
		
		String [] img = {"C:\\Users\\Administrator\\Desktop\\excel\\img.png", "5,5,14,8"};

		mergeCellUtil.mergeCell(fileName, sheetName, rowName, content, mergeHear, mergeFoot, img);
	}

}

效果图：
在这里插入图片描述
注意点：

此处数据正文我是随机生成了一些数据，真正使用的时候先查数据，然后把查到的数据集放到二维数组content 里面去。
表格的第一列会自动添加序号列，所以rowName里面无需序号
rowName的元素个数要和数据正文content 里面的元素格式一致，避免数组越界错误
mergeHear若使用，第一个元素的坐标位置的起始行位置从0开始往后递增。
mergeFoot若使用，则需要自己计算好第一个元素坐标位置的起始行位置，计算方法是row=mergeHear行数+1（表头只有一行）+content行数，至少从row行开始往后递增。若mergeFoot的起始行坐标小于row则会覆盖上面的数据正文行。
mergeHear和mergeHear的起始列坐标，根据总列数自行分配。但结束列不要大于rowName元素个数。
img如果是要覆盖在表格正文上的，就要处理成透明背景图，不然遮盖图片下面的单元格
img的第二个元素是图片的坐标，可以是两个数也可以是四个数。建议使用四个数的坐标，即指定了图片的四个角所在单元格行列。因为它可以控制好图片的大小，但要调试一下图片的坐标以免图片太窄或者太扁使之四方四正的一个图。而两个数字的坐标，表示只指定图片的开始行和列，根据图片大小自动确定结束行和列，不同的图片在excel里面占据面积不可控
表格的样式依个人喜欢可以设计很多，工具类里面有些设置样式的方法并没有使用，有需要的可以去调试下看效果。更多的样式设计也可以去网上搜索，这里只写了区区几个抛砖引玉
如果因为样式或其中枚举有红色波浪线找不到依赖，可以用最后一个方法。CellStyle和HSSFCellStyle都可以用于cell.setCellStyle()。HSSFCell有的CellStyle也有，写法略微不同。CellStyle的更多样式也很多这里就不过多列举了。

2.只有表头和数据正文，调用方法mergeCellUtil.mergeCell(fileName, sheetName, rowName, content, null, null, null);
在这里插入图片描述
3.无正文数据，调用方法mergeCellUtil.mergeCell(fileName, sheetName, rowName, null, mergeHear, mergeFoot, null);mergeFoot的坐标是从3（mergeHear2行+表头行1行+数据行0行=3）开始的，自行根据公式计算。

工具类 mergeCellUtil.java

package ExcelTest;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 导出excel工具类(支持合并单元格)
 * 
 * @author yulisao
 * @createDate 2020年11月16日
 */
public class mergeCellUtil {
	
	private static final short FONT_SIZE_TITLE = 14; //标题字体大小
	private static final short FONT_SIZE_HEAD = 10; // 表头字体大小
	private static final short FONT_SIZE_EXCEL = 10; // 正文字体大小
	private static final short FONT_SIZE_FOOT = 8; // 结尾字体大小
	
	private static short BORDER_COLOR = 11; // 单元格边框颜色，必须大于10
	
	private static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
    private static final int UNIT_OFFSET_LENGTH = 7;
    private static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };
 

    /**
     * 导出excel(支持合并单元格)
     * 
     * @param filePath 输出路径
     * @param sheetName 文件名称
     * @param rowName 表头标题行字符串数组
     * @param dataArry 数据正文二维数据
     * @param mergeHear 表头标题行 上面行 字符串二维数组(比如大标题，统计日期等信息)， 元素1=单元格坐标(格式：起始行,结束行,起始列,结束列)，元素2=单元格内容
     * @param mergeFoot 数据正文 下面行 字符串二维数组(比如合计，备注说明等信息)，元素1=单元格坐标(格式：起始行,结束行,起始列,结束列)，元素2=单元格内容
     * @param img 图片字符串数组(公章，统计图表等) 元素1=图片路径，元素2=图片坐标(格式：起始行,起始列,结束行,结束列)
     */
    public static void mergeCell(String filePath, String sheetName,
			String[] rowName, String[][] dataArry, String[][] mergeHear,
			String[][] mergeFoot, String [] img) {
		// 参数检查
		if (StringUtils.isBlank(filePath) || rowName.length == 0 ) {
			return;
		}

		HSSFWorkbook wb = new HSSFWorkbook(); // 新建HSSFWorkbook

		HSSFSheet sheet = wb.createSheet(sheetName); // 新建sheet

		HSSFRow row = null; // 声明行对象
		HSSFCell cell = null; // 声明列对象
		int totalRowSize = 0; // 总行数

		/** 标题以及附属行 */
		if (!arrayIsNullOrEmpty(mergeHear)) {

			int nextRow = 0; // 下一行行数
			for (int i = 0; i < mergeHear.length; i++) {
				String mergeInfo = mergeHear[i][0]; // 需要合并的第n行
				String cellText = mergeHear[i][1]; // 需要填充的内容

				try {
					String[] range = mergeInfo.split(",");
					int begRow = Integer.parseInt(range[0]);
					int endRow = Integer.parseInt(range[1]);
					int begCol = Integer.parseInt(range[2]);
					int endCol = Integer.parseInt(range[3]);

					if (nextRow == endRow) { // 下一行与当前行相同，则创建行，同行不创建
						row = sheet.createRow(i); // 创建第n行
						nextRow += 1;
					}

					// 合并单元格
					CellRangeAddress cellRangeAddress = new CellRangeAddress(
							begRow, endRow, begCol, endCol);// 起始行,结束行,起始列,结束列
					sheet.addMergedRegion(cellRangeAddress);
					// 第一行的单元格
					cell = row.createCell(begCol);
					cell.setCellValue(cellText);
					
					if (nextRow == 1) { // 第一行 样式
						cell.setCellStyle(setDefaultHeadCenterStyle(wb)); // 设置标题样式 
					} else { // 其他行样式
						cell.setCellStyle(setDefaultHeadLeftStyle(wb)); // 设置表头样式 
					}
				} catch (Exception e) {
					System.out.println(e.getMessage());
					e.printStackTrace();
				}

			}

			totalRowSize = nextRow;
		}

		/** 表头 */
		row = sheet.createRow(totalRowSize);
		// 自动添加首列-序号
		cell = row.createCell(0);
		cell.setCellValue("序号");
		cell.setCellStyle(setDefaultTitleCenterStyle(wb)); // 设置标题行样式 
		
		// 创建表头标题
		for (int i = 0; i < rowName.length; i++) {
			cell = row.createCell(i + 1);
			cell.setCellValue(rowName[i]);
			cell.setCellStyle(setDefaultTitleCenterStyle(wb)); // 设置标题行样式 
		}

		totalRowSize += 1;

		/** 数据正文 */
		if (!arrayIsNullOrEmpty(dataArry)) {
			// 创建数据正文内容
			for (int i = 0; i < dataArry.length; i++) {
				row = sheet.createRow(i + totalRowSize); // 开始创建数据行
				cell = row.createCell(0);
				cell.setCellValue(i + 1);
				cell.setCellStyle(setDefaultContextStyle(wb)); // 第一列正文左对齐
				for (int j = 0; j < dataArry[i].length; j++) { // 数据列
					// 将内容按顺序赋给对应的列对象
					cell = row.createCell(j + 1);
					cell.setCellValue(dataArry[i][j]);
					cell.setCellStyle(setDefaultContextStyle(wb)); // 第一列正文左样式
				}
			}
			totalRowSize += dataArry.length;
		}

		/** 表结尾汇总或者说明 */
		if (!arrayIsNullOrEmpty(mergeFoot)) {

			int nextRow = totalRowSize;
			for (int i = 0; i < mergeFoot.length; i++) {
				String mergeInfo = mergeFoot[i][0]; // 需要合并的第n行
				String cellText = mergeFoot[i][1]; // 需要填充的内容

				try {
					String[] range = mergeInfo.split(",");
					int begRow = Integer.parseInt(range[0]);
					int endRow = Integer.parseInt(range[1]);
					int begCol = Integer.parseInt(range[2]);
					int endCol = Integer.parseInt(range[3]);

					if (nextRow == endRow) { // 下一行与当前行相同，则创建行，同行不创建
						row = sheet.createRow(nextRow); // 创建第n行
						nextRow += 1;
					}

					// 合并单元格
					CellRangeAddress cellRangeAddress = new CellRangeAddress(
							begRow, endRow, begCol, endCol);// 起始行,结束行,起始列,结束列
					sheet.addMergedRegion(cellRangeAddress);
					// 第n行的单元格
					cell = row.createCell(begCol);
					cell.setCellValue(cellText);
					cell.setCellStyle(setDefaultFootStyle(wb)); // 设置底部样式
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}

			}
			
			totalRowSize = nextRow;
		}
		
		/** 添加图片 */
        if (img != null && img.length != 0) {
			try {
				byte[] bt = FileUtils.readFileToByteArray(new File(img[0]));
				String [] location = img[1].split(",");
				
	            int pictureIdx = wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG);
	            CreationHelper helper = wb.getCreationHelper();
	            Drawing drawing = sheet.createDrawingPatriarch();
	            ClientAnchor anchor = helper.createClientAnchor();
	            anchor.setDx1(pixel2WidthUnits(60));
	            anchor.setDy1(pixel2WidthUnits(60));
	            
	            anchor.setRow1(Integer.parseInt(location[0])); //图片开始行数
	            anchor.setCol1(Integer.parseInt(location[1])); //图片开始列数
	            if (location.length > 2) { // 指定图片位置和大小
	            	anchor.setRow2(Integer.parseInt(location[2]));//图片结束行数
		            anchor.setCol2(Integer.parseInt(location[3])); //图片结束列数
		            drawing.createPicture(anchor, pictureIdx);
				} else { // // 指定图片位置,大小自动拓展
		            Picture picture = drawing.createPicture(anchor, pictureIdx); // 浮动设计
		            picture.resize();// 自动拓展
				}
	            
			} catch (IOException e) {
				e.printStackTrace();
				System.err.println("添加图片异常");
			}
		}
        
		
		/** 整体样式设置  */
        for(int i = 0; i < totalRowSize; i++) { // 设置行高
            row = sheet.getRow(i);
            row.setHeightInPoints(20);
            
            for(int j = 0; j < rowName.length + 1; j++) { // 设置边框
                cell = row.getCell(j);
            }
        }
        for(int j = 0; j < rowName.length + 1; j++) { // 设置列宽
            sheet.setColumnWidth(j, pixel2WidthUnits(120));
        }
        for(int i = 0; i < totalRowSize; i++) { // 设置行高
        	row = sheet.getRow(i);
        	for(int j = 0; j < rowName.length + 1; j++) { // 设置行高
                row = sheet.getRow(i);
                row.setHeightInPoints(20);
            }
        }
        
		// 输出文件流
		try {
			wb.write(new FileOutputStream(filePath));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		System.out.println("文件成功导出至：" + filePath);
	}
    
	/**
	 * 判断二维数组是否空
	 * 
	 * @param arr
	 * @return
	 */
	private static boolean arrayIsNullOrEmpty(String[][] arr) {
		if (arr == null || arr.length == 0)
			return true;
		int len = arr.length;
		for (int i = 0; i < len; i++) {
			if (arr[i] != null && arr[0].length != 0)
				return false;
		}
		return true;
	}
	
    /**
     * 默认表表头样式
     * 
     * @param wb
     * @return
     */
    private static HSSFCellStyle setDefaultHeadCenterStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();  
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格对齐方式 居中
		
		hssfCellStyle.setFillForegroundColor(BORDER_COLOR); //BORDER_COLOR背景颜色下标值
		hssfCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
		font.setFontHeightInPoints(FONT_SIZE_TITLE); // 字体大小
		hssfCellStyle.setFont(font);
		
		return hssfCellStyle;
	}
    
    /**
     * 默认表头样式
     * 
     * @param wb
     * @return
     */
    private static HSSFCellStyle setDefaultHeadLeftStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); 
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 设置单元格对齐方式 左
		
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
		font.setFontHeightInPoints(FONT_SIZE_HEAD); // 字体大小
		hssfCellStyle.setFont(font);

		return hssfCellStyle;
	}
    
    /**
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle setDefaultTitleCenterStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); 
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格对齐方式 居中
		
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
		font.setFontHeightInPoints(FONT_SIZE_HEAD); // 字体大小
		
		hssfCellStyle.setFont(font);

		return hssfCellStyle;
	}
	
    /**
     * 设置默认正文数据样式
     * 
     * @param wb
     * @return
     */
    private static HSSFCellStyle setDefaultContextStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); 
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 设置单元格对齐方式 左
		
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints(FONT_SIZE_EXCEL); // 字体大小
		hssfCellStyle.setFont(font);

		return hssfCellStyle;
	}
    
    /**
     * 设置默认底部样式
     * 
     * @param wb
     * @return
     */
    private static HSSFCellStyle setDefaultFootStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); 
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 设置单元格对齐方式 左
		
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints(FONT_SIZE_FOOT); // 字体大小
		hssfCellStyle.setFont(font);

		return hssfCellStyle;
	}
    
    /**
     * 设置列宽
     * 
     * @param pxs
     * @return
     */
    private static short pixel2WidthUnits(int pxs) {
        short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
        widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
        return widthUnits;
    }

	/**
	 * 设置单元格对齐方式
	 * 
	 * @param wb
	 * @param style
	 * @return
	 */
	private static HSSFCellStyle setAlignStyle(HSSFWorkbook wb, short style) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); // 设置单元格对齐方式
		hssfCellStyle.setAlignment(style); // 创建一个居中格式

		return hssfCellStyle;
	}

	/**
	 * 设置字体
	 * 
	 * @param wb
	 * @param style
	 * @return
	 */
	private static HSSFCellStyle setFontStyle(HSSFWorkbook wb, short fontSize) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setItalic(true); // 是否斜体
		font.setUnderline(HSSFFont.U_SINGLE); // 下滑线
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
		font.setFontHeightInPoints(fontSize); // 字体大小
		// other style....
		hssfCellStyle.setFont(font);
		return hssfCellStyle;
	}
	
	private static HSSFCellStyle setBorderStyle(HSSFWorkbook wb) {
		HSSFCellStyle hssfCellStyle = wb.createCellStyle(); // 设置单元格边框 与颜色
		hssfCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		hssfCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		hssfCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		hssfCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		
		hssfCellStyle.setBottomBorderColor(BORDER_COLOR++);
		hssfCellStyle.setLeftBorderColor(BORDER_COLOR++);
		hssfCellStyle.setRightBorderColor(BORDER_COLOR++);
		hssfCellStyle.setTopBorderColor(BORDER_COLOR++);

		return hssfCellStyle;
	}

}

	/**
	 * 一般适用的样式
	 * 
	 * @param wb 文档
	 * @param aligin 1   左   2   中   3   右
	 * @param size 字体大小
	 * @param bold 是否加粗
	 * @return
	 */
	private static CellStyle setCellStyle(HSSFWorkbook wb, int aligin, short size, boolean bold) {
		CellStyle hssfCellStyle = wb.createCellStyle(); 
		hssfCellStyle.setWrapText(true);

		switch (aligin) {
		case 1:
			hssfCellStyle.setAlignment(CellStyle.ALIGN_LEFT); 
			break;
		case 2:
			hssfCellStyle.setAlignment(CellStyle.ALIGN_CENTER); 
			break;
		case 3:
			hssfCellStyle.setAlignment(CellStyle.ALIGN_RIGHT); 
			break;
		default:
			hssfCellStyle.setAlignment(CellStyle.ALIGN_LEFT); 
			break;
		}
		
		HSSFFont font = wb.createFont();
		if (bold) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加多粗，自己点击进去参考枚举
		}
		font.setFontHeightInPoints(size); 
		hssfCellStyle.setFont(font);

		return hssfCellStyle;
	}