当前位置：首页 > news >正文

Java导出Excel给每一列设置不同样式示例

news 来源：原创 2024/9/19 9:25:39

Excel导出这里不讲，方法很多，原生的POI可以参照 Java原生POI实现的Excel导入导出（简单易懂）
这里只说怎么给Excel每一列设置不同的样式，比如下面这样的

在这里插入图片描述

直接上代码

@Overridepublic void exportTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception {try (Workbook wb = new SXSSFWorkbook(1000)) {// 构建一个导出页Sheet sheet = wb.createSheet("用户信息表");CellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setWrapText(true);// 自动换行Font font = wb.createFont();font.setFontName("宋体");font.setFontHeightInPoints((short) 11);style.setFont(font);DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat("@")); // 设置为文本格式sheet.setDefaultRowHeight((short) 500);// 构建excel抬头Row row0 = sheet.createRow((int) 0); // 第一行Cell cell0_0 = row0.createCell(0);cell0_0.setCellValue("序号");cell0_0.setCellStyle(style);sheet.setColumnWidth(0, 15 * 256);// 列宽sheet.setDefaultColumnStyle(0, style);Cell cell0_1 = row0.createCell(1);cell0_1.setCellValue("姓名");cell0_1.setCellStyle(style);sheet.setColumnWidth(1, 15 * 256);// 列宽sheet.setDefaultColumnStyle(1, style);Cell cell0_2 = row0.createCell(2);cell0_2.setCellValue("国家标识码");cell0_2.setCellStyle(style);sheet.setColumnWidth(2, 15 * 256);// 列宽sheet.setDefaultColumnStyle(2, style);Cell cell0_3 = row0.createCell(3);cell0_3.setCellValue("手机号");cell0_3.setCellStyle(style);sheet.setColumnWidth(3, 15 * 256);// 列宽sheet.setDefaultColumnStyle(3, style);Cell cell0_4 = row0.createCell(4);cell0_4.setCellValue("身份证号");cell0_4.setCellStyle(style);sheet.setColumnWidth(4, 20 * 256);// 列宽sheet.setDefaultColumnStyle(4, style);Cell cell0_5 = row0.createCell(5);cell0_5.setCellValue("部门名称（选填）");cell0_5.setCellStyle(style);sheet.setColumnWidth(5, 20 * 256);// 列宽sheet.setDefaultColumnStyle(5, style);Cell cell0_6 = row0.createCell(6);cell0_6.setCellValue("职位（选填，多个用英文/分隔）");cell0_6.setCellStyle(style);sheet.setColumnWidth(6, 35 * 256);// 列宽sheet.setDefaultColumnStyle(6, style);// 构建示例Row row1 = sheet.createRow((int) 1); // 第二行CellStyle style1 = wb.createCellStyle();style1.setAlignment(HorizontalAlignment.CENTER);style1.setVerticalAlignment(VerticalAlignment.CENTER);style1.setBorderBottom(BorderStyle.THIN);style1.setBorderLeft(BorderStyle.THIN);style1.setBorderTop(BorderStyle.THIN);style1.setBorderRight(BorderStyle.THIN);style1.setWrapText(true);// 自动换行style1.setFont(font);DataFormat format1 = wb.createDataFormat();style1.setDataFormat(format1.getFormat("@")); // 设置为文本格式style1.setFillForegroundColor((short) 13);// 设置背景色style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);Cell cell1_0 = row1.createCell(0);cell1_0.setCellValue("示例（保留）");cell1_0.setCellStyle(style1);Cell cell1_1 = row1.createCell(1);cell1_1.setCellValue("张三");cell1_1.setCellStyle(style1);Cell cell1_2 = row1.createCell(2);cell1_2.setCellValue("+86");cell1_2.setCellStyle(style1);Cell cell1_3 = row1.createCell(3);cell1_3.setCellValue("13700002222");cell1_3.setCellStyle(style1);Cell cell1_4 = row1.createCell(4);cell1_4.setCellValue("452186200001010001");cell1_4.setCellStyle(style1);Cell cell1_5 = row1.createCell(5);cell1_5.setCellValue("技术部");cell1_5.setCellStyle(style1);Cell cell1_6 = row1.createCell(6);cell1_6.setCellValue("科室主任/细胞生物学家/博士生导师");cell1_6.setCellStyle(style1);// 构建第二列Row row2 = sheet.createRow((int) 2); // 第一行Cell cell2_0 = row2.createCell(0);cell2_0.setCellValue("1");cell2_0.setCellStyle(style);Cell cell2_1 = row2.createCell(1);cell2_1.setCellValue("");cell2_1.setCellStyle(style);Cell cell2_2 = row2.createCell(2);cell2_2.setCellValue("");cell2_2.setCellStyle(style);Cell cell2_3 = row2.createCell(3);cell2_3.setCellValue("");cell2_3.setCellStyle(style);Cell cell2_4 = row2.createCell(4);cell2_4.setCellValue("");cell2_4.setCellStyle(style);Cell cell2_5 = row2.createCell(5);cell2_5.setCellValue("");cell2_5.setCellStyle(style);Cell cell2_6 = row2.createCell(6);cell2_6.setCellValue("");cell2_6.setCellStyle(style);String fileName = "用户信息表.xlsx";if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器} else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {fileName = URLEncoder.encode(fileName, "UTF-8");// IE浏览器} else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// 谷歌}response.reset();response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  response.setHeader("Content-Disposition", "attachment; filename=" + fileName);wb.write(response.getOutputStream());response.getOutputStream().close();}}