How to open an Excel file on Excelize using URL link?

new line 0
Viewed 25

I tried to parse an Excel file and process it using Excelize. I only have a URL link that can be used to access the file.

For my case, I tested using a file I uploaded to Dropbox. The url from dropbox is like this www.dropbox.com/s/t537135761/filename.xlsx, but somehow when I try to use this link, the Excelize throws off this error:

open: www.dropbox.com/s/t537135761/filename.xlsx: no such file or directory

The file itself is already open for public. I can browse it on incognito mode so it means it's not because of restricted access problem. It has the extension file on the URL so I assume the URL touches the actual file that can be parsed by Excelize

I'm not sure if the problem is on Dropbox itself or is on Excelize, but that's only the cloud storage I can use to test my code.

Anyone can tell me how to use Excelize to open file using URL link? I tried searching for another function but can't find anything.

This is roughly my code (simplified) for testing.

package main

func main() {

var urlLink = "www.dropbox.com/s/t537135761/filename.xlsx"

exlz, err := excelize.OpenFile(urlLink)
if err != {
  fmt.Println(err)
  return
}

defer func() {
  if err = exlz.Close(); err != nil {
    fmt.Println(err)
  }
}

  return
}
go
1 Answers

This is the solution, below the explanation.

package main

import (
    "archive/zip"
    "bytes"
    "fmt"
    "io"
    "io/ioutil"
    "net/http"

    excelize "github.com/xuri/excelize/v2"
)

func main() {

    var urlLink = "https://www.dropbox.com/s/t537135761/filename.xlsx"

    data, err := getData(urlLink)
    if err != nil {
        panic(err)
    }

    zr, err := bytesAsZipFile(data, "filename.xlsx")
    if err != nil {
        panic(err)
    }

    // Open the ZIP file with Excelize
    exlz, err := excelize.OpenReader(zr)
    if err != nil {
        fmt.Println("Reader", err)
        return
    }

    defer func() {
        if err = exlz.Close(); err != nil {
            fmt.Println(err)
        }
    }()

    fmt.Println("Done")

}

func bytesAsZipFile(data []byte, filename string) (io.Reader, error) {
    // encode the file as ZIP
    var buf bytes.Buffer

    zw := zip.NewWriter(&buf)
    f, err := zw.Create(filename)
    if err != nil {
        return nil, err
    }

    _, err = f.Write(data)
    if err != nil {
        return nil, err
    }

    if err = zw.Close(); err != nil {
        return nil, err
    }

    return &buf, nil
}

func getData(url string) ([]byte, error) {

    r, err := http.Get(url)
    if err != nil {
        panic(err)
    }

    defer r.Body.Close()

    return ioutil.ReadAll(r.Body)
}

If you check the source code at line 102 you can see that OpenFile does not accept URL's. Only local file paths.

At line 139 there is an OpenReader(), so there is hope. But reading through the code you see that at line 166 it expects a ZIP file in input.

So either we have a local file or a zip archive. So that's what I did: I downloaded the file, zipped it and fed it to OpenReader() who was then happy to unzip it and read it.

useruGkL0
Related