public void export(HttpServletResponse response) throws IOException {
//获取所有需要导出的sheet名称(班级)
ListcaseList = classMapper.getClassName();
// 定义整个 Excel 表格 book 对象
SXSSFWorkbook book = new SXSSFWorkbook();
int i = 0;
//循环sheet名称
for (Class class : caseList) {
List> sheetDataList = new ArrayList<>();
// 获取动态表头
List
SheetStyle类
package com.unisoc.ip.utils.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
public class SheetStyle {private static final String XLSX = ".xlsx";
public static final String ROW_MERGE = "row_merge";
public static final String COLUMN_MERGE = "column_merge";
private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
private static final int CELL_OTHER = 0;
private static final int CELL_ROW_MERGE = 1;
private static final int CELL_COLUMN_MERGE = 2;
public static CellStyle setHeadStyle(SXSSFWorkbook workbook) {
CellStyle cellstyle = workbook.createCellStyle();
// 设置颜色
cellstyle.setFillForegroundColor(IndexedColors.SEA_GREEN.index);
// 设置填充方式
cellstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 水平居中
cellstyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体
Font font = workbook.createFont();
// 字体
font.setFontName("微软雅黑");
// 字号
font.setFontHeightInPoints((short)10);
// 加粗
font.setBold(true);
cellstyle.setFont(font);
setBorderStyle(cellstyle);
return cellstyle;
}
public static CellStyle setDefaultCellStyle(SXSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体
Font font = workbook.createFont();
// 字体
font.setFontName("微软雅黑");
// 字号
font.setFontHeightInPoints((short)10);
style.setFont(font);
// style.setWrapText(true);//设置内容文本自动换行
setBorderStyle(style);
return style;
}
public static void setBorderStyle(CellStyle style) {
// 下边框
style.setBorderBottom(BorderStyle.THIN);
// 左边框
style.setBorderLeft(BorderStyle.THIN);
// 上边框
style.setBorderTop(BorderStyle.THIN);
// 右边框
style.setBorderRight(BorderStyle.THIN);
}
public static void setCellStyleYellow(CellStyle style) {
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
public static void setCellStyleLime(CellStyle style) {
style.setFillForegroundColor(IndexedColors.LIME.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
public static void setFontRedColor(XSSFWorkbook workbook, CellStyle style) {
// 字体
Font font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
}
public staticvoid dataOrder(List rowData, List table, Class cls, Object o) {
Field[] fields = cls.getDeclaredFields();
for (Object needName : table) {
String fieldName = (String)needName;
for (Field allFieldName : fields) {//判断实体类中的某个字段是否时需要导出的字段
if (allFieldName.getName().equals(fieldName)) {
// 暴力反射开启
allFieldName.setAccessible(true);
Object value = null;
try {
value = allFieldName.get(o);
// 获取字段的值
} catch (IllegalAccessException e) {
e.printStackTrace();
}
rowData.add(value);
}
}
}
}
public static void export(HttpServletResponse response, String fileName, String sheetName,
List> sheetDataList, Integer columnWidth) {
// 整个 Excel 表格 book 对象
SXSSFWorkbook book = new SXSSFWorkbook();
// 每个 Sheet 页
Sheet sheet = book.createSheet(sheetName);
CellStyle headStyle = setHeadStyle(book);
// 设置表身背景色(默认色)
CellStyle rowStyle = setDefaultCellStyle(book);
// 设置表格列宽度(默认为12个字节)
sheet.setDefaultColumnWidth(columnWidth);
// 创建合并算法数组
int rowLength = sheetDataList.size();
int columnLength = sheetDataList.get(0).size();
int[][] mergeArray = new int[rowLength][columnLength];// 创建一个DataFormat对象
DataFormat format = book.createDataFormat();for (int i = 0; i < sheetDataList.size(); i++) {
// 每个 Sheet 页中的行数据
Row row = sheet.createRow(i);
ListrowList = sheetDataList.get(i);
for (int j = 0; j < rowList.size(); j++) {
// 每个行数据中的单元格数据
Object o = rowList.get(j);
int v = 0;Cell cell = row.createCell(j);
if (o instanceof ExcelObject) {
ExcelObject eo = (ExcelObject)o;
if (eo.isBlank()) {
v = setCellValue(cell, eo.getO(), headStyle, format);
} else {
v = setCellValue(cell, eo.getO(), rowStyle, format);
}
} else {
if (i == 0) {
// 第一行为表头行
v = setCellValue(cell, o, headStyle, format);
} else {
// 其他行为数据行,默认白底色
v = setCellValue(cell, o, rowStyle, format);
}
}
mergeArray[i][j] = v;
}
}
// 写数据
if (response != null) {
// 前端导出
try {
write(response, book, fileName);
} catch (IOException e) {
e.printStackTrace();
}
}
}public static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX;
response.addHeader("Content-Disposition", "attachment;filename=" + name);
ServletOutputStream out = response.getOutputStream();
book.write(out);
out.flush();
out.close();
}private static int setCellValue(Cell cell, Object o, CellStyle style, DataFormat dataFormat) {
// 设置样式
style.setDataFormat(dataFormat.getFormat("@"));
cell.setCellStyle(style);
// 数据为空时
if (o == null) {
cell.setCellType(CellType.STRING);
cell.setCellValue("");
return CELL_OTHER;
}
// 是否为字符串
if (o instanceof String) {
String s = o.toString();
if (isNumeric(s)) {
cell.setCellType(CellType.STRING);
// cell.setCellValue(Double.parseDouble(s));
cell.setCellValue(s);
return CELL_OTHER;
} else {
cell.setCellType(CellType.STRING);
cell.setCellValue(s);
}
if (s.equals(ROW_MERGE)) {
return CELL_ROW_MERGE;
} else if (s.equals(COLUMN_MERGE)) {
return CELL_COLUMN_MERGE;
} else {
return CELL_OTHER;
}
}
// 是否为字符串
if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(Double.parseDouble(o.toString()));
return CELL_OTHER;
}
// 是否为Boolean
if (o instanceof Boolean) {
cell.setCellType(CellType.BOOLEAN);
cell.setCellValue((Boolean)o);
return CELL_OTHER;
}
// 如果是BigDecimal,则默认3位小数
if (o instanceof BigDecimal) {
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(((BigDecimal)o).setScale(3, RoundingMode.HALF_UP).doubleValue());
return CELL_OTHER;
}
// 如果是Date数据,则显示格式化数据
if (o instanceof Date) {
cell.setCellType(CellType.STRING);
cell.setCellValue(formatDate((Date)o));
return CELL_OTHER;
}
// 如果是其他,则默认字符串类型
cell.setCellType(CellType.STRING);
cell.setCellValue(o.toString());
return CELL_OTHER;
}private static String formatDate(Date date) {
if (date == null) {
return "";
}
SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
return format.format(date);
}private static boolean isNumeric(String str) {
if (str == null || str.isEmpty()) {
return false;
}
if ("0.0".equals(str)) {
return true;
}
for (int i = str.length(); --i >= 0;) {
if (!Character.isDigit(str.charAt(i))) {
return false;
}
}
return true;
}
public static void exports(SXSSFWorkbook book, HttpServletResponse response, String sheetName,
List> sheetDataList, Integer columnWidth) {
// 每个 Sheet 页
Sheet sheet = book.createSheet(sheetName);
CellStyle headStyle = setHeadStyle(book);
// 设置表身背景色(默认色)
CellStyle rowStyle = setDefaultCellStyle(book);
// 设置表格列宽度(默认为12个字节)
sheet.setDefaultColumnWidth(columnWidth);
// 创建合并算法数组
int rowLength = sheetDataList.size();
int columnLength = sheetDataList.get(0).size();
int[][] mergeArray = new int[rowLength][columnLength];// 创建一个DataFormat对象
DataFormat format = book.createDataFormat();for (int i = 0; i < sheetDataList.size(); i++) {
// 每个 Sheet 页中的行数据
Row row = sheet.createRow(i);
ListrowList = sheetDataList.get(i);
for (int j = 0; j < rowList.size(); j++) {
// 每个行数据中的单元格数据
Object o = rowList.get(j);
int v = 0;Cell cell = row.createCell(j);
if (o instanceof ExcelObject) {
ExcelObject eo = (ExcelObject)o;
if (eo.isBlank()) {
v = setCellValue(cell, eo.getO(), headStyle, format);
} else {
v = setCellValue(cell, eo.getO(), rowStyle, format);
}
} else {
if (i == 0) {
// 第一行为表头行
v = setCellValue(cell, o, headStyle, format);
} else {
// 其他行为数据行,默认白底色
v = setCellValue(cell, o, rowStyle, format);
}
}
mergeArray[i][j] = v;
}
}
}
}



