current position:Home>Springboot implements excel import and export, which is easy to use, and poi can be thrown away

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

2022-01-27 01:40:46 m0_ sixty-four million eight hundred and sixty-seven thousand e

@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;

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

  • 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/

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

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

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

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 .

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

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;

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

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

SpringBoot Realization Excel Import and export , Good use of explosion ,POI You can throw it away

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();

copyright notice
author[m0_ sixty-four million eight hundred and sixty-seven thousand e],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270140443615.html

Random recommended