pom文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.3</version>
</dependency>
实体类中加上注解 index代表单元格的第几列,会自动跳过第一行
@ExcelProperty(index = 1)
private Integer sum;
工具类
package cn.chinaunicom.mall.usercenter.excel;
/**
* <p>
* excel工具类
* </p>
*
* @author yinna
* @version: V1.0
*/
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.apache.http.entity.ContentType;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param clz 实体类映射
* @return Excel 数据 list
*/
public static Object headerRead(MultipartFile excel, Class<T> clz) {
InputStream is;
try {
is = new BufferedInputStream(excel.getInputStream());
} catch (Exception e) {
return null;
}
SyncReadListener tmpListener = new SyncReadListener();
return tmpListener.getList();
}
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param clz 实体类映射
* @return Excel 数据 list
*/
public static <T> List<T> readExcel(MultipartFile excel, Class<T> clz) {
InputStream is;
try {
is = new BufferedInputStream(excel.getInputStream());
} catch (Exception e) {
return null;
}
SyncReadListener tmpListener = new SyncReadListener();
ReadSheet readSheet = new ReadSheet();
readSheet.setClazz(clz);
EasyExcel.read(is).registerReadListener(tmpListener).build().read(readSheet);
return (List<T>) tmpListener.getList();
}
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param clz 实体类映射
* @return Excel 数据 list
*/
public static <T> List<T> readExcel(String path, Class<T> clz) {
SyncReadListener tmpListener = new SyncReadListener();
ReadSheet readSheet = new ReadSheet();
readSheet.setClazz(clz);
EasyExcel.read(path).registerReadListener(tmpListener).build().read(readSheet);
return (List<T>) tmpListener.getList();
}
/**
* 导出文件时为Writer生成OutputStream
*
* @param fileName
* @param response
* @return
* @throws Exception
*/
public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
throws Exception {
try {
fileName = URLEncoder.encode(fileName, "utf-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//此处指定了文件类型为xls,如果是xlsx的,请自行替换修改
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出文件失败!");
}
}
public static MultipartFile writeExcel(Class clazz, List<?> list, String fileName, InputStream templateFile) throws Exception {
ByteArrayOutputStream os1 = new ByteArrayOutputStream();
EasyExcel.write(os1, clazz).withTemplate(templateFile).sheet(fileName).doFill(list);
byte[] b1 = os1.toByteArray();
InputStream inputStream = new ByteArrayInputStream(b1);
MultipartFile file = new MockMultipartFile(fileName, fileName, ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
return file;
}
public static String getPath() {
return ExcelUtil.class.getResource("/").getPath();
}
}
直接调用工具类即可