0

I am trying to find a way to check the content of an excel (XLS) file over network (Using HTTP URL), and if content (Data from the particular cell, and sheet name) is fine, to save it localy. What I do now is I download the file, check it with Iterop, and if the content doesn't match I remove/delete it. I was was thinking about getting the file content with BinaryReader and than converting this to FileStream, but didn't find a way to do this. At the moment I am still checking NPOI library, but until now also didn't find a way to acomplish this...

Denis
  • 124
  • 2
  • 6
  • xls uses the BIFF format, I'd definitely recommend a library to parse this (it can be done but it'll take a while to write the parsers depending on how much information you want) but you'll need to find one that accepts a stream or byte[]. Recommending a library is off topic – James Apr 28 '15 at 16:43
  • You would suggest someone to write an own parser, because recommending a library is off topic? If that is a rule, it is a strange one. – Denis Apr 28 '15 at 21:33
  • Regarding a library recommendation, I would definitely go with NPOI. One could achieve the same with SpreadsheetGear I asume, but NPOI is open source, what is a big plus from my POV. – Denis Apr 28 '15 at 21:41
  • The only library I've worked with for xls was XlsReadWriteII in Delphi. Although we ended writing a BIFF decoder and comparison tool for debugging. They were 3 separate points. 1) Use a library which takes a stream. 2) You could decode it yourself (but I wouldn't recommend it) 3) Tool recommendations are off topic and likely to get your question closed – James Apr 28 '15 at 22:24
  • Thanks for the warning about libraries. I can partly understand the reason behind such a policy, but on the other side I see there is a workaround if one wants to promote a particular library. One just puts its name in a question/title, like 'Trying to read xls file with NPOI, without having to download it first'. In my case I just didn't care about tools/libraries/frameworks, I just wanted to accomplish the task. Anyway, important thing for me is that I have got my solution. If they decide to close it, there will be no bad feelings on my side. – Denis Apr 29 '15 at 12:14

2 Answers2

2

You can pass a Stream into the constructor for ExcelPackage if using the EPPlus library.

Unfortunately said stream needs to support seek operations which rules out just passing the web response stream in directly.

You could, however, copy to a MemoryStream as below:

var webRequest = HttpWebRequest.Create("http://spreadsheetpage.com/downloads/xl/worksheet%20functions.xlsx") as HttpWebRequest;
var webResponse = webRequest.GetResponse();

using (var webResponseStream = webResponse.GetResponseStream())
using (var memoryStream = new MemoryStream())
{
    webResponseStream.CopyTo(memoryStream);
    using (var excelPackage = new ExcelPackage(memoryStream))
    {
        var value = excelPackage.Workbook.Worksheets.First().Cells[1, 1].Value;
        //etc...
    }
}
Stewart_R
  • 13,764
  • 11
  • 60
  • 106
0

@Stewart_R Thanks for a MemoryStream hint! I only had to use NPOI instead of EPPlus. EPPlus library works only with OOXML (and I need support for older xls format), but NPOI HSSFWorkbook constructor also accepts streams with support for seek operations. So basically the solution was to convert HttpWebRequest response stream to MemoryStream, and use the MemoryStream as an argument to initialise HSSFWorkbook constructor. This constructor unfortunately accepts all kind of streams (like System.IO.Stream) without error during compile time, but will then generate a runtime exception about missing support for seek operation. Anyway good thing is that MemoryStream works fine (for now:)). This code works for me:

 using (var memoryStream = new MemoryStream())
 {
     webResponseStream.CopyTo(memoryStream);

     HSSFWorkbook workbook = new HSSFWorkbook(memoryStream);
     HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

     Console.WriteLine("Name of the sheet is: {0}", sheet.SheetName);
     Console.ReadKey(true);
 }
Denis
  • 124
  • 2
  • 6