在工作中经常会遇到列表数据的导出，每次需要的时候都要去开发一次，且数据不断在变化，于是就有了下述的工具类，可传入各种实体对象的List，最终以指定格式导出excel，废话不多说，上代码~
控制层代码
@PostMapping("/test")
public void test(HttpServletResponse response) throws Exception {
//查询任意数据列表
List<ErrorLog> list = errorLogService.selectListByInfo();
//导出操作
CommonExcelUtils.exportDynamicsData(response, list, "日志", "日志数据");
}此处的list可以是任意数据，也可以是任意SQL组装的list数据，标题会以sql的别名为准.
CommonExcelUtils工具类
/**
* 动态列表导出
* @param response
* @param list 数据list
* @param sheetName 页签名称，也是总标题的名称
* @param fileName 导出文件名
*/
@SuppressWarnings("all")
public static String exportDynamicsData(HttpServletResponse response,
List list, String sheetName, String fileName) throws IOException {
//将list数据转成指定类型
List<LinkedHashMap<String, Object>> data = CommonBeanUtils.convertListToMap(list);
List<List> rows = new ArrayList<>();//excel导出整体数据
List<String> titles = new ArrayList<>();//excel导出标题（首行）
List<String> title = new ArrayList<>();
title.add(sheetName);
rows.add(title);
//组装标题
LinkedHashMap<String,Object> m = (LinkedHashMap<String,Object>) data.get(0);
Set<String> keySet = m.keySet();
for (String t : keySet) {
titles.add(t);
}
rows.add(titles);
//组装数据
for (LinkedHashMap<String,Object> info : data) {
List d = new ArrayList<>();
Set<Entry<String, Object>> entrySet = info.entrySet();
for (Entry<String, Object> da : entrySet) {
d.add(da.getValue());
}
rows.add(d);
}
fileName = fileName +"-"+ DateUtils.parseDateToStr("yyMMdd", new Date()) +".xlsx";//导出文件名称
//声明一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()-1));//合并第一行的单元格，作标题
sheet.setDefaultColumnWidth(14); //设置表格列宽度
//导出操作
ExcelUtil.exportExcel(response, rows, workbook, sheet, fileName);
return fileName;
}CommonBeanUtils工具类
/**
* List转Map
* @param <T>
* @param list
* @return
*/
public static <T> List<LinkedHashMap<String, Object>> convertListToMap(List<T> list) {
return list.stream()
.map(CommonBeanUtils::objectToMap)
.collect(Collectors.toList());
}
/**
* object 转 Map
* @param <T>
* @param object
* @return
*/
private static <T> LinkedHashMap<String, Object> objectToMap(T object) {
LinkedHashMap<String, Object> map = new LinkedHashMap<>();
for (Field field : object.getClass().getDeclaredFields()) {
field.setAccessible(true);
try {
map.put(field.getName(), field.get(object));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return map;
}ExcelUtil工具类
import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelUtil<T>
{
/**
* 自定义导出
* @param response
* @param excelData
* @param sheetName
* @param fileName
* @param columnWidth
* @throws IOException
*/
public static void exportExcel(HttpServletResponse response,List<List> excelData,XSSFWorkbook workbook, XSSFSheet sheet,String fileName) throws IOException {
//设置单元格字体
XSSFFont fontName = workbook.createFont();
fontName.setFontName("宋体");
//写入List<List<String>>中的数据
int rowIndex = 0;
int rowFlag = -2;
XSSFCellStyle createTitleCellStyle = createTitleCellStyle(workbook);
XSSFCellStyle createTableCellStyle = createTableCellStyle(workbook);
XSSFCellStyle rightRowCellStyle = createRightRowCellStyle(workbook);
XSSFCellStyle leftRowCellStyle = createLeftRowCellStyle(workbook);
for(List data : excelData){
if(rowFlag == 8) rowFlag = 0;
rowFlag++;
//创建一个row行，然后自增1
XSSFRow row = sheet.createRow(rowIndex++);
if(rowIndex==1) row.setHeight((short)(20*30));
//遍历添加本行数据
for (int i = 0; i < data.size(); i++) {
//创建一个单元格
XSSFCell cell = row.createCell(i);
//value单元格值
Object value = data.get(i);
//设置第一个行标题的样式
if(i==0 && rowIndex==1) {
cell.setCellStyle(createTitleCellStyle);
}
//设置表头样式
if(rowIndex==2) {
cell.setCellStyle(createTableCellStyle);
}
if(rowIndex>2) {
//如果是数字类型，则字体向右对齐
if(value instanceof BigDecimal || value instanceof Integer) {
row.getCell(i).setCellStyle(rightRowCellStyle);
}else {
row.getCell(i).setCellStyle(leftRowCellStyle);
}
}
//将内容对象的文字内容写入到单元格中（单独处理数值类型）
if(value instanceof BigDecimal) {
BigDecimal v = (BigDecimal)value;
cell.setCellValue(v.doubleValue());
}else {
cell.setCellValue(String.valueOf(value));
}
}
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中，供页面下载该Excel文件
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();
}
/**
* 设置标题单元样式
*
* @param workbook
* @return
*/
public static XSSFCellStyle createTitleCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 20);
font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);// 设置列标题样式
XSSFColor color = new XSSFColor();
//根据你需要的rgb值获取byte数组
color.setRGB(intToByteArray(getIntFromColor(255,231,228)));
//设置自定义背景颜色
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置字体垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
return cellStyle;
}
/**
* 设置表头单元样式
*
* @param workbook
* @return
*/
public static XSSFCellStyle createTableCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);// 设置列标题样式
XSSFColor color = new XSSFColor();
//根据你需要的rgb值获取byte数组
color.setRGB(intToByteArray(getIntFromColor(251,241,227)));
//设置自定义背景颜色
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
return cellStyle;
}
/**
* 设置内容单元样式
*
* @param workbook
* @return
*/
public static XSSFCellStyle createRowCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFColor color = new XSSFColor();
//根据你需要的rgb值获取byte数组
color.setRGB(intToByteArray(getIntFromColor(220,220,220)));
//设置自定义背景颜色
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
XSSFColor borderColor = new XSSFColor();
//设置字体水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置字体垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//根据你需要的rgb值获取byte数组
borderColor.setRGB(intToByteArray(getIntFromColor(181,181,181)));
cellStyle.setLeftBorderColor(borderColor);
cellStyle.setRightBorderColor(borderColor);
cellStyle.setBottomBorderColor(borderColor);
cellStyle.setTopBorderColor(borderColor);
return cellStyle;
}
/**
* 设置内容单元样式(字体靠右对齐)
* 数字类型
* @param workbook
* @return
*/
public static XSSFCellStyle createRightRowCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
//设置字体水平居中
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//设置字体垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
/**
* 设置内容单元样式(字体靠左对齐)
* 文本类型
* @param workbook
* @return
*/
public static XSSFCellStyle createLeftRowCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
//设置字体位置
cellStyle.setAlignment(HorizontalAlignment.LEFT);
//设置字体垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
/**
* rgb转int
*/
private static int getIntFromColor(int Red, int Green, int Blue){
Red = (Red << 16) & 0x00FF0000;
Green = (Green << 8) & 0x0000FF00;
Blue = Blue & 0x000000FF;
return 0xFF000000 | Red | Green | Blue;
}
/**
* int转byte[]
*/
public static byte[] intToByteArray(int i) {
byte[] result = new byte[4];
result[0] = (byte)((i >> 24) & 0xFF);
result[1] = (byte)((i >> 16) & 0xFF);
result[2] = (byte)((i >> 8) & 0xFF);
result[3] = (byte)(i & 0xFF);
return result;
}
}
最终导出效果：
