How to stream Excel file from Dropbox using C#?

Question

I am trying to stream a 100 MB Excel file from Dropbox and write to a SQL database.

I created a Dropbox API app and created Dropbox client in C# code. Connectivity with Dropbox is working but I get error while trying to parse the Excel doc stream.

Same excel file when streamed from my local machine, works fine.

====

CODE:

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Dropbox.Api;
using ExcelDataReader;
using System.Data.SqlClient;

class Program
{
static void Main(string[] args)
{
var task = Task.Run((Func<Task>)Program.Run);
task.Wait();
}

static async Task Run()
{
using (var dbx = new DropboxClient("Access Key"))
{
var full = await dbx.Users.GetCurrentAccountAsync();
Console.WriteLine("{0} - {1}", full.Email, full.Name.DisplayName);

var list = await dbx.Files.ListFolderAsync(string.Empty);

var response = await dbx.Files.DownloadAsync("/Input Files/SampleInputExcelFile.xlsx");

// This Http stream doesn't work
Stream stream1 = await response.GetContentAsStreamAsync();

// This stream does work
Stream stream = File.Open("C:\\<PATH>\\Input Files\\SampleInputExcelFile.xlsx", FileMode.Open, FileAccess.Read);

// This line of code throws the error..
IExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream1);

DataSet result = reader.AsDataSet();
//reader.IsFirstRowAsColumnNames = true;
DataTable dt = result.Tables[0];
string text = "'" + dt.Rows[10][0].ToString() + "'" + "," + dt.Rows[10][1].ToString() + "," + dt.Rows[10][2].ToString() + "," + dt.Rows[10][3].ToString();

// SQL steps start from here...
}

}

}

Unhandled Exception: System.AggregateException: One or more errors occurred. (Specified method is not supported.) ---> System.NotSupportedException: Specified method is not supported. at System.Net.Http.HttpContentStream.Seek(Int64 offset, SeekOrigin origin) at ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(Stream fileStream, ExcelReaderConfiguration configuration) in C:\projects\exceldatareader\src\ExcelDataReader\ExcelReaderFactory.cs

Would appreciate any help! Thanks in advance! P.S I am an absolute noob in C# so please pardon the naivety in my code.

Answer 1

I was able to make it work by copying the Http stream into a memory stream first. Solution below:

Stream StreamFromDropbox = await response.GetContentAsStreamAsync();         

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
MemoryStream StreamFromDropboxCopyAsync = new MemoryStream();
await StreamFromDropbox.CopyToAsync(StreamFromDropboxCopyAsync);
StreamFromDropboxCopyAsync.Seek(offset: 0, loc: SeekOrigin.Begin);
IExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(StreamFromDropboxCopyAsync, new ExcelReaderConfiguration() { FallbackEncoding = System.Text.Encoding.GetEncoding(1252) });
DataSet result = reader.AsDataSet();