CODEWITHSUNDEEP
c#excel
user3772119
user3772119

Reputation: 504

httpWebRequest Download and parse excel file?

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?

Upvotes: 1

Views: 5364

Answers (1)

HaukurHaf
HaukurHaf

Reputation: 13816

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.

Upvotes: 3

Related Questions