CODEWITHSUNDEEP
c#.netsftpssh.netexceldatareader
Prasanna Nagineni
Prasanna Nagineni

Reputation: 13

ExcelReaderFactory, reading from SftpFileStream

I am getting an error while reading a .xlsx file using ExcelReaderFactory.CreateOpenXmlReader(streamReader) and stream reader is a SftpFileStream (SftpClient.OpenRead(filePath)).

The error message i am getting is

Renci.SshNet.Common.SshException: General failure

I am not able to get through the first line of code.

using (var reader = ExcelReaderFactory.CreateOpenXmlReader(streamReader))
{
     //Get reader as DataSet
     var result = reader.AsDataSet(new ExcelDataSetConfiguration()
     {
        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
        {
           UseHeaderRow = true
        }
                    });
.......
}

But when I use the code using System.IO.File.Open then it works fine.

Stack:

at Renci.SshNet.Sftp.SftpSession.RequestRead(Byte[] handle, UInt64 offset, UInt32 length)
   at Renci.SshNet.Sftp.SftpFileStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.Compression.ZipHelper.ReadBytes(Stream stream, Byte[] buffer, Int32 bytesToRead)
   at System.IO.Compression.ZipHelper.SeekBackwardsAndRead(Stream stream, Byte[] buffer, Int32& bufferPointer)
   at System.IO.Compression.ZipHelper.SeekBackwardsToSignature(Stream stream, UInt32 signatureToFind)
   at System.IO.Compression.ZipArchive.ReadEndOfCentralDirectory()
   at System.IO.Compression.ZipArchive.Init(Stream stream, ZipArchiveMode mode, Boolean leaveOpen)
   at System.IO.Compression.ZipArchive..ctor(Stream stream, ZipArchiveMode mode, Boolean leaveOpen, Encoding entryNameEncoding)
   at ExcelDataReader.Core.ZipWorker..ctor(Stream fileStream)
   at ExcelDataReader.ExcelOpenXmlReader..ctor(Stream stream)
   at ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(Stream fileStream, ExcelReaderConfiguration configuration)

Upvotes: 1

Views: 2290

Answers (1)

Martin Prikryl
Martin Prikryl

Reputation: 202088

There is obviously a bug in SftpFileStream.Seek code. When called with SeekOrigin.End, it substracts the offset from the position of the end of the file, rather than adding it.

If you can modify SSH.NET code, change both instances of this statement in SftpFileStream.Seek:

newPosn = attributes.Size - offset;

to

newPosn = attributes.Size + offset;

I've submitted a pull request with this fix to the SSH.NET repository.

If you cannot change the SSH.NET code, you will have to workaround that.

  1. Either copy the SftpFileStream contents to a temporary MemoryStream and use that with ExcelReaderFactory.

     using (var memoryStream = new MemoryStream())
 {
     sftpFileStream.CopyTo(memoryStream);
     memoryStream.Position = 0;
     using (var reader = ExcelReaderFactory.CreateOpenXmlReader(memoryStream))
     {
         // ...
     }
 }

  2. Or if you do not want to waste memory holding another copy of the file, you can implement own Stream wrapper around SftpFileStream, which translates SeekOrigin.End to SeekOrigin.Begin with a proper offset.

    For an example of such implementation, see:
    List files inside ZIP file located on SFTP server in C#

Note that ZipArchive (internally used by ExcelReaderFactory) uses Stream.Seek with SeekOrigin.End, because ZIP central directory is at the end of the ZIP file. – XLSX file is basically a ZIP file with a specific structure.

Upvotes: 2

Related Questions