目录
一、创建注解
//使用元注解,表示该注解不仅被保存到class文件中,jvm加载class文件之后,仍然存在
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
//列数
public int columnIndex() default 0;
//列名
public String columnName() default "";
}
二、根据excel表格内容创建实体类
以下表格为例,创建相应实体类。
实现代码:
@Data
@ToString
public class EmployeeProvidentRatio {
//使用注解说明列数和列名
@ExcelField(columnIndex = 0, columnName = "工号")
private String empNo;
@ExcelField(columnIndex = 1, columnName = "姓名")
private String name;
@ExcelField(columnIndex = 2, columnName = "工资基数")
private double wageBase;
@ExcelField(columnIndex = 3, columnName = "公积金比例")
private double providentRatio;
}
三、创建ExcelUtils类实现读写功能
public class ExcelUtils {
/**
* 按照classpath:template_enterprise_monthy_cost.xlsx格式输出excel文件
* @param data 数据集合
* @param target 输出路径
* @param startRow 开始输出行
*/
public static void writeExcelFile(List<?> data, String target, int startRow) {
//读取导出表格的表头格式 将template_enterprise_monthy_cost.xlsx放至resource文件夹中
Workbook wb = workBookFromClasspath("/template_enterprise_monthy_cost.xlsx");
// 创建sheet
Sheet sheet = wb.getSheetAt(0);
// 行
Row row = null;
// 创建单元格样式
CellStyle style = wb.createCellStyle();
// 居中显示
style.setAlignment(HorizontalAlignment.CENTER);
// 获取实体所有属性
Field[] fields = data.get(0).getClass().getDeclaredFields();
// 列索引
int index = 0;
//字段注解
ExcelField myAnnotation;
int rowIndex = startRow;
for (Object obj : data) {
// 创建新行,索引加1,为创建下一行做准备
row = sheet.createRow(rowIndex++);
for (Field f : fields) {
// 设置属性可访问
f.setAccessible(true);
// 判断是否是注解
if (f.isAnnotationPresent(ExcelField.class)) {
// 获取注解
myAnnotation = f.getAnnotation(ExcelField.class);
// 获取列索引
index = myAnnotation.columnIndex();
try {
// 创建单元格 f.get(obj)从obj对象中获取值设置到单元格中
createCell(row, index, (f.get(obj)), style);
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
}
}
}
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(target);
wb.write(outputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
//释放资源
try {
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 读取excel文件,并把读取到的数据封装到clazz中
*
* @param path
* 文件路径
* @param clazz
* 实体类
* @return 返回clazz集合
*/
public static <T extends Object> List<T> readExcelFile(String path, Class<T> clazz) {
// 存储excel数据
List<T> list = new ArrayList<>();
Workbook wookbook = workBookFromFile(path);
// 得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
// 获取行总数
int rows = sheet.getLastRowNum() + 1;
Row row;
// 获取类所有属性
Field[] fields = clazz.getDeclaredFields();
T obj = null;
int coumnIndex = 0;
Cell cell = null;
ExcelField myAnnotation = null;
for (int i = 1; i < rows; i++) {
// 获取excel行
row = sheet.getRow(i);
try {
// 创建实体
obj = clazz.getDeclaredConstructor().newInstance();
for (Field f : fields) {
// 设置属性可访问
f.setAccessible(true);
// 判断是否是注解
if (f.isAnnotationPresent(ExcelField.class)) {
// 获取注解
myAnnotation = f.getAnnotation(ExcelField.class);
// 获取列索引
coumnIndex = myAnnotation.columnIndex();
// 获取单元格
cell = row.getCell(coumnIndex);
// 设置属性
setFieldValue(obj, f, wookbook, cell);
}
}
// 添加到集合中
list.add(obj);
} catch (InstantiationException e1) {
e1.printStackTrace();
} catch (IllegalAccessException e1) {
e1.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
try {
//释放资源
wookbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
* 设置属性值
*
* @param obj
* 操作对象
* @param f
* 对象属性
* @param cell
* excel单元格
*/
private static void setFieldValue(Object obj, Field f, Workbook wookbook, Cell cell) {
try {
if (f.getType() == int.class || f.getType() == Integer.class) {
f.set(obj, getInt(cell));
} else if (f.getType() == Double.class || f.getType() == double.class) {
f.set(obj, getDouble(null, cell));
} else {
f.set(obj, getString(cell));
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 获取正数
*
* @param cell
* @return
*/
@SuppressWarnings("deprecation")
private static int getInt(Cell cell) {
if (cell == null) {
return 0;
}
if (cell.getCellType() == CellType.BLANK) {
return 0;
}
if (cell.getCellType() == CellType.STRING) {
return Integer.parseInt(getString(cell));
}
return Integer.parseInt(NumberToTextConverter.toText(cell.getNumericCellValue()));
}
/**
* 获取double stringpute svc
*
* @param cell
* @return
*/
@SuppressWarnings("deprecation")
private static double getDouble(Workbook wookbook, Cell cell) {
double d = 0;
switch (cell.getCellType()) {
case BLANK:
// 空白字符 返回0
d = 0;
break;
case FORMULA:
// 公式
FormulaEvaluator formulaEval = wookbook.getCreationHelper().createFormulaEvaluator();
d = formulaEval.evaluate(cell).getNumberValue();
break;
case NUMERIC:
// 数字格式
d = Double.parseDouble(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case STRING:
d = Double.parseDouble(getString(cell));
break;
case BOOLEAN:
break;
case ERROR:
break;
default:
d = 0;
break;
}
return d;
}
/**
* 获取字符串
*
* @param cell
* @return
*/
@SuppressWarnings("deprecation")
private static String getString(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == CellType.BLANK) {
return "";
} else if (cell.getCellType() == CellType.NUMERIC) {
return NumberToTextConverter.toText(cell.getNumericCellValue());
}
cell.setCellType(CellType.STRING);
return cell.getStringCellValue().toString();
}
/**
* 从classpath下的模板构建一个Workbook
* @param templateName
* @return
*/
private static Workbook workBookFromClasspath(String templateName) {
InputStream is = ExcelUtils.class.getResourceAsStream(templateName);
return createWorkbook(is, templateName);
}
/**
* 从一个外部路径获取一个Workbook
* @param path
* @return
*/
private static Workbook workBookFromFile(String path) {
FileInputStream is = null;
try {
is = new FileInputStream(new File(path));
} catch (FileNotFoundException e1) {
throw new RuntimeException("文件路径异常");
}
return createWorkbook(is, path);
}
private static Workbook createWorkbook(InputStream is, String path) {
Workbook wookbook = null;
// 根据excel文件版本获取工作簿
if (path.endsWith(".xls")) {
wookbook = xls(is);
} else if (path.endsWith(".xlsx")) {
wookbook = xlsx(is);
} else {
throw new RuntimeException("文件出错,非excel文件");
}
return wookbook;
}
/**
* 对excel 2003处理
*/
private static Workbook xls(InputStream is) {
try {
// 得到工作簿
return new HSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 对excel 2007处理
*/
private static Workbook xlsx(InputStream is) {
try {
// 得到工作簿
return new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 创建单元格
*
* @param row
* @param c
* @param cellValue
* @param style
*/
private static void createCell(Row row, int c, Object cellValue, CellStyle style) {
Cell cell = row.createCell(c);
if (cellValue == null) {
String nullVal = null;
cell.setCellValue(nullVal);
} else if (cellValue instanceof String) {
cell.setCellValue((String)cellValue);
} else if (cellValue instanceof Double) {
cell.setCellValue((Double)cellValue);
} else if (cellValue instanceof Integer) {
cell.setCellValue((Integer)cellValue);
} else {
cell.setCellValue(String.valueOf(cellValue));
}
cell.setCellStyle(style);
}
}
四、在mian中调用ExcelUtils类函数
List<EmployeeProvidentRatio> employeeProvidentRatios = 、
ExcelUtils.readExcelFile("./data/员工五险一金申报表.xlsx", EmployeeProvidentRatio.class);
String target = "./data/企业人员月度工资成本支付表.xlsx";
//mylist为存储写入表格内容对象的队列
ExcelUtils.writeExcelFile(mylist, target, 2);