package com.panzhihua.common.utlis;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
|
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
|
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
|
import com.panzhihua.common.excel.CustomSheetWriteHandler;
|
import com.panzhihua.common.model.dtos.FtpConfig;
|
import lombok.extern.slf4j.Slf4j;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.InputStream;
|
import java.util.List;
|
|
/**
|
* @author lyq
|
* excel工具类
|
*/
|
@Slf4j
|
public class ExcelUtils {
|
|
public static String adminExport(FtpConfig config, String name, List<List<String>> headList
|
, List<List<Object>> datalist, String sheetName, Object obj) {
|
String ftpUrl = "/mnt/data/web/excel/";
|
try {
|
SFTPUtil sftp = new SFTPUtil(config.getUserName(), config.getPassword(), config.getHost(), config.getPort());
|
sftp.login();
|
boolean existDir = sftp.isExistDir(ftpUrl + name);
|
if (!existDir) {
|
String property = System.getProperty("user.dir");
|
String fileName = property + File.separator + name;
|
ExcelWriter excelWriter = null;
|
InputStream inputStream = null;
|
try {
|
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
|
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
|
|
contentWriteCellStyle.setWrapped(true);
|
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
|
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
|
LongestMatchColumnWidthStyleStrategy longestMatchColumnWidthStyleStrategy = new LongestMatchColumnWidthStyleStrategy();
|
|
excelWriter = EasyExcel.write(fileName, Object.class)
|
.registerWriteHandler(horizontalCellStyleStrategy)
|
.registerWriteHandler(longestMatchColumnWidthStyleStrategy)
|
.registerWriteHandler(new CustomSheetWriteHandler()).build();
|
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(headList).build();
|
excelWriter.write(datalist, writeSheet);
|
excelWriter.finish();
|
File file = new File(fileName);
|
inputStream = new FileInputStream(file);
|
sftp.uploadMore(ftpUrl, name, inputStream);
|
sftp.logout();
|
inputStream.close();
|
String absolutePath = file.getAbsolutePath();
|
boolean delete = file.delete();
|
log.info("删除excel【{}】结果【{}】", absolutePath, delete);
|
} finally {
|
// 千万别忘记finish 会帮忙关闭流
|
if (inputStream != null) {
|
inputStream.close();
|
}
|
if (excelWriter != null) {
|
excelWriter.finish();
|
}
|
}
|
return config.getExcelUrl() + name;
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("文件传输失败【{}】", e.getMessage());
|
return null;
|
}
|
return null;
|
}
|
}
|
