Download de planilhas do Google Drive para XLSX em C #

Xiolin

Como faço o download de uma Planilha Google como um tipo de arquivo xlsx?

Eu tenho o código abaixo e funciona muito bem, exceto para o tipo de arquivo. Ele baixará o arquivo como xlsx, mas se falhar quando tento abri-lo.

O erro que recebo diz "O Excel não pode abrir o arquivo 'arquivo.xlsx' porque o formato do arquivo de extensão não é válido"

Obrigado pela ajuda.

using System;
using System.IO;
using System.Net;

public static class FileDownloader
{
    private const string GOOGLE_DRIVE_DOMAIN = "drive.google.com";
    private const string GOOGLE_DRIVE_DOMAIN2 = "https://drive.google.com";

    // Normal example: FileDownloader.DownloadFileFromURLToPath( "http://example.com/file/download/link", @"C:\file.txt" );
    // Drive example: FileDownloader.DownloadFileFromURLToPath( "http://drive.google.com/file/d/FILEID/view?usp=sharing", @"C:\file.txt" );
    public static FileInfo DownloadFileFromURLToPath( string url, string path )
    {
        if( url.StartsWith( GOOGLE_DRIVE_DOMAIN ) || url.StartsWith( GOOGLE_DRIVE_DOMAIN2 ) )
            return DownloadGoogleDriveFileFromURLToPath( url, path );
        else
            return DownloadFileFromURLToPath( url, path, null );
    }

    private static FileInfo DownloadFileFromURLToPath( string url, string path, WebClient webClient )
    {
        try
        {
            if( webClient == null )
            {
                using( webClient = new WebClient() )
                {
                    webClient.DownloadFile( url, path );
                    return new FileInfo( path );
                }
            }
            else
            {
                webClient.DownloadFile( url, path );
                return new FileInfo( path );
            }
        }
        catch( WebException )
        {
            return null;
        }
    }

    // Downloading large files from Google Drive prompts a warning screen and
    // requires manual confirmation. Consider that case and try to confirm the download automatically
    // if warning prompt occurs
    private static FileInfo DownloadGoogleDriveFileFromURLToPath( string url, string path )
    {
        // You can comment the statement below if the provided url is guaranteed to be in the following format:
        // https://drive.google.com/uc?id=FILEID&export=download
        url = GetGoogleDriveDownloadLinkFromUrl( url );

        using( CookieAwareWebClient webClient = new CookieAwareWebClient() )
        {
            FileInfo downloadedFile;

            // Sometimes Drive returns an NID cookie instead of a download_warning cookie at first attempt,
            // but works in the second attempt
            for( int i = 0; i < 2; i++ )
            {
                downloadedFile = DownloadFileFromURLToPath( url, path, webClient );
                if( downloadedFile == null )
                    return null;

                // Confirmation page is around 50KB, shouldn't be larger than 60KB
                if( downloadedFile.Length > 60000 )
                    return downloadedFile;

                // Downloaded file might be the confirmation page, check it
                string content;
                using( var reader = downloadedFile.OpenText() )
                {
                    // Confirmation page starts with <!DOCTYPE html>, which can be preceeded by a newline
                    char[] header = new char[20];
                    int readCount = reader.ReadBlock( header, 0, 20 );
                    if( readCount < 20 || !( new string( header ).Contains( "<!DOCTYPE html>" ) ) )
                        return downloadedFile;

                    content = reader.ReadToEnd();
                }

                int linkIndex = content.LastIndexOf( "href=\"/uc?" );
                if( linkIndex < 0 )
                    return downloadedFile;

                linkIndex += 6;
                int linkEnd = content.IndexOf( '"', linkIndex );
                if( linkEnd < 0 )
                    return downloadedFile;

                url = "https://drive.google.com" + content.Substring( linkIndex, linkEnd - linkIndex ).Replace( "&amp;", "&" );
            }

            downloadedFile = DownloadFileFromURLToPath( url, path, webClient );

            return downloadedFile;
        }
    }

    // Handles 3 kinds of links (they can be preceeded by https://):
    // - drive.google.com/open?id=FILEID
    // - drive.google.com/file/d/FILEID/view?usp=sharing
    // - drive.google.com/uc?id=FILEID&export=download
    public static string GetGoogleDriveDownloadLinkFromUrl( string url )
    {
        int index = url.IndexOf( "id=" );
        int closingIndex;
        if( index > 0 )
        {
            index += 3;
            closingIndex = url.IndexOf( '&', index );
            if( closingIndex < 0 )
                closingIndex = url.Length;
        }
        else
        {
            index = url.IndexOf( "file/d/" );
            if( index < 0 ) // url is not in any of the supported forms
                return string.Empty;

            index += 7;

            closingIndex = url.IndexOf( '/', index );
            if( closingIndex < 0 )
            {
                closingIndex = url.IndexOf( '?', index );
                if( closingIndex < 0 )
                    closingIndex = url.Length;
            }
        }

        return string.Format( "https://drive.google.com/uc?id={0}&export=download", url.Substring( index, closingIndex - index ) );
    }
}

// Web client used for Google Drive
public class CookieAwareWebClient : WebClient
{
    private class CookieContainer
    {
        Dictionary<string, string> _cookies;

        public string this[Uri url]
        {
            get
            {
                string cookie;
                if( _cookies.TryGetValue( url.Host, out cookie ) )
                    return cookie;

                return null;
            }
            set
            {
                _cookies[url.Host] = value;
            }
        }

        public CookieContainer()
        {
            _cookies = new Dictionary<string, string>();
        }
    }

    private CookieContainer cookies;

    public CookieAwareWebClient() : base()
    {
        cookies = new CookieContainer();
    }

    protected override WebRequest GetWebRequest( Uri address )
    {
        WebRequest request = base.GetWebRequest( address );

        if( request is HttpWebRequest )
        {
            string cookie = cookies[address];
            if( cookie != null )
                ( (HttpWebRequest) request ).Headers.Set( "cookie", cookie );
        }

        return request;
    }

    protected override WebResponse GetWebResponse( WebRequest request, IAsyncResult result )
    {
        WebResponse response = base.GetWebResponse( request, result );

        string[] cookies = response.Headers.GetValues( "Set-Cookie" );
        if( cookies != null && cookies.Length > 0 )
        {
            string cookie = "";
            foreach( string c in cookies )
                cookie += c;

            this.cookies[response.ResponseUri] = cookie;
        }

        return response;
    }

    protected override WebResponse GetWebResponse( WebRequest request )
    {
        WebResponse response = base.GetWebResponse( request );

        string[] cookies = response.Headers.GetValues( "Set-Cookie" );
        if( cookies != null && cookies.Length > 0 )
        {
            string cookie = "";
            foreach( string c in cookies )
                cookie += c;

            this.cookies[response.ResponseUri] = cookie;
        }

        return response;
    }
}

DaImTo

Você deve tentar, você precisa adicionar um tipo MIME ao seu comando de exportação para dizer em qual formato deseja fazer o download do arquivo. Existem alguns tipos MIME diferentes suportados para planilhas que você pode encontrá-los aqui

https://drive.google.com/uc?id={0}&export=download&mimetype=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Como uma observação lateral, você pode querer considerar o uso da biblioteca cliente Google .net, ela não o ajudará com seu problema, mas é muito mais fácil de trabalhar.

código do downloader de mídia

{
    // Create the service using the client credentials.
    var storageService = new StorageService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = "APP_NAME_HERE"
        });
    // Get the client request object for the bucket and desired object.
    var getRequest = storageService.Objects.Get("BUCKET_HERE", "OBJECT_HERE");
    using (var fileStream = new System.IO.FileStream(
        "FILE_PATH_HERE",
        System.IO.FileMode.Create,
        System.IO.FileAccess.Write))
    {
        // Add a handler which will be notified on progress changes.
        // It will notify on each chunk download and when the
        // download is completed or failed.
        getRequest.MediaDownloader.ProgressChanged += Download_ProgressChanged;
        getRequest.Download(fileStream);
    }
}

static void Download_ProgressChanged(IDownloadProgress progress)
{
    Console.WriteLine(progress.Status + " " + progress.BytesDownloaded);
}

Este artigo é coletado da Internet.

Se houver alguma infração, entre em [email protected] Delete.

editar em2021-01-21

deixe-me dizer algumas palavras

0comentários

loginDepois de participar da revisão

Postagem anterior:Formato de entrada Angular Material Datepicker

TOP lista

artigo