如何使用apache poi为excel工作表中的特定行或页眉添加背景色

Question

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");
}
}

Answer 1

总是很好读：繁忙的开发人员HSSF和XSSF特性指南。

在你的情况下，特别是填充和颜色。

你问题的内容不太清楚。但从您的代码示例来看，我怀疑需要为标题单元格设置一个特殊的样式。

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");
 }

}