alibaba easyexcel导出excel

语雀参考地址
新文档

一、获取文件流并下载

import com.alibaba.excel.util.FileUtils;
try {
String resourceFileName = "Autel_桩_导入_模板.xlsx";
String resourceFilePath = "xls/" + resourceFileName;
InputStream inputStream = ChargeCardExcelEntity.class.getClassLoader().getResourceAsStream(resourceFilePath);
File tempFile = File.createTempFile("test", "xlsx");
FileUtils.writeToFile(tempFile, inputStream);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
downLoadFileName = URLEncoder.encode(downLoadFileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + downLoadFileName + ".xlsx");
byte[] fileToByteArray = FileUtils.readFileToByteArray(tempFile);
response.getOutputStream().write(fileToByteArray);
} catch (IOException e) {
e.printStackTrace();
}

1.同一个对象写到同一个sheet

ExcelWriter excelWriter = null;
String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 需要指定写用哪个class去写
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
for (int i = 0; i < 5; i++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}

2.同一个对象写到不同sheet(writerSheet方法添加sheetNO)

ExcelWriter excelWriter = null;
fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 指定文件
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
for (int i = 0; i < 5; i++) {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}

3.不同对象写到不同sheet（ EasyExcel.write去除指定实体类，writerSheet添加指定实体类）

ExcelWriter excelWriter = null;
fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 指定文件
excelWriter = EasyExcel.write(fileName).build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
for (int i = 0; i < 5; i++) {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(DemoData.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}

4.localdatetime处理

1.添加LocalDateTimeConverter 文件
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
2.ExcelProperty注解里添加convert属性
@ExcelProperty(value = "反馈时间", converter = LocalDateTimeConverter.class)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime userFeedBackTime;

5.EasyExcelUtil

public class EasyExcelUtil {
/**
* 读取实体对象集合
*
* @param inputStream 输入流
* @param tClass
返回对象类型
* @param <T>
发挥对象泛型
* @return 实体对象集合
*/
public static <T> List<T> readData(InputStream inputStream, Class<T> tClass) {
EasyExcelDataListener<T> easyExcelDataListener = new EasyExcelDataListener<>();
EasyExcel.read(inputStream, tClass, easyExcelDataListener).sheet().doRead();
return new ArrayList<>(easyExcelDataListener.dataLists);
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadData(HttpServletResponse response, String fileName, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadIncludeData(HttpServletResponse response, String fileName, Set<String> includeColumnFiledNames, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).includeColumnFiledNames(includeColumnFiledNames).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadExcludeData(HttpServletResponse response, String fileName, Set<String> excludeColumnFiledNames, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).excludeColumnFiledNames(excludeColumnFiledNames).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadMergeColData(HttpServletResponse response, String fileName, int mergeBeginRowIndex, int[] mergeColIndexs, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").registerWriteHandler(new ExcelFillCellMergeColStrategy(mergeBeginRowIndex, mergeColIndexs)).doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadMergeRowData(HttpServletResponse response, String fileName, int mergeBeginRowIndex, List<MergeCol> mergeCols, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").registerWriteHandler(new ExcelFillCellMergeRowStrategy(mergeBeginRowIndex, mergeCols))
.doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response
http响应
* @param fileName
文件名
* @param titles
标题
* @param tupleList 查询结果集
* @param <T>
数据类型泛型
*/
public static <T> void downloadData(HttpServletResponse response, String fileName, List<String> titles, List<Tuple> tupleList) {
try {
setDownloadParam(response, fileName);
//构建标题头
List<List<String>> heads = new ArrayList<>();
for (String title : titles) {
List<String> list = new ArrayList<>();
list.add(title);
heads.add(list);
}
//构建数据
List<List<String>> dataList = new ArrayList<>();
for (int i = 0; i < tupleList.size(); i++) {
List<String> cellList = new ArrayList<>();
Tuple tuple = tupleList.get(i);
Object[] objs = tuple.toArray();
for (int j = 0; j < tuple.size(); j++) {
cellList.add(formatAttributeValue(objs[j]));
}
dataList.add(cellList);
}
EasyExcel.write(response.getOutputStream()).head(heads).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 设置下载参数
*
* @param response 响应对象
* @param fileName 文件名
* @throws UnsupportedEncodingException 编码异常
*/
private static void setDownloadParam(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
/**
* 转换属性值为字符串
*
* @param value 属性值
* @return 属性值字符串
*/
private static String formatAttributeValue(Object value) {
if (value == null) {
return "";
}
String attributeValue;
Class type = value.getClass();
if (type == String.class) {
attributeValue = (String) value;
} else if (type == Integer.class || type == Integer.TYPE) {
attributeValue = ((Integer) value).toString();
} else if (type == LocalDateTime.class) {
attributeValue = ((LocalDateTime) value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
} else if (type == LocalDate.class) {
attributeValue = ((LocalDate) value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
} else if (type == LocalTime.class) {
attributeValue = ((LocalTime) value).format(DateTimeFormatter.ofPattern("HH:mm:ss"));
} else if (type == Long.class || type == Long.TYPE) {
attributeValue = ((Long) value).toString();
} else if (type == Double.class || type == Double.TYPE) {
attributeValue = value.toString();
} else if (type == Float.class || type == Float.TYPE) {
attributeValue = value.toString();
} else if (type == Boolean.class || type == Boolean.TYPE) {
attributeValue = ((Boolean) value).toString();
} else {
if (type != BigDecimal.class) {
log.error("类型转换未定义！");
throw new RuntimeException("类型转换未定义！");
}
attributeValue = value.toString();
}
return attributeValue;
}
}

最后

以上就是精明微笑为你收集整理的alibaba easyexcel导出excel的全部内容，希望文章能够帮你解决alibaba easyexcel导出excel所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。