maven配置
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelHeader {
/**
* 表头
* @return
*/
String value() default "";
/**
* 列索引
* @return
*/
int columnIndex() default 0;
}
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
@ExcelHeader(value = "账号")
private String username;
@ExcelHeader(value = "密码")
private String password;
@ExcelHeader(value = "生日")
private LocalDateTime birthday;
// 该字段没有添加注解, Excel不导出
private Integer age;
}
ExcelUtil
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.*;
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.util.*;
public class ExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/**
* @param data 需要导出的数据
* @param clz 数据对应的实体类
* @param <T> 泛型
* @return Excel文件
*/
public static <T> boolean exportExcel(List<T> data, Class<T> clz, String path) {
try {
Field[] fields = clz.getDeclaredFields();
List<String> headers = new LinkedList<>();
List<String> variables = new LinkedList<>();
// 创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表对象
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(0, "sheetName");//设置sheet的Name
// 创建表头
Row rowHeader = sheet.createRow(0);
// 表头处理
for (int h = 0; h < fields.length; h++) {
Field field = fields[h];
if (field.isAnnotationPresent(ExcelHeader.class)) {
// 表头
ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
headers.add(annotation.value());
rowHeader.createCell(h).setCellValue(annotation.value());
// 字段
variables.add(field.getName());
}
}
// 数据处理
for (int i = 0; i < data.size(); i++) {
//创建工作表的行(表头占用1行, 这里从第二行开始)
HSSFRow row = sheet.createRow(i + 1);
// 获取一行数据
T t = data.get(i);
Class<?> aClass = t.getClass();
// 填充列数据
for (int j = 0; j < variables.size(); j++) {
Field declaredField = aClass.getDeclaredField(variables.get(j));
declaredField.setAccessible(true);
Object value = declaredField.get(t);
row.createCell(j).setCellValue(value.toString());
}
}
//IO操作
workbook.write(new File(path));
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public static List<Map<String, String>> readExcel(String filePath) {
return readExcel(filePath, null);
}
/**
* 读取Excel中文件的特定的表，返回数据对象 * * @param filePath 文件路径 * @param sheetName 表格名字（索引） * @return
*/
public static List<Map<String, String>> readExcel(String filePath, String sheetName) {
Workbook workbook = null;
List<Map<String, String>> resultList = new ArrayList<>();
try {
String fileType = filePath.substring(filePath.lastIndexOf("."));
workbook = getWorkbook(filePath, fileType);
if (workbook == null) {
return null;
}
if (sheetName == null) {
sheetName = workbook.getSheetAt(0).getSheetName();
}
resultList = analysisExcel(workbook, sheetName);
return resultList;
} catch (Exception e) {
logger.error("读取Excel文件失败" + filePath + "错误信息", e);
return null;
} finally {
try {
if (null != workbook)
workbook.close();
} catch (Exception ignored) {
}
}
}
/**
* 解析Excel文件中特定的表，返回数据对象 * * @param workbook 生成对应的excel处理 * @param sheetName 表格名字（索引） * @return
*/
private static List<Map<String, String>> analysisExcel(Workbook workbook, String sheetName) {
List<Map<String, String>> dataList = new ArrayList<>();
Sheet sheet = workbook.getSheet(sheetName);
int firstRowCount = sheet.getFirstRowNum();//获取第一行的序号
Row firstRow = sheet.getRow(firstRowCount);
int cellCount = firstRow.getLastCellNum();//获取列数
List<String> mapKey = new ArrayList<>();
for (int i = 0; i < cellCount; i++) {
mapKey.add(firstRow.getCell(i).toString());
}
//解析每一行数据，构成数据对象
int rowStart = firstRowCount + 1;
int rowEnd = sheet.getPhysicalNumberOfRows();
for (int j = rowStart; j < rowEnd; j++) {
Row row = sheet.getRow(j);//获取对应的row对象
if (row == null) continue;
Map<String, String> dataMap = new HashMap<>();
//将每一行数据转化为一个Map对象
dataMap = convertRowToData(row, cellCount, mapKey);
dataList.add(dataMap);
}
return dataList;
}
/**
* 根据文件后缀获取对应Workbook对象 * * @param filePath * @param fileType * @return
*/
private static Workbook getWorkbook(String filePath, String fileType) {
Workbook workbook = null;
FileInputStream fileInputStream = null;
try {
File excelFile = new File(filePath);
if (!excelFile.exists()) {
logger.info(filePath + "文件不存在");
return null;
}
fileInputStream = new FileInputStream(excelFile);
if (fileType.equalsIgnoreCase(".xls")) {
workbook = new HSSFWorkbook(fileInputStream);
} else if (fileType.equalsIgnoreCase(".xlsx")) {
workbook = new XSSFWorkbook(fileInputStream);
}
} catch (Exception e) {
logger.error("获取文件失败", e);
} finally {
try {
if (null != fileInputStream) {
fileInputStream.close();
}
} catch (Exception e) {
return null;
}
}
return workbook;
}
/**
* 将每一行数据转化为一个Map对象 * * @param row 行对象 * @param cellCount 列数 * @param mapKey 表头Map * @return
*/
private static Map<String, String> convertRowToData(Row row, int cellCount, List<String> mapKey) {
if (mapKey == null) {
logger.info("没有表头信息");
return null;
}
Map<String, String> resultMap = new HashMap<>();
Cell cell = null;
for (int i = 0; i < cellCount; i++) {
cell = row.getCell(i);
if (cell == null) {
resultMap.put(mapKey.get(i), "");
} else {
resultMap.put(mapKey.get(i), getCellVal(cell));
}
}
return resultMap;
}
/**
* 获取单元格的值 * * @param cel * @return
*/
public static String getCellVal(Cell cel) {
if (cel.getCellType() == CellType.STRING) {
return cel.getRichStringCellValue().getString();
}
if (cel.getCellType() == CellType.NUMERIC) {
return cel.getNumericCellValue() + "";
}
if (cel.getCellType() == CellType.BOOLEAN) {
return cel.getBooleanCellValue() + "";
}
if (cel.getCellType() == CellType.FORMULA) {
return cel.getCellFormula() + "";
}
return cel.toString();
}
}
实例
public static void main(String[] args) throws NoSuchFieldException, IllegalAccessException, IOException {
// 创建模拟数据
User user1 = new User("admin管理员", "123456", LocalDateTime.now());
User user2 = new User("test", "123456", LocalDateTime.now());
List<User> users = Arrays.asList(user1, user2);
// 文件路径
String path = "F:\\others\\Desktop\\111\\aa.xls";
ExcelUtil.exportExcel(users,User.class,path);
}
Comments | 0 条评论