easyPoi使用教程
- 添加pom.xml依赖
cn.afterturn easypoi-spring-boot-starter 4.1.0
- ExcelUtils导入导出excel工具类
package com.yss.dataMiddle.utils;import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;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;public class ExcelUtils {/*** excel 导出** @param list 数据* @param title 标题* @param sheetName sheet名称* @param pojoClass pojo类型* @param fileName 文件名称* @param isCreateHeader 是否创建表头* @param response*/public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName,boolean isCreateHeader, HttpServletResponse response) throws IOException {ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** excel 导出** @param list 数据* @param title 标题* @param sheetName sheet名称* @param pojoClass pojo类型* @param fileName 文件名称* @param response*/public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName,HttpServletResponse response) throws IOException {defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));}/*** excel 导出** @param list 数据* @param pojoClass pojo类型* @param fileName 文件名称* @param response* @param exportParams 导出参数*/public static void exportExcel(List list, Class pojoClass, String fileName, ExportParams exportParams,HttpServletResponse response) throws IOException {defaultExport(list, pojoClass, fileName, response, exportParams);}/*** excel 导出** @param list 数据* @param fileName 文件名称* @param response*/public static void exportExcel(List> list, String fileName, HttpServletResponse response)throws IOException {defaultExport(list, fileName, response);}/*** 默认的 excel 导出** @param list 数据* @param pojoClass pojo类型* @param fileName 文件名称* @param response* @param exportParams 导出参数*/private static void defaultExport(List list, Class pojoClass, String fileName, HttpServletResponse response,ExportParams exportParams) throws IOException {Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);downLoadExcel(fileName, response, workbook);}/*** 默认的 excel 导出** @param list 数据* @param fileName 文件名称* @param response*/private static void defaultExport(List> list, String fileName, HttpServletResponse response)throws IOException {Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);downLoadExcel(fileName, response, workbook);}/*** 下载** @param fileName 文件名称* @param response* @param workbook excel数据*/private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)throws IOException {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));workbook.write(response.getOutputStream());} catch (Exception e) {throw new IOException(e.getMessage());}}/*** excel 导入** @param filePath excel文件路径* @param titleRows 标题行* @param headerRows 表头行* @param pojoClass pojo类型* @param * @return*/public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass)throws IOException {if (StringUtils.isBlank(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setNeedSave(true);params.setSaveUrl("/excel/");try {return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);} catch (NoSuchElementException e) {throw new IOException("模板不能为空");} catch (Exception e) {throw new IOException(e.getMessage());}}/*** excel 导入** @param file excel文件* @param pojoClass pojo类型* @param * @return*/public static List importExcel(MultipartFile file, Class pojoClass) throws IOException {return importExcel(file, 1, 1, pojoClass);}/*** excel 导入** @param file excel文件* @param titleRows 标题行* @param headerRows 表头行* @param pojoClass pojo类型* @param * @return*/public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass)throws IOException {return importExcel(file, titleRows, headerRows, false, pojoClass);}/*** excel 导入** @param file 上传的文件* @param titleRows 标题行* @param headerRows 表头行* @param needVerfiy 是否检验excel内容* @param pojoClass pojo类型* @param * @return*/public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy,Class pojoClass) throws IOException {if (file == null) {return null;}try {return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);} catch (Exception e) {throw new IOException(e.getMessage());}}/*** excel 导入** @param inputStream 文件输入流* @param titleRows 标题行* @param headerRows 表头行* @param needVerify 是否检验excel内容* @param pojoClass pojo类型* @param * @return*/public static List importExcel(InputStream inputStream, Integer titleRows, Integer headerRows,boolean needVerify, Class pojoClass) throws IOException {if (inputStream == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setSaveUrl("/excel/");params.setNeedSave(true);params.setNeedVerify(needVerify);try {return ExcelImportUtil.importExcel(inputStream, pojoClass, params);} catch (NoSuchElementException e) {throw new IOException("excel文件不能为空");} catch (Exception e) {throw new IOException(e.getMessage());}}/*** Excel 类型枚举*/enum ExcelTypeEnum {XLS("xls"), XLSX("xlsx");private String value;ExcelTypeEnum(String value) {this.value = value;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}}} - 实体类添加注解@Excel
package com.yss.dataMiddle.entity;import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;/*** * 表分类*
** @author Han LiDong* @since 2020-07-08*/
@TableName("p_table_category")
@ApiModel(value="PTableCategory对象", description="表分类")
public class PTableCategory extends Model {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "id")@TableId(value = "id", type = IdType.INPUT)@Excel(name = "id", orderNum = "1", width = 15)private Integer id;@Excel(name = "分类名称", orderNum = "2", width = 15)@ApiModelProperty(value = "分类名称")private String categoryName;@Excel(name = "分类编码", orderNum = "3", width = 15)@ApiModelProperty(value = "分类编码")private String categoryCode;@Excel(name = "排序", orderNum = "4", width = 15)@ApiModelProperty(value = "排序")private Integer categorySort;@Excel(name = "状态", orderNum = "5", width = 15)@ApiModelProperty(value = "状态")private String status;@Excel(name = "优先数据源", orderNum = "6", width = 15)@ApiModelProperty(value = "优先数据源")private String priorityData;@Excel(name = "备注", orderNum = "7", width = 15)@ApiModelProperty(value = "备注")private String remarks;@Excel(name = "创建时间", orderNum = "8", width = 15)@ApiModelProperty(value = "创建时间")private Date createTime;@Excel(name = "更新时间", orderNum = "9", width = 15)@ApiModelProperty(value = "更新时间")private Date updateTime;@Excel(name = "删除标志", orderNum = "10", width = 15)@ApiModelProperty(value = "删除标志")private String delFlag;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getCategoryName() {return categoryName;}public void setCategoryName(String categoryName) {this.categoryName = categoryName;}public String getCategoryCode() {return categoryCode;}public void setCategoryCode(String categoryCode) {this.categoryCode = categoryCode;}public Integer getCategorySort() {return categorySort;}public void setCategorySort(Integer categorySort) {this.categorySort = categorySort;}public String getStatus() {return status;}public void setStatus(String status) {this.status = status;}public String getPriorityData() {return priorityData;}public void setPriorityData(String priorityData) {this.priorityData = priorityData;}public String getRemarks() {return remarks;}public void setRemarks(String remarks) {this.remarks = remarks;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public Date getUpdateTime() {return updateTime;}public void setUpdateTime(Date updateTime) {this.updateTime = updateTime;}public String getDelFlag() {return delFlag;}public void setDelFlag(String delFlag) {this.delFlag = delFlag;}@Overrideprotected Serializable pkVal() {return this.id;}@Overridepublic String toString() {return "PTableCategory{" +"id=" + id +", categoryName=" + categoryName +", categoryCode=" + categoryCode +", categorySort=" + categorySort +", status=" + status +", priorityData=" + priorityData +", remarks=" + remarks +", createTime=" + createTime +", updateTime=" + updateTime +", delFlag=" + delFlag +"}";}
} - 接口调用生成excel并下载
@ApiOperation(value="easyExcel测试",notes="easyExcel测试")@ApiImplicitParams({@ApiImplicitParam(name="id",value="主键",required=true,paramType="query")})@GetMapping("/easyExcel")public void exportExcel(@RequestParam("id")Integer id, HttpServletResponse response) throws IOException {List list = new ArrayList<>();list.add(id);List categorys = (List) pTableCategoryService.listByIds(list);List titles = Arrays.asList("主键","分类名称","分类编码","排序","状态","优先数据源","备注","创建时间","更新时间","删除标志");ExcelUtils.exportExcel(categorys, "员工信息", "员工信息sheet", PTableCategory.class, "员工信息表", response);//ExcelUtils.exportExcel(categorys,"hld", "sheet00",PTableCategory.class,"hldtt",true,response); 参考文章：EasyPoi使用教程
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！