java后端通过pio导出excel时，如何通过代码给单元格加样式

wscXue
459 阅读2分钟

POI部分已过时方法（设置对齐方式、设置字体颜色）的替换、向Excel中插入图片的示例代码...

过时方法示例：

HSSFWorkbook wb = new HSSFWorkbook();          HSSFSheet sheet = wb.createSheet("new   sheet");          HSSFCellStyle style = wb.createCellStyle(); // 样式对象          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平          /**字体begin*/          style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);          //背景颜色//          style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//          style.setBorderRight(HSSFCellStyle.BORDER_THIN);//          style.setBorderTop(HSSFCellStyle.BORDER_THIN);//          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);           //生成一个字体          HSSFFont font=wb.createFont();          font.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色          font.setFontHeightInPoints((short)12);          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗         //把字体应用到当前的样式          style.setFont(font);          /**字体end*/          HSSFRow row = sheet.createRow((short) 0);          HSSFRow row2 = sheet.createRow((short) 1);

新方法示例：

// 单元格格式    CellStyle cellStyle = excel.createCellStyle();    cellStyle.setRotation((short) 255);// 文字竖排列    cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中     // 单元格格式    CellStyle cellStyle2 = excel.createCellStyle();    cellStyle2.setWrapText(true);// 文字自动换行    cellStyle2.setVerticalAlignment(VerticalAlignment.TOP);// 向上对齐        // 单元格格式    Font font = excel.createFont();    font.setColor(IndexedColors.BLUE.getIndex());    font.setFontName("宋体");    CellStyle cellStyle3 = excel.createCellStyle();    cellStyle3.setAlignment(HorizontalAlignment.CENTER);// 左右居中    cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中    cellStyle3.setFont(font);

插入图片的示例：

/**   * 插入图片到Excel.   * @param dirName   * @param excel   */  private void insertPics(String dirName, Workbook excel) throws Exception {    File dir = new File(dirName);     FileInputStream[] input = null;    try {      File[] listFiles = dir.listFiles();      int len = listFiles.length;      input = new FileInputStream[len];      for (int i = 0; i < len; i++) {        Sheet sheet = excel.createSheet("图片" + (i + 1));         input[i] = new FileInputStream(listFiles[i]);         int pictureIdx = excel.addPicture(IOUtils.toByteArray(input[i]), Workbook.PICTURE_TYPE_PNG);         // 图片插入坐标        ClientAnchor anchor = excel.getCreationHelper().createClientAnchor();        anchor.setCol1(0);        anchor.setRow1(1);         // 插入图片        sheet.createDrawingPatriarch().createPicture(anchor, pictureIdx).resize();      }    } catch (FileNotFoundException e) {      throw new RuntimeException("向Excel插入图片时未找到目标图片", e);    } catch (IOException e) {      throw new RuntimeException("向Excel插入图片失败", e);    } finally {      if (input != null) {        input.close();      }    }  }

转载于:my.oschina.net/qw3670/blog…

用法： 先创建cellStyle

HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFWorkbook对象        //新方法示例:        HSSFCellStyle hssfCellStyle = wb.createCellStyle();        //hssfCellStyle.setRotation((short) 255);// 文字竖排列        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中        //设置边框线        //新方法--BorderStyle.THIN   //旧方法--HSSFCellStyle.THIN        hssfCellStyle.setBorderTop(BorderStyle.THIN);        hssfCellStyle.setBorderBottom(BorderStyle.THIN);        hssfCellStyle.setBorderLeft(BorderStyle.THIN);        hssfCellStyle.setBorderRight(BorderStyle.THIN);        // 单元格格式        HSSFCellStyle hssfCellStyle2 = wb.createCellStyle();        hssfCellStyle2.setWrapText(true);// 文字自动换行        hssfCellStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中        hssfCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中        //新方法--BorderStyle.THIN   //旧方法--HSSFCellStyle.THIN        hssfCellStyle2.setBorderTop(BorderStyle.THIN);        hssfCellStyle2.setBorderBottom(BorderStyle.THIN);        hssfCellStyle2.setBorderLeft(BorderStyle.THIN);        hssfCellStyle2.setBorderRight(BorderStyle.THIN);

再给行或单元格设置cellStyle

//在sheet里创建第二行        HSSFRow row2=sheet.createRow(0);//1        for(int i=0;i<titleList.size();i++){            row2.createCell(i).setCellValue(titleList.get(i));            //设置宽度            sheet.setColumnWidth(i, titleList.get(i).getBytes().length * 256);            //设置行高            row2.setHeight((short)500);            //设置标题行样式            //HSSFCellStyle cellStyle = wb.createCellStyle();            //cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());            //cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);            HSSFFont font = wb.createFont();            font.setBold(true);//加粗            hssfCellStyle.setFont(font);            row2.getCell(i).setCellStyle(hssfCellStyle);        }        //在sheet里创建第三行        for(int j=0;j<dataList.size();j++){            HSSFRow row3=sheet.createRow(j+1);//j+2            for(int i=0;i<titleList.size();i++){                row3.createCell(i).setCellValue(dataList.get(j).get(i));                row3.getCell(i).setCellStyle(hssfCellStyle2);            }            //整行加样式            //row3.setRowStyle(hssfCellStyle2);        }        //输出Excel文件        OutputStream output=response.getOutputStream();        response.reset();        //设置响应头        response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode(filename, "UTF-8"));        response.setContentType("application/msexcel");        //设置response返回编码        response.setCharacterEncoding("UTF-8");        wb.write(output);        output.close();

颜色与代码参考：

//关键点 IndexedColors.AQUA.getIndex() 对应颜色    hssfCellStyle.setFillForegroundColor(***IndexedColors.AQUA.getIndex()***);    //设置前景色后，可能需要添加以下行    //新版本设置方式    hssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //旧版本设置方式    hssfCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

image.png

IndexedColors.AQUA.getIndex()IndexedColors.AUTOMATIC.getIndex()IndexedColors.BLUE.getIndex()IndexedColors.BLUE_GREY.getIndex()IndexedColors.BRIGHT_GREEN.getIndex()IndexedColors.BROWN.getIndex()IndexedColors.CORAL.getIndex()IndexedColors.CORNFLOWER_BLUE.getIndex()IndexedColors.DARK_BLUE.getIndex()IndexedColors.DARK_GREEN.getIndex()IndexedColors.DARK_RED.getIndex()IndexedColors.DARK_TEAL.getIndex()IndexedColors.DARK_YELLOW.getIndex()IndexedColors.GOLD.getIndex()IndexedColors.GREEN.getIndex()IndexedColors.GREY_25_PERCENT.getIndex()IndexedColors.GREY_40_PERCENT.getIndex()IndexedColors.GREY_50_PERCENT.getIndex()IndexedColors.GREY_80_PERCENT.getIndex()IndexedColors.INDIGO.getIndex()IndexedColors.LAVENDER.getIndex()IndexedColors.LEMON_CHIFFON.getIndex()IndexedColors.LIGHT_BLUE.getIndex()IndexedColors.LEMON_CHIFFON.getIndex()IndexedColors.LIGHT_BLUE.getIndex()IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()IndexedColors.LIGHT_GREEN.getIndex()IndexedColors.LIGHT_ORANGE.getIndex()IndexedColors.LIGHT_TURQUOISE.getIndex()IndexedColors.LIGHT_YELLOW.getIndex()IndexedColors.LIME.getIndex()IndexedColors.MAROON.getIndex()IndexedColors.OLIVE_GREEN.getIndex()IndexedColors.ORANGE.getIndex()IndexedColors.ORCHID.getIndex()IndexedColors.PALE_BLUE.getIndex()IndexedColors.PINK.getIndex()IndexedColors.PLUM.getIndex()IndexedColors.RED.getIndex()IndexedColors.ROSE.getIndex()IndexedColors.ROYAL_BLUE.getIndex()IndexedColors.SEA_GREEN.getIndex()IndexedColors.SKY_BLUE.getIndex()IndexedColors.TAN.getIndex()IndexedColors.TEAL.getIndex()IndexedColors.TURQUOISE.getIndex()IndexedColors.VIOLET.getIndex()IndexedColors.WHITE.getIndex()IndexedColors.YELLOW.getIndex()
avatar
文章
阅读
粉丝