poi导出excel设置样式

朱雀 2020-10-01 02:19 1086阅读 0赞

示例代码

/**
    	 *
    	 * @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");