CODEWITHSUNDEEP
excelvb.netapihttpwebrequest
Prat
Prat

Reputation: 1

Download .xlsx file using an api returns XML formatted file? VB.NET

I am attempting to download a .xlsx file from a web application using an API. However, the string content of the file is always scrambled and seems to have XML content in it. I'm assuming its XML since its got '[Content_Types].xml' mentioned in the first line.

The response headers mention that the content-type being returned is 'application/octet-stream'. I tried adding content-type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" in my request headers, but the operation was 'not recognized' by the web application. So I cant return .xlsx type files...

When I try downloading this file and saving it as .xlsx, I cant open it since it always says that the file is corrupt. However, using Postman to download the file works with no corruption. I'm not sure where I am going wrong while downloading and saving the binary 64 base encoded data. Please help! Here is the code I'm using do download and save file.

If My.Computer.FileSystem.DirectoryExists(DownloadLocation) = False Then
        MsgBox("Folder path '" & DownloadLocation & "' does not exist.", MsgBoxStyle.Information)
        Return
    End If

    Dim url As String = 'I am setting the URL here, tested on postman and no issues here

Getting filename from the response header using a function, No issues with the filepath and name.

 Dim Filepath As String = DownloadLocation & "\" & filename.Split(".").First & "_" & Format(Now, "yyyymmdd hhmmss") & "." & filename.Split(".").Last

    Dim credentials As String = ""
    credentials = "Basic " + Convert.ToBase64String(Encoding.UTF8.GetBytes(LoginName + ":" + PW))
    Dim Request As HttpWebRequest = DirectCast(HttpWebRequest.Create(url), HttpWebRequest)
    With Request
        .Headers.Set(HttpRequestHeader.Authorization, credentials)
        .Headers.Set("X-Application-Key", My.Settings.APIKey)
        .Method = "GET"
        .AutomaticDecompression = DecompressionMethods.GZip
    End With

    Try
        Dim response As HttpWebResponse
        response = Request.GetResponse
        Dim stream As Stream = response.GetResponseStream
        Dim reader As New StreamReader(stream)
        Dim Ofile As FileStream = New FileStream(Filepath, FileMode.Create)
        Dim Owrite As StreamWriter = New StreamWriter(Ofile)
        Owrite.Write(reader.ReadToEnd)
        reader.Close()
        Owrite.Close()
        Ofile.Close()
    Catch ex As Exception
        MsgBox("Download failed..." & vbNewLine & vbNewLine & ex.ToString, MsgBoxStyle.Information)
        Return
    End Try

The file is saved as a .xlsx file, but when I try to open the file, Excel says that the file is corrupted. Anyone know whats happening here?

Upvotes: 0

Views: 1087

Answers (1)

PavlinII
PavlinII

Reputation: 1083

Remove the GZip decompression. First, you don't need it. Second, it's causing you this trouble.

XLSX file is set of XML files zipped into one file. And renamed to .xlsx extension. Take any of your xlsx files, rename it to zip and unzip the content. You'll see the XML structure and it's directories.

AutomaticDecompression propably detects the *ZIP in the response stream bytes and unzip its for you on the client side.

GZipping Zipped content is not needed, because compressing compressed file makes it lager in most of cases. Since the entropy of the message is already maximalized.

Upvotes: 0

Related Questions