Convert Excel to CSV | SFTP | large file

Viewed 373

I'm trying to convert large file from Excel to CSV (200 MB+) but with my logic of implementationn i'm observing performance issue and also memory issues. Any pointers to read large excel files from FTP and write CSV file back to FTP will be of great help.

Summary of code: Source: Excel is read from SFTP server Target: CSV file is pushed to SFTP directory

Below is the converter implementation:

public class ExcelConvertServiceImpl implements ExcelConvertService {

    private static final Logger logger = Logger.getLogger(ExcelConvertServiceImpl.class.getName());
    private StringBuffer data = new StringBuffer();
    private FTPOperationsImpl ftpOperationsImpl = new FTPOperationsImpl();
    private Map<String, StringBuffer> cSVFiles = new HashedMap<String, StringBuffer>();


    public void createCSV(Sheet sheet) throws HandledException {
        try {
            // Row row = null;
            Cell cell = null;
            int lastCellNum = 0;
            this.data.setLength(0);

            for (Row row : sheet) {
                lastCellNum = row.getLastCellNum() - 1;
                for (int cn = 0; cn <= lastCellNum; cn++) {
                    cell = row.getCell(cn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    this.data.append(StringEscapeUtils.escapeCsv(cell.toString().replaceAll("\\s+", "")));
                    if (cn != lastCellNum) {
                        this.data.append(",");
                    }
                }
                this.data.append("\n");
            }
        } catch (Exception ex) {
            logger.log(Level.SEVERE, null, ex);
            throw new HandledException(ex.getClass().getCanonicalName(), ex.getMessage(), ex);
        }
    }


    public Map<String, StringBuffer> readXLSX(InputStream inputStream, String fileName, ChannelSftp channelSftp)
            throws HandledException {
        try {
            cSVFiles.clear();
            String writeFileName = null;
            Workbook wb = WorkbookFactory.create(inputStream);
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                logger.log(Level.INFO, "---------Reading Sheet : " + wb.getSheetAt(i).getSheetName() + "---------");
                createCSV(wb.getSheetAt(i));
                writeFileName = fileName.substring(0, fileName.lastIndexOf(".")) + "_" + wb.getSheetAt(i).getSheetName()
                        + ".csv";
                this.cSVFiles.put(writeFileName, this.data);
            }
            inputStream.close();
            wb.close();
            return cSVFiles;
            // uploadFilesToFTP(channelSftp);
        } catch (IOException ex) {
            logger.log(Level.SEVERE, "IOException", ex);
            throw new HandledException(ex.getClass().getCanonicalName(), ex.getMessage(), ex);
        } catch (Exception ex) {
            logger.log(Level.SEVERE, "Exception", ex);
            throw new HandledException(ex.getClass().getCanonicalName(), ex.getMessage(), ex);
        } finally {
            try {
                inputStream.close();
                return cSVFiles;
            } catch (IOException ex) {
                logger.log(Level.SEVERE, null, ex);
            }
        }
    }       
}

Below is the orchestrator:

public List<FileList> excelToCSVSingle(Request input) throws HandledException {
        List<FileList> fileList = new ArrayList<FileList>();
        logger.log(Level.INFO, "******************** Started Excel Coverter Instance ********************");
        ChannelSftp channelSftp = null;
        FTPOperations ftpOperations = new FTPOperationsImpl();
        try {
            channelSftp = this.fTPSessionService.createJSCHsession();
            channelSftp.cd(input.getfTPSourceDirectory());
            logger.log(Level.INFO, "---------FTP Directory : " + input.getfTPSourceDirectory() + "---------");
            //String sourceFileName = input.getFileList().stream().findFirst().get().getFileName();
            String sourceFileName = input.getFileName();
            Map<String, StringBuffer> cSVFiles = this.excelConvertService.readXLSX(
                    channelSftp.get(input.getfTPSourceDirectory() + sourceFileName), sourceFileName, channelSftp);
            ftpOperations.uploadFilesToFTP(cSVFiles, channelSftp, input.getfTPTargetDirectory());
            fileList.addAll(cSVFiles.keySet().stream().map(m -> new FileList(m)).collect(Collectors.toList()).stream().sequential().collect(Collectors.toList()));
            logger.log(Level.INFO, "---------Completed converting file : " + sourceFileName + "---------");

            logger.log(Level.INFO, "******************** Completed Excel Coverter Instance ********************");
            return fileList;
        } catch (Exception ex) {
            logger.log(Level.SEVERE, "Exception", ex);
            throw new HandledException(ex.getClass().getCanonicalName(), ex.getMessage(), ex);
        } finally {
            if (channelSftp != null) {
                this.fTPSessionService.disconnect(channelSftp);
            }
        }

    }

Below is SFTP file put method:

public void pushFileToFTP(StringBuffer data, String fileName, ChannelSftp channelSftp, String targetDirectory) {
        try {
            InputStream stream = new ByteArrayInputStream(data.toString().getBytes());
            String writeDir = targetDirectory + fileName;
            logger.log(Level.INFO,"---------Pushed file to FTP : " + writeDir);
            channelSftp.put(stream, writeDir);
        } catch (SftpException ex) {
            logger.log(Level.SEVERE, "SftpException", ex);
        } catch (Exception ex) {
            logger.log(Level.SEVERE, "Exception", ex);
        }
    }

Thanks

0 Answers
Related