read xlsx file from sftp server in c#

You can read an .xlsx file from an SFTP server in C# using Free Spire.XLS for .NET by combining an SFTP client library (like SSH.NET) to download the file into a stream, and then loading it into a Workbook object.

Before getting started, install the required libraries:

Install-Package SSH.NET

Install-Package FreeSpire.XLS

Here’s a complete example:

using Renci.SshNet;
using Spire.Xls;
using System;
using System.IO;

namespace ReadExcelFile
{
    class Program
    {
        static void Main(string[] args)
        {

            string host = "your.sftp.server";
            int port = 22; // default SFTP port
            string username = "yourUsername";
            string password = "yourPassword";
            string remoteFilePath = "/remote/path/YourFile.xlsx";

            // Connect to SFTP and download file into a memory stream
            using (var sftp = new SftpClient(host, port, username, password))
            {
                sftp.Connect();
                using (var ms = new MemoryStream())
                {
                    sftp.DownloadFile(remoteFilePath, ms);
                    ms.Position = 0; // Reset stream position

                    // Load the XLSX file using Spire.XLS
                    Workbook workbook = new Workbook();
                    workbook.LoadFromStream(ms);

                    // Now you can work with workbook
                    Worksheet sheet = workbook.Worksheets[0];
                    string value = sheet.Range["A1"].Value;
                    Console.WriteLine("Value in A1: " + value);
                }
                sftp.Disconnect();
            }
        }
    }
}

using System;
using System.IO;
using WinSCP;
using ClosedXML.Excel;

class Program
{
    static void Main()
    {
        // SFTP session options
        SessionOptions sessionOptions = new SessionOptions
        {
            Protocol = Protocol.Sftp,
            HostName = "sftp.example.com",
            UserName = "your_username",
            Password = "your_password",
            SshHostKeyFingerprint = "ssh-rsa 2048 xxxxx..."
        };

        using (Session session = new Session())
        {
            session.Open(sessionOptions);
            Console.WriteLine("Connected to SFTP");

            string remoteFilePath = "/remote/path/to/file.xlsx";

            // Download file to memory stream
            using (MemoryStream ms = new MemoryStream())
            {
                session.GetFiles(remoteFilePath, ms).Check();
                ms.Position = 0;

                // Read Excel using ClosedXML
                using (var workbook = new XLWorkbook(ms))
                {
                    var worksheet = workbook.Worksheet(1); // first sheet
                    foreach (var row in worksheet.RowsUsed())
                    {
                        foreach (var cell in row.Cells())
                        {
                            Console.Write(cell.Value + "\t");
                        }
                        Console.WriteLine();
                    }
                }
            }

            session.Dispose();
            Console.WriteLine("Disconnected from SFTP");
        }
    }
}

check whether this code can be used