代码写的可能不太好,大侠勿喷,欢迎大家提出宝贵意见和建议!!!
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> 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 效果图
文章图片
调用借口实例:
/**
* 第三方入账明细导出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> 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 payLogMXVos = JsonUtil.readJsonList(JsonUtil.toString(result), PayLogMXVo.class);
for (PayLogMXVo vo : payLogMXVos) {
List 附加转载帖之背景色设置:
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);
【最近在写导出excel表格的代码,发现网上大部分代码都是导出简单格式的excel,所以自己顺便就写了一个导出复杂表格的工具类,有些代码是借鉴网友的,同时也谢谢分享代码的朋友们.】
对应表格背景颜色:
文章图片
