#使用tools-excel进行excel表格导入导出
#使用tools-excel进行excel表格导入导出
依赖
cn.gjing
tools-excel
2.0.0
1.对应实体
package com.itany.myexcel.entity;
import cn.gjing.tools.excel.Excel;
import cn.gjing.tools.excel.ExcelField;
import cn.gjing.tools.excel.write.valid.ExcelDateValid;
import cn.gjing.tools.excel.write.valid.OperatorType;
import lombok.Data;
import java.time.LocalDate;
import java.util.Date;
/**
* Author:XuDing
* Date:2020/4/11
*/
@Data
@Excel("单级表头")
public class SingleHead {
@ExcelField("姓名")
private String userName;
@ExcelField("爱好")
private String favorite;
@ExcelField(value = "年龄", format = "0")
private Integer age;
@ExcelField(value = "生日", format = "yyyy-MM-dd")
@ExcelDateValid(expr1 = "2000-01-01", operatorType = OperatorType.LESS_OR_EQUAL, errorContent = "出生日期不能超过2000年")
private Date birthday;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getFavorite() {
return favorite;
}
public void setFavorite(String favorite) {
this.favorite = favorite;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
2.controller类.导出
package com.itany.myexcel.controller;
import cn.gjing.tools.excel.ExcelFactory;
import com.itany.myexcel.entity.MultiHead;
import com.itany.myexcel.entity.SingleHead;
import com.itany.myexcel.service.UserService;
import com.wordnik.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* Author:XuDing
* Date:2020/4/11
*/
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/test_export")
@ApiOperation("导出一级表头")
public void testExcel(HttpServletResponse response){
//指定映射的实体为刚刚定义的
ExcelFactory.createWriter(SingleHead.class,response)
.write(null).flush();
}
@GetMapping("/test_export2")
@ApiOperation("导出一级表头")
public void testExcel2(HttpServletResponse response){
//指定映射的实体为刚刚定义的
ExcelFactory.createWriter(MultiHead.class,response)
//需要在write前激活多级表头，否则不会自动合并
.enableMultiHead()
.write(null).flush();
}
@GetMapping("/test_export3")
@ApiOperation("带时间校验")
public void testExport(HttpServletResponse response) {
List all = userService.findAll();
ExcelFactory.createWriter(SingleHead.class, response)
//需要在write前激活校验
.enableValid()
.write(all)
.flush();
}
}
service：
@Override
public List findAll() {
System.out.println("---------------");
Listlist=new LinkedList();
for(int i=0;i<10;i++){
SingleHead s=new SingleHead();
s.setAge(10+i);
s.setBirthday(new Date());
s.setFavorite(“吃饭”+i);
s.setUserName(“姓名”+i);
list.add(s);
System.out.println("-----------------------");
}
return list;
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TPKOh9wH-1586678607858)(C:\Users\86151\AppData\Roaming\Typora\typor
a-user-images\image-20200412160312998.png)]
3.controller导入
package com.itany.myexcel.controller;
import cn.gjing.tools.excel.ExcelFactory;
import cn.gjing.tools.excel.write.resolver.ExcelWriter;
import com.google.gson.JsonArray;
import com.itany.myexcel.entity.ReadExcelUtil;
import com.itany.myexcel.entity.SingleHead;
import com.itany.myexcel.service.UserService;
import com.wordnik.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import org.springframework.web.servlet.ModelAndView;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.Reader;
/**
* Author:XuDing
* Date:2020/4/11
*/
@RestController
@RequestMapping("/test")
public class TestController {
@Resource
private UserService userService;
@PostMapping("/post")
@ApiOperation("导入单表头")
public void userExcel(@RequestParam(name = "file" ,required = false) MultipartFile file)throws IOException {
System.out.println("file===>"+file);
// JsonArray array=null;
// String fileName = file.getName().toLowerCase();
// Workbook workbook=null;
// if(fileName.endsWith(".xlsx")){
// workbook=new XSSFWorkbook(new FileInputStream((File) file));
// }else if (fileName.endsWith(".xls")) {
// POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
// workbook = new HSSFWorkbook(poifsFileSystem);
// }
// array= Read(workbook);
ExcelFactory.createReader(file, SingleHead.class)
//在read()方法前通过订阅方法增加一个结果监听器，该监听器会在每一次read()结束之后触发
//如果Excel中数据量太大，不建议使用结果监听器，会造成生成了过多的映射实体对象造成内存溢出
.subscribe(e->this.userService.saveUser(e))
.read().end();
}
@ExceptionHandler(Exception.class)
public ModelAndView exceptionHander(Exception e){
ModelAndView mav=new ModelAndView();
mav.addObject("msg",e.getMessage());
mav.setViewName("exception");
return mav;
}
}
ModelAndView exceptionHander(Exception e){
ModelAndView mav=new ModelAndView();
mav.addObject("msg",e.getMessage());
mav.setViewName("exception");
return mav;
}
}
作者：南方的烈阳.
