ExcelReaderFactory，从SftpFileStream读取

问题描述

使用 ExcelReaderFactory.CreateOpenXmlReader(streamReader)读取.xlsx文件时出现错误，并且流读取器是 SftpFileStream ( SftpClient.OpenRead(filePath)).

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

我收到的错误消息是

我无法遍历第一行代码.

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
        }
                    });
.......
}

但是当我使用通过 System.IO.File.Open 的代码时，它就可以正常工作.

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

堆栈:

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)

推荐答案

SftpFileStream.Seek 代码中显然存在一个错误.当使用 SeekOrigin.End 调用时，它从文件末尾的位置减去 offset ，而不是添加它.

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.

如果可以修改SSH.NET代码，请在 SftpFileStream.Seek 中更改该语句的两个实例:

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

newPosn = attributes.Size - offset;

到

newPosn = attributes.Size + offset;

我已向SSH提交了拉动请求.NET存储库.

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

如果您无法更改SSH.NET代码，则必须解决该问题.

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

1. 将 SftpFileStream 的内容复制到临时的 MemoryStream 并将其与 ExcelReaderFactory 一起使用.

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))
     {
         // ...
     }
 }

或者如果您不想浪费内存来保存文件的另一个副本，则可以在 SftpFileStream 周围实现自己的 Stream 包装器，该包装器将翻译SeekOrigin.End 到 SeekOrigin.Begin ，并带有适当的 offset .

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.

有关此类实现的示例，请参见:
列出位于C#的SFTP服务器上的ZIP文件中的文件

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

---

请注意， ZipArchive (由 ExcelReaderFactory 内部使用)使用 Stream.Seek 和 SeekOrigin.End ，因为ZIP中央目录位于ZIP文件的末尾.-XLSX文件基本上是具有特定结构的ZIP文件.

---

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.

这篇关于ExcelReaderFactory，从SftpFileStream读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！