java - 如何使用 apache poi 为 Excel 工作表中的特定行或标题添加背景颜色

Question

How to add background color for a specific row or header in excel sheets using apache poi in the code below which was given as an example in the official giude

package com.raz.api.engine.apiengine.util;
import java.io.File;    
import java.io.FileOutputStream;

import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class xlGen {
public static void main(String[] args) throws Exception {

  //Create blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook(); 

  //Create a blank sheet
  XSSFSheet spreadsheet = workbook.createSheet(" Employee Info ");

  //Create row object
  XSSFRow row;

  //Colors
  CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    //style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font font = workbook.createFont();
    font.setColor(IndexedColors.RED.getIndex());
    style.setFont(font);


  //This data needs to be written (Object[])
  Map < String, Object[] > empinfo = 
  new TreeMap < String, Object[] >();
  empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
  empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" });
  empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" });
  empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" });
  empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" });
  empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" });

  //Iterate over data and write to sheet
  Set < String > keyid = empinfo.keySet();
  int rowid = 0;

  for (String key : keyid) {
     row = spreadsheet.createRow(rowid++);
     Object [] objectArr = empinfo.get(key);
     int cellid = 0;

     for (Object obj : objectArr) {
        Cell cell = row.createCell(cellid++);
        cell.setCellValue((String)obj);
     }
  }
  //cell1.setCellStyle(style);


  //Write the workbook in file system
  FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println("Writesheet.xlsx written successfully");
}
}

do not read this paragraph below.. So i need to give some color for the header and i want to learn some features and methods which is used in the apache poi In this page we will learn how to set color in our XLSX. Generally we need to set background color and font color for our rows and cell in excel file. Setting color can be used as heading or column name that increases the readability of excel file. In this example we will understand from scratch how to color in XLSX. All we need is to get an instance of CellStyle and then set the desired color to CellStyle and then assign it to XLSX cell. Create a XSSFWorkbook. Get CellStyle from XSSFWorkbook as below.

Answer 1

总是值得阅读:Busy Developers' Guide to HSSF and XSSF Features .

在你的情况下尤其是 Fills and colors .

你的问题的文字不太清楚。但我怀疑从您的代码示例中，需要为标题单元格设置特殊样式。

需要在工作簿级别创建CellStyle。然后，在创建单元格时，需要使用 Cell.setCellStyle 将该 CellStyle 应用于适当的单元格。

以下示例使用当前的 apache poi 4.1.1:

import java.io.File;    
import java.io.FileOutputStream;

import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlGen {

 public static void main(String[] args) throws Exception {

  //Create blank workbook
  Workbook workbook = new XSSFWorkbook(); 

  //Create cell style for header row
  CellStyle style = workbook.createCellStyle();
  style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  Font font = workbook.createFont();
  font.setColor(IndexedColors.YELLOW.getIndex());
  style.setFont(font);

  //Create a blank sheet
  Sheet spreadsheet = workbook.createSheet(" Employee Info ");

  //This data needs to be written (Object[])
  Map < String, Object[] > empinfo = 
  new TreeMap < String, Object[] >();
  empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
  empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" });
  empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" });
  empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" });
  empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" });
  empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" });

  //Iterate over data and write to sheet
  Set < String > keyid = empinfo.keySet();
  int rowid = 0;

  //Create row object
  Row row;
  //Create cell object
  Cell cell;

  for (String key : keyid) {
   row = spreadsheet.createRow(rowid++);
   Object [] objectArr = empinfo.get(key);
   int cellid = 0;

   for (Object obj : objectArr) {
    cell = row.createCell(cellid++);
    cell.setCellValue((String)obj);

    if (rowid == 1) { // it is header row
     cell.setCellStyle(style); // set style for header cells
    }
   }
  }

  for (int c = 0; c < empinfo.get("1").length; c++) {
   spreadsheet.autoSizeColumn(c);
  }

  //Write the workbook in file system
  FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println("Writesheet.xlsx written successfully");
 }

}

结果: