excel 对应的java代码_1.（JAVA）用Annotation 实现Excel列与Pojo的映射

最近没啥心情更新算法题。写点JAVA换换脑子。今天的代码没有重构，因为太晚3了。打算明后天重构一版再上传。

题目：

Apache POI提供了读取Excel文件的接口。里面的类大概长这样：

Workbook -> Sheet -> Row -> Cell

其中，一个Workbook代表了一个Excel文件。剩下的类就对应了Excel里面的概念了。

一般JAVA读Excel是为了处理里面的数据。于是也就需要把里面的内容映射到Java的类里面。

POI本身提供的功能是根据Row和Column的位置来读取相应Cell的数据。

比如现在有一个User类：

public class User {

private String name;

private String sex;

// omit getter / setter

}

那么用POI进行读取的时候就可能是这样

Some pseudocode

User user = new User;

user.setName(row.getCellAt(0).getStringValue());

user.setAge(row.getCellAt(1).getStringValue());

也就是说，为了读一个excel文件，我们需要写一个方法将POJO中的字段与Excel中列的号码一一对应。当列很多或者经常被改动的时候，这样并不容易维护。

我尝试写了一下用Annotation的方式进行这个映射。写的时候感觉这是一个非常好的练习泛型和反射的机会。反正我这种程序员实际项目里一般也写不到这种功能的代码。

这里要导入的Excel文件长这样

image.png

而我们用到的POJO加上注解之后长这样

/**

* Created by creat on 2018/8/26.

*/

@ExcelEntity

public class User {

@ExcelCol(colName = "Name")

private String name;

@ExcelCol(colName = "Age")

private Integer age;

@ExcelCol(colName = "Date of Birth")

private Date dateOfBirth;

// Omit getter / setter

测试结果：

image.png

测试界面：

image.png

项目代码：

用intellij 起一个spring-boot项目，自己加个Swagger方便测试.

项目结构：

image.png

POM文件：(h2和mybatis是自己练习其他功能用的，这里不需要)

必备的Dependency是 POI, Spring-boot, Swagger

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.example

demo

0.0.1-SNAPSHOT

jar

demo

Demo project for Spring Boot

org.springframework.boot

spring-boot-starter-parent

2.0.4.RELEASE

UTF-8

UTF-8

1.8

org.springframework.boot

spring-boot-starter-data-rest

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-web-services

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

com.h2database

h2

runtime

org.springframework.boot

spring-boot-starter-test

test

io.springfox

springfox-swagger-ui

2.4.0

io.springfox

springfox-swagger2

2.4.0

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

org.springframework.boot

spring-boot-maven-plugin

Annotation for COL:

@Retention(RetentionPolicy.RUNTIME)

@Target(ElementType.FIELD)

public @interface ExcelCol {

String colName() default "";

}

Annotation for POJO class:

@Retention(RetentionPolicy.RUNTIME)

@Target(ElementType.TYPE)

public @interface ExcelEntity {

}

Controller: 看第二个

@RestController

@Api("swagger test")

@RequestMapping("/test")

public class TestController {

@Autowired

private ExcelService excelService;

@RequestMapping(method = RequestMethod.GET)

@ApiOperation(value = "test method")

public TestResponse getTest() {

return new TestResponse();

}

@RequestMapping(value="/excel/user", method = RequestMethod.POST)

@ApiOperation(value = "test method")

public List getUserFromExcel(@RequestBody MultipartFile file) {

try(InputStream is = file.getInputStream()) {

Workbook wb = new XSSFWorkbook(is);

Sheet sheet = wb.getSheetAt(0);

return excelService.resolveSheet(sheet, User.class);

} catch (IOException e) {

throw new ExcelTestException("Read Excel Error");

}

}

}

Excel Service的代码, 对应的interface就不贴了

package com.example.demo.service.impl;

import com.example.demo.annotation.ExcelCol;

import com.example.demo.annotation.ExcelEntity;

import com.example.demo.exception.ExcelTestException;

import com.example.demo.service.ExcelService;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.stereotype.Service;

import org.springframework.util.StringUtils;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.util.*;

/**

* Created by creat on 2018/8/26.

*/

@Service

public class ExcelServiceImpl implements ExcelService {

@Value("${test.excel.col.title.row.num}")

private int colTitleRowNum;

@Value("${test.excel.content.row.num}")

private int contentRowNum;

private final String SETTER_PREFIX = "set";

@Override

public List resolveSheet(Sheet sheet, Class clazz) {

List entities = new ArrayList<>();

Map fieldMap = mapColNumWithField(sheet, clazz);

for (int i = contentRowNum; i <= sheet.getLastRowNum(); i++) {

entities.add(resolveRow(sheet.getRow(i), clazz, fieldMap));

}

return entities;

}

private T resolveRow(Row row, Class clazz, Map fieldMap) {

try {

T entity = clazz.newInstance();

for (Cell cell : row) {

Field field = fieldMap.get(cell.getColumnIndex());

setField(field, cell, entity);

}

return entity;

} catch (IllegalAccessException | InstantiationException | NoSuchMethodException e) {

throw new ExcelTestException("setter method error");

}

}

private void setField(Field field, Cell cell, T entity) throws NoSuchMethodException {

Method setter = createSetter(field, entity);

Class[] paramTypes = setter.getParameterTypes();

assignField(cell, paramTypes[0], setter, entity);

}

private Method createSetter(Field field, T entity) {

try {

String setterName = createSetterName(field.getName());

return entity.getClass().getDeclaredMethod(setterName, field.getType());

} catch (NoSuchMethodException e) {

throw new ExcelTestException("No Setter");

}

}

private void assignField(Cell cell, Class paramClass, Method setter, T entity) {

try {

if (paramClass.equals(Date.class)) {

setter.invoke(entity, cell.getDateCellValue());

} else if (paramClass.equals(String.class)) {

setter.invoke(entity, cell.getStringCellValue());

} else if (paramClass.equals(Integer.class)) {

setter.invoke(entity, (int) cell.getNumericCellValue());

}

} catch (IllegalAccessException | InvocationTargetException e) {

throw new ExcelTestException("Assign cell value error");

}

}

private Map mapColNumWithField(Sheet sheet, Class clazz) {

Row titleRow = sheet.getRow(colTitleRowNum);

Map fieldMap = extractFieldMap(clazz);

Map fieldColNumMap = new HashMap<>();

for (Cell cell : titleRow) {

cell.setCellType(CellType.STRING);

Field field = fieldMap.get(cell.getStringCellValue());

if (null != field) {

fieldColNumMap.put(cell.getColumnIndex(), field);

}

}

return fieldColNumMap;

}

private Map extractFieldMap(Class clazz) {

checkResolvable(clazz);

Map fieldMap = new HashMap<>();

ArrayList fields = new ArrayList<>(Arrays.asList(clazz.getDeclaredFields()));

fields.stream().filter(field -> field.getAnnotation(ExcelCol.class).colName().length() > 0)

.forEach(fieldForCol -> {

String colName = fieldForCol.getAnnotation(ExcelCol.class).colName();

fieldMap.put(colName, fieldForCol);

});

return fieldMap;

}

private void checkResolvable(Class clazz) {

ExcelEntity annotation = clazz.getAnnotation(ExcelEntity.class);

if (null == annotation) {

throw new ExcelTestException("Entity is not made for excel conversion");

}

}

private String createSetterName(String fieldName) {

if (StringUtils.isEmpty(fieldName)) {

throw new ExcelTestException("No Setter");

}

String firstLetter = fieldName.substring(0, 1).toUpperCase();

return SETTER_PREFIX + firstLetter + fieldName.substring(1);

}

}

Spring-boot Application

@SpringBootApplication

public class DemoApplication {

public static void main(String[] args) {

SpringApplication.run(DemoApplication.class, args);

}

}

Swagger的配置：

@Configuration

@EnableSwagger2

public class Swagger2 {

@Bean

public Docket createRestApi() {

return new Docket(DocumentationType.SWAGGER_2)

.apiInfo(apiInfo())

.select()

.apis(RequestHandlerSelectors.basePackage("com.example.demo.controller"))

.paths(PathSelectors.any())

.build();

}

private ApiInfo apiInfo() {

return new ApiInfoBuilder()

.title("My Test")

.version("1.0")

.description("Test Springboot")

.build();

}

}

application.properties

server.port=8081

test.excel.col.title.row.num=0

test.excel.content.row.num=1

TestException:

public class ExcelTestException extends RuntimeException {

public ExcelTestException(String msg) {

super(msg);

}

}