poi导出excel设置样式

最新推荐文章于 2022-09-19 14:47:13 发布

Z丶royAl

最新推荐文章于 2022-09-19 14:47:13 发布

阅读量8w

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/qq_15260315/article/details/82620032

本文详细介绍了一种使用Java进行Excel文件导出的方法，并重点介绍了如何设置Excel文件的各种格式属性，包括字体、边框、对齐方式等。

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

一、效果如下

二、后台代码

	/**
	 *
	 * @Date 2018年9月11日 下午3:53:26
	 * @Fcunction exportExcel
	 * @param sheetName
	 * @param value_columns1
	 * @param value_columns2
	 * @param value_columns3
	 * @param list
	 * @return HSSFWorkbook
	 *
	 */
	public static HSSFWorkbook exportExcel(String sheetName, String[] value_columns1,String[] value_columns2,String[] value_columns3, List<List<String>> list,String name){
		//创建一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建一个工作表
		HSSFSheet sheet = workbook.createSheet(sheetName);
		//设置宽度
		for (int i = 0; i < value_columns1.length; i++) {
			sheet.setColumnWidth(i, 3000);
		}
		//设置单元格格式居中
		HSSFCellStyle titleStyle = workbook.createCellStyle(); //标题
		HSSFCellStyle headStyle = workbook.createCellStyle(); //表头
		HSSFCellStyle cellStyle = workbook.createCellStyle(); //表格内容
		HSSFCellStyle twoStyle = workbook.createCellStyle(); //第二行不居中显示
		HSSFCellStyle remarkStyle = workbook.createCellStyle(); //备注，第二行不居中显示
		HSSFCellStyle recordStyle = workbook.createCellStyle(); //记录内容
		HSSFCellStyle recordHumanLeftStyle = workbook.createCellStyle(); //记录人左边
		HSSFCellStyle recordHumanRightStyle = workbook.createCellStyle(); //记录人右边
		//设置单元格样式
		HSSFFont titleFont = workbook.createFont(); //标题字体
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
		titleFont.setFontHeightInPoints((short)16); //字号
		titleStyle.setFont(titleFont);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFFont headFont = workbook.createFont(); //表头字体
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
		headFont.setFontHeightInPoints((short)16); //字号
		headStyle.setFont(headFont);
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
		headStyle.setWrapText(true); //自动换行

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中

		remarkStyle.setWrapText(true); //自动换行
		remarkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
		remarkStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		remarkStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		remarkStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		remarkStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		remarkStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		twoStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		recordStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); //垂直居上
		recordStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		recordStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		recordStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		recordStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		recordHumanLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		recordHumanLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		recordHumanRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		recordHumanRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		recordHumanRightStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		//添加表头行
		HSSFRow hssfRow = sheet.createRow(0);
		//第一行标题
		HSSFCell titleCell = hssfRow.createCell(0);
		titleCell.setCellValue(sheetName);
		titleCell.setCellStyle(titleStyle);
		//合并第一行所有列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, value_columns1.length-1));
		//第二行
		hssfRow = sheet.createRow(1);
		for (int i = 0; i < value_columns1.length; i++) {
			HSSFCell twoCell = hssfRow.createCell(i);
			if(i==0){
				twoCell.setCellValue("日期："+new SimpleDateFormat("yyyy年M月d日").format(new Date()));
				twoCell.setCellStyle(twoStyle);
			}else if(i==value_columns1.length-3){
				twoCell.setCellValue("交班领导："+name);
				twoCell.setCellStyle(twoStyle);
			}
		}
		//合并第二行
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, value_columns1.length-4));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, value_columns1.length-3, value_columns1.length-1));
		//第三行开始表格
		hssfRow = sheet.createRow(2);
		for (int i = 0; i < value_columns1.length; i++) {
			//添加表头内容
			HSSFCell headCell = hssfRow.createCell(i);
			headCell.setCellValue(value_columns1[i]);
			headCell.setCellStyle(cellStyle);
		}
		hssfRow = sheet.createRow(3);
		for (int i = 0; i < value_columns2.length; i++) {
			//添加表头内容
			HSSFCell headCell = hssfRow.createCell(i);
			headCell.setCellValue(value_columns2[i]);
			headCell.setCellStyle(cellStyle);
		}
		hssfRow = sheet.createRow(4);
		for (int i = 0; i < value_columns3.length; i++) {
			//添加表头内容
			HSSFCell headCell = hssfRow.createCell(i);
			headCell.setCellValue(value_columns3[i]);
			headCell.setCellStyle(cellStyle);
		}
		//合并表头（3、4、5行）
		sheet.addMergedRegion(new CellRangeAddress(2, 4, 0, 0));
		sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 2));
		sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 4));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 8));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 6));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 8));
		sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 10));
		//把数据添加到excel
		for (int i = 0; i < list.size(); i++) {
			hssfRow = sheet.createRow(i + 5);
			for (int j = 0; j < list.get(i).size(); j++) {
				//创建单元格，并设置值
				HSSFCell cell = hssfRow.createCell(j);
				cell.setCellValue(list.get(i).get(j));
				if(i==list.size()-1&&j!=0){
					cell.setCellStyle(remarkStyle);
				}else {
					cell.setCellStyle(cellStyle);
				}
			}
		}
		for (int i = 5+list.size(); i < 5+list.size()+2; i++) {
			hssfRow = sheet.createRow(i);
			for (int j = 0; j < value_columns1.length; j++) {
				HSSFCell cell = hssfRow.createCell(j);
				cell.setCellValue("");
				cell.setCellStyle(remarkStyle);
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(4+list.size(), 4+list.size()+2, 0, 0));
		sheet.addMergedRegion(new CellRangeAddress(4+list.size(), 4+list.size()+2, 1, 10));
		//会议记录
		for (int i = 7+list.size(); i < 7+list.size()+5; i++) {
			hssfRow = sheet.createRow(i);
			for (int j = 0; j < value_columns1.length; j++) {
				HSSFCell cell = hssfRow.createCell(j);
				if(i == 7+list.size()&&j==0) {
					cell.setCellValue("会议记录：");
				}else {
					cell.setCellValue("");
				}
				cell.setCellStyle(recordStyle);
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(7+list.size(), 7+list.size()+4, 0, 10));
		//记录人
		hssfRow = sheet.createRow(7+list.size()+5);
		for (int j = 0; j < value_columns1.length; j++) {
			HSSFCell cell = hssfRow.createCell(j);
			if(j>=value_columns1.length-3) {
				cell.setCellValue("记录人：");
				cell.setCellStyle(recordHumanRightStyle);
			}else {
				cell.setCellValue("");
				cell.setCellStyle(recordHumanLeftStyle);
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(7+list.size()+5, 7+list.size()+5, 0, value_columns1.length-4));
		sheet.addMergedRegion(new CellRangeAddress(7+list.size()+5, 7+list.size()+5, value_columns1.length-3, value_columns1.length-1));
		return workbook;
	}

setColumnWidth方法设置具体某一列的宽度（第一个参数是第几列从0开始，第二个参数是宽度值）

HSSFSheet sheet = workbook.createSheet(date+sheetName);
sheet.setColumnWidth((short) 6, (short) 1600);

设置不同字体，如下，titleStyle是加粗的，默认字号大小为10;

HSSFCellStyle titleStyle = workbook.createCellStyle();
//设置字体
HSSFFont font =workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
font.setFontHeightInPoints((short)16); //字号
titleStyle.setFont(font);
HSSFCellStyle cellStyle = workbook.createCellStyle();

设置自动换行，setWrapText(true)；内容里加\r\n，在对应地方自动换行；不加\r\n按照单元格宽度自动换行；不调用setWrapText(true)方法，只在内容加\r\n，只有点开单元格时才换行

remarkStyle.setWrapText(true); //自动换行
cell.setCellValue("hello\r\n word");