0

Is there a way to simply read as excel file using MemoryStream in C#? I am currently using the native microsoft library Microsoft.Office.Interop.Excel. Is there a way that I could read a excel file with this library?

Opening a Excel file

using Microsoft.Office.Interop.Excel; // using this namespace

public ExcelHelper(string path)
{
   _xlApp = new Application();
   _xlWorkbook = _xlApp.Workbooks.Open(path);
}

Helper

public static MemoryStream DownloadFile(FtpHandler handler, FtpDirectoryOrFileDetail detail)
{
    var fileByte = handler
    .Download
    .FileByPath(detail.FilePart.Filename, detail.SubFolderPath + "/", false)?.Data;
    if (fileByte is null) return stream;

    using (var fileStream = new MemoryStream(fileByte))
       return fileStream;

    return null;
}

The problem is that Open extension seems using only a string parameter and no option for MemoryStream. Does anyone encounter this issue and able to fix this? I am very open to any suggestion.

Alvin Quezon
  • 1,089
  • 3
  • 11
  • 29
  • Not sure what you are really trying to do here, but how to download a file is pretty well documented. Also the _using_ statement disposes the disposable object when the code exits the using block. So here you are returning a disposed stream. I don't think it will never work – Steve Oct 26 '19 at 17:46
  • Well you have a point @Steve, but I can do a workaround for that. Moreover, my main concern is that how can I read excel file with memorystream? Do you have any idea on how to do this? – Alvin Quezon Oct 26 '19 at 17:49
  • 2
    _"I am very open to any suggestion"_ If you don't need to support older Excel formats (xls), I'd suggest you use any 3rd-party library that's based on OpenXML. You'll get more flexibility and your users won't even need to have Office installed. Check [ClosedXML](https://github.com/ClosedXML/ClosedXML), for example. It allows you to open an Excel file using a path or [using a stream](https://github.com/ClosedXML/ClosedXML/blob/develop/ClosedXML/Excel/XLWorkbook.cs#L768). – 41686d6564 stands w. Palestine Oct 26 '19 at 18:17
  • 1
    If you have to use Excel Object Library, your best option would be to actually write the stream to disk and then pass the path of the file to the `WorkBooks.Open()` method. – 41686d6564 stands w. Palestine Oct 26 '19 at 18:23
  • 2
    Perhaps see th answer from VDWWD here: https://stackoverflow.com/questions/560435/read-excel-file-from-a-stream - EPPlus library can read an excel from a stream. If you think that queston has some good relevant answers, maybe mark this queston as a duplicate of it.. – Caius Jard Oct 26 '19 at 18:41
  • Hi @AhmedAbdelhameed, that suggestion is good however I will stick to the `MemoryStream` since the client want it to be that way. However I was able to use `ExcelDataReader` library for reading the excel file using `MemoryStream`. It seems that I was able to fix it using this. And thank you so much for the help! – Alvin Quezon Oct 28 '19 at 14:03
  • Hi @CaiusJard, yes, it was one of my option earlier however, I go with the ExcelDataReader library. Thanks for the suggestion. – Alvin Quezon Oct 28 '19 at 14:05
  • I suggest not to use Interop.Excel com component if you are planning to move to cloud in future. It would be really hard to deploy to app service. – Techiemanu Mar 19 '21 at 06:23

1 Answers1

1
Below is tested solution which writing Excel workbook to memory stream and returning _objectstream which can be used to write to blob and anywhere else.



 using (var fs = new FileStream(fileName, FileMode.Append, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                logic goes here......
                
                workbook.Write(obj_stream);
                using (MemoryStream tempStream = new MemoryStream())
                {
                    workbook.Write(tempStream);
                    var byteArray = tempStream.ToArray();
                    stream = new MemoryStream(byteArray);
                    obj_stream = stream;
    
                }
          } 
     return obj_stream
Techiemanu
  • 786
  • 7
  • 24