-
前言
-
多sheet页导出工具类代码
-
封装思路
-
封装对象
-
怎么用呢?
-
FTP上传文件
-
FTP工具类:
-
使用
前言
之前有个需求，需要把数据库中的历史数据，统计数据，写到同一个excel的不同sheet页中,一个sheet页是历史数据一个sheet页是统计数据，并且要求按照给定的格式定时存到txt文件中，最后通过ftp上传到文件服务器，要求上传一份excel（文件名为当天日期），一份txt（文件名为当天日期）。这个功能有遇到过的吗?
当时觉得挺简单的，结果发现这块涉及到三个问题:
-
1.他希望txt的头部和excel的表格头是动态获取的，也就是不同的数据库的不同表的字段不同，如果产品经理今天想导这个表，明天想导那个表，不是累死了，得写一个封装方法，封装一个sheet页的标题头和每页数据。 -
2.上传到ftp,需要先从数据库中的数据处理完好写到本地，然后再上传到ftp服务器 -
3.每天生成一张excel，一个txt，得做定时任务，这个很简单。
最后实现了，感觉可以分享的是: 自己封装的多sheet页工具类，和ftp上传工具类。也为了自己以后查阅。
多sheet页导出工具类代码
封装思路
poi中，一个excel是一个XSSFWorkbook， 如果想多个sheet页导出，得有个sheet页的对象(本例定义为ExcelHeaderModel)用来保存每个sheet页的标题头集合和每个sheet页的数据集合， 同时标题头需要和数据集合有有所关联，在标题头对象中可以存数据库的字段名称，每个sheet页的数据集合，最好在写查询列表接口时候，返回List<Map<String， Object>> ，map的key 可以通过标题头对象的字段名称给获取，这步很核心。对于整个excel导出来说，就是组装一个sheet页的集合，一个excel的名称，以及一个要导出的目录位置。
下面看我的封装代码:
封装对象
-
ExcelHeaderModel(Excel某列表头对象)'
/**
* Created by hfl
*
* 表题头对象（excel最上面一个格子的对象，可以控制整个列的宽度，标题，
* 定义column为了以后获取数据用）
*
*/
public class ExcelHeaderModel {
private String column;//表头某列对应的字段
private String title; //表头某列的标题
private Integer width;
public ExcelHeaderModel() {
}
public ExcelHeaderModel(String column, String title) {
this.column = column;
this.title = title;
this.width = 15;
}
public ExcelHeaderModel(String column, String title,Integer width) {
this.column = column;
this.title = title;
this.width = width;
}
public String getColumn() {
return column;
}
public void setColumn(String column) {
this.column = column;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getWidth() {
return width;
}
public void setWidth(Integer width) {
this.width = width;
}
}
-
Sheet页对象
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Created by hfl
*
* Excel文件中的Sheet对象
*
*/
public class SheetModel {
private String sheetName;//该sheet页名称
private String txtName;//txt文件名，一般项目不需要，这个字段我的业务需要
private List<ExcelHeaderModel> excelHeaderModelList = new ArrayList<>();//该sheet表头
private List<Map<String, Object>> datas= new ArrayList<>();//该sheet的结果集
public SheetModel() {
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<ExcelHeaderModel> getExcelHeaderModelList() {
return excelHeaderModelList;
}
public void setExcelHeaderModelList(List<ExcelHeaderModel> excelHeaderModelList) {
this.excelHeaderModelList = excelHeaderModelList;
}
public List<Map<String, Object>> getDatas() {
return datas;
}
public void setDatas(List<Map<String, Object>> datas) {
this.datas = datas;
}
public String getTxtName() {
return txtName;
}
public void setTxtName(String txtName) {
this.txtName = txtName;
}
}
导出工具类:
import models.poi.ExcelHeaderModel;
import models.poi.SheetModel;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* Created by hfl
*
* 数据库表导出到本地文件工具类
*/
public class ExportExcelUtil {
/**
* 多个sheet页导出
*
* @param sheetModelList
* @param exportExcelName
* @param targetPath
*/
public static void exportExcelMore(List<SheetModel> sheetModelList, String exportExcelName, String targetPath) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成表格中非标题栏的样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.WHITE.index);//背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成表格中非标题栏的字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontName("Microsoft Yahei");
font.setFontHeightInPoints((short) 10);
font.setBold(false);
// 把字体应用到当前的样式
style.setFont(font);
// 设置表格标题栏的样式
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置标题栏字体
XSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 10);
titleFont.setBold(true);
titleFont.setColor(HSSFColor.WHITE.index);
titleFont.setFontName("Microsoft Yahei");
// 把字体应用到当前的样式
titleStyle.setFont(titleFont);
//遍历添加sheet名 标题头 数据
for (int i = 0, len = sheetModelList.size(); i < len; i++) {
SheetModel sheetmodel = sheetModelList.get(i);
String sheetName = sheetmodel.getSheetName();
List<ExcelHeaderModel> headerList = sheetmodel.getExcelHeaderModelList();
List<Map<String, Object>> dataList = sheetmodel.getDatas();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(sheetName);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short j = 0; j < headerList.size(); j++) {
ExcelHeaderModel excelHeaderModel = headerList.get(j);
XSSFCell cell = row.createCell(j);
cell.setCellStyle(titleStyle);
XSSFRichTextString text = new XSSFRichTextString(excelHeaderModel.getTitle());
cell.setCellValue(text);
sheet.setColumnWidth(j, excelHeaderModel.getWidth() * 256);
}
Iterator<Map<String, Object>> it = dataList.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
Map<String, Object> data = it.next();
int k = 0;
String value = null;
for (short j = 0; j < headerList.size(); j++) {
ExcelHeaderModel excelHeaderModel = headerList.get(j);
for (String key : data.keySet()) {
XSSFCell cell = row.createCell(k);
if (excelHeaderModel.getColumn().equalsIgnoreCase(key)) { //标题和数据对应的条件判断
value = data.get(key) + "";
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString("null".equalsIgnoreCase(value) ? "" : value);
cell.setCellValue(text);
break;
}
}
k++;
}
}
}
OutputStream out = null;
try {
String tmpPath = targetPath + exportExcelName + ".xlsx";
out = new FileOutputStream(tmpPath);
workbook.write(out);
System.out.println("数据库表导出成功");
} catch (IOException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
封装的代码就是上面的这些.
怎么用呢?
可以参考如何组装sheet页,如下代码
/**
* 电表快照
*
* @return
*/
public static SheetModel loadSnapshot(Date beginDate, Date endDate) {
String beginDateStr = DateUtil.dateToString(beginDate, "yyyy-MM-dd");
String endDateStr = DateUtil.dateToString(endDate, "yyyy-MM-dd");
SheetModel sheetModel = new SheetModel();
sheetModel.setSheetName("电表快照");
sheetModel.setTxtName("electricity_detail");
sheetModel.setExcelHeaderModelList(new ArrayList<>());
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("laster_read_time", "抄读时间", 20));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("serial_number", "客户编号", 9));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("meter_address", "表计地址"));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("meter_type", "表计类型"));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("level_1", "区域", 8));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("level_2", "公司", 5));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("level_3", "楼层", 5));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("level_4", "房间", 5));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("user_name", "用户名", 10));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("home_address", "用户住址", 9));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("beilv", "电量调整系数"));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("total", "正向有功总电能"));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("a", "A相电流", 10));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("b", "B相电流", 10));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("c", "C相电流", 10));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("aw", "A相有功功率(Kw)", 17));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("bw", "B相有功功率(Kw)", 17));
sheetModel.getExcelHeaderModelList().add(new ExcelHeaderModel("cw", "C相有功功率(Kw)", 17));
String snapshotSql = "select " +
" t3.read_hour as laster_read_time, " +
" serial_number, " +
" meter_address, " +
" t2.text as meter_type, " +
" level_1, " +
" level_2, " +
" level_3, " +
" level_4, " +
" user_name, " +
" home_address, " +
" beilv, " +
" ifnull(t3.value,0) as total, " +
" ifnull(t3.a,0) as a, " +
" ifnull(t3.b,0) as b, " +
" ifnull(t3.c,0) as c, " +
" CONVERT(ifnull(t3.a,0) * 220 /1000,decimal(10,2)) as aw, " +
" CONVERT(ifnull(t3.b,0) * 220 /1000,decimal(10,2)) as bw, " +
" CONVERT(ifnull(t3.c,0) * 220 /1000,decimal(10,2)) as cw " +
" from user_info t1 " +
" left join meter_type t2 on t1.meter_type_id = t2.id " +
" left join jhw_snapshots t3 on t1.id = t3.user_id" +
" where t3.read_time>='%s' and t3.read_time<'%s'"+
" order by t1.serial_number,t3.read_time";
Query query = JPA.em().createNativeQuery(String.format(snapshotSql,beginDateStr,endDateStr));
//特别注意，这里是用的 List<Map<String, Object>> map为<字段，值>
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
sheetModel.setDatas(query.getResultList());
return sheetModel;
}
这样这个多sheet页文件就会下载到自己指定目录中了。
备注：本人使用的java敏捷开发框架play，springboot就参考下也一样，play标红的位置是从配置文件中取变量值。
FTP上传文件
我的上传要求一个数据库表一个txt文件，所以我需要根据sheet页的标题生成4个txt文件。这里复用刚才生成excel时组装的每个sheet页数据，直接用SheetModel对象来上传。
下面是我生成txt的代码。为了说明用，可以不贴的。
/**
* 数据写入到本地的方法
*
* @param filePath 文件目录如: "/JHuaW/JRongB/shouxinjieduan/history_data"
* @param sheetModel
*/
public static void write(String filePath, SheetModel sheetModel) {
List<ExcelHeaderModel> excelHeaderModelList = sheetModel.getExcelHeaderModelList(); //表头
int len = sheetModel.getExcelHeaderModelList().size(); //列数
List<Map<String, Object>> datas = sheetModel.getDatas(); //每页sheet数据集合
BufferedWriter bw = null;
FileWriter fw = null;
try {
if (excelHeaderModelList != null && len > 0) {
//File file = createFile("G:/excel",sheetModel.getTxtName());
File file = createFile(filePath, sheetModel.getTxtName());
fw = new FileWriter(file.getAbsoluteFile());
bw = new BufferedWriter(fw);
ExcelHeaderModel model = null;
//遍历每条数据
for (int j = 0; j < datas.size(); j++) {
StringBuilder builder = new StringBuilder();
Map<String, Object> data = datas.get(j); //每条数据
for (int i = 0; i < len; i++) {
model = excelHeaderModelList.get(i);
String content = model.getColumn(); //每个字段名称
String val = data.get(content) != null ? StringUtil.initValue(data.get(content)) : "null"; //每个单元格数据
if (null == val || "".equals(val.trim()) || "null".equals(val.trim())) { //空数据用空格代替
builder.append(" ");
} else {
builder.append(val);
}
if (i < len - 1) builder.append("@@@");//最后一个单元格数据后面不用加分隔符号
}
String strLine = builder.toString();
bw.write(strLine);
bw.newLine(); //换行
}
}
bw.close();
System.out.println("导出txt完成");
} catch (IOException e) {
e.printStackTrace();
}
}
有了txt和excel文件了，开始ftp上传了。这里封装了个FTP工具类.
FTP工具类:
import org.apache.commons.lang3.Validate;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.io.*;
import java.net.UnknownHostException;
import java.util.ArrayList;
import java.util.List;
/**
* @author hfl
* ftp上传工具类
*/
@Component
public class FTPUtil {
private final static Logger logger = LoggerFactory.getLogger(FTPUtil.class);
public static final String ANONYMOUS_LOGIN = "anonymous";
private FTPClient ftp;
private boolean isConnected;
/**
* OPTS UTF8字符串常量
**/
private static final String OPTS_UTF8 = "OPTS UTF8";
/**
* UTF-8字符编码
**/
private static final String CHARSET_UTF8 = "UTF-8";
/** 本地字符编码 */
private static String LOCAL_CHARSET = "GBK";
@Value("${ftp.host}")
public String host;
@Value("${ftp.port}")
public int port;
@Value("${ftp.username}")
public String username;
@Value("${ftp.password}")
public String password;
@Value("${ftp.filepath}")
public String filepath;
private String temporaryPath;
public FTPUtil() {
ftp = new FTPClient();
isConnected = false;
}
public FTPUtil(int defaultTimeoutSecond, int connectTimeoutSecond, int dataTimeoutSecond) {
ftp = new FTPClient();
isConnected = false;
ftp.setDefaultTimeout(defaultTimeoutSecond * 1000);
ftp.setConnectTimeout(connectTimeoutSecond * 1000);
ftp.setDataTimeout(dataTimeoutSecond * 1000);
}
/**
* @param isTextMode text / binary mode switch
* @throws IOException on I/O errors
*/
public void connect(boolean isTextMode) throws IOException {
// Connect to server.
try {
ftp.connect(host, port);
} catch (UnknownHostException ex) {
throw new IOException("Can't find FTP server '" + host + "'");
}
// Check rsponse after connection attempt.
int reply = ftp.getReplyCode();
if (FTPReply.isPositiveCompletion(ftp.sendCommand(OPTS_UTF8, "ON"))) {// 开启服务器对UTF-8的支持，如果服务器支持就用UTF-8编码，否则就使用本地编码（GBK）.
LOCAL_CHARSET = CHARSET_UTF8;
}
ftp.setControlEncoding(LOCAL_CHARSET);
ftp.enterLocalPassiveMode();// 设置被动模式
if (!FTPReply.isPositiveCompletion(reply)) {
disconnect();
throw new IOException("Can't connect to server '" + host + "'");
}
if ("".equals(username)) {
username = ANONYMOUS_LOGIN;
}
// Login.
if (!ftp.login(username, password)) {
isConnected = false;
disconnect();
throw new IOException("Can't login to server '" + host + "'");
} else {
isConnected = true;
}
// Set data transfer mode.
if (isTextMode) {
ftp.setFileType(FTP.ASCII_FILE_TYPE);
} else {
ftp.setFileType(FTP.BINARY_FILE_TYPE);
}
}
/**
* Uploads the file to the FTP server.
* 将文件上传到FTP服务器。
*
* @param ftpFileName server file name (with absolute path)
* @param localFile local file to upload
* @throws IOException on I/O errors
*/
public void upload(String ftpFileName, File localFile) throws IOException {
// File check.
if (!localFile.exists()) {
throw new IOException("Can't upload '" + localFile.getAbsolutePath() + "'. This file doesn't exist.");
}
// Upload.
InputStream in = null;
try {
// Use passive mode to pass firewalls.
ftp.enterLocalPassiveMode();
in = new BufferedInputStream(new FileInputStream(localFile));
if (!ftp.storeFile(ftpFileName, in)) {
throw new IOException("Can't upload file '" + ftpFileName + "' to FTP server. Check FTP permissions and path.");
}
} finally {
try {
in.close();
} catch (IOException ex) {
}
}
}
/**
* 上传文件 流的方式
* 创建文件
*
* @param ftpFileName 包含文件夹路径
* @throws IOException
*/
public void howToStartTheUploadStream(String ftpFileName, InputStream inputStream) throws Exception {
//判断文件夹是否存在
if (!filepath.endsWith("/")) {
filepath += "/";
}
temporaryPath = filepath + System.currentTimeMillis();
String[] pathList = temporaryPath.split("/");
for (int i = 0; i < pathList.length; i++) {
if ("".equals(pathList[i].toString().trim())) {
continue;
}
//不存在 则创建 防止解决中文乱码
String s = new String(pathList[i].getBytes(LOCAL_CHARSET), FTP.DEFAULT_CONTROL_ENCODING);
if (!this.existsDir(s)) {
//新建后已切换路径
this.createDir(s);
}
}
// Upload.
InputStream in = null;
try {
ftp.enterLocalPassiveMode();
in = new BufferedInputStream(inputStream);
if (!ftp.storeFile(new String(ftpFileName.getBytes(LOCAL_CHARSET), FTP.DEFAULT_CONTROL_ENCODING), in)) {
logger.error("Can't upload file '{}' to FTP server. Check FTP permissions and path.", ftpFileName);
throw new IOException("Can't upload file '" + ftpFileName + "' to FTP server. Check FTP permissions and path.");
}
} finally {
try {
in.close();
} catch (IOException ex) {
logger.error("上传文件 流的方式,错误日志：{}", ex.toString());
}
}
}
/**
* Downloads the file from the FTP server.
* *从FTP服务器下载文件
*
* @param ftpFileName server file name (with absolute path)
* @param localFile local file to download into
* @throws IOException on I/O errors
*/
public void download(String ftpFileName, File localFile) throws IOException {
// Download.
OutputStream out = null;
try {
// Use passive mode to pass firewalls.
ftp.enterLocalPassiveMode();
// Get file info.
FTPFile[] fileInfoArray = ftp.listFiles(ftpFileName);
if (fileInfoArray == null) {
throw new FileNotFoundException("File " + ftpFileName + " was not found on FTP server.");
}
// Check file size.
FTPFile fileInfo = fileInfoArray[0];
long size = fileInfo.getSize();
if (size > Integer.MAX_VALUE) {
throw new IOException("File " + ftpFileName + " is too large.");
}
// Download file.
out = new BufferedOutputStream(new FileOutputStream(localFile));
if (!ftp.retrieveFile(ftpFileName, out)) {
throw new IOException("Error loading file " + ftpFileName + " from FTP server. Check FTP permissions and path.");
}
out.flush();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException ex) {
}
}
}
}
/**
* Removes the file from the FTP server.
* 从FTP服务器上删除文件。
*
* @param ftpFileName server file name (with absolute path)
* @throws IOException on I/O errors
*/
public static void remove(String ftpFileName) throws IOException {
FTPUtil ftpUtil = new FTPUtil();
ftpUtil.connect(false);
if (!ftpUtil.ftp.deleteFile(ftpFileName)) {
throw new IOException("Can't remove file '" + ftpFileName + "' from FTP server.");
}
}
/**
* Lists the files in the given FTP directory.
* 列出给定FTP目录中的文件。
*
* @param path absolute path on the server
* @return files relative names list
* @throws IOException on I/O errors
*/
public List<String> list(String path) throws IOException {
List<String> fileList = new ArrayList<String>();
// Use passive mode to pass firewalls.
ftp.enterLocalPassiveMode();
FTPFile[] ftpFiles = ftp.listFiles(path);
int size = (ftpFiles == null) ? 0 : ftpFiles.length;
for (int i = 0; i < size; i++) {
FTPFile ftpFile = ftpFiles[i];
if (ftpFile.isFile()) {
fileList.add(ftpFile.getName());
}
}
return fileList;
}
/**
* Sends an FTP Server site specific command
* 发送FTP服务器站点特定命令
*
* @param args site command arguments
* @throws IOException on I/O errors
*/
public void sendSiteCommand(String args) throws IOException {
if (ftp.isConnected()) {
try {
ftp.sendSiteCommand(args);
} catch (IOException ex) {
}
}
}
/**
* Disconnects from the FTP server
* *断开与FTP服务器的连接
*
* @throws IOException on I/O errors
*/
public void disconnect() throws IOException {
if (ftp.isConnected()) {
try {
ftp.logout();
ftp.disconnect();
isConnected = false;
} catch (IOException ex) {
}
}
}
/**
* Makes the full name of the file on the FTP server by joining its path and
* the local file name.
* 通过加入文件路径和*本地文件名，在FTP服务器上形成文件的全名。
*
* @param ftpPath file path on the server
* @param localFile local file
* @return full name of the file on the FTP server
*/
public String makeFTPFileName(String ftpPath, File localFile) {
if ("".equals(ftpPath)) {
return localFile.getName();
} else {
String path = ftpPath.trim();
if (path.charAt(path.length() - 1) != '/') {
path = path + '/';
}
return path + localFile.getName();
}
}
/**
* Test coonection to ftp server
* 测试与ftp服务器的连接
*
* @return true, if connected
*/
public boolean isConnected() {
return isConnected;
}
/**
* Get current directory on ftp server
*
* @return current directory
*/
public String getWorkingDirectory() {
if (!isConnected) {
return "";
}
try {
return ftp.printWorkingDirectory();
} catch (IOException e) {
}
return "";
}
/**
* Set working directory on ftp server
*
* @param dir new working directory
* @return true, if working directory changed
*/
public boolean setWorkingDirectory(String dir) {
if (!isConnected) {
return false;
}
try {
return ftp.changeWorkingDirectory(dir);
} catch (IOException e) {
}
return false;
}
/**
* Change working directory on ftp server to parent directory
*
* @return true, if working directory changed
*/
public boolean setParentDirectory() {
if (!isConnected) {
return false;
}
try {
return ftp.changeToParentDirectory();
} catch (IOException e) {
}
return false;
}
/**
* Get parent directory name on ftp server
*
* @return parent directory
*/
public String getParentDirectory() {
if (!isConnected) {
return "";
}
String w = getWorkingDirectory();
setParentDirectory();
String p = getWorkingDirectory();
setWorkingDirectory(w);
return p;
}
/**
* Get directory contents on ftp server
*
* @param Path
* directory
* @return list of FTPFileInfo structures
* @throws IOException
*/
// public List<FfpFileInfo> listFiles(String Path) throws IOException {
// List<FfpFileInfo> fileList = new ArrayList<FfpFileInfo>();
//
// // Use passive mode to pass firewalls.
// ftp.enterLocalPassiveMode();
// FTPFile[] ftpFiles = ftp.listFiles(Path);
// int size = (ftpFiles == null) ? 0 : ftpFiles.length;
// for (int i = 0; i < size; i++) {
// FTPFile ftpFile = ftpFiles[i];
// FfpFileInfo fi = new FfpFileInfo();
// fi.setName(ftpFile.getName());
// fi.setSize(ftpFile.getSize());
// fi.setTimestamp(ftpFile.getTimestamp());
// fi.setType(ftpFile.isDirectory());
// fileList.add(fi);
// }
//
// return fileList;
// }
/**
* Get file from ftp server into given output stream
*
* @param ftpFileName file name on ftp server
* @param out OutputStream
* @throws IOException
*/
public void getFile(String ftpFileName, OutputStream out) throws IOException {
try {
// Use passive mode to pass firewalls.
ftp.enterLocalPassiveMode();
// Get file info.
FTPFile[] fileInfoArray = ftp.listFiles(ftpFileName);
if (fileInfoArray == null) {
throw new FileNotFoundException("File '" + ftpFileName + "' was not found on FTP server.");
}
// Check file size.
FTPFile fileInfo = fileInfoArray[0];
long size = fileInfo.getSize();
if (size > Integer.MAX_VALUE) {
throw new IOException("File '" + ftpFileName + "' is too large.");
}
// Download file.
if (!ftp.retrieveFile(ftpFileName, out)) {
throw new IOException("Error loading file '" + ftpFileName + "' from FTP server. Check FTP permissions and path.");
}
out.flush();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException ex) {
}
}
}
}
/**
* Put file on ftp server from given input stream
*
* @param ftpFileName file name on ftp server
* @param in InputStream
* @throws IOException
*/
public void putFile(String ftpFileName, InputStream in) throws IOException {
try {
// Use passive mode to pass firewalls.
ftp.enterLocalPassiveMode();
if (!ftp.storeFile(ftpFileName, in)) {
throw new IOException("Can't upload file '" + ftpFileName + "' to FTP server. Check FTP permissions and path.");
}
} finally {
try {
in.close();
} catch (IOException ex) {
}
}
}
/**
* 判断文件夹是否存在 存在并切换该路径
*
* @param path ftp路径
*/
public boolean existsDir(String path) {
logger.info("正在判断文件夹是否存在,path=" + path);
Validate.notNull(path, "ftp路径");
try {
//登录
//this.connect(false);
boolean sate = this.ftp.changeWorkingDirectory(path);
logger.info("FTP 文件上传 文件夹是否存在:{},path={}", sate, path);
return sate;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
/**
* 改变路径
*
* @param path 新路径
* @throws Exception
*/
private void changeDirectory(String path) throws Exception {
this.connect(false);
try {
if (!this.ftp.changeWorkingDirectory(path)) {
String[] temp = path.split("/");
String tmpStr = "";
for (int i = 1; i < temp.length; i++) {
tmpStr += "/" + temp[i];
if (this.ftp.changeWorkingDirectory(tmpStr)) {
continue;
} else {
this.ftp.makeDirectory(tmpStr);
}
}
this.ftp.changeWorkingDirectory(path);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 判断文件是否存在
*
* @param pathAndName
* @return
* @throws IOException
*/
public boolean exsitsFile(String pathAndName) throws IOException {
FTPUtil ftpUtil = new FTPUtil();
ftpUtil.connect(false);
Validate.notNull(pathAndName, "判断文件是否存在ftp目录上");
String path = pathAndName.substring(pathAndName.lastIndexOf("/") + 1);
String fileName = pathAndName.substring(0, pathAndName.lastIndexOf("/") + 1);
try {
changeDirectory(path);
// 获取远程ftp上指定文件的InputStream
InputStream is = ftpUtil.ftp.retrieveFileStream(fileName);
if (null != is) {
return true;
}
} catch (Exception e) {
logger.info("FTP 文件上传 判断文件是否存在 执行异常 path={} fileName={}", path + ", fileName=", fileName);
throw new RuntimeException("判断文件是否存在ftp目录上时报错,path=" + path + ", fileName=" + fileName);
}
return false;
}
/**
* 上传文件
* 包含文件目录（）
*
* @param pathname
* @param originfilename
*/
public void transfer(String pathname, String originfilename) {
try {
this.connect(false);
this.upload(pathname, new File(originfilename));
logger.info("FTP 文件上传 上传成功 文件名称：{}", pathname);
} catch (IOException e) {
logger.info("FTP 文件上传 上传失败 文件名称：{} 错误日志：{}", pathname, e.toString());
e.printStackTrace();
}
}
/**
* 上传文件
* 包含文件目录（）
* 流的方式
*
* @param pathname 含目录的文件名称
* @param in 文件流
*/
public String howToUploadFileStream(String pathname, InputStream in) {
try {
this.connect(false);
this.howToStartTheUploadStream(pathname, in);
if (!temporaryPath.startsWith("/")) {
temporaryPath = "/" + temporaryPath;
}
if (!temporaryPath.endsWith("/")) {
temporaryPath += "/";
}
String url = "ftp://" + username + ":" + password + "@" + host + ":" + port + temporaryPath + pathname;
logger.info("FTP 文件上传 上传成功 返回URL:{}", url);
return url;
} catch (Exception e) {
e.printStackTrace();
logger.error("FTP 文件上传 上传失败 错误日志:{}", e.toString());
return e.toString();
}
}
public boolean createDir(String dirname) {
try {
//登录
//this.connect(false);
logger.info("FTP 文件上传 正在在目标服务器上创建文件夹:{} ", dirname);
//创建文件夹
boolean state = this.ftp.makeDirectory(dirname);
this.ftp.changeWorkingDirectory(dirname);
logger.info("FTP 文件上传 在目标服务器上创建文件夹文件夹:{} 是否创建成功：{} ", dirname, state);
return state;
} catch (Exception ex) {
logger.error("FTP 文件上传 创建文件夹失败 错误日志：{}", ex.getMessage());
}
return false;
}
}
使用
//本地新建文件夹和文件
public static File createFile(String filePath, String name) throws IOException {
File file = new File(filePath + File.separator + name + ".txt");
File fileParent = file.getParentFile();
if (!fileParent.exists()) fileParent.mkdirs(); //没有文件目录就创建
file.createNewFile();
return file;
}
//上传方法
public static void up(SheetModel s) {
try {
TXTUtil.write(excelPath, s);
String datePath = DateUtil.dateToString(DateUtil.getAfterDate(new Date(), -1), "yyyy-MM-dd");
String originfilename = excelPath + s.getTxtName() + ".txt";
String upPath = ftpPath + datePath + "/"; //拼接ftp上的文件夹
String pathFile = upPath + s.getTxtName() + ".txt"; //ftp上的文件夹和文件名
//判断是否存在，如果存在就先删除
try {
FTPUtil.remove(pathFile);
} catch (Exception ex) {
ex.printStackTrace();
}
//上传
FTPUtil.createDir(upPath);
FTPUtil.transfer(pathFile, originfilename);
} catch (Exception e) {
log.error("任务没有执行成功");
}
}
-
用到的jar包可能有（如果报错了可以试下） 
以上就是这个任务的主要功能点，支持多sheet导出和文件写入本地，然后上传到服务器。仅供参考。
开通了个微信公众号：
搜索：怒放de每一天
后续可能不定时推送相关文章，期待和大家一起成长！！
大功告成，完！
原文始发于微信公众号（怒放de每一天）：多sheet页导出 + FTP上传文件实战
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/106819.html
