本文详细介绍使用阿里巴巴EasyExcel库进行Java环境下Excel的导入导出操作，包括小规模数据（少于1000行）和大规模数据的处理流程，以及如何通过监听器自定义数据读取方式。
java 导入导出excel
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import lombok.Data;
/**
* @description: 阿里巴巴EasyExcel工具
* @author: ***
* @date: 2019-11-05 13:22
*/
public class EasyExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);
private static Sheet initSheet;
static {
initSheet = new Sheet(1, 0);
initSheet.setSheetName("sheet");
// 设置自适应宽度
initSheet.setAutoWidth(Boolean.TRUE);
}
/**
* 导入
* 少于1000行数据 默认样式
* @param filePath 文件绝对路径
* @return
*/
public static List<Object> readLessThan1000Row(String filePath) {
return readLessThan1000RowBySheet(filePath, null);
}
/**
* 导入
* 少于1000行数据，带样式的
* @param filePath 文件绝对路径
* @param sheet
* @return
*/
public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(filePath);
return EasyExcelFactory.read(inputStream, sheet);
} catch (FileNotFoundException e) {
logger.error("找不到文件或者文件路径错误", e);
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
logger.error("excel文件读取失败，失败原因：{}", e);
}
}
return null;
}
/**
* 导入
* 大于1000行数据 默认样式
* @param filePath
* @return
*/
public static List<Object> readMoreThan1000Row(String filePath) {
return readMoreThan1000RowBySheet(filePath, null);
}
/**
* 导入
* 大于1000行数据 自定义样式
* @param filePath
* @param sheet
* @return
*/
public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(filePath);
ExcelListener excelListener = new ExcelListener();
EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
return excelListener.getDatas();
} catch (FileNotFoundException e) {
logger.error("找不到文件或者文件路径错误");
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
logger.error("excel文件读取失败，失败原因：{}", e);
}
}
return null;
}
/**
* 导出单个sheet
* @param response
* @param dataList
* @param sheet
* @param fileName
* @throws UnsupportedEncodingException
*/
public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) {
if (CollectionUtils.isEmpty(dataList)) {
throw new RuntimeException("导出的表格数据为空!");
}
// 如果sheet为空，则使用默认的
if (null == sheet) {
sheet = initSheet;
}
try {
String value = "attachment; filename=" + new String(
(fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", value);
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
// 设置属性类
sheet.setClazz(dataList.get(0).getClass());
writer.write(dataList, sheet);
writer.finish();
out.flush();
} catch (IOException e) {
logger.error("导出失败，失败原因：{}", e);
}
}
/**
* @Author lockie
* @Description 导出excel 支持一张表导出多个sheet
* @Param OutputStream 输出流
* Map<String, List> sheetName和每个sheet的数据
* ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
* @Date 上午12:16 2019/1/31
*/
public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
if (CollectionUtils.isEmpty(dataList)) {
throw new RuntimeException("导出的表格数据为空!");
}
try {
String value = "attachment; filename=" + new String(
(fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", value);
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
// 设置多个sheet
setMutilSheet(dataList, writer);
writer.finish();
out.flush();
} catch (IOException e) {
logger.error("导出异常", e);
}
}
/**
* @Author lockie
* @Description //setSheet数据
* @Date 上午12:39 2019/1/31
*/
private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
int sheetNum = 1;
for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
sheet.setSheetName(stringListEntry.getKey());
writer.write(stringListEntry.getValue(), sheet);
sheetNum++;
}
}
/**
* 导出监听
*/
@Data
public static class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 逐行解析
* @param object 当前行的数据
* @param analysisContext
*/
@Override
public void invoke(Object object, AnalysisContext analysisContext) {
if (object != null) {
datas.add(object);
}
}
/**
* 解析完所有数据后会调用该方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
}
单独封装的导出excel属性
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.*;
import java.util.Date;
/**
* @description: 导出信息
* @author: ***
* @date: 2019-11-05 15:48
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class IesSysLogExcelVo extends BaseRowModel {
/**
* id
*/
@ExcelProperty(value = "id",index = 0)
private Integer id;
/**
* 日志类型
*/
@ExcelProperty(value = "日志类型",index = 1)
private Integer type;
/**
* 登录时间
*/
@ExcelProperty(value = "登录时间",index = 2)
@JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
private Date logintime;
/**
* 登出时间
*/
@ExcelProperty(value = "登出时间",index = 3)
@JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
private Date logouttime;
/**
* 操作用户_id
*/
@ExcelProperty(value = "操作用户_id",index = 4)
private Integer userId;
/**
* 操作用户IP
*/
@ExcelProperty(value = "操作用户IP",index = 5)
private String ip;
/**
* 操作功能
*/
@ExcelProperty(value = "操作功能",index = 6)
private String func;
/**
* 操作结果（成功/失败）
*/
@ExcelProperty(value = "操作结果",index = 7)
private String result;
/**
* 操作时间
*/
@ExcelProperty(value = "操作时间",index = 8)
@JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
private Date createtime;
/**
* 操作用户名字
*/
@ExcelProperty(value = "操作用户名字",index = 9)
private String userName;
}
测试调用
import com.wwhy.ieacommon.utils.BizException;
import com.wwhy.ieacommon.utils.CommonEnum;
import com.wwhy.ieacommon.utils.EasyExcelUtils;
import com.wwhy.ieacommon.utils.ResponseResult;
import com.wwhy.iesgenerator.entity.*;
import com.wwhy.iesgenerator.vo.outExcelVo.*;
import com.wwhy.iespirservice.service.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
@Controller
@RequestMapping("/outExcel")
@CrossOrigin
@Api(value = "EasyExcelController", tags = "outExcel", description = "导出excel fuyunhoa")
public class EasyExcelController {
@Autowired
private IIesSysLogService iesSysLogService;
@ApiOperation(value="导出Excel", response= ResponseResult.class)
@RequestMapping(value = "outExcel",method = RequestMethod.GET)
public ResponseResult outExcel(HttpServletResponse response,Integer typeId,String fileName){
try{
if (typeId == 2){ //系统操作日志导出 根据typeId选择导出的文件属性，fileName为导出的excel名称
List<IesSysLogExcelVo> dataList = new ArrayList<>();
getSysLogDataList(dataList);
EasyExcelUtils.writeExcelOneSheet(response,dataList , null,fileName);
}
}catch (Exception u){
u.printStackTrace();
throw new BizException(CommonEnum.EXPORT_FAIL); //自定义的全局处理异常，可不写
}
return ResponseResult.buildSuccess(null);
}
//系统操作日志导出
private void getSysLogDataList(List<IesSysLogExcelVo> dataList){
List<IesSysLog> list=iesSysLogService.selectAllData();
for(IesSysLog log:list){
IesSysLogExcelVo vo = new IesSysLogExcelVo();
BeanUtils.copyProperties(log, vo);
dataList.add(vo);
}
}
}
前端使用
/** 导出按钮操作 */
handleExport () {
this.$confirm('确认导出?', '提示', {
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(() => {
window.location.href = '接口路径?typeId=5&&fileName=会议记录'
}).catch(() => {
this.$message({
type: 'info',
message: '已取消'
})
})
}
依赖jar
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
<scope>provided</scope>
</dependency>
博主精品推荐：
1.微软发布激进支出计划：大举投资满足 AI 服务需求
2.国家网信办等七部门联合公布《生成式人工智能服务管理暂行办法》，8 月 15 日起施行
3.马斯克宣布人工智能公司 xAI 正式成立，旨在“了解宇宙的真实本质
4.月份中国采购经理指数公布 制造业采购经理指数继续回升
推荐关注微信公众号：带你了解最前沿的科技资讯：
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
