Java Excel文件导出POI合并单元格

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
private XSSFWorkbook writeExcel(List<SupplyKcPlan> list) {
		if(CollectionUtils.isEmpty(list)){
			return new XSSFWorkbook();
		}
		XSSFWorkbook wb = new XSSFWorkbook();
		Map<String, CellStyle> styles = createStyles(wb);
		XSSFSheet sheet = wb.createSheet("sheet1");
		//定义列标题 14列
		String[] titles = {"日期", "单位名称",	"专业类型",	"序号",	"考察时间","",	"行程名称",	"地址项目概况",	"项目名称",	"项目甲方",	"承包内容",	"施工阶段",	"联系人", "电话"};
		XSSFRow rowTitle = sheet.createRow(0);
		XSSFCell titleCell = rowTitle.createCell(0);
		titleCell.setCellValue("考察行程");
		titleCell.setCellStyle(styles.get("title"));
		rowTitle.setHeight((short) 800);
		//合并第一行的列
		CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, titles.length - 1);
		sheet.addMergedRegion(titleRegion);
		CellRangeAddress headerRegion = new CellRangeAddress(1, 1,4,5);
		sheet.addMergedRegion(headerRegion);

		XSSFRow row = sheet.createRow(1);
		// 将列名写入
		for (int i = 0; i < titles.length; i++) {
			// 给列写入数据,创建单元格，写入数据
			XSSFCell headerCell = row.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}
		// 设置单元格宽度
		int curColWidth = 0;
		for (int i = 0; i <= titles.length; i++) {
			// 列自适应宽度，对于中文半角不友好，如果列内包含中文需要对包含中文的重新设置。
			sheet.autoSizeColumn(i, true);
			// 为每一列设置一个最小值，方便中文显示
			curColWidth = sheet.getColumnWidth(i);
			if(curColWidth<3000){
				sheet.setColumnWidth(i, 3000);
			}
			// 第2列文字较多，设置较大点。
			sheet.setColumnWidth(1, 8000);
			sheet.setColumnWidth(7,8000);
		}

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		//放入数据
		for (int i = 0; i < list.size(); i++) {
			SupplyKcPlan plan = list.get(i);
			List<SupplyKcPlanUser> supplyKcPlanUserList = plan.getSupplyKcPlanUserList();
			//取到组长和组员
			String headMan = supplyKcPlanUserList.stream().filter(user -> user.getKcFlag().equals("1")).map(SupplyKcPlanUser::getKcUserName).collect(Collectors.joining(","));
			String members = supplyKcPlanUserList.stream().filter(user -> user.getKcFlag().equals("0")).map(SupplyKcPlanUser::getKcUserName).collect(Collectors.joining("、"));
			XSSFRow userRow = sheet.createRow(i + 2);//前面一行大标题，一行题头
			XSSFCell userCell1 = userRow.createCell(0);
			CellRangeAddress userRange = new CellRangeAddress(i + 2, i + 2, 0, titles.length - 1);
			sheet.addMergedRegion(userRange);
			String s = String.format("考察组长：%s         考察成员：%s", headMan, members);
			userCell1.setCellValue(s);

			XSSFRow dataRow = sheet.createRow(i + 3);
			XSSFCell cell = dataRow.createCell(0);
			cell.setCellValue(sdf.format(plan.getKcDate()));
			cell.setCellStyle(styles.get("cell"));


		}

		return wb;
	}

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
//excel样式
	private Map<String, CellStyle> createStyles(Workbook wb)
	{
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 18);
		titleFont.setBold(true);
		style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); //背景色
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		Font font = wb.createFont();
		font.setBold(true); //字体加粗
		style.setFont(font);
		styles.put("header", style);

		style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true);
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
		styles.put("formula", style);

		style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
		styles.put("formula_2", style);

		return styles;
	}

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
try {

			// 文件名
			String fileName = "考察日程.xlsx";
			// 解决文件乱码
			final String userAgent = request.getHeader("user-agent");
			if (userAgent != null && userAgent.indexOf("Firefox") >= 0) {
				fileName = new String(fileName.getBytes(), "ISO8859-1");
			} else {
				fileName = URLEncoder.encode(fileName, "UTF-8");
			}
			// 下载文件
			response.setContentType("application/octet-stream");
			response.setHeader("Content-disposition", "attachment;filename="+fileName);
			response.flushBuffer();
			wb.write(response.getOutputStream());

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

1
2
3
4
5
6
7
8
jx:mergeCells(
lastCell="Merge cell ranges"
[, cols="Number of columns combined"]
[, rows="Number of rows combined"]
[, minCols="Minimum number of columns to merge"]
[, minRows="Minimum number of rows to merge"]
)