Java导入导出excel,easypoi的简单使用

作者: adm 分类: java 发布时间: 2021-10-28

基于spring boot框架,先上pom配置

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>

实体类

package com.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableName;
import com.common.data.entity.CurdEntity;

@TableName("wx_user")
public class User extends CurdEntity {
@Excel(name = "姓名")
String name;
@Excel(name = "用户")
String user;
@Excel(name = "年龄")
int groupValue;

public int getGroupValue() {
return groupValue;
}

public void setGroupValue(int groupValue) {
this.groupValue = groupValue;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getUser() {
return user;
}

public void setUser(String user) {
this.user = user;
}
}

 

通用方法类

package com.common.util;

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;
import java.util.NoSuchElementException;

/**
* @Description: 表格工具类
*/
public class PoiUtils {
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);
if (workbook != null) ;
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) {
throw new RuntimeException(e.getMessage());
}
}

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

/**
* 导入
* @param filePath
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
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;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
return list;
}

/**
* 导入表格
* @param file
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
try {
List<T> list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
return list;
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
}

 

接口调用相关方法,这里演示一下导入功能

@PostMapping("excelList")//批量保存,一条保存失败,数据全部回滚,测试事务是否生效
public MsgDataBody excelList(MultipartFile file){
MsgDataBody<List<User>> msgBody = new MsgDataBody();
msgBody.setCode(Constant.Error.getCode());
msgBody.setMsg("导入失败");
try {
List<User> userList = PoiUtils.importExcel(file,User.class);
/*
* 相关导入操作
* */
msgBody.setData(userList);
msgBody.setCode(Constant.Success.getCode());
msgBody.setMsg("导入失败");
}catch (Exception e){
msgBody.setMsg("导入失败["+e.getMessage()+"]");
}
return msgBody;
}

如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作!