1、很多新手刚刚使用J-Excel的时候不知道怎么编辑Excel里面的格式才能被后台的类识别。其实文档经过poi解析后成为sheet，sheet转换问model的时候需要一定格式才能正常转化，不然会报异常。
2、如下图所示：第一行的第一空不能为空，为空会出现异常，最好填个0（这个值跟表名有关，一般转化model的时候用不到。）；第二行是该表的表名让变结构更加清晰自然；第三行就是转化model的关键的东西了。{"columnName":"id","innerColumn":"","length":0,"size":0,"tookValue":"","tookName":""}这个是后台识别model的属性字段，"columnName":"id"中的id对应了PersonVo类中的id属性。第四行则就是属性的对应中文名称了。红色部分的就是需要转化为model的内容了。
3、我的代码是这样的：import org.jplus.hyberbin.excel.annotation.ExcelVoConfig;import org.jplus.hyberbin.excel.annotation.Lang;import org.jplus.hyberbin.excel.annotation.input.InputTextConfig;import org.jplus.hyberbin.excel.annotation.output.OutputDefaultConfig;import org.jplus.hyberbin.excel.bean.BaseExcelVo;/** * Created by 30721 on 2019/7/1. */@ExcelVoConfigpublic class PersonVo extends BaseExcelVo { // 策略名称 @InputTextConfig(nullAble = false, tip = "策略名称不能为空!") @OutputDefaultConfig(nullAble = false) @Lang(value = "id")//Excel导出的配置 protected String id; // 策略名称 @InputTextConfig(nullAble = false, tip = "策略名称不能为空!") @OutputDefaultConfig(nullAble = false) @Lang(value = "name")//Excel导出的配置 protected String name; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "PersonVo{" + "id='" + id + '\'' + ", name='" + name + '\'' + '}'; } @Override public int getHashVal() { return 0; } //用于自定义验证数据// @Override// public boolean validate() {// return true;// } }
4、service层：import cn.cy.vo.PersonVo;import org.apache.poi.ss.usermodel.Sheet;import org.jplus.hyberbin.excel.service.ImportExcelService;import org.springframework.stereotype.Service;@Servicepublic class ImportService { public void importPerson(Sheet sheet) throws Exception { ImportExcelService service = new ImportExcelService(PersonVo.class, sheet); List<PersonVo> personVos = service.doImport(); for (PersonVo personVo: personVos) { System.out.println(personVo); } }}
5、测试代码：import cn.cy.service.excel.ImportService;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import javax.annotation.Resource;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;/** * Created by 30721 on 2019/7/1. */@RunWith(SpringRunner.class)@SpringBootTestpublic class VoTest { @Resource private ImportService importService; @Test public void doImport() { File file = new File("C:\\Users\\30721\\Desktop\\personVo.xlsx"); try { Workbook workbook = new XSSFWorkbook(new FileInputStream(file)); Sheet sheet = workbook.getSheet("personVo"); importService.importPerson(sheet); } catch (Exception e) { e.printStackTrace(); } }}
6、控制层的代码：@RequestMapping(value = "/upload")@ResponseBodypublic void uploadCloudPortFile(HttpServletRequest request) { try { Map<String, Object> map = new HashMap<String, Object>(); // 取得上传的文件 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; CommonsMultipartFile file = (CommonsMultipartFile) multipartRequest .getFile("file"); // 得到文件名称 String realFileName = file.getOriginalFilename(); String suffix = realFileName.substring(realFileName.indexOf("."), realFileName.length()); Workbook workbook = null; if (".xlsx".equals(suffix)) { workbook = new XSSFWorkbook(file.getInputStream()); } else { workbook = new HSSFWorkbook(file.getInputStream()); } Sheet sheet = workbook.getSheet("personVo"); importService.importPerson(sheet); } catch (Exception e) { e.printStackTrace(); }}
