package com.qianwen.core.excel.util;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
|
import com.alibaba.excel.read.listener.ReadListener;
|
import com.alibaba.excel.util.DateUtils;
|
import com.alibaba.excel.write.handler.WriteHandler;
|
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import java.io.BufferedInputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.net.URLEncoder;
|
import java.util.Date;
|
import java.util.List;
|
import javax.servlet.http.HttpServletResponse;
|
import org.apache.commons.codec.Charsets;
|
import org.apache.commons.fileupload.FileItem;
|
import org.apache.commons.fileupload.disk.DiskFileItem;
|
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
|
import com.qianwen.core.excel.listener.DataListener;
|
import com.qianwen.core.excel.listener.ImportListener;
|
import com.qianwen.core.excel.support.ExcelException;
|
import com.qianwen.core.excel.support.ExcelImporter;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.util.StringUtils;
|
import org.springframework.web.multipart.MultipartFile;
|
import org.springframework.web.multipart.commons.CommonsMultipartFile;
|
|
|
public class ExcelUtil {
|
public static <T> List<T> read(MultipartFile excel, Class<T> clazz) {
|
DataListener<T> dataListener = new DataListener<>();
|
ExcelReaderBuilder builder = getReaderBuilder(excel, dataListener, clazz);
|
if (builder == null) {
|
return null;
|
}
|
builder.doReadAll();
|
return dataListener.getDataList();
|
}
|
|
public static <T> List<T> read(MultipartFile excel, int sheetNo, Class<T> clazz) {
|
return read(excel, sheetNo, 1, clazz);
|
}
|
|
public static <T> List<T> read(MultipartFile excel, int sheetNo, int headRowNumber, Class<T> clazz) {
|
DataListener<T> dataListener = new DataListener<>();
|
ExcelReaderBuilder builder = getReaderBuilder(excel, dataListener, clazz);
|
if (builder == null) {
|
return null;
|
}
|
builder.sheet(Integer.valueOf(sheetNo)).headRowNumber(Integer.valueOf(headRowNumber)).doRead();
|
return dataListener.getDataList();
|
}
|
|
public static <T> void save(MultipartFile excel, ExcelImporter<T> importer, Class<T> clazz) {
|
ImportListener<T> importListener = new ImportListener<>(importer);
|
ExcelReaderBuilder builder = getReaderBuilder(excel, importListener, clazz);
|
if (builder != null) {
|
builder.doReadAll();
|
}
|
}
|
|
public static <T> void export(HttpServletResponse response, List<T> dataList, Class<T> clazz) {
|
export(response, DateUtils.format(new Date(), "yyyyMMddHHmmss"), "导出数据", dataList, clazz);
|
}
|
|
public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz) {
|
try {
|
response.setContentType("application/vnd.ms-excel");
|
response.setCharacterEncoding(Charsets.UTF_8.name());
|
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, Charsets.UTF_8.name()) + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
|
}catch(Exception e) {
|
throw new RuntimeException(e);
|
}
|
}
|
|
public static <T> MultipartFile exportToMultipartFile(String excelFileName, String sheetName, List<T> dataList, Class<T> clazz) {
|
try {
|
String fileName = new String(excelFileName.getBytes(), "UTF-8");
|
DiskFileItemFactory factory = new DiskFileItemFactory();
|
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));
|
FileItem fileItem = factory.createItem("excel", "application/vnd.ms-excel", true, fileName);
|
try (OutputStream os = fileItem.getOutputStream()) {
|
EasyExcel.write(os, clazz).sheet(sheetName).doWrite(dataList);
|
}
|
return (MultipartFile)new CommonsMultipartFile(fileItem);
|
} catch (Throwable ex) {
|
throw new RuntimeException(ex);
|
}
|
}
|
|
public static <T, D> MultipartFile exportFillTemplateToMultipartFile(String filePath, String excelFileName, String sheetName, List<T> dataList, D normalInfo, AbstractMergeStrategy mergeStrategy) {
|
try {
|
String fileName = new String(excelFileName.getBytes(), "UTF-8");
|
DiskFileItemFactory factory = new DiskFileItemFactory();
|
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));
|
FileItem fileItem = factory.createItem("excel", "application/vnd.ms-excel", true, fileName);
|
OutputStream os = fileItem.getOutputStream();
|
ClassPathResource classPathResource = new ClassPathResource(filePath);
|
InputStream inputStream = classPathResource.getInputStream();
|
ExcelWriter excelWriter = EasyExcel.write(os).registerWriteHandler(mergeStrategy).withTemplate(inputStream).build();
|
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
|
excelWriter.fill(dataList, writeSheet);
|
if (null != normalInfo) {
|
excelWriter.fill(normalInfo, writeSheet);
|
}
|
excelWriter.finish();
|
os.close();
|
return new CommonsMultipartFile(fileItem);
|
} catch (Throwable ex) {
|
throw new RuntimeException(ex);
|
}
|
}
|
|
public static MultipartFile fileToMultipartFile(File file) {
|
try {
|
String fileName = file.getName();
|
DiskFileItem diskFileItem = new DiskFileItem(fileName, "application/vnd.ms-excel", false, file.getName(), (int)file.length(), file.getParentFile());
|
byte[] buffer = new byte[4096];
|
try(InputStream inputStream = new FileInputStream(file); OutputStream os = diskFileItem.getOutputStream()) {
|
int n;
|
while ((n = inputStream.read(buffer, 0, 4096)) != -1) {
|
os.write(buffer, 0, n);
|
}
|
return new CommonsMultipartFile((FileItem)diskFileItem);
|
|
}
|
} catch (Throwable ex) {
|
throw new RuntimeException(ex);
|
}
|
}
|
|
public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, WriteHandler writeHandler, Class<T> clazz) {
|
try {
|
response.setContentType("application/vnd.ms-excel");
|
response.setCharacterEncoding(Charsets.UTF_8.name());
|
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, Charsets.UTF_8.name()) + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(dataList);
|
} catch (Throwable ex) {
|
throw new RuntimeException(ex);
|
}
|
}
|
|
public static <T> ExcelReaderBuilder getReaderBuilder(MultipartFile excel, ReadListener<T> readListener, Class<T> clazz) {
|
String filename = excel.getOriginalFilename();
|
if (StringUtils.isEmpty(filename)) {
|
throw new ExcelException("请上传文件!");
|
}
|
if (!StringUtils.endsWithIgnoreCase(filename, ".xls") && !StringUtils.endsWithIgnoreCase(filename, ".xlsx")) {
|
throw new ExcelException("请上传正确的excel文件!");
|
}
|
try {
|
InputStream inputStream = new BufferedInputStream(excel.getInputStream());
|
return EasyExcel.read(inputStream, clazz, readListener);
|
} catch (IOException e) {
|
e.printStackTrace();
|
return null;
|
}
|
}
|
}
|
