读取FTP中不同文件格式的文件流后导出到浏览器

序言

有一个新的需求，前端提供下载的入口，后端能将指定了全路径的各种文件格式的文件下载到浏览器。
对于压缩的zip文件格式需要解析后写入到txt文件格式的文件中，其他的写入原本的文件格式的文件中。

1、连接ftp

<!--   jsch-sftp连接     --><dependency><groupId>com.jcraft</groupId><artifactId>jsch</artifactId><version>0.1.54</version></dependency>

java">@Slf4j
public class SftpUtil {public static ChannelSftp login(String userName, String host, String port, String password) throws JSchException {Session session;Channel channel;JSch jSch = new JSch();try {session = jSch.getSession(userName, host, Integer.parseInt(port));session.setPassword(password);// 配置链接的属性Properties properties = new Properties();properties.setProperty("StrictHostKeyChecking", "no");session.setConfig(properties);//设置超时时间：3分钟session.setTimeout(180000);// 进行sftp链接session.connect();// 获取通信通道channel = session.openChannel("sftp");channel.connect();log.info("连接ftp成功，ftp信息为:userName:{}, host:{}, port:{}", userName, host, port);} catch (JSchException e) {log.error("连接ftp异常，异常信息：{}", e.getMessage());return null;}return (ChannelSftp) channel;}
}

2、设置不同文件格式的格式名和导出的文件格式的枚举类

java">import lombok.Getter;/*** @version 1.0* @date 2024年08月22日 11:55*/
@Getter
public enum FileFormatEnum {XLSX(1001, "xlsx","xlsx"),XLS(1002, "xls","xls"),CSV(1003, "csv","csv"),ZIP(1004, "zip","txt"),TXT(1005, "txt","txt"),NO_FORMAT(1007, "",""),;private final Integer code;private final String value;/*** 解压或解密后的文件格式**/private final String parseFormat;FileFormatEnum(Integer code, String value, String desc) {this.code = code;this.value = value;this.parseFormat = desc;}
}

3、读取各种类型的文件数据

3.1 通过监听器来读取大数据量的xls文件：

java">import com.google.common.collect.Lists;
import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;public class MyHSSFListener implements HSSFListener {public MyHSSFListener(Integer readRowNum) {this.readRowNum = readRowNum;}/*** 当前行*/private final Integer readRowNum;/*** 是否输出formula，还是它对应的值*/private final boolean outputFormulaValues = true;/*** 用于转换formulas*/private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;//excel2003工作簿private HSSFWorkbook stubWorkbook;private SSTRecord sstRecord;private FormatTrackingHSSFListener formatListener;private final HSSFDataFormatter formatter = new HSSFDataFormatter();private BoundSheetRecord[] orderedBSRs;private final List<BoundSheetRecord> boundSheetRecords = new ArrayList<>();private boolean outputNextStringRecord;//存储一行记录所有单元格的容器private final List<String> cellList = Lists.newLinkedList();/*** 判断整行是否为空行的标记*/private boolean flag = false;/*** 遍历excel下所有的sheet*/public void process(InputStream inputStream) throws Exception {POIFSFileSystem fs = new POIFSFileSystem(inputStream);MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);formatListener = new FormatTrackingHSSFListener(listener);HSSFEventFactory factory = new HSSFEventFactory();HSSFRequest request = new HSSFRequest();if (outputFormulaValues) {request.addListenerForAllRecords(formatListener);} else {workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);request.addListenerForAllRecords(workbookBuildingListener);}factory.processWorkbookEvents(request, fs);}/*** HSSFListener 监听方法，处理Record* 处理每个单元格* @param record 文件内容*/@Overridepublic void processRecord(Record record) {int thisColumn;String thisStr = null;String value;//当前行switch (record.getSid()) {case BoundSheetRecord.sid:boundSheetRecords.add((BoundSheetRecord) record);break;case BOFRecord.sid: //开始处理每个sheetBOFRecord br = (BOFRecord) record;if (br.getType() == BOFRecord.TYPE_WORKSHEET) {//如果有需要，则建立子工作簿if (workbookBuildingListener != null && stubWorkbook == null) {stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();}if (orderedBSRs == null) {orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);}}break;case SSTRecord.sid:sstRecord = (SSTRecord) record;break;case BlankRecord.sid: //单元格为空白BlankRecord brec = (BlankRecord) record;thisColumn = brec.getColumn();cellList.add(thisColumn, "");break;case BoolErrRecord.sid: //单元格为布尔类型BoolErrRecord berec = (BoolErrRecord) record;thisColumn = berec.getColumn();thisStr = berec.getBooleanValue() + "";cellList.add(thisColumn, thisStr);checkRowIsNull(thisStr);  //如果里面某个单元格含有值，则标识该行不为空行break;case FormulaRecord.sid://单元格为公式类型FormulaRecord frec = (FormulaRecord) record;thisColumn = frec.getColumn();if (outputFormulaValues) {if (Double.isNaN(frec.getValue())) {outputNextStringRecord = true;} else {thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';}} else {thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';}cellList.add(thisColumn, thisStr);checkRowIsNull(thisStr);  //如果里面某个单元格含有值，则标识该行不为空行break;case StringRecord.sid: //单元格中公式的字符串if (outputNextStringRecord) {outputNextStringRecord = false;}break;case LabelRecord.sid:LabelRecord lrec = (LabelRecord) record;thisColumn = lrec.getColumn();value = lrec.getValue().trim();value = value.equals("") ? "" : value;cellList.add(thisColumn, value);checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行break;case LabelSSTRecord.sid: //单元格为字符串类型LabelSSTRecord lsrec = (LabelSSTRecord) record;thisColumn = lsrec.getColumn();if (sstRecord == null) {cellList.add(thisColumn, "");} else {value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();value = value.equals("") ? "" : value;cellList.add(thisColumn, value);checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行}break;case NumberRecord.sid: //单元格为数字类型NumberRecord numrec = (NumberRecord) record;thisColumn = numrec.getColumn();//参照formatNumberDateCell里面的实现方法编写double valueDouble = numrec.getValue();String formatString = formatListener.getFormatString(numrec);if (formatString.contains("m/d/yy")){formatString="yyyy-MM-dd hh:mm:ss";}int formatIndex = formatListener.getFormatIndex(numrec);value = formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();value = value.equals("") ? "" : value;//向容器加入列值cellList.add(thisColumn, value);checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行break;default:break;}//空值的操作if (record instanceof MissingCellDummyRecord) {MissingCellDummyRecord mc = (MissingCellDummyRecord) record;thisColumn = mc.getColumn();cellList.add(thisColumn, "");}//行结束时的操作if (record instanceof LastCellOfRowDummyRecord) {int rowNum = ((LastCellOfRowDummyRecord) record).getRow();//有些地方只需要读取前10行，根据MyHSSFListener构造中传过来的readRowNum来判断是否继续把读到的数据放到集合中if (rowNum >= 0 && Objects.nonNull(readRowNum) && rowNum >= readRowNum) {return;}if (flag) {ExcelForXlsRowListUtil.sendRows(cellList);}//清空容器cellList.clear();flag = false;}}/*** 如果里面某个单元格含有值，则标识该行不为空行* @param value 数据值*/public void checkRowIsNull(String value){if (value != null && !"".equals(value)) {flag = true;}}
}

3.2 将监听类读取到的每行数据放到新的集合中：

java">public class ExcelForXlsRowListUtil {public static List<List<String>> rowList = new ArrayList<>();public static void sendRows(List<String> cellList) {rowList.add(new ArrayList<>(cellList));}public static List<List<String>> getRowList() {return rowList;}
}

3.3 读取xlsx，xls以及csv文件格式的工具类：

java">@Slf4j
public class ExcelReadUtil {/*** <p>* 	通过输入流创建workbook，单独调用记得关闭流（上面通过try()的方式会自动关闭流，因为他们实现了AutoCloseble）* </p>* @param inputStream excel文件流* @return Workbook对象*/public static Workbook getWorkbookByInputStream(InputStream inputStream) {try {return StreamingReader.builder().rowCacheSize(5000)  //缓存到内存中的行数，默认是10.bufferSize(4096)  //读取资源时，缓存到内存的字节大小，默认是1024.open(inputStream);//打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件} catch (Exception e) {log.error("通过输入流创建workbook异常，异常信息为:{}", e.getMessage());}return null;}/*** 读取xlsx文件流的数据后并写入新的workbook中* @param inputStream 文件流* @date  2024/8/22 15:34* @return  Workbook**/public static Workbook getWorkbookForXlsx(InputStream inputStream) {Workbook workbook = getWorkbookByInputStream(inputStream);if (Objects.isNull(workbook)) {log.error("读取xlsx文件流为空");return null;}Workbook newWorkbook = null;try {newWorkbook = new XSSFWorkbook();Sheet newSheet = newWorkbook.createSheet("Sheet1");Sheet sheet = workbook.getSheetAt(0);int i = 0;for (Row row : sheet) {Row newRow = newSheet.createRow(i);int j = 0;for (Cell cell : row) {CellType cellType = cell.getCellType();String cellValue = getCellValue(cellType, cell);Cell newCell = newRow.createCell(j);newCell.setCellValue(cellValue);j++;}i++;}} catch (Exception e) {log.error("读取xlsx文件异常，异常信息为:{}", e.getMessage());}return newWorkbook;}/*** 读取xls文件流的数据* @param inputStream 文件流* @date  2024/8/22 11:30* @return  Workbook**/public static Workbook getWorkbookForXls(InputStream inputStream) {MyHSSFListener excelXls = new MyHSSFListener(null);Workbook workbook;try {excelXls.process(inputStream);List<List<String>> rowList = ExcelForXlsRowListUtil.getRowList();workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet1");for (int i = 0; i < rowList.size(); i++) {Row row = sheet.createRow(i);List<String> cellDataList = rowList.get(i);for (int j = 0; j < cellDataList.size(); j++) {Cell cell = row.createCell(j);cell.setCellValue(cellDataList.get(j));}}} catch (Exception e) {throw new ServiceException("读取xls文件异常，异常信息为:" + e.getMessage());}return workbook;}/*** 读取csv文件流的数据内容* @param inputStream 文件流* @date  2024/8/22 11:40* @return  String**/public static String getFileDataForCsv(InputStream inputStream) {StringBuilder data = new StringBuilder();String CSV_ROW_SEPARATOR = System.lineSeparator();try (CSVReader reader = new CSVReader(new InputStreamReader(inputStream,"GB2312"))) {String[] line;while ((line = reader.readNext()) != null) {String join = String.join(",", line);data.append(join).append(CSV_ROW_SEPARATOR);}} catch (IOException | CsvValidationException e) {throw new ServiceException("导出csv文件异常，异常信息为:" + e.getMessage());}return data.toString();}}
}

3.4 读取zip文件的数据内容（无需解压到本地）

java">@Slf4j
public class ZipReadUtil {/*** 读取未加密的zip文件流数据* @param inputStream 文件流* @date  2024/8/22 11:36* @return data**/public static String getFileDataForZIP(InputStream inputStream) {ZipInputStream zipInputStream = new ZipInputStream(inputStream, StandardCharsets.UTF_8);ZipEntry zipEntry;String data = "";try {while ((zipEntry = zipInputStream.getNextEntry()) != null) {if (!zipEntry.isDirectory() && zipEntry.getName().toLowerCase().endsWith(".txt")) {long size = zipEntry.getSize();if (size == -1) {ByteArrayOutputStream baos = new ByteArrayOutputStream();while (true) {int bytes = zipInputStream.read();if (bytes == -1) {break;}baos.write(bytes);data = baos.toString();}baos.close();}}}} catch (IOException e) {throw new ServiceException("获取zip文件异常，异常信息为:" + e.getMessage());}return data;}
}

3.5 读取txt和无后缀的文件格式的文件内容

java">@Slf4j
public class TXTAndNoSuffixReadUtil {/*** 读取txt文件或无后缀文件流的内容* @param inputStream 文件流* @date  2024/8/22 11:39* @return  String**/public static String getFileData(InputStream inputStream) {//无格式文件和txt的读取方式一样StringBuilder data = new StringBuilder();try (InputStreamReader reader = new InputStreamReader(inputStream)) {BufferedReader bufferedReader = new BufferedReader(reader);String line;while ((line = bufferedReader.readLine()) != null) {data.append(line).append(System.lineSeparator());}} catch (IOException e) {throw new ServiceException("对无后缀文件时异常:" + e.getMessage());}return data.toString();}
}

4、文件导出到浏览器的工具类

java">import com.tr.common.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Objects;/*** @version 1.0* @date 2024年08月22日 11:32*/
@Slf4j
public class ExportFileUtil {/*** 导出excel文件到浏览器* @param response 响应* @param fileName 文件名* @param workbook  workbook* @date  2024/8/21 17:23**/public static void browserDownloadForExcel(HttpServletResponse response, String fileName, Workbook workbook) {if (Objects.isNull(workbook)) {throw new ServiceException("要导出的excel文件数据为空，文件名为:" + fileName);}try (OutputStream outputStream = response.getOutputStream()) {response.reset();response.setContentType("application/octet-stream; charset=utf-8");response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));workbook.write(outputStream);} catch (IOException e) {log.error(e.getMessage());} finally {try {workbook.close();} catch (IOException e) {log.error(e.getMessage());}}}/*** 导出txt文件到浏览器* @param response 响应* @param fileName 文件名* @param text  文件内容* @date  2024/8/21 17:26**/public static void browserDownloadForTxt(HttpServletResponse response, String fileName, String text) {if (StringUtils.isBlank(text)) {throw new ServiceException("要导出的数据为空，文件名为:" + fileName);}try {response.setCharacterEncoding("utf-8");//设置响应的内容类型response.setContentType("text/plain");//设置名称格式，没有这个中文名称无法显示response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));} catch (UnsupportedEncodingException e) {log.error(e.getMessage());}BufferedOutputStream buff = null;ServletOutputStream outStr = null;try {outStr = response.getOutputStream();buff = new BufferedOutputStream(outStr);buff.write(text.getBytes(StandardCharsets.UTF_8));buff.flush();buff.close();} catch (Exception e) {throw new ServiceException("导出文件文件出错:" + e.getMessage());} finally {try {if (buff != null) {buff.close();}if (outStr != null) {outStr.close();}} catch (Exception e) {log.error("关闭流对象出错:" + e.getMessage());}}}
}

这里需要注意：由于有些文件是有中文名的，所以需要后端通过使用 URLEncoder 将汉字编码为 URL 兼容的格式。前端使用 decodeURIcomponent 将从后端接收到的编码字符串解码为原始汉字。
在这里插入图片描述

5、下载的service接口

java">/*** @version 1.0* @date 2024年08月21日 15:20*/
public interface FileDownLoadService {void downloadFile(Long filesaveId, String filePath, HttpServletResponse response);
}

6、下载的service实现类

java">@Slf4j
@Service
@RequiredArgsConstructor
public class FileDownLoadServiceImpl implements FileDownLoadService {@Overridepublic void downloadFile(FTPConfig ftpConfig,String filePath,String sourceFileFormat, HttpServletResponse response) {String fullFileName = filePath.substring(filePath.lastIndexOf("/") + 1);//获取文件名+文件格式
String fileName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.lastIndexOf(".") + 1);//只要文件名，去掉文件后缀ChannelSftp channelSftp;//连接sftp，读取要写入数据库的文件全路径列表try {channelSftp = SftpUtil.login(ftpConfig.getFtpUser(), ftpConfig.getFtpHost(), ftpConfig.getFtpPort(),ftpConfig.getFtpPassword());} catch (JSchException e) {throw new ServiceException("连接ftp异常，异常信息为:" + e.getMessage());}InputStream inputStream = null;try {inputStream = channelSftp.get(filePath);if (Objects.equals(FileFormatEnum.XLSX.getValue(), sourceFileFormat)) {Workbook workbook = ExcelReadUtil.getWorkbookForXlsx(inputStream);ExportFileUtil.browserDownloadForExcel(response, fullFileName, workbook);try {if (workbook != null) {workbook.close();}} catch (IOException e) {log.error("关闭流异常，异常信息为:{}", e.getMessage());}} else if (Objects.equals(FileFormatEnum.XLS.getValue(), sourceFileFormat)) {Workbook workbook = ExcelReadUtil.getWorkbookForXls(inputStream);ExportFileUtil.browserDownloadForExcel(response, fullFileName, workbook);try {workbook.close();} catch (IOException e) {log.error("关闭流异常，异常信息为:{}", e.getMessage());}} else if (Objects.equals(FileFormatEnum.ZIP.getValue(), sourceFileFormat)) {String data = ZipReadUtil.getFileDataForNoSecret(inputStream);ExportFileUtil.browserDownloadForTxt(response, fileName + FileFormatEnum.ZIP.getParseFormat(), data);} else if (Objects.equals(FileFormatEnum.NO_FORMAT.getValue(), sourceFileFormat)|| Objects.equals(FileFormatEnum.TXT.getValue(), sourceFileFormat)) {String data = TXTAndNoSuffixReadUtil.getFileData(inputStream);ExportFileUtil.browserDownloadForTxt(response, fullFileName, data);} else if (Objects.equals(FileFormatEnum.CSV.getValue(), sourceFileFormat)) {String data = ExcelReadUtil.getFileDataForCsv(inputStream);ExportFileUtil.browserDownloadForTxt(response, fullFileName, data);}   } catch (SftpException e) {throw new ServiceException("ftp获取文件异常，异常信息为:" + e.getMessage());} finally {if (Objects.nonNull(inputStream)) {try {inputStream.close();} catch (IOException e) {log.error("关闭文件流失败, 异常信息为: {}", e.getMessage());}}if (sftp != null) {if (sftp.isConnected()) {sftp.disconnect();  //若sftp正在连接，则断开}}}   }

7、下载的controller

java">import com.tr.common.exception.ServiceException;
import com.tr.itom.service.FileDownLoadService;
import io.swagger.annotations.ApiOperation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.util.Objects;/*** @author FanHuiFang* @version 1.0* @date 2024年08月21日 15:19*/
@Tag(name = "文件下载", description = "文件下载")
@RequiredArgsConstructor
@RestController
@RequestMapping("/file")
public class FileDownLoadController {private final FileDownLoadService fileDownLoadService;@ApiOperation("文件下载")@PostMapping(value = "/download")public void downloadFile(@NotNull Long filesaveId, @NotNull String filePath, HttpServletResponse response) {if (Objects.isNull(filesaveId) || StringUtils.isBlank(filePath)) {throw new ServiceException("规则id或文件路径不允许为空!");}fileDownLoadService.downloadFile(filesaveId, filePath, response);}
}