0

Thank you in advance. I have a sFTP server where excel file is stored. I want to open and read the file from sFTP server without downloading into local or anywhere. can I get some idea on same.

Sandeep
  • 41
  • 5
  • _"I want to open and read the file from sFTP server without downloading into local or anywhere"_ - **you can't**: Excel requires a workbook file to exist on disk somewhere. While Excel does support _some_ kinds of network-shares (like SMB, WebDAV, etc), it doesn't support opening files over FTP because FTP has no concept of an "open" or locked file which is a fundamental filesystem feature that Excel depends on (while NTFS, SMB, WebDAV, etc) _do_. – Dai Oct 03 '22 at 11:50
  • Then do i need to download it somewhere in local or azure blob and then read it? – Sandeep Oct 03 '22 at 12:18
  • 1
    Save it to `%TEMP%`, I guess – Dai Oct 03 '22 at 12:27
  • Even if Microsoft Excel API really does not support this (I do not know), it seems there are libraries that do: [Read excel file from a stream](https://stackoverflow.com/q/560435/850848). – Martin Prikryl Oct 03 '22 at 15:38

1 Answers1

0

To use ClosedXML you must write this using statement at the top of your code after downloading the nuget package.

using ClosedXML.Excel;

I wrote a method to read excel file as memory stream over SFTP.

public static Stream GetFileStream(SftpClient sftpClient, string sourcePath)
{
    var memoryStream = new MemoryStream();

    sftpClient.DownloadFile(sourcePath, memoryStream);

    memoryStream.Position = 0;

    return memoryStream;
}

After that with the help of ClosedXML nuget package, read the excel content from stream.

string host = @"host_name";
string remoteDirectory = "directory_path";
using (var sftp = new SftpClient(host, "username", "password"))
{
       sftp.Connect();
       Stream stream = GetFileStream(sftp, file_full_name);

       using (XLWorkbook wb = new XLWorkbook(stream))
       {
            var ws = wb.Worksheet(1);

            foreach (var r in ws.RangeUsed().RowsUsed().Skip(1))
            {
                var cell1=r.Cell(1).GetString();
                var cell2=r.Cell(2).GetString();
            }
       }
}
MrAlbino
  • 308
  • 2
  • 10