EasyPoi 工具类

本文主要是介绍EasyPoi 工具类，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

前言

为了快速实现Java 对 excel 的导入导出操作，编写了一些工具类，可以快速实现导入导出、模板处理等功能。需要的话直接拿去用就好了。

maven依赖

        <dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>3.2.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>3.2.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>3.2.0</version></dependency>

导入导出工具类


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;public class ExcelCommon {public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));}public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {defaultExport(list, fileName, response);}private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);downLoadExcel(fileName, response, workbook);}private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);if (workbook != null) ;downLoadExcel(fileName, response, workbook);}public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {if (StringUtils.isBlank(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);return list;}public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (Exception e) {e.printStackTrace();}return list;}
}

Web使用


import com.cetc.common.response.ResResult;
import com.cetc.icas.web.entity.device.ResourceDeviceEntity;
import com.cetc.icas.web.excel.ExcelCommon;
import com.cetc.icas.web.service.device.DeviceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;@RestController
@RequestMapping("/device/excel")
public class ExcelDeviceController {@Autowiredprivate DeviceService deviceService;@GetMapping("/export")public void download(HttpServletResponse httpResponse) {ExcelCommon.exportExcel(deviceService.list(null),"设备列表" , "设备列表",ResourceDeviceEntity.class,"deviceResource.xls",httpResponse);}@GetMapping("/template")public void template(HttpServletResponse httpResponse) {ExcelCommon.exportExcel(new ArrayList<>(1),"设备列表" , "设备列表",ResourceDeviceEntity.class,"deviceResource.xls",httpResponse);}@PostMapping("/import")public ResResult importExcel(@RequestParam("file") MultipartFile file){List<ResourceDeviceEntity> resourceDeviceEntities = ExcelCommon.importExcel(file, 1, 1, ResourceDeviceEntity.class);deviceService.saveBatch(resourceDeviceEntities);return new ResResult<>().ok(resourceDeviceEntities.size());}
}

使用@Excel注解


import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;public class ResourceDeviceEntity{@Excel(name = "设备编号")private String deviceNo;@Excel(name = "设备名称")private String deviceName;@ExcelIgnoreprivate String deviceType;@Excel(name = "设备类型")private String deviceTypeName;@Excel(name = "街道地址")private String streetName;@Excel(name = "经纬度坐标，经纬度以逗号分隔")private String coordinate;@Excel(name = "省份")private String provinceName;@Excel(name = "地市")private String cityName;@Excel(name = "区县")private String regionName;@ExcelIgnoreprivate String provinceCode;@ExcelIgnoreprivate String cityCode;@ExcelIgnoreprivate String regionCode;//省略 GET、SET
}