Springboot implements excel import and export, which is easy to use, and poi can be thrown away

@ApiOperation(value = “ Export member list Excel”)

@RequestMapping(value = “/exportMemberList”, method = RequestMethod.GET)

public void exportMemberList(ModelMap map,

HttpServletRequest request,

HttpServletResponse response) {

List memberList = LocalJsonUtil.getListFromJson(“json/members.json”, Member.class);

ExportParams params = new ExportParams(“ Membership list ”, “ Membership list ”, ExcelType.XSSF);

map.put(NormalExcelConstants.DATA_LIST, memberList);

map.put(NormalExcelConstants.CLASS, Member.class);

map.put(NormalExcelConstants.PARAMS, params);

map.put(NormalExcelConstants.FILE_NAME, “memberList”);

PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);

}

}

• LocalJsonUtil Tool class , Can be directly from resources Get... In the directory JSON Data and convert to objects , For example, the members.json;

• Run the project , Directly through Swagger Access interface , Pay attention to Swagger The provider in cannot download directly , You need to click the download button in the return result , Access address ：http://localhost:8088/swagger-ui/

• When the download is complete , Look at the file , A standard Excel The file has been exported .

Simple import

====

The import function is also very simple to implement , Let's take the import of member information list as an example .

• stay Controller Add an interface for importing Member Information , Here we need to pay attention to the use of @RequestPart Comments modify file upload parameters , Otherwise, in the Swagger You can't display the upload button in ;

/**

• EasyPoi Import and export tests Controller

• Created by macro on 2021/10/12.

*/

@Controller

@Api(tags = "EasyPoiControll

《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 Full content open source sharing

er", description = “EasyPoi Import and export tests ”)

@RequestMapping("/easyPoi")

public class EasyPoiController {

@ApiOperation(“ from Excel Import member list ”)

@RequestMapping(value = “/importMemberList”, method = RequestMethod.POST)

@ResponseBody

public CommonResult importMemberList(@RequestPart(“file”) MultipartFile file) {

ImportParams params = new ImportParams();

params.setTitleRows(1);

params.setHeadRows(1);

try {

List list = ExcelImportUtil.importExcel(

file.getInputStream(),

Member.class, params);

return CommonResult.success(list);

} catch (Exception e) {

e.printStackTrace();

return CommonResult.failed(“ Import failed ！”);

}

}

}

• And then in Swagger Middle test interface , Select the previously exported Excel File can , After successful import, the parsed data will be returned .

Complex Export

====

Of course EasyPoi You can also implement more complex Excel operation , For example, export an order list nested with member information and product information , Now let's realize ！

• First add the item object Product, Used to encapsulate product information ;

/**

• goods

• Created by macro on 2021/10/12.

*/

@Data

@EqualsAndHashCode(callSuper = false)

public class Product {

@Excel(name = “ID”, width = 10)

private Long id;

@Excel(name = “ goods SN”, width = 20)

private String productSn;

@Excel(name = “ Name of commodity ”, width = 20)

private String name;

@Excel(name = “ Product subtitle ”, width = 30)

private String subTitle;

@Excel(name = “ The brand name ”, width = 20)

private String brandName;

@Excel(name = “ commodity price ”, width = 10)

private BigDecimal price;

@Excel(name = “ Purchase quantity ”, width = 10, suffix = “ Pieces of ”)

private Integer count;

}

• Then add the order object Order, Orders and members are one-to-one , Use @ExcelEntity The note indicates , Orders and goods are a one to many relationship , Use @ExcelCollection The note indicates ,Order This is the nested order data we need to export ;

/**

• Order

• Created by macro on 2021/10/12.

*/

@Data

@EqualsAndHashCode(callSuper = false)

public class Order {

@Excel(name = “ID”, width = 10,needMerge = true)

private Long id;

@Excel(name = “ The order number ”, width = 20,needMerge = true)

private String orderSn;

@Excel(name = “ Creation time ”, width = 20, format = “yyyy-MM-dd HH:mm:ss”,needMerge = true)

private Date createTime;

@Excel(name = “ Shipping address ”, width = 20,needMerge = true )

private String receiverAddress;

@ExcelEntity(name = “ Member information ”)

private Member member;

@ExcelCollection(name = “ List of goods ”)

private List productList;

}

• The next in Controller Add an interface to export the order list , Because of some member information, we don't need to export , You can call ExportParams Medium setExclusions Methods eliminate ;

/**

• EasyPoi Import and export tests Controller

• Created by macro on 2021/10/12.

*/

@Controller

@Api(tags = “EasyPoiController”, description = “EasyPoi Import and export tests ”)

@RequestMapping("/easyPoi")

public class EasyPoiController {

@ApiOperation(value = “ Export order list Excel”)

@RequestMapping(value = “/exportOrderList”, method = RequestMethod.GET)

public void exportOrderList(ModelMap map,

HttpServletRequest request,

HttpServletResponse response) {

List orderList = getOrderList();

ExportParams params = new ExportParams(“ Order list ”, “ Order list ”, ExcelType.XSSF);

// Exclude some fields when exporting

params.setExclusions(new String[]{“ID”, “ Date of birth ”, “ Gender ”});

map.put(NormalExcelConstants.DATA_LIST, orderList);

map.put(NormalExcelConstants.CLASS, Order.class);

map.put(NormalExcelConstants.PARAMS, params);

map.put(NormalExcelConstants.FILE_NAME, “orderList”);

PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);

}

}

• stay Swagger Access interface test in , Export order list Excel;

• When the download is complete , Look at the file ,EasyPoi Export complex Excel It's also very simple ！

Custom processing

=====

If you want to do some custom processing on the exported fields ,EasyPoi Also supportive , For example, in the member information , If the user does not set a nickname , We added the information that has not been set yet .

• We need to add a processor to inherit the default ExcelDataHandlerDefaultImpl class , And then in exportHandler Method ;

/**

• Custom field handling

• Created by macro on 2021/10/13.

*/

public class MemberExcelDataHandler extends ExcelDataHandlerDefaultImpl {

@Override

public Object exportHandler(Member obj, String name, Object value) {

if(“ nickname ”.equals(name)){

String emptyValue = “ Not set yet ”;

if(value==null){

return super.exportHandler(obj,name,emptyValue);

}

if(value instanceof String&&StrUtil.isBlank((String) value)){

return super.exportHandler(obj,name,emptyValue);

}

}

return super.exportHandler(obj, name, value);

}

@Override

public Object importHandler(Member obj, String name, Object value) {

return super.importHandler(obj, name, value);

}

}

• And then modify Controller The interface , call MemberExcelDataHandler The processor setNeedHandlerFields Set the fields that need custom processing , And call ExportParams Of setDataHandler Set custom processor ;

/**

• EasyPoi Import and export tests Controller

• Created by macro on 2021/10/12.

*/

@Controller

@Api(tags = “EasyPoiController”, description = “EasyPoi Import and export tests ”)

@RequestMapping("/easyPoi")

public class EasyPoiController {

@ApiOperation(value = “ Export member list Excel”)

@RequestMapping(value = “/exportMemberList”, method = RequestMethod.GET)

public void exportMemberList(ModelMap map,

HttpServletRequest request,

HttpServletResponse response) {

List memberList = LocalJsonUtil.getListFromJson(“json/members.json”, Member.class);

ExportParams params = new ExportParams(“ Membership list ”, “ Membership list ”, ExcelType.XSSF);

// Customize the export results

MemberExcelDataHandler handler = new MemberExcelDataHandler();