多sheet页导出 + FTP上传文件实战

/**
 * 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;
    }
}

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();
                }
            }
        }
    }
}

/**
     * 电表快照
     *
     * @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;
    }

/**
     * 数据写入到本地的方法
     *
     * @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();
        }
    }

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("任务没有执行成功");
        }
    }