Java用POI动态生成Excel并导出,支持多页签,行合并,列合并,小白轻松看懂的工具类_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();
1
2
3
4
5
6

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;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245