httpWebRequest Download and parse excel file?

Question

I am looking for a way to download an excel file from a url using httpWebRequest and parse it somehow - whether this means converting it to a .csv file so I can simply use TextFieldParser or leaving it as an excel file, I don't know.

private byte[] GetExcelFile()
        {
            var httpWebRequest = (HttpWebRequest)WebRequest.Create("url_To_Excel_File"); 
            httpWebRequest.ContentType = "application/vnd.ms-excel";
            httpWebRequest.Method = "GET";

            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();

            try
            {

                using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
                {
                    var contents = streamReader.ReadToEnd();
                    return contents;
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
         }

It is my understanding that contents should be an array of bytes? How can I correctly download this excel file and parse the response?

Why not use the WebClient class instead? Use DownloadData() to download the file - it returns a byte array. And if you need to read the file, I recommend query-ing it using OleDB. See here: http://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider — HaukurHaf, Jul 15 '14 at 21:52
Can you please provide example code for using `DownloadData()` in the WebClient class? — user3772119, Jul 15 '14 at 22:06
You cant read excel from from a memorystream using oledb you have to save it to a file before hand. — dmportella, Jul 15 '14 at 22:18
Excel files as in xsl file generated by excel are usually in binary form and wont be parseable with out excel/oledb. if however you are getting a CSV file you could parse it in memory. But excel save it to disk and use OleDB to read it. — dmportella, Jul 15 '14 at 22:20

score 3 · Accepted Answer · answered Jul 15 '14 at 22:23

How to use the WebClient class to download an Excel file, using DownloadFile() instead of DownloadData() (Simpler).

string destFilename = HttpContext.Current.Server.MapPath("~/YourExcelFile.xlsx");
WebClient client = new WebClient();
client.DownloadFile("http://www.blabla.com/YourExcelFile.xlsx", destFilename);

This should download the file to the root of your application.

The next step is to read the file. In my experience, the easiest way to programmatically read Excel files is just to query it using SQL/OleDB.

Example how to read the first sheet of the file into a DataTable:

string connectionString = GetExcelConnectionString(destFilename);

string sheetName = GetFirstSheet(filePath);

OleDbConnection excelCon = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), excelCon);

DataTable dataTable = new DataTable("ExcelDocument");
adapter.Fill(dataTable);

Helper function to get connection string:

// Currently only supports Excel 12.0 (.xlsx files), the connection string for .xls is a little different.
public string GetExcelConnectionString(string filePath)
{
    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
}

Helper to read the name of the first sheet in the file:

public string GetFirstSheet(string filePath)
{
    string connectionString = GetExcelConnectionString(filePath);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable dtSheet = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        return dtSheet.Rows[0]["TABLE_NAME"].ToString();
    }

    return String.Empty;
}

Now you should have the contents of the file in a DataTable, which makes it trivial to do whatever you want with the data.

Note that this is only to give you an idea, might not be perfect. You might want to clean up after processing - delete the excel file from the root of your application for example.

httpWebRequest Download and parse excel file?

1 Answers1