一、获取文件流并下载
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;
}
}
二、百万数据的导入(excel数据导入到数据库)
方案1:单线程逐行读取sheet,单线程逐行存储库
方案2:单线程逐行读取sheet,单线程批量存储库
方案3:多线程读取sheet,单线程批量插入(2分钟)
方案4:多线程解析,多线程批量插入(1分钟)
三、百万数据的导出(数据库保存到excel
方案1:查全表,写入一个sheet
方案2:查全部,写入多个sheet
方案3:分页查询,每页数据写入每个sheet
方案4:多线程分页查询,每页数据写入每个sheet
方案4前提知道数据库有100万条,分成20页
1、多线程查询数据库20页数据,
2、多线程存储到20个sheet
四、设置导出的excel的单元格格式(是设置-单元格格式哪种:常规、数值、货币)
com.alibaba.excel.constant.BuiltinFormats BUILTIN_FORMATS_CN
@ContentStyle(dataFormat = 14) //BUILTIN_FORMATS_CN数值下标,14索引位是yyyy/m/d,是日期单元格格式
@DateTimeFormat("yyyy/M/dd")
@ExcelProperty({"时间"})
private Date exportDate;
public static void main(String[] args) {
String[] builtinFormatsCn = BuiltinFormats.BUILTIN_FORMATS_CN;
for (int i = 0; i <= builtinFormatsCn.length - 1; i++) {
String s = builtinFormatsCn[i];
System.out.println(i + ": " + s);
}
}