目录
本文主要是提供一些Apache POI 常用读取、写入功能的封装方法。
简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
POI 中主要提供的读写 Microsoft Office 功能点如下：
HSSF -- 提供读写Microsoft Excel格式档案的功能。
XSSF -- 提供读写Microsoft Excel OOXML格式档案的功能。
HWPF -- 提供读写Microsoft Word格式档案的功能。
HSLF -- 提供读写Microsoft PowerPoint格式档案的功能。
HDGF -- 提供读写Microsoft Visio格式档案的功能。
本文主要是提供一些Apache POI 常用读取、写入Excel功能的封装方法。
引入POI依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>使用注解配置Java类属性和Excel列名/工作簿名
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
/**
* 列索引
*/
int columnIndex() default 0;
/**
* 列名
*/
String columnName() default "未知列名";
/**
* 合并列数据
*/
boolean mergeColumnCell() default false;
}@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelSheet {
String value();
}POI工具类
import com.alibaba.excel.util.DateUtils;
import org.apache.commons.lang3.ArrayUtils;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class PoiUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(PoiUtil.class);
public static final String EXCEL_XLS = ".xls";
public static final String EXCEL_XLSX = ".xlsx";
/**
* 写入本地文件
*
* @param data 数据集
* @param path 文件路径
*/
public void write(List<?> data, String path) {
if (data.size() == 0) {
return;
}
write(data, data.getClass(), path);
}
/**
* 写入本地文件
*
* @param data 数据集
* @param clazz 列名基于实体注解
* @param path 文件路径
*/
public static <T> void write(List<?> data, Class<T> clazz, String path) {
createFile(path);
File file = new File(path);
Workbook workbook = writeWorkbook(data, clazz, file.getName());
try (FileOutputStream outputStream = new FileOutputStream(file)) {
assert workbook != null;
workbook.write(outputStream);
} catch (Exception e) {
LOGGER.error("写入Excel文件失败", e);
}
}
/**
* 多种数据写入同一个workbook
*/
public static Workbook writeWorkbook(List<List<?>> data, String fileName, Boolean isMultiSheet) {
int total = data.stream().mapToInt(List::size).sum();
Workbook workbook = buildWorkbook(fileName, total);
Integer startRowNum = 0;
if (isMultiSheet) {
for (int i = 0; i < data.size(); i++) {
startRowNum = 0;
List<?> list = data.get(i);
if (list.size() == 0) {
continue;
}
Class<?> clazz = list.get(0).getClass();
// 判断是否设置“工作铺名称”
String sheetName = "sheet" + (i + 1);
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
sheetName = clazz.getAnnotation(ExcelSheet.class).value();
}
Sheet sheet = workbook.createSheet(sheetName);
writeSheet(sheet, workbook, list, clazz, startRowNum);
}
} else {
// 判断是否设置“工作铺名称”
String sheetName = "sheet";
Sheet sheet = workbook.createSheet(sheetName);
for (List<?> list : data) {
if (list.size() == 0) {
continue;
}
Class<?> clazz = list.get(0).getClass();
writeSheet(sheet, workbook, list, clazz, startRowNum);
startRowNum += 4;
}
}
return workbook;
}
/**
* 写入Workbook
*
* @param data 数据集合
*/
public static Workbook writeWorkbook(List<?> data, List<String> fieldNames, String fileName) {
if (data.size() == 0 || fieldNames.size() == 0) {
return null;
}
Workbook workbook = buildWorkbook(fileName, data.size());
Class<?> aClass = data.get(0).getClass();
// 判断是否设置“工作铺名称”
String sheetName = "sheet";
if (aClass.isAnnotationPresent(ExcelSheet.class)) {
sheetName = aClass.getAnnotation(ExcelSheet.class).value();
}
Sheet sheet = workbook.createSheet(sheetName);
// 设置表头（列名）
Row row = sheet.createRow(0);
// 设置单元格样式
CellStyle headerStyle = buildHeaderStyle(workbook);
// 列索引
int columnIndex = 0;
// 列名称
String columnName;
ExcelField excelField;
for (String fieldName : fieldNames) {
Field field = existsField(aClass, fieldName);
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
// 获取注解
excelField = field.getAnnotation(ExcelField.class);
columnName = excelField.columnName();
// 创建单元格
createCell(row, columnIndex, columnName, headerStyle);
columnIndex++;
}
}
}
// 行索引 因为表头已经设置，索引行索引从1开始
int rowIndex = 1;
for (Object obj : data) {
// 创建新行，索引加1,为创建下一行做准备
row = sheet.createRow(rowIndex++);
columnIndex = 0;
for (String fieldName : fieldNames) {
Field field = existsField(aClass, fieldName);
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
try {
Object value = typeConvert(field.get(obj), field);
// 创建单元格 field.get(obj)从obj对象中获取值设置到单元格中
createCell(row, columnIndex, value, null);
} catch (IllegalAccessException e) {
LOGGER.error("对象非法访问异常", e);
}
columnIndex++;
}
}
}
}
return workbook;
}
/**
* 写入Workbook
*
* @param data 数据集合
*/
private static <T> Workbook writeWorkbook(List<?> data, Class<T> clazz, String fileName) {
if (data.size() == 0) {
return null;
}
Workbook workbook = buildWorkbook(fileName, data.size());
// 判断是否设置“工作铺名称”
String sheetName = "sheet";
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
sheetName = clazz.getAnnotation(ExcelSheet.class).value();
}
Sheet sheet = workbook.createSheet(sheetName);
// 设置表头（列名）
Row row = sheet.createRow(0);
CellStyle headerStyle = buildHeaderStyle(workbook);
// 列索引
int columnIndex = 0;
// 列名称
String columnName;
ExcelField excelField;
Field[] declaredFields = clazz.getDeclaredFields();
for (Field field : declaredFields) {
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
// 获取注解
excelField = field.getAnnotation(ExcelField.class);
columnName = excelField.columnName();
// 创建单元格
createCell(row, columnIndex, columnName, headerStyle);
columnIndex++;
}
}
}
// 行索引 因为表头已经设置，索引行索引从1开始
int rowIndex = 1;
JsonFormat jsonFormat;
for (Object obj : data) {
// 创建新行，索引加1,为创建下一行做准备
row = sheet.createRow(rowIndex++);
columnIndex = 0;
for (Field field : declaredFields) {
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
try {
Object value = typeConvert(field.get(obj), field);
// 创建单元格 field.get(obj)从obj对象中获取值设置到单元格中
createCell(row, columnIndex, value, null);
} catch (IllegalAccessException e) {
LOGGER.error("对象非法访问异常", e);
}
columnIndex++;
}
}
}
}
return workbook;
}
/**
* 写入workbook，可重复传入使用
*
* @param data 数据集合
*/
private static <T> Workbook writeWorkbook(List<?> data, Class<T> clazz, List<String> fieldNames, String fileName, Workbook workbook) {
Sheet sheet;
Row row;
// 行索引
int rowIndex = 1;
// 列索引
int columnIndex = 0;
if (workbook == null) {
workbook = buildWorkbook(fileName, data.size());
// 判断是否设置“工作铺名称”
String sheetName = "sheet";
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
sheetName = clazz.getAnnotation(ExcelSheet.class).value();
}
sheet = workbook.createSheet(sheetName);
// 设置表头（列名）
row = sheet.createRow(0);
// 设置样式
CellStyle headerStyle = buildHeaderStyle(workbook);
// 列名称
String columnName;
ExcelField excelField;
for (String fieldName : fieldNames) {
Field field = existsField(clazz, fieldName);
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
// 获取注解
excelField = field.getAnnotation(ExcelField.class);
columnName = excelField.columnName();
// 创建单元格
createCell(row, columnIndex, columnName, headerStyle);
columnIndex++;
}
}
}
} else {
sheet = workbook.getSheetAt(0);
rowIndex = sheet.getLastRowNum() + 1;
}
for (Object obj : data) {
// 创建新行，索引加1,为创建下一行做准备
row = sheet.createRow(rowIndex++);
columnIndex = 0;
for (String fieldName : fieldNames) {
Field field = existsField(clazz, fieldName);
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
try {
// 创建单元格 field.get(obj)从obj对象中获取值设置到单元格中
createCell(row, columnIndex, field.get(obj), null);
} catch (IllegalAccessException e) {
LOGGER.error("对象非法访问异常", e);
}
columnIndex++;
}
}
}
}
return workbook;
}
/**
* 读取Excel文件内容
*
* @param path 读取Excel文件的路径
* @param clazz 返回的实体类泛型
* @param <T> 泛型
* @return 文件内容
*/
public static <T> List<T> read(String path, Class<T> clazz) {
return read(path, clazz, 0);
}
/**
* 读取Excel文件内容
*
* @param path 读取Excel文件的路径
* @param clazz 返回的实体类泛型
* @param sheetNum sheet下标
* @param <T> 泛型
* @return 文件内容
*/
public static <T> List<T> read(String path, Class<T> clazz, int sheetNum) {
File file = new File(path);
try (FileInputStream inputStream = new FileInputStream(file)) {
return read(inputStream, file.getName(), clazz, sheetNum);
} catch (Exception e) {
LOGGER.error("读取Excel文件失败", e);
return Collections.emptyList();
}
}
/**
* 读取Excel文件内容
*
* @param inputStream 读取Excel文件流
* @param clazz 返回的实体类泛型
* @param <T> 泛型
* @return 文件内容
*/
public static <T> List<T> read(InputStream inputStream, String fileName, Class<T> clazz) {
return read(inputStream, fileName, clazz, 0);
}
/**
* 读取Excel文件内容
*
* @param inputStream 读取Excel文件流
* @param clazz 返回的实体类泛型
* @param sheetNum sheet下标
* @param <T> 泛型
* @return 文件内容
*/
public static <T> List<T> read(InputStream inputStream, String fileName, Class<T> clazz, int sheetNum) {
Workbook workbook = readWorkbook(inputStream, fileName);
assert workbook != null;
Sheet sheet = workbook.getSheetAt(sheetNum);
// 判断是否设置“工作铺名称”
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
String sheetName = clazz.getAnnotation(ExcelSheet.class).value();
sheet = workbook.getSheet(sheetName);
}
// 获取总行数
int rows = sheet.getLastRowNum() + 1;
// 获取列名和列索引关系
Map<String, Integer> columnMap = getColumnMap(sheet, clazz);
// 获取类所有属性
Field[] fields = getAllField(clazz);
ExcelField excelField;
// 存储读取到的excel数据
List<T> list = new ArrayList<>();
T t;
Row row;
Cell cell;
int columnIndex;
// 遍历Excel每行
for (int i = 1; i < rows; i++) {
row = sheet.getRow(i);
// 判断空行
if (isRowEmpty(row)) {
continue;
}
try {
t = clazz.newInstance();
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
excelField = field.getAnnotation(ExcelField.class);
columnIndex = columnMap.get(excelField.columnName());
if (columnIndex != -1) {
cell = row.getCell(columnIndex);
setFieldValue(t, field, cell);
}
}
}
list.add(t);
} catch (Exception e) {
LOGGER.error("读取Excel文件失败", e);
}
}
try {
workbook.close();
} catch (IOException e) {
LOGGER.error("关闭workbook失败", e);
}
return list;
}
/**
* 读取Excel文件内容
*/
public static <T> List<T> read(String path, String[] fields, Class<T> clazz, int startRow) {
List<T> content = new ArrayList<>();
T t;
File file = new File(path);
Workbook workbook = readWorkbook(file);
assert workbook != null;
Sheet sheet = workbook.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
for (int i = startRow; i <= rowNum; i++) {
row = sheet.getRow(i);
// 判断空行
if (isRowEmpty(row)) {
continue;
}
int j = 0;
try {
t = clazz.newInstance();
while (j < colNum) {
//当 j 等于传入的 fields 数组长度时 跳过循环 防止出现数组越界异常
if (j == fields.length) {
break;
}
Field declaredField = clazz.getDeclaredField(fields[j]);
declaredField.setAccessible(true);
setFieldValue(t, declaredField, row.getCell(j));
j++;
}
content.add(t);
} catch (InstantiationException | IllegalAccessException | NoSuchFieldException e) {
LOGGER.error("读取Excel文件失败", e);
}
}
return content;
}
/**
* 读取Excel文件内容
*
* @param path 读取Excel文件的路径
* @return 文件内容Map集合
*/
public static List<Map<String, Object>> read(String path) {
File file = new File(path);
Workbook workbook = readWorkbook(file);
assert workbook != null;
Sheet sheet = workbook.getSheetAt(0);
// 获取总行数
int rows = sheet.getLastRowNum() + 1;
// 获取所有列名
Row columnNameRow = sheet.getRow(0);
String[] columnNameArr = new String[columnNameRow.getLastCellNum()];
for (int i = 0; i < columnNameArr.length; i++) {
columnNameArr[i] = columnNameRow.getCell(i).getStringCellValue();
}
// 存储读取到的excel数据
List<Map<String, Object>> list = new ArrayList<>();
Map<String, Object> data;
Row row;
// 遍历Excel每行
for (int i = 1; i < rows; i++) {
row = sheet.getRow(i);
// 判断空行
if (isRowEmpty(row)) {
continue;
}
try {
data = new HashMap<>();
for (int j = 0; j < columnNameArr.length; j++) {
data.put(columnNameArr[j], getCellValue(row.getCell(j)));
}
list.add(data);
} catch (Exception e) {
LOGGER.error("读取Excel文件失败", e);
}
}
try {
workbook.close();
} catch (IOException e) {
LOGGER.error("关闭workbook失败", e);
}
return list;
}
/**
* 创建sheet列名称
*/
private static <T> void createSheetColumnName(Sheet sheet, CellStyle cellStyle, Class<T> clazz, int startRowNum) {
Row row = sheet.createRow(startRowNum);
row.setHeight((short) (20 * 20));
int columnIndex = 0;
String columnName;
ExcelField excelField;
Field[] declaredFields = clazz.getDeclaredFields();
for (Field field : declaredFields) {
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
// 获取注解
excelField = field.getAnnotation(ExcelField.class);
columnName = excelField.columnName();
// 创建单元格
createCell(row, columnIndex, columnName, cellStyle);
columnIndex++;
}
}
}
}
/**
* 创建sheet数据
*/
private static <T> void createSheetColumnValue(Sheet sheet, CellStyle cellStyle, List<?> data, Class<T> clazz, Integer startRowNum) {
int tempStartRowNum = startRowNum;
Row row;
int columnIndex;
Field[] declaredFields = clazz.getDeclaredFields();
for (Object obj : data) {
// 创建新行，索引加1,为创建下一行做准备
row = sheet.createRow(startRowNum++);
row.setHeight((short) (20 * 20));
columnIndex = 0;
for (Field field : declaredFields) {
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
try {
Object value = typeConvert(field.get(obj), field);
// 创建单元格 field.get(obj)从obj对象中获取值设置到单元格中
createCell(row, columnIndex, value, cellStyle);
} catch (IllegalAccessException e) {
LOGGER.error("对象非法访问异常", e);
}
columnIndex++;
}
}
}
}
ExcelField excelField;
Field field;
for (int i = 0; i < declaredFields.length; i++) {
field = declaredFields[i];
if (field != null) {
// 设置属性可访问
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
// 获取注解
excelField = field.getAnnotation(ExcelField.class);
if (excelField.mergeColumnCell()) {
mergeColCell(sheet, i, tempStartRowNum, startRowNum - 1);
}
}
}
}
}
/**
* 生成Workbook
*/
private static Workbook buildWorkbook(String fileName, int total) {
Workbook workbook = null;
if (fileName.endsWith(EXCEL_XLS)) { // Excel 2003
workbook = new HSSFWorkbook();
} else if (fileName.endsWith(EXCEL_XLSX)) { // Excel 2007/2010
if (total < 1000) {
workbook = new XSSFWorkbook();
} else {
workbook = new SXSSFWorkbook();
}
}
return workbook;
}
/**
* 读取文件的Workbook
*/
private static Workbook readWorkbook(File file) {
Workbook workbook = null;
try (FileInputStream inputStream = new FileInputStream(file)) {
checkExcelValid(file);
if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
workbook = new HSSFWorkbook(inputStream);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
workbook = new XSSFWorkbook(inputStream);
}
} catch (Exception e) {
LOGGER.error("读取Workbook异常", e);
}
return workbook;
}
/**
* 读取文件的Workbook
*/
private static Workbook readWorkbook(InputStream inputStream, String fileName) {
Workbook workbook = null;
try {
if (fileName.endsWith(EXCEL_XLS)) { // Excel 2003
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(EXCEL_XLSX)) { // Excel 2007/2010
workbook = new XSSFWorkbook(inputStream);
}
} catch (Exception e) {
LOGGER.error("读取Workbook异常", e);
}
return workbook;
}
/**
* 获取列名和列索引关系
*/
private static <T> Map<String, Integer> getColumnMap(Sheet sheet, Class<T> clazz) {
// 获取类所有属性
Field[] fields = getAllField(clazz);
ExcelField excelField;
// 获取列名和列索引关系
Map<String, Integer> columnMap = new HashMap<>();
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelField.class)) {
excelField = field.getAnnotation(ExcelField.class);
columnMap.put(excelField.columnName(), getColumnIndex(sheet, excelField.columnName()));
}
}
return columnMap;
}
/**
* 创建Excel单元格
*/
private static void createCell(Row row, int c, Object cellValue, CellStyle style) {
Cell cell = row.createCell(c);
cell.setCellStyle(style);
// cell.setCellType(Cell.CELL_TYPE_STRING);
if (cellValue != null) {
cell.setCellValue(String.valueOf(cellValue));
} else {
cell.setCellValue("");
}
}
/**
* 获取Excel单元格内容
*/
private static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 把数字当成String来读，避免出现1读成1.0的情况
/*if(cell.getCellType() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}*/
// 判断数据的类型
switch (cell.getCellType()) {
case NUMERIC: // 数字
SimpleDateFormat sdf = null;
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 处理日期格式、时间格式
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
} else {
cell.setCellType(CellType.STRING);
cellValue = String.valueOf(cell.getStringCellValue());
}
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 设置对象属性值
*
* @param obj 操作对象
* @param field 对象属性
* @param cell excel单元格
*/
private static void setFieldValue(Object obj, Field field, Cell cell) {
try {
if (cell != null) {
String cellValue = getCellValue(cell);
if (!(cellValue == null || cellValue.length() == 0)) {
if (field.getType() == int.class || field.getType() == Integer.class) {
field.set(obj, Integer.valueOf(cellValue));
} else if (field.getType() == Double.class) {
field.set(obj, Double.parseDouble(cellValue));
} else if (field.getType() == LocalDate.class) {
field.set(obj, LocalDate.parse(cellValue, DateTimeFormatter.ofPattern("yyyy-MM-dd")));
} else {
field.set(obj, cellValue);
}
}
} else {
if (field.getType() == Number.class) {
field.setInt(obj, 0);
} else {
field.set(obj, null);
}
}
} catch (Exception e) {
LOGGER.error("设置对象属性值", e);
}
}
/**
* 创建文件
*
* @param path 文件路径
*/
private static void createFile(String path) {
if (!Files.exists(Paths.get(path))) {
try {
Files.createDirectories(Paths.get(path).getParent());
Files.createFile(Paths.get(path));
} catch (IOException e) {
LOGGER.error("创建文件失败：{}", path, e);
}
}
}
/**
* 校验Excel文件
*/
private static void checkExcelValid(File file) {
if (file == null || !Files.exists(file.toPath())) {
throw new RuntimeException("文件不存在");
}
if (!((file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new RuntimeException("不是Excel文件");
}
}
/**
* 根据列名（第一行）获取列索引
*
* @param sheet 工作铺
* @param columnName 列名
* @return 列索引
*/
private static int getColumnIndex(Sheet sheet, String columnName) {
int coefficient = -1;
Row row = sheet.getRow(0);
int cellNum = row.getPhysicalNumberOfCells();
for (int i = 0; i < cellNum; i++) {
if ((row.getCell(i).toString()).equals(columnName)) {
coefficient = i;
}
}
return coefficient;
}
/**
* 检验是否空行
*/
private static boolean isRowEmpty(Row row) {
if (row == null) {
return true;
}
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) {
return false;
}
}
return true;
}
/**
* 数据转换
*/
private static Object typeConvert(Object value, Field field) {
if (value != null) {
JsonFormat jsonFormat;
String pattern = "yyyy-MM-dd";
if (field.getType() == Date.class || field.getType() == LocalDateTime.class || field.getType() == LocalDate.class) {
if (field.isAnnotationPresent(JsonFormat.class)) {
jsonFormat = field.getAnnotation(JsonFormat.class);
pattern = jsonFormat.pattern();
}
if (field.getType() == Date.class) {
value = DateUtils.format((Date) value, pattern);
} else if (field.getType() == LocalDate.class) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
value = formatter.format((LocalDate) value);
} else if (field.getType() == LocalDateTime.class) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
value = formatter.format((LocalDateTime) value);
}
}
}
return value;
}
/**
* 合并单元格
*/
private static void mergeColCell(Sheet sheet, int colNum, int startRow, int endRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, colNum, colNum));
}
/**
* 写入sheet
*/
private static <T> void writeSheet(Sheet sheet, Workbook workbook, List<?> data, Class<T> clazz, Integer startRowNum) {
sheet.setDefaultRowHeightInPoints(20);
// 设置列名样式
CellStyle headerStyle = buildHeaderStyle(workbook);
createSheetColumnName(sheet, headerStyle, clazz, startRowNum);
startRowNum++;
// 内容样式
CellStyle contentStyle = buildContentStyle(workbook);
createSheetColumnValue(sheet, contentStyle, data, clazz, startRowNum);
}
/**
* 生成列名样式
*/
private static CellStyle buildHeaderStyle(Workbook workbook) {
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font font = workbook.createFont();
font.setFontName("黑体");
font.setColor(IndexedColors.WHITE.getIndex());
font.setFontHeightInPoints((short) 12);
headerStyle.setFont(font);
return headerStyle;
}
/**
* 生成内容样式
*/
private static CellStyle buildContentStyle(Workbook workbook) {
CellStyle contentStyle = workbook.createCellStyle();
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return contentStyle;
}
/**
* 判断是否存在某一字段，存在则返回，否则返回null
*/
private static Field existsField(Class<?> clazz, String fieldName) {
Field[] fields = clazz.getDeclaredFields();
clazz = clazz.getSuperclass();
while (clazz != null) {
fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
clazz = clazz.getSuperclass();
}
for (Field field : fields) {
if (fieldName.equals(field.getName())) {
return field;
}
}
return null;
}
/**
* 获得所有字段，包括父类的所有字段
*/
private static Field[] getAllField(Class<?> clazz) {
Field[] fields = clazz.getDeclaredFields();
clazz = clazz.getSuperclass();
while (clazz != null) {
fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
clazz = clazz.getSuperclass();
}
return fields;
}
}
注意：导出任务数据量近100W甚至更多，导出的项目就会内存溢出，挂掉。
推荐使用：EasyExcel，Java使用EasyExcel读取写入Excel
116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
