Java使用poi生成Execl表格，并合并单元格

72 阅读 0 评论 48 点赞

我是靠谱客的博主风中酒窝，最近开发中收集的这篇文章主要介绍Java使用poi生成Execl表格，并合并单元格，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、前言

公司框架中，生成Execl表格的工具类已经存在，但是后面有一个需求，生成的Execl需要有合并行单元格，现有的不通用，所以只能写一个方法到工具类中，以前没有接触过，折腾了一天多，总算弄好，记录下来。

效果图：

引入的jar包

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.11-beta2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11-beta2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11-beta2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.11-beta2</version>
        </dependency>

这里遇到一个问题：我直接引入的poi包，结果出现的是一个common包？？？莫名其妙。。不解

代码块：

public static XSSFWorkbook exportMargeExecl(List<Execl> execls) {

		logger.info("Xiaoshishu >>> 开始导出execl，版本为2007版本，后缀为xlsx。。。");
		long startTime = System.currentTimeMillis();

		// 定义一个工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();

		// 创建标题样式
		XSSFCellStyle cellTitleStyle = workbook.createCellStyle();
		// 垂直居中
		cellTitleStyle.setAlignment(HorizontalAlignment.CENTER);
		cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellTitleStyle.setBorderLeft(BorderStyle.THIN);
		// 标题字体
		XSSFFont fontTitle = workbook.createFont();
		fontTitle.setFontName("宋体");
		// 粗体显示
		fontTitle.setBold(true);
		fontTitle.setFontHeightInPoints((short) 20);  //字体大小
		cellTitleStyle.setFont(fontTitle);

		// 创建副标题样式
		XSSFCellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(new XSSFColor(Color.WHITE));
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成字体
		XSSFFont font = workbook.createFont();
		// font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		font.setFontName("宋体");
		// font.setColor(new XSSFColor(Color.BLACK));
		font.setFontHeightInPoints((short) 11);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 标题行的样式
		XSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));
		style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		XSSFFont font2 = workbook.createFont();
		// font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		int size = execls.size();
		for (int s = 0; s < size; s++) {
			Execl execl = execls.get(s);
			String sheetName = execl.getSheetName();
			if (StringUtils.isBlank(sheetName)) {
				sheetName = "sheet" + (s + 1);
			}

			// 定义一个表格
			XSSFSheet sheet = workbook.createSheet(sheetName);

			// 如果这行没有了，整个公式都不会有自动计算的效果的
			sheet.setForceFormulaRecalculation(true);

			// 设置表格宽度
			sheet.setDefaultColumnWidth(20);

			int index = 0;

			String title = execl.getTitle();
			boolean isTitle = StringUtils.isNotBlank(title);
			if (isTitle) {
				// 产生表格标题行

				XSSFRow row = sheet.createRow(index++);
				XSSFCell row0cell0 = row.createCell(0, CellType.STRING);
				row0cell0.setCellValue(title);
				row0cell0.setCellStyle(cellTitleStyle);

				boolean isMap = execl.isMapContent();
				// 合并表格标题
				if (isMap) {
					// 合并，参数说明：1：开始行 2：结束行  3：开始列 4：结束列
					sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, execl.getColumnMapTitles().length - 1));
				}
			}
			logger.info("Xiaoshishu >>> 完成标题创建[{}]", isTitle ? title : null);
			// 副标题需要合并为几行
			Integer[] rowMargeSize = execl.getRowMargeSize();

			// 合并的副标题值
			String[] subTitles = execl.getSubTitle();
			XSSFRow row1 = sheet.createRow(index++);
			XSSFCell cellHeader = null;
			int rowSizeIndex = 0;
			for (int i = 0; i < subTitles.length; i++) {
				cellHeader = row1.createCell(rowSizeIndex);
				XSSFCellStyle titleStyle = workbook.createCellStyle();
				// 垂直居中
				titleStyle.setAlignment(HorizontalAlignment.CENTER);
				titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				titleStyle.setWrapText(true);
				titleStyle.setBorderBottom(BorderStyle.THIN);//下边框
				titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
				titleStyle.setBorderTop(BorderStyle.THIN);//上边框
				titleStyle.setBorderRight(BorderStyle.THIN);//右边框

				XSSFFont font1 = workbook.createFont();
				font1.setFontName("宋体");
				//字体大小
				font1.setFontHeightInPoints((short) 13);
				//选择需要用到的字体格式
				titleStyle.setFont(font1);
				cellHeader.setCellStyle(titleStyle);
				cellHeader.setCellType(CellType.STRING);
				cellHeader.setCellStyle(titleStyle);
				cellHeader.setCellValue(new XSSFRichTextString(subTitles[i]));

				rowSizeIndex = rowMargeSize[i];
			}

			// 合并副标题单元格，并且设置边框线
			int sss = 0;
			List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
			CellRangeAddress titleRow1 = null;
			for (int ss = 0; ss < rowMargeSize.length; ss++) {

				if (isTitle) {
					titleRow1 = new CellRangeAddress(1, 2, sss, rowMargeSize[ss] - 1);
				}else {
					titleRow1 = new CellRangeAddress(0, 1, sss, rowMargeSize[ss] - 1);
				}

				sheet.addMergedRegion(titleRow1);
				cellRangeAddressList.add(titleRow1);
				sss = rowMargeSize[ss];
			}
			// 合并单元格之后，有些边框线没有设置完整，这里是补全边框线
			for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
				RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
				RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
				RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
				RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
			}
			logger.info("Xiaoshishu >>> 完成副标题的创建并且合并单元格。。。");

			// 添加子标题
			XSSFRow row2 = sheet.createRow(++index);
			String[] childCells = execl.getColumnMapTitles();
			XSSFCell childCell;
			for (int i = 0; i < childCells.length; i++) {
				childCell = row2.createCell(i);
				childCell.setCellStyle(style);
				childCell.setCellValue(new XSSFRichTextString(childCells[i]));
			}
			logger.info("Xiaoshishu >>> 完成子标题的创建。。。");

			// 填充数据行
			List<?> list = execl.getList();
			if (list != null && list.size() > 0) {
				String[] keys = execl.getColumnMapKeys();
				for (int j = 0; j < list.size(); j++) {
					Map<String,Object> map = (Map<String, Object>) list.get(j);
					// 创建一行
					XSSFRow row3 = sheet.createRow(++index);
					for (int jj = 0; jj < keys.length; jj++) {

						XSSFCell cell3 = row3.createCell(jj, CellType.STRING);
						cell3.setCellValue(String.valueOf(map.getOrDefault(keys[jj], "")));
						cell3.setCellStyle(style2);
					}

				}
			}
			logger.info("Xiaoshishu >>> 完成数据行的填充。。。");
		}
		long endTime = System.currentTimeMillis();
		logger.info("Xiaoshishu >>> 结束导出execl-xlsx，本次耗时{}毫秒", endTime - startTime);
		return workbook;
	}

Main方法块执行

public void static Main(String[] args){

 List<Execl> execls = new ArrayList<Execl>();
        for (int j = 0; j < 1; j++) {
            Execl execl = new Execl();
            execl.setSheetName("文档名称");
            execl.setTitle("标题");
            String[] subTitle = {"合并副标题1","合并副标题2"};
            // ：如需要合并两行，第一行是2个单元格，第二行是3个单元格。则参数为{2,5};同样，第三行如果也是3个单元格，则是{2,5,8}
            Integer[] rowMargeSize = {2,5};
            execl.setRowMargeSize(rowMargeSize);
            execl.setSubTitle(subTitle);
            execl.setColumnMapTitles("id","名字","年龄","备注","备注1");
            execl.setColumnMapKeys("id","name","age","note","note1");
            execl.setMapContent(true);
            List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
            for (int i = 0; i < 10; i++) {
                Map<String, Object> execlTest = new HashMap<String, Object>();
                execlTest.put("id", "id"+i);
                execlTest.put("age", i);
                execlTest.put("name", "name"+i);
                execlTest.put("note", "备注"+i);
                execlTest.put("note1", "备注1"+i);

                data.add(execlTest);
            }
            execl.setList(data);

            execls.add(execl);
        }
        String filePath = "D:/Xiaoshishu.xlsx";
        XSSFWorkbook workbook = ExeclUtil.exportMargeExecl(execls);
        ExeclUtil.exportToFile(workbook, filePath);
        System.out.println("导出成功");

}

exportToFile导出为文件

public static void exportToFile(XSSFWorkbook workbook, String filePath){
        // workbook
        FileOutputStream fileOutputStream = null;
        try {
            // workbook 2 FileOutputStream
            fileOutputStream = new FileOutputStream(filePath);
            workbook.write(fileOutputStream);
            // flush
            fileOutputStream.flush();
        } catch (Exception e) {
        	logger.error("Xiaoshishu >>> 导出Excel错误，msg=[{}]", e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
        	try {
        		if (workbook != null) {
        			workbook.close();
				}
			} catch (IOException e1) {
				e1.printStackTrace();
			}
            try {
                if (fileOutputStream!=null) {
                    fileOutputStream.close();
                }
            } catch (Exception e) {
            }
        }
    }

参考网址：

https://blog.csdn.net/datangxiajun/article/details/78308979（java导出Excel合并单元格）

https://blog.csdn.net/l1028386804/article/details/79659605（Java之——导出Excel通用工具类）

https://blog.csdn.net/qq_24035541/article/details/81541406（java XSSFWorkbook 导出Excel）

总结：