JAVA -- 生成Excel 并上传到 FTP 不生成本地文件

直接上代码

POM.XML 这里只是主要的jar

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

/**
     * 导出excel
     * @param fileName 要导出的文件名（包含路径）
     * @param data
     * @return
     */
    public boolean exportToExcel(String fileName, List<String[]> data)  {

        log.info("导出excel开始，fileName: " + fileName + ", data个数：" + data.size());
        if (!fileName.endsWith(".xlsx")){
            log.error("fileName有误，需要以xlsx结尾");
            return false;
        }
        // 声明一个工作薄
        Workbook workBook = null;
        boolean result = false;
        ByteArrayOutputStream ops = null;
        ByteArrayInputStream in = null;
        File file = new File(fileName);
        try {
            if (file.exists()) {
                file.delete();
            }
            file = new File(fileName);
            //重新复制模版文件
            FileUtils.copyFile(new File(ExcelExportUtil.class.getResource("/template/policy_Template.xlsx").getFile()),file);
            // 声明一个工作薄
            workBook = new XSSFWorkbook(new FileInputStream(file));
            // 生成一个表格
            Sheet sheet = workBook.getSheetAt(0);
            if (sheet == null) {
                sheet = workBook.createSheet("AutoPolicy");
            }
            //最新Excel列索引,从0开始
            int lastRowIndex = sheet.getLastRowNum();
            lastRowIndex++;
            // 设置表格默认列宽度
            sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
            // 产生表格表头列标题行
            Row row ;
            NumberFormat decimalFormat = new DecimalFormat("###,###.00");
            DecimalFormat zeroFormat = new DecimalFormat("0.00");
            // 遍历集合数据,产生数据行,前两行为标题行与表头行
            for (String[] dataRow : data) {
                row = sheet.createRow(lastRowIndex);
                lastRowIndex++;
                for (int j = 0; j < dataRow.length; j++) {
                    Cell contentCell = row.createCell(j);
                    String dataObject = dataRow[j];
                    if (dataObject != null) {
                        if (isNumeric(dataObject)){
                            contentCell.setCellType(CellType.NUMERIC);
                            if (dataObject.contains(".")){
                                if(new BigDecimal(dataObject).compareTo(BigDecimal.ZERO) == 0){
                                    contentCell.setCellValue(zeroFormat.format(0));
                                }else {
                                    contentCell.setCellValue(decimalFormat.format(new BigDecimal(dataObject)));
                                }
                            }else {
                                contentCell.setCellValue(dataObject);
                            }
                        } else {
                            contentCell.setCellType(CellType.STRING);
                            contentCell.setCellValue(dataObject);
                        }
                    } else {
                        // 设置单元格内容为字符型
                        contentCell.setCellValue("");
                    }
                }
            }
            ops = new ByteArrayOutputStream();
            workBook.write(ops);
            byte[] b = ops.toByteArray();
            in = new ByteArrayInputStream(b);
            fileStreamUpload.uploadFile(in, fileName);
            result = true;
            ops.flush();
            file.delete();
            System.out.println("success");
        } catch (Exception e) {
            log.error("导出excel失败，失败原因：" + e.getMessage());
            e.printStackTrace();
        }finally {
            IOUtils.closeQuietly(workBook);
            IOUtils.closeQuietly(ops);
            IOUtils.closeQuietly(in);
        }
        return result;
    }

其中：

注意点： 1、fileName 为文件名（.xlsx）。 

                2、只需要生成本地文件，只需要修改如下：

ops = new FileOutputStream(file);
workBook.write(ops);
ops.flush();
result = true;

               3、policy_Template.xlsx 为模板文件（放着我们生成的excel文件的Title）

               4、NumberFormat decimalFormat = new DecimalFormat("###,###.00"); 格式化金额

               5、FTP 上传代码：

public void uploadFile(InputStream inputStream,String fileName) {
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		byte[] buffer = new byte[1024];
		int len;
		try {
			while ((len = inputStream.read(buffer)) > -1) {
				baos.write(buffer, 0, len);
			}
			baos.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}
		InputStream stream1 = new ByteArrayInputStream(baos.toByteArray());
		InputStream stream2 = new ByteArrayInputStream(baos.toByteArray());
		BufferedInputStream inBuf = null;
		String ftpPath = CommUtil.fetchSystemDate("yyyy-MM-dd") + "/export_policy/";
		// 数据库存入地址
		try {
			inBuf = new BufferedInputStream(stream1);
			// 开始上传文件
			FileFtp ftp = new FileFtp();
			ftp.login(ftpIp, ftpUser, ftpPassWord);
			ftp.CreateDirecroty(ftpPath);
			ftp.uploadFile(inBuf, fileName);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (stream1 != null)
					stream1.close();
				if (stream2 != null)
					stream2.close();
				if (stream2 != null)
					inputStream.close();
				if (inBuf != null)
					inBuf.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

如有疑问，评论或加QQ+41717972@qq.com

补充FileFtp代码：
 

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;

public class FileFtp {
    FileFtp instance;
	private FTPClient ftpClient = null;
	
	public synchronized FileFtp getInstance() {
		if (instance == null) {
			instance = new FileFtp();
		}
		return instance;
	}
	
	public void login(String s_url, String uname, String pass) {
		ftpClient = new FTPClient();
		try {
			// 连接
			ftpClient.connect(s_url);
			ftpClient.login(uname, pass);
			// 检测连接是否成功
			int reply = ftpClient.getReplyCode();
			if (!FTPReply.isPositiveCompletion(reply)) {
				this.closeCon();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			// 关闭
			this.closeCon();
		}
	}
	
	public boolean uploadFile(InputStream in, String targetFname) {
		boolean flag = false;
		if (ftpClient != null) {
			BufferedInputStream buff = new BufferedInputStream(in);
			try {
				// 设置上传目录
				ftpClient.setBufferSize(1024);
				ftpClient.setControlEncoding("UTF-8");
				// 设置文件类型（二进制）
				ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
				// 重新开放新端口
				ftpClient.enterLocalPassiveMode();
				// 上传
				flag = ftpClient.storeFile(targetFname, buff);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if(buff != null)
					buff.close();
					if(in != null)
					in.close();
					this.closeCon();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	
	public void closeCon() {
		if (ftpClient != null) {
			if (ftpClient.isConnected()) {
				try {
					ftpClient.logout();
					ftpClient.disconnect();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			ftpClient = null;
		}
	}
	
	
    public boolean removeFile(String srcFname){  
        boolean flag = false;  
        if( ftpClient!=null ){  
            try {  
                flag = ftpClient.deleteFile(srcFname);  
            } catch (IOException e) {  
                e.printStackTrace();
            }finally{
            	 this.closeCon();  
            }
        }  
        return flag;  
    }
	
	public boolean CreateDirecroty(String remote) throws IOException {
		boolean success = true;
		String directory = remote.substring(0, remote.lastIndexOf("/") + 1);
		// 设置初始地址
		ftpClient.changeWorkingDirectory("/");
		// 如果远程目录不存在，则递归创建远程服务器目录
		if (!directory.equalsIgnoreCase("/") && !ftpClient.changeWorkingDirectory(new String(directory))) {
			int start = 0;
			int end = 0;
			if (directory.startsWith("/")) {
				start = 1;
			} else {
				start = 0;
			}
			end = directory.indexOf("/", start);
			while (true) {
				String subDirectory = new String(remote.substring(start, end));
				if (!ftpClient.changeWorkingDirectory(subDirectory)) {
					if (ftpClient.makeDirectory(subDirectory)) {
						ftpClient.changeWorkingDirectory(subDirectory);
					} else {
						success = false;
					}
				}
				start = end + 1;
				end = directory.indexOf("/", start);
				// 检查所有目录是否创建完毕
				if (end <= start) {
					break;
				}
			}
		}
		return success;
	}
}