代码写的可能不太好,大侠勿喷,欢迎大家提出宝贵意见和建议!!!
package com.cetripnet.utils;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
/**
* POI导出excel表格
* @author lv617
* @version 1.0
*/
public class GenerateXmlUtil {
private static SimpleDateFormat DATE_FORAMT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 导出最精简的excel表格
* @param headers
* @param datas
* @param name
* @return
*/
public static Workbook generateCreateXsl(String headers[], List<List<Object>> datas, String name) {
if (headers == null || headers.length < 1 || CollectionUtils.isEmpty(datas) || !StringUtils.hasText(name)) {
return null;
}
HSSFWorkbook work = new HSSFWorkbook();
HSSFSheet sheet = work.createSheet(name);
// 设置标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (List<Object> data : datas) {
row = sheet.createRow(datas.indexOf(data) + 1);
for (int i = 0; i < data.size(); i++) {
HSSFCell cell = row.createCell(i);
GenerateXmlUtil.setValue(cell, data.get(i));
}
}
return work;
}
/**
* 导出复杂的excel表格(自定义格式)
* @param listHeaders 复杂表头数据(结合合并单元格使用,实现复杂表头)
* @param datas 表格内容数据
* @param name 表格中sheet的名称
* @param merges 合并单元格; 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
* @param subheads 将表格数据设置为副表头; 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
* @param hFontName 表格表头字体格式
* @param dFontName 表格内容字体格式
* @param hSize 表格表头字体大小
* @param dSize 表格内容字体大小
* @param hBold 表头字体加粗
* @param dBold 内容字体加粗
* @param hCenter 表头字体居中
* @param dCenter 内容字体居中
* @param hForegroundColor 表头背景色; 例:红色,黄色,绿色,蓝色,紫色,灰色.
* @param dForegroundColor 内容背景色; 例:红色,黄色,绿色,蓝色,紫色,灰色.
* @param hWrapText 表头自动换行
* @param dWrapText 内容自动换行
* @param borderBottom 表格添加边框
* @param autoWidths 设置自适应列宽的最大展示宽度
* @param columnWidths 自定义列宽设置; 例:参数1:哪一列 参数2:列宽值
* @param rowHeights 自定义行高设置; 例:参数1:哪一行 参数2:行高值
* @return
*/
public static Workbook exportComplexExcel(List<String[]> listHeaders, List<List<Object>> datas, String name, List<Integer[]> merges, List<Integer[]> subheads, String hFontName, String dFontName,
Integer hSize, Integer dSize, boolean hBold, boolean dBold, boolean hCenter, boolean dCenter, String hForegroundColor, String dForegroundColor, boolean hWrapText, boolean dWrapText,
boolean borderBottom, Integer autoWidths, List<Integer[]> columnWidths, List<Integer[]> rowHeights) {
if (listHeaders == null || listHeaders.get(0) == null || listHeaders.get(0).length < 1 || CollectionUtils.isEmpty(datas) || !StringUtils.hasText(name)) {
return null;
}
HSSFWorkbook work = new HSSFWorkbook();
HSSFSheet sheet = work.createSheet(name);
// 设置列宽
if (columnWidths != null && columnWidths.size() > 0 && columnWidths.get(0).length == 2) {
for (Integer[] columnWidth : columnWidths) {
sheet.setColumnWidth(columnWidth[0], columnWidth[1] * 256);
}
}
// 设置自适应列宽
if (autoWidths != null) {
List<Integer> maxCalls = GenerateXmlUtil.getMaxCall(listHeaders, datas);
for (int i = 0, j = maxCalls.size(); i < j; i++) {
// 最大列宽设置
if (maxCalls.get(i) > autoWidths) {
sheet.setColumnWidth(i, autoWidths * 256);
} else {
sheet.setColumnWidth(i, maxCalls.get(i) * 256);
}
}
}
// 设置合并单元格
if (merges != null && merges.size() > 0 && merges.get(0).length == 4) {
// 设置合并单元格//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
for (Integer[] merge : merges) {
CellRangeAddress region = new CellRangeAddress(merge[0], merge[1], merge[2], merge[3]);
sheet.addMergedRegion(region);
}
}
// 设置行高
if (rowHeights != null && rowHeights.size() > 0 && rowHeights.get(0).length == 2) {
// 设置表头格式
HSSFCellStyle style = GenerateXmlUtil.setStyle(work, hFontName, hSize, hBold, hCenter, hForegroundColor, hWrapText, borderBottom);
// 设置 表格内容格式
HSSFCellStyle style2 = GenerateXmlUtil.setStyle(work, dFontName, dSize, dBold, dCenter, dForegroundColor, dWrapText, borderBottom);
for (Integer[] rowHeight : rowHeights) {
// 插入表格表头数据
GenerateXmlUtil.addHeader(listHeaders, sheet, style, rowHeight);
// 插入表格内容数据
GenerateXmlUtil.addData(listHeaders, datas, subheads, work, sheet, style2, rowHeight, style);
}
} else {
// 设置表头格式
HSSFCellStyle style = GenerateXmlUtil.setStyle(work, hFontName, hSize, hBold, hCenter, hForegroundColor, hWrapText, borderBottom);
// 设置 表格内容格式
HSSFCellStyle style2 = GenerateXmlUtil.setStyle(work, dFontName, dSize, dBold, dCenter, dForegroundColor, dWrapText, borderBottom);
// 插入表格表头数据
GenerateXmlUtil.addHeader(listHeaders, sheet, style, null);
// 插入表格内容数据
GenerateXmlUtil.addData(listHeaders, datas, subheads, work, sheet, style2, null, style);
}
return work;
}
/**
* 导出复杂的excel表格
* (复杂表头固定格式:表头字体,宋体12号,加粗,紫色背景色;内容字体,宋体10号;字体居中,自动换行,自适应列宽(最大30 ),表格加边框)
* @param listHeaders 复杂表头数据(结合合并单元格使用,实现复杂表头)
* @param datas 表格内容数据
* @param name 表格中sheet的名称
* @param merges 合并单元格; 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
* @param subheads 将表格数据设置为副表头; 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
* @return
*/
public static Workbook exportComplexExcel(List<String[]> listHeaders, List<List<Object>> datas, String name, List<Integer[]> merges, List<Integer[]> subheads) {
if (listHeaders == null || listHeaders.get(0) == null || listHeaders.get(0).length < 1 || CollectionUtils.isEmpty(datas) || !StringUtils.hasText(name)) {
return null;
}
HSSFWorkbook work = new HSSFWorkbook();
HSSFSheet sheet = work.createSheet(name);
// 设置自适应列宽
List<Integer> maxCalls = GenerateXmlUtil.getMaxCall(listHeaders, datas);
for (int i = 0, j = maxCalls.size(); i < j; i++) {
// 最大列宽设置
if (maxCalls.get(i) > 30) {
sheet.setColumnWidth(i, 30 * 256);
} else {
sheet.setColumnWidth(i, maxCalls.get(i) * 256);
}
}
// 设置标题行格式
HSSFCellStyle style = GenerateXmlUtil.setStyle(work, "宋体", 12, true, true, "紫色", true, true);
// 设置表格内容行格式
HSSFCellStyle style2 = GenerateXmlUtil.setStyle(work, "宋体", 10, false, true, null, true, true);
// 设置合并单元格
if (merges != null && merges.size() > 0 && merges.get(0).length == 4) {
// 设置合并单元格//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
for (Integer[] merge : merges) {
CellRangeAddress region = new CellRangeAddress(merge[0], merge[1], merge[2], merge[3]);
sheet.addMergedRegion(region);
}
}
// 插入表格表头数据
GenerateXmlUtil.addHeader(listHeaders, sheet, style, null);
// 插入表格内容数据
GenerateXmlUtil.addData(listHeaders, datas, subheads, work, sheet, style2, null, style);
return work;
}
/**
* 导出复杂的excel表格
* (单行表头固定格式:表头字体,宋体12号,加粗,紫色背景色;内容字体,宋体10号;字体居中,自动换行,自适应列宽(最大30 ),表格加边框)
* @param headers 单行表头数据
* @param datas 表格内容数据
* @param name 表格中sheet的名称
* @return
*/
public static Workbook exportComplexExcel(String headers[], List<List<Object>> datas, String name) {
if (headers == null || headers.length < 1 || CollectionUtils.isEmpty(datas) || !StringUtils.hasText(name)) {
return null;
}
HSSFWorkbook work = new HSSFWorkbook();
HSSFSheet sheet = work.createSheet(name);
List<String[]> listHeaders = new ArrayList<>();
listHeaders.add(headers);
// 设置自适应列宽
List<Integer> maxCalls = GenerateXmlUtil.getMaxCall(listHeaders, datas);
for (int i = 0, j = maxCalls.size(); i < j; i++) {
// 最大列宽设置
if (maxCalls.get(i) > 30) {
sheet.setColumnWidth(i, 30 * 256);
} else {
sheet.setColumnWidth(i, maxCalls.get(i) * 256);
}
}
// 设置标题行格式
HSSFCellStyle style = GenerateXmlUtil.setStyle(work, "宋体", 12, true, true, "紫色", true, true);
// 设置表格内容行格式
HSSFCellStyle style2 = GenerateXmlUtil.setStyle(work, "宋体", 10, false, true, null, true, true);
// 插入表格表头数据
for (int m = 0, n = listHeaders.size(); m < n; m++) {
HSSFRow row = sheet.createRow(m);
String[] header = listHeaders.get(m);
for (int i = 0; i < header.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(header[i]);
cell.setCellValue(text);
// 单元格格式设置
cell.setCellStyle(style);
}
}
// 插入表格内容数据
for (List<Object> data : datas) {
Integer rowNum = datas.indexOf(data) + listHeaders.size();
HSSFRow row = sheet.createRow(rowNum);
for (int i = 0, j = data.size(); i < j; i++) {
HSSFCell cell = row.createCell(i);
GenerateXmlUtil.setValue(cell, data.get(i));
// 单元格格式设置
cell.setCellStyle(style2);
}
}
return work;
}
/************************************* 手动分割线 ******************************************************/
/**
* 插入表格的表头数据
* @param listHeaders 复杂表头数据(结合合并单元格使用,实现复杂表头)
* @param sheet HSSFSheet
* @param style 表格格式
* @param rowHeight 设置行高; 参数1:哪一行 参数2:行高值
*/
private static void addHeader(List<String[]> listHeaders, HSSFSheet sheet, HSSFCellStyle style, Integer[] rowHeight) {
for (int m = 0, n = listHeaders.size(); m < n; m++) {
HSSFRow row = sheet.createRow(m);
if (rowHeight != null && rowHeight[0] == m) {
row.setHeightInPoints(rowHeight[1]);
} else {
// 设置默认行高
sheet.setDefaultRowHeightInPoints(100);
}
String[] headers = listHeaders.get(m);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
// 单元格格式设置
cell.setCellStyle(style);
}
}
}
/**
* 插入表格的内容数据
* @param listHeaders 复杂表头数据(结合合并单元格使用,实现复杂表头)
* @param datas 表格内容数据
* @param subheads 将表格数据设置为副表头; 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
* @param work HSSFWorkbook
* @param sheet HSSFSheet
* @param style2 表格格式
* @param rowHeight 设置行高; 参数1:哪一行 参数2:行高值
*/
private static void addData(List<String[]> listHeaders, List<List<Object>> datas, List<Integer[]> subheads, HSSFWorkbook work, HSSFSheet sheet, HSSFCellStyle style2, Integer[] rowHeight,
HSSFCellStyle style3) {
for (List<Object> data : datas) {
Integer rowNum = datas.indexOf(data) + listHeaders.size();
HSSFRow row = sheet.createRow(rowNum);
if (rowHeight != null && rowHeight[0] == rowNum) {
row.setHeightInPoints(rowHeight[1]);
} else {
// 设置默认行高
sheet.setDefaultRowHeightInPoints(50);
}
for (int i = 0, j = data.size(); i < j; i++) {
HSSFCell cell = row.createCell(i);
GenerateXmlUtil.setValue(cell, data.get(i));
// 设置副表头格式
if (subheads != null && subheads.size() > 0 && subheads.get(0).length == 4) {
// 设置副表头格式
for (Integer[] subhead : subheads) {
for (int w = 0, v = subhead.length; w < v; w++) {
for (int m = subhead[0], n = subhead[1]; m <= n; m++) {
if (m == rowNum) {
for (int x = subhead[2], y = subhead[3]; x <= y; x++) {
if (x == i) {
// 单元格格式设置
cell.setCellStyle(style3);
}
}
break;
}
// 单元格格式设置
cell.setCellStyle(style2);
}
}
}
} else {
// 单元格格式设置
cell.setCellStyle(style2);
}
}
}
}
/**
* 设置表格格式
* @param work HSSFWorkbook
* @param fontName 字体样式
* @param size 字体大小
* @param bold 加粗
* @param center 居中
* @param foregroundColor 背景色
* @param wrapText 自动换行
* @param borderBottom 边框
* @return
*/
private static HSSFCellStyle setStyle(HSSFWorkbook work, String fontName, Integer size, boolean bold, boolean center, String foregroundColor, boolean wrapText, boolean borderBottom) {
// 创建字体样式
HSSFFont font = work.createFont();
// 创建格式
HSSFCellStyle style = work.createCellStyle();
// 设置字体
if (fontName != null) {
font.setFontName("宋体");
}
// 设置字体加粗
if (bold) {
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
// 设置字体大小
if (size != null) {
// 设置字体高度
font.setFontHeightInPoints(size.shortValue());
// 设置字体宽度
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
// 设置字体格式
style.setFont(font);
// 设置居中
if (center) {
style.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
}
// 设置背景颜色
if (foregroundColor != null) {
if ("黄色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("绿色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("红色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("蓝色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("紫色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("灰色".equals(foregroundColor)) {
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
}
// 设置边框
if (borderBottom) {
style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
style.setBorderRight(CellStyle.BORDER_THIN);// 右边框
}
// 设置自动换行
if (wrapText) {
style.setWrapText(true);
}
return style;
}
/**
* 最大列宽
* @param headers
* @param datas
* @return
*/
private static List<Integer> getMaxCall(List<String[]> listHeaders, List<List<Object>> datas) {
// 创建最大列宽集合
List<Integer> maxCall = new ArrayList<>();
List<List<Integer>> lss = new ArrayList<>();
// 计算标题行数据的列宽
for (int i = 0, j = listHeaders.size(); i < j; i++) {
List<Integer> hls = new ArrayList<>();
for (int m = 0, n = listHeaders.get(i).length; m < n; m++) {
int length = listHeaders.get(i)[m].getBytes().length;
hls.add(length);
if (i == 0) {
// 最大列宽赋初值
maxCall.add(0);
}
}
lss.add(hls);
}
// 计算内容行数据的列宽
for (int i = 0, j = datas.size(); i < j; i++) {
List<Integer> dls = new ArrayList<>();
for (int m = 0, n = datas.get(i).size(); m < n; m++) {
Object obj = datas.get(i).get(m);
if (obj.getClass() == Date.class) {
// 日期格式类型转换
obj = GenerateXmlUtil.DATE_FORAMT.format(obj);
}
int length = obj.toString().getBytes().length;
dls.add(length);
}
lss.add(dls);
}
// 根据列宽计算出每列的最大宽度
for (int i = 0, j = lss.size(); i < j; i++) {
for (int m = 0, n = lss.get(i).size(); m < n; m++) {
Integer a = lss.get(i).get(m);
Integer b = maxCall.get(m);
if (a > b) {
maxCall.set(m, a);
}
}
}
return maxCall;
}
/**
* 表格数据类型转换
* @param cell
* @param value
*/
private static void setValue(Cell cell, Object value) {
if (value == null) {
cell.setCellValue("");
} else if (value.getClass() == String.class) {
cell.setCellValue((String) value);
} else if (value.getClass() == Integer.class) {
cell.setCellValue((Integer) value);
} else if (value.getClass() == Double.class) {
cell.setCellValue((Double) value);
} else if (value.getClass() == Date.class) {
cell.setCellValue(GenerateXmlUtil.DATE_FORAMT.format(value));
} else if (value.getClass() == Long.class) {
cell.setCellValue((Long) value);
}
}
/*TODO
private static String getValue(Cell cell) {
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getNumericCellValue());
}
return value;
}*/
}
效果图
调用借口实例:
/**
* 第三方入账明细导出excel表格
*
* @param begintime
* @param endtime
* @param p
* @param l
* @param payType
* @param k
* @param response
*/
@RequestMapping("exportPayLogExcel")
public void exportPayLogExcel(String begintime, String endtime, Integer p, Integer l, Integer payType, String k,
HttpServletResponse response) {
// 创建excel表序号参数
Long num = 0l;
// 逐页查询数据,将所有数据导出到excel表中(注:此方法中不传p,l参数,使用的是service层中,默认的第1页开始,每页显示50条)
Integer pp = 1;
Long tt = 1l;
List<List<Object>> datas = new ArrayList<>();
OutputStream out = null;
while (true) {
pp++;
// 查询数据库
ListResponse<?> listResponse = this.payApi.selectPayLog(begintime, endtime, p, l, payType, k);
// 获取总页数
Long total = listResponse.getTotal();
if (tt > 0) {
tt = total - pp;
} else {
break;
}
// 获取查询结果,数据列表
Object result = listResponse.getResult();
// 类型转换
if (result != null) {
List<PayLogMXVo> payLogMXVos = JsonUtil.readJsonList(JsonUtil.toString(result), PayLogMXVo.class);
for (PayLogMXVo vo : payLogMXVos) {
List<Object> data = new ArrayList<>();
// excel表序号设置
num++;
data.add(num);// 序号
data.add(vo.getEndTime());// 入账时间
data.add(vo.getNo());// 内部交易流水号
data.add(vo.getTradeno());// 第三方流水号
data.add(vo.getAmount());// 入账金额(元)
data.add(vo.getpType());// 第三方渠道
datas.add(data);
}
}
// 重新设置分页参数
p = pp;
}
// 设置表格表头
List<String[]> listHeaders = new ArrayList<>();
// String[] headers = new String[] { "序号", "入账时间", "内部交易流水号", "第三方流水号", "入账金额(元)", "第三方渠道" };
listHeaders.add(new String[] { "序号", "入账时间", "交易流水号", "", "入账金额(元)", "第三方渠道" });
listHeaders.add(new String[] { "", "", "内部交易流水号", "第三方流水号", "", "" });
// 设置合并单元格
List<Integer[]> merges = new ArrayList<>();
merges.add(new Integer[] { 0, 0, 2, 3 });
merges.add(new Integer[] { 0, 1, 0, 0 });
merges.add(new Integer[] { 0, 1, 1, 1 });
merges.add(new Integer[] { 0, 1, 4, 4 });
merges.add(new Integer[] { 0, 1, 5, 5 });
// 设置副表头
List<Integer[]> subheads = new ArrayList<>();
subheads.add(new Integer[] { 3, 3, 0, 5 });
// // 设置列宽
// List<Integer[]> columnWidths = new ArrayList<>();
// columnWidths.add(new Integer[] { 0, 50 });
// columnWidths.add(new Integer[] { 2, 50 });
// // 设置行高
// List<Integer[]> rowHeights = new ArrayList<>();
// rowHeights.add(new Integer[] { 0, 50 });
// 导出
try {
Workbook book = GenerateXmlUtil.exportComplexExcel(listHeaders, datas, "第三方入账明细", merges, subheads, "宋体",
"宋体", 12, 10, true, false, true, true, "紫色", "灰色", true, true, true, 30, null,null);
// Workbook book3 = GenerateXmlUtil.exportComplexExcel(listHeaders, datas, "第三方入账明细", merges, subheads, "宋体",
// "宋体", 12, 10, true, false, true, true, "紫色", "灰色", true, true, true, null, columnWidths,rowHeights);
// Workbook book1 = GenerateXmlUtil.exportComplexExcel(listHeaders, datas, "第三方入账明细", merges, subheads);
// Workbook book2 = GenerateXmlUtil.exportComplexExcel(headers, datas, "第三方入账明细");
String fileName = "第三方入账明细" + ".xls";
response.reset(); // 清空response
response.setContentType("application/x-msdownload");
response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
response.setCharacterEncoding("UTF-8");
out = response.getOutputStream();
book.write(out);
out.flush();
} catch (Exception e) {
ResponseUtil.text(TraceUtil.trace(e), response);
}
}
附加转载帖之背景色设置:
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X1");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X2");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 3);
cell.setCellValue("X3");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 4);
cell.setCellValue("X4");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 5);
cell.setCellValue("X5");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row2 = sheet.createRow((short) 2);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BROWN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 1);
cell.setCellValue("X6");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 2);
cell.setCellValue("X7");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 3);
cell.setCellValue("X8");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 4);
cell.setCellValue("X9");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 5);
cell.setCellValue("X10");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row3 = sheet.createRow((short) 3);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 1);
cell.setCellValue("X11");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_TEAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 2);
cell.setCellValue("X12");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 3);
cell.setCellValue("X13");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 4);
cell.setCellValue("X14");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 5);
cell.setCellValue("X15");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row4 = sheet.createRow((short) 4);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 1);
cell.setCellValue("X16");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 2);
cell.setCellValue("X17");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 3);
cell.setCellValue("X18");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 4);
cell.setCellValue("X19");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 5);
cell.setCellValue("X20");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row5 = sheet.createRow((short) 5);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 1);
cell.setCellValue("X21");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 2);
cell.setCellValue("X22");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 3);
cell.setCellValue("X23");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 4);
cell.setCellValue("X24");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 5);
cell.setCellValue("X25");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row6 = sheet.createRow((short) 6);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 1);
cell.setCellValue("X26");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 2);
cell.setCellValue("X27");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 3);
cell.setCellValue("X28");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 4);
cell.setCellValue("X29");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 5);
cell.setCellValue("X30");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row7 = sheet.createRow((short) 7);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIME.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 1);
cell.setCellValue("X31");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.MAROON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 2);
cell.setCellValue("X32");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.OLIVE_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 3);
cell.setCellValue("X33");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 4);
cell.setCellValue("X34");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORCHID.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 5);
cell.setCellValue("X35");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row8 = sheet.createRow((short) 8);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 1);
cell.setCellValue("X36");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PINK.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 2);
cell.setCellValue("X37");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PLUM.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 3);
cell.setCellValue("X38");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 4);
cell.setCellValue("X39");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 5);
cell.setCellValue("X40");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row9 = sheet.createRow((short) 9);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 1);
cell.setCellValue("X41");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 2);
cell.setCellValue("X42");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 3);
cell.setCellValue("X43");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TAN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 4);
cell.setCellValue("X44");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TEAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 5);
cell.setCellValue("X45");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row10 = sheet.createRow((short) 10);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 1);
cell.setCellValue("X46");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 2);
cell.setCellValue("X47");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 3);
cell.setCellValue("X48");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 3);
cell.setCellValue("X49");
cell.setCellStyle(style);
对应表格背景颜色: