javaweb使用poi下载excel设置样式、合并单元格、设置列宽

@Override
	public void exportMajorInfo(@RequestBody StudentInfoDto studentInfoDto) {
		// 至少存在4列
		int miniCell = 4;
		// 查询有多少个年级存在在校人数
		List<String> grades = studentStatusMapper.getOnSchoolGrade();
		// 构建excel
		HSSFWorkbook workbook = new HSSFWorkbook();
		//2.创建工作表
		HSSFSheet sheet = workbook.createSheet("在校专业人数");
		String fileName = "*****学院在校生专业人数--预览.xls";
		// 设置默认列宽
		setExcelColumnWidth(sheet);
		// 年级集合是否存在数据，不存在数据之间返回
		if (CollectionUtils.isNotEmpty(grades)) {
			// 创建excel头标题内容
			createExcelHeader(workbook, miniCell, sheet, grades, setExcelStyle(workbook, 12));
			// 创建内容
			createExcelContent(miniCell, sheet, grades, setExcelStyle(workbook, 10));
		}
		response.setContentType("application/octet-stream;charset=utf-8");
		try {
			response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName));
			workbook.write(response.getOutputStream());
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 设置excel列宽
	 * @param sheet
	 */
	private void setExcelColumnWidth(HSSFSheet sheet) {
		sheet.setDefaultColumnWidth(12);
		// y = 256*width+184
		sheet.setColumnWidth(0, 256 * 25 + 184);
		sheet.setColumnWidth(2, 256 * 30 + 184);
	}

	/**
	 * 设置样式
	 * @param workbook
	 * @param fontSize
	 * @return
	 */
	private HSSFCellStyle setExcelStyle(HSSFWorkbook workbook, int fontSize) {
		HSSFCellStyle style = createCellStyle(workbook,(short)fontSize,false,true);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		return style;
	}

	/**
	 * 创建在校学生内容
	 * @param miniCell
	 * @param sheet
	 * @param grades
	 * @param headStyle
	 */
	private void createExcelContent(int miniCell, HSSFSheet sheet, List<String> grades, HSSFCellStyle headStyle) {
		// 获取二级学院集合
		List<StudentStatusVo> departments = studentStatusMapper.getDepartmentList();
		if (CollectionUtils.isNotEmpty(departments)) {
			int index = 3;
			// 查询获取专业
			for (int i = 0; i < departments.size(); i++) {
				List<StudentStatusVo> majors = studentStatusMapper.getMajorList(departments.get(i).getStudentDepartment());
				if (CollectionUtils.isEmpty(majors)) {
					continue;
				}
				// 创建一个学院的行列数据
				HSSFRow row = sheet.createRow(index);
				CellRangeAddress cellRangeAddress = new CellRangeAddress(index, index + majors.size() - 1,0,0);//起始行,结束行,起始列,结束列
				sheet.addMergedRegion(cellRangeAddress);
				HSSFCell department = row.createCell(0);
				department.setCellStyle(headStyle);
				department.setCellValue(departments.get(i).getStudentDepartmentName());
				for (int j = 0; j < majors.size(); j++) {
					// 创建一个学院下专业的行列数据
					HSSFRow majorRow = j == 0 ? row : sheet.createRow(index + j);
					// 空白格填充
					if (j != 0) {
						HSSFCell nullCell = majorRow.createCell(0);
						nullCell.setCellStyle(headStyle);
					}
					// 序号
					HSSFCell noCell = j == 0 ? row.createCell(1) : majorRow.createCell(1);
					noCell.setCellStyle(headStyle);
					noCell.setCellValue(index + j - 2);
					// 专业名称
					HSSFCell majorCell = j == 0 ? row.createCell(2) : majorRow.createCell(2);
					majorCell.setCellStyle(headStyle);
					majorCell.setCellValue(majors.get(j).getMajorName());
					// 班级人数
					HSSFCell classCell = j == 0 ? row.createCell(3) : majorRow.createCell(3);
					classCell.setCellStyle(headStyle);
					classCell.setCellValue(studentStatusMapper.getClassNum(majors.get(j).getMajorId(), grades));
					int atSchool = 0;
					for (int k = 0; k < grades.size(); k++) {
						int gradeNum = studentStatusMapper.getAtSchoolNum(majors.get(j).getMajorId(), grades.get(k));
						atSchool = atSchool + gradeNum;
						// 年级在校人数
						HSSFCell atSchoolCell = j == 0 ? row.createCell(miniCell + k) : majorRow.createCell(miniCell + k);
						atSchoolCell.setCellStyle(headStyle);
						atSchoolCell.setCellValue(gradeNum);
						if (k + 1 == grades.size()) {
							HSSFCell atSchoolTotalCell = j == 0 ? row.createCell(miniCell + k + 1) : majorRow.createCell(miniCell + k + 1);
							atSchoolTotalCell.setCellStyle(headStyle);
							atSchoolTotalCell.setCellValue(atSchool);
						}
					}
					int totalNum = 0;
					for (int k = 0; k < grades.size(); k++) {
						int gradeNum = studentStatusMapper.getTotalSchoolNum(majors.get(j).getMajorId(), grades.get(k));
						totalNum = totalNum + gradeNum;
						// 年级总共人数
						HSSFCell atSchoolCell = j == 0 ? row.createCell(miniCell + k + grades.size() + 1) : majorRow.createCell(miniCell + k + grades.size() + 1);
						atSchoolCell.setCellStyle(headStyle);
						atSchoolCell.setCellValue(gradeNum);
						if (k + 1 == grades.size()) {
							HSSFCell atSchoolTotalCell = j == 0 ? row.createCell(miniCell + k + grades.size() + 2) : majorRow.createCell(miniCell + k + grades.size() + 2);
							atSchoolTotalCell.setCellStyle(headStyle);
							atSchoolTotalCell.setCellValue(totalNum);
						}
					}
				}
				index = index + majors.size();
			}
		}
	}

	/**
	 * 创建excel头
	 * @param workbook
	 * @param miniCell
	 * @param sheet
	 * @param grades
	 * @param headStyle
	 */
	private void createExcelHeader(HSSFWorkbook workbook, int miniCell, HSSFSheet sheet, List<String> grades, HSSFCellStyle headStyle) {
		// 创建第一行及内容
		HSSFRow row = sheet.createRow(0);
		// 创建第一行第一列 （主题）
		CellRangeAddress titleRange = new CellRangeAddress(0, 0,0,miniCell + grades.size() * 2 + 1);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(titleRange);
		HSSFCell excelTitle = row.createCell(0);
		// 加载单元格样式
		excelTitle.setCellStyle(setExcelStyle(workbook, 15));
		excelTitle.setCellValue("*****学院在校生专业人数--预览（截止至" + new SimpleDateFormat("yyyy年MM月dd日").format(new Date()) + ")");

		// 创建第二行
		HSSFRow title = sheet.createRow(1);
		// 创建第二行第一列 （二级学院）
		CellRangeAddress departmentRange = new CellRangeAddress(1, 2,0,0);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(departmentRange);
		HSSFCell department = title.createCell(0);
		department.setCellStyle(headStyle);
		department.setCellValue("二级学院");

		// 创建第二行第二列 （序号）
		CellRangeAddress noRange = new CellRangeAddress(1, 2,1,1);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(noRange);
		HSSFCell no = title.createCell(1);
		no.setCellStyle(headStyle);
		no.setCellValue("序号");

		// 创建第二行第三列 （专业名称）
		CellRangeAddress majorRange = new CellRangeAddress(1, 2,2,2);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(majorRange);
		HSSFCell major = title.createCell(2);
		major.setCellStyle(headStyle);
		major.setCellValue("专业名称");

		// 创建第二行第四列 （班级数）
		CellRangeAddress classRange = new CellRangeAddress(1, 2,3,3);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(classRange);
		HSSFCell classCell = title.createCell(3);
		classCell.setCellStyle(headStyle);
		classCell.setCellValue("班级数");

		// 创建第二行（在校人数）
		CellRangeAddress atSchool = new CellRangeAddress(1, 1,miniCell,miniCell + grades.size());//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(atSchool);
		HSSFCell atSchoolCell = title.createCell(miniCell);
		atSchoolCell.setCellStyle(headStyle);
		atSchoolCell.setCellValue("在校人数");

		// 创建第二行（总人数）
		CellRangeAddress totalNum = new CellRangeAddress(1, 1,miniCell + grades.size() + 1,miniCell + grades.size() * 2 + 1);//起始行,结束行,起始列,结束列
		sheet.addMergedRegion(totalNum);
		HSSFCell totalNumCell = title.createCell(miniCell + grades.size() + 1);
		totalNumCell.setCellStyle(headStyle);
		totalNumCell.setCellValue("总人数");
		// 填充空白格格式
		for (int i = miniCell + 1; i <= grades.size() + miniCell; i++) {
			HSSFCell nullCell = title.createCell(i);
			nullCell.setCellStyle(headStyle);
		}
		for (int i = grades.size() + miniCell + 2; i <= grades.size() * 2 + miniCell + 1; i++) {
			HSSFCell nullCell = title.createCell(i);
			nullCell.setCellStyle(headStyle);
		}

		// 创建第三行及内容
		HSSFRow thirdRow = sheet.createRow(2);
		// 填充空白格格式
		for (int i = 0; i < miniCell; i++) {
			HSSFCell nullCell = thirdRow.createCell(i);
			nullCell.setCellStyle(headStyle);
		}
		for (int i = 0; i < grades.size(); i++) {
			HSSFCell gradeCell = thirdRow.createCell(miniCell + i);
			gradeCell.setCellStyle(headStyle);
			gradeCell.setCellValue(grades.get(i) + "级");
			if (i + 1 == grades.size()) {
				HSSFCell majorTotalCell = thirdRow.createCell(miniCell + i + 1);
				majorTotalCell.setCellStyle(headStyle);
				majorTotalCell.setCellValue("专业总人数");
			}
		}
		for (int i = 0; i < grades.size(); i++) {
			HSSFCell gradeCell = thirdRow.createCell(miniCell + i + grades.size() + 1);
			gradeCell.setCellStyle(headStyle);
			gradeCell.setCellValue(grades.get(i) + "级");
			if (i + 1 == grades.size()) {
				HSSFCell majorTotalCell = thirdRow.createCell(miniCell + i + grades.size() + 2);
				majorTotalCell.setCellStyle(headStyle);
				majorTotalCell.setCellValue("专业总人数");
			}
		}
	}

	/**
	 *
	 * @param workbook
	 * @param fontsize
	 * @return 单元格样式
	 */
	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {
		// TODO Auto-generated method stub
		HSSFCellStyle style = workbook.createCellStyle();
		//是否水平居中
		if(flag1){
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		}

		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		//创建字体
		HSSFFont font = workbook.createFont();
		//是否加粗字体
		if(flag){
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		font.setFontHeightInPoints(fontsize);
		//加载字体
		style.setFont(font);
		return style;
	}

合并单元格使用

CellRangeAddress departmentRange = new CellRangeAddress(1,2,0,0);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(departmentRange);

设置列宽

sheet.setDefaultColumnWidth(12); // 默认列宽
// y = 256*width+184
sheet.setColumnWidth(0, 256 * 25 + 184); // 每列宽度
sheet.setColumnWidth(2, 256 * 30 + 184);

设置水平居中、垂直居中、字体大小、是否加粗

HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//创建字体
HSSFFont font = workbook.createFont();
//加粗字体
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(fontsize);
//加载字体
style.setFont(font);

实现图为：