Java用POI动态生成Excel并导出,支持多页签,行合并,列合并,小白轻松看懂的工具类

本文链接：https://blog.youkuaiyun.com/tusbasa1/article/details/132385259

本文介绍了如何使用Java和POI库5.2.X版本动态创建Excel文件，包括支持多页签的导出以及行、列合并功能，提供示例代码和详细步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Java用POI动态生成Excel并导出,支持多页签,行合并,列合并,小白轻松看懂的工具类

文章目录

Java用POI动态生成Excel并导出,支持多页签,行合并,列合并,小白轻松看懂的工具类

1 技术实现思路

使用强大的org.apache.poi  5.2.X版本  
个人封装的这个工具类ExcelUtil有2个功能
1 传统的java数据导出到 多个页签的Excel中 的工具类
   见方法 public static ByteArrayOutputStream dataToExcel(final ExcelUtilPram ...pramList);
2 将数据 行合并，列合并 导出的excel的案例(看懂代码代码的都知道，带有数据列合并的封装控件不方便)
   见方法 private static void dataToExcelUnitTable();

2 效果展示

合并效果图包含行合并和列合并

多标签导出图

在这里插入图片描述

3 代码实现

3.1 传参工具类

import java.util.ArrayList;
import java.util.List;
/**
 * Excel工具类传参.
 */
public class ExcelUtilPram {
	/**
	 * 页签名称.
	 */
	private String sheetName = "Sheet1";
	/**
	 * 表头名称.
	 */
	private List<String> tableHead = new ArrayList<String>();
	/**
	 * 表头平均列宽.
	 */
	private Integer tableHeadPerWidth = 5000;
	/**
	 * 表数据.
	 */
	private List<List<Object>> tableData = new ArrayList<>();
	
	public String getSheetName() {
		return sheetName;
	}
	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}
	public List<String> getTableHead() {
		return tableHead;
	}
	public void setTableHead(List<String> tableHead) {
		this.tableHead = tableHead;
	}
	public Integer getTableHeadPerWidth() {
		return tableHeadPerWidth;
	}
	public void setTableHeadPerWidth(Integer tableHeadPerWidth) {
		this.tableHeadPerWidth = tableHeadPerWidth;
	}
	public List<List<Object>> getTableData() {
		return tableData;
	}
	public void setTableData(List<List<Object>> tableData) {
		this.tableData = tableData;
	}
}

3.2 实现工具类含Main

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.File;
import com.meta.metaverse.util.CMUtil;
/**
 * Excel工具类.此为5.2.X高版本，注意和低版本3.8.X的语法是不同的
 * 但实现思路类似.
 *  <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.2</version>
    </dependency>
 */
public class ExcelUtil {

	@SuppressWarnings("deprecation")
	public static void main(String[] args) {
		try {
			boolean doMutiSheetExport = true;//测试 多页签导出
			boolean doHeBingSheetDemoExport = true;//测试 合并单元格案例
			if (doMutiSheetExport) {
				final List<List<Object>> tbdata = new ArrayList<>();
			    for (int i = 1; i <= 8; i++) {
			    	List<Object> list = new ArrayList<Object>();
			    	list.add("张三"+i);
			    	list.add(Math.floor(Math.random()*100));
			    	list.add(Math.floor(Math.random()*100));
			    	list.add(new Date().toLocaleString());
			    	list.add(new Date().getTime()+i);
			    	tbdata.add(list);
				}
			    //
			    final ExcelUtilPram pram = new ExcelUtilPram();
			    pram.setSheetName("一班考试成绩Sheet");
			    pram.setTableHead(Arrays.asList("姓名,数学,语文,考试时间,考生号".split(",")));
			    pram.setTableData(tbdata);
			    final ExcelUtilPram pram1 = new ExcelUtilPram();
			    pram1.setSheetName("二班考试成绩Sheet");
			    pram1.setTableHead(Arrays.asList("姓名,英语,物理,考试时间,考生号".split(",")));
			    pram1.setTableData(tbdata);
			    //
			    final ByteArrayOutputStream bas = ExcelUtil.dataToExcel(pram,pram1);
			    //
			    final String savePath = "D://Excel导出_"+new Date().getTime()+".xlsx";
				FileUtils.writeByteArrayToFile(new File(savePath), bas.toByteArray());
				CMUtil.info("字节大小:"+bas.size()+",导出成功:" + savePath);
			} 
			if (doHeBingSheetDemoExport) {
				dataToExcelUnitTable();
			}
		} catch (Exception e) {
			CMUtil.error("导出数据失败100",e);  
		}
	}

	/**
	 * 多Sheet/简单导出 工具类.
	 * @param pram
	 * @return
	 */
	public static ByteArrayOutputStream dataToExcel(final ExcelUtilPram ...pramList) {
		ByteArrayOutputStream bas = null;
	    // 创建工作簿
	    final Workbook workbook = new XSSFWorkbook();
		try {
			for (ExcelUtilPram pram:pramList) {//多sheet导出 注意 sheetname不能重复
			    // 创建工作表
				final Sheet sheet = workbook.createSheet(pram.getSheetName());
			    // 创建表头行
				final Row headerRow = sheet.createRow(0);
			    // 设置表头样式
				final CellStyle headerCellStyle = createHeadStyle(workbook);
		        // 填充表头数据
				final List<String> headerData = pram.getTableHead();
		        for (int i = 0; i < headerData.size(); i++) {
		            Cell cell = headerRow.createCell(i);
		            cell.setCellValue(headerData.get(i).toString());
		            cell.setCellStyle(headerCellStyle);
		            sheet.setColumnWidth(i, pram.getTableHeadPerWidth()); //设置列宽度
		        }
			    // 设置表样式
				final CellStyle tbStyle = createDataStyle(workbook);
		        final List<List<Object>> tableData = pram.getTableData();
			    // 填充每一行数据
		        final int tbHeadUseNo = 1;
			    for (int rowIndex = 0; rowIndex < tableData.size(); rowIndex++) {
			    	//创建每一行
			    	final Row row = sheet.createRow(rowIndex+tbHeadUseNo);
			    	final List<Object> rowData = tableData.get(rowIndex);
			        for (int columnIndex = 0; columnIndex < rowData.size(); columnIndex++) {
			            Cell cell = row.createCell(columnIndex);
			            //cell.setCellType(CellType.STRING);
			            cell.setCellValue(rowData.get(columnIndex).toString());
			            cell.setCellStyle(tbStyle);
			        }
			    }
			}
			bas = new ByteArrayOutputStream();
			workbook.write(bas);
		} catch (Exception e) {
			CMUtil.error("导出数据失败",e);  
		} finally {
			CMUtil.ioClose(workbook);
		}
		return bas;
	}
	/**
	 * 合并单元格案例.
	 * 因为合并的特性无法封装为通用函数.
	 */
	private static void dataToExcelUnitTable() {
		ByteArrayOutputStream bas = null;
		final Workbook workbook = new XSSFWorkbook();
		try {
		    // 创建工作表
			final Sheet sheet = workbook.createSheet("合并单元格案例");
		    // 设置表头样式
			final CellStyle headerCellStyle = createHeadStyle(workbook);
	        // 填充表头数据
			final List<List<String>> headerData = new ArrayList<List<String>>();
			// 制造点表头的数据
			headerData.add(Arrays.asList("性别,班级,姓名,科目,科目,考试时间,考生号".split(",")));
			headerData.add(Arrays.asList("性别,班级,姓名,数学,语文,考试时间,考生号".split(",")));
	        for (int i = 0; i < headerData.size(); i++) {
			    // 创建表头行
				final Row headerRow = sheet.createRow(i);
	        	for (int j = 0; j < headerData.get(i).size(); j++) {
	        		Cell cell = headerRow.createCell(j);
		            cell.setCellValue(headerData.get(i).get(j).toString());
		            cell.setCellStyle(headerCellStyle);
		            sheet.setColumnWidth(j, 5000); //设置列宽度
				}
	        }
	        //表头垂直合并
		    sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
		    sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
		    sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
		    sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
		    sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
		    //表头水平合并
		    sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4));
		    //表头已经占用的行数
	        final int tbHeadUseNo = headerData.size();
		    // 设置表数据样式
			final CellStyle tbStyle = createDataStyle(workbook);
	        final List<List<String>> tableData = new ArrayList<List<String>>();
	        // 造点数据
	        tableData.add(Arrays.asList("男,一班,张三,60,61,2023-08-19,100001".split(",")));
	        tableData.add(Arrays.asList("男,一班,李四,70,71,2023-08-19,100002".split(",")));
	        tableData.add(Arrays.asList("男,二班,王五,80,81,2023-08-19,100003".split(",")));
	        tableData.add(Arrays.asList("男,二班,赵六,90,91,2023-08-19,100004".split(",")));
	        tableData.add(Arrays.asList("女,一班,邹七,10,11,2023-08-19,100005".split(",")));
	        tableData.add(Arrays.asList("女,一班,胡八,20,21,2023-08-19,100006".split(",")));
	        tableData.add(Arrays.asList("女,二班,老九,30,31,2023-08-19,100007".split(",")));
	        tableData.add(Arrays.asList("女,二班,陈十,40,41,2023-08-19,100008".split(",")));
	        tableData.add(Arrays.asList("女,二班,拾一,50,51,2023-08-19,100009".split(",")));
		    for (int rowIndex = 0; rowIndex < tableData.size(); rowIndex++) {
		    	//创建每一行
		    	final Row row = sheet.createRow(rowIndex+tbHeadUseNo);
		    	final List<String> rowData = tableData.get(rowIndex);
		        for (int columnIndex = 0; columnIndex < rowData.size(); columnIndex++) {
		            Cell cell = row.createCell(columnIndex);
		            //cell.setCellType(CellType.STRING);
		            cell.setCellValue(rowData.get(columnIndex).toString());
		            cell.setCellStyle(tbStyle);
		        }
		    }
		    // 合并数据单元格 垂直合并
		    sheet.addMergedRegion(new CellRangeAddress(2, 5, 0, 0));//性别男合并
		    sheet.addMergedRegion(new CellRangeAddress(6,10, 0, 0));//性别女合并
		    sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));//班级合并
		    sheet.addMergedRegion(new CellRangeAddress(4, 5, 1, 1));//班级合并
		    sheet.addMergedRegion(new CellRangeAddress(6, 7, 1, 1));//班级合并
		    sheet.addMergedRegion(new CellRangeAddress(8,10, 1, 1));//班级合并
		    //
			bas = new ByteArrayOutputStream();
			workbook.write(bas);
			//
		    final String savePath = "D://Excel导出合并单元格_"+new Date().getTime()+".xlsx";
			FileUtils.writeByteArrayToFile(new File(savePath), bas.toByteArray());
			CMUtil.info("字节大小:"+bas.size()+",导出合并单元格成功:" + savePath);
		} catch (Exception e) {
			CMUtil.error("导出合并单元格数据失败",e);  
		} finally {
			CMUtil.ioClose(workbook);
		}
	}
	/**
	 * 创建表头样式.
	 * @param workbook
	 * @param headerCellStyle
	 */
	private static CellStyle createHeadStyle(final Workbook workbook) {
		final CellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		headerCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框 
		headerCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框	
		headerCellStyle.setBorderTop(BorderStyle.THIN);// 上边框		
		headerCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
		headerCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);//蓝色背景色
		headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//全填充模式
		final Font font = workbook.createFont();
		font.setColor(IndexedColors.WHITE.getIndex());//设置字体颜色
		font.setBold(true);
		headerCellStyle.setFont(font);//表头字体加粗
		return headerCellStyle;
	}
	/**
	 * 创建数据样式.
	 * @param workbook
	 * @param headerCellStyle
	 */
	private static CellStyle createDataStyle(final Workbook workbook) {
		final CellStyle tbStyle = workbook.createCellStyle();
		tbStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		tbStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		tbStyle.setBorderBottom(BorderStyle.THIN);// 下边框 
		tbStyle.setBorderLeft(BorderStyle.THIN);// 左边框	
		tbStyle.setBorderTop(BorderStyle.THIN);// 上边框		
		tbStyle.setBorderRight(BorderStyle.THIN);// 右边框
		final Font tbfont = workbook.createFont();
		tbfont.setColor(IndexedColors.BLUE.getIndex());//设置字体颜色
		tbfont.setBold(false);
		tbStyle.setFont(tbfont);//表头字体加粗
		return tbStyle;
	}
}