Exporting Google Sheets to Excel 2016

Question

I'm making a console application in .NET framework that uploads a .XLSX file to Google Drive (converting it to Google Sheets format in that process) and also downloads the Google Sheets to .XLSX format.

Basically I'm trying to sync a local drive with Google Drive, preserving all formatting of the sheets.

I've used the Google Drive API for .NET framework to do this and it is working pretty well, except that when downloading Google Sheets to a .XLSX file, the .XLSX file downloaded is opened without any problem by Excel 2007 but shows the following error when opened with Excel 2016:

We found a problem with some content in the file. Do you want us to try to recover as much as we can?

On clicking "Yes", Excel opens the file successfully, but shows a message with an error log. I opened the error log, and there were no details in it except a mention of "Conditional Formatting". Further, a few freeze rows in the original Google Sheets were not freezed anymore in Excel 2016, but were intact when the file was opened in Excel 2007.

Do you have any suggestions to make the .XLSX file downloaded from Google Sheets compatible with Excel 2016, or a totally different approach that will eliminate the problem with different versions and different formats?

Answer 1

Since this is an old post you probably fixed your problem already. But for future reference i'll try to answer your question using v3 of the Google Drive api.

Google Drive uses it's own MIME Types. A list of these MIME Types can be found here.

Google Drive MIME can be converted in their corresponding MIME type. A list of corresponding MIME types can be found on the bottom of this page.

So if you want to convert a Google you have to find out what MIME type it is and what the corresponding MIME type is.

public FileResult Download(string fileId, string fileName, string mimeType)
{
        DriveService service = new DriveService(new BaseClientService.Initializer
        {
            HttpClientInitializer = <user_credential>
            ApplicationName = "APPNAME"
        });

        string convertedMimeType = "";
        bool googleDoc = true;

        switch (mimeType)
        {
            case "application/vnd.google-apps.document":
            {
                convertedMimeType = "application/vnd.oasis.opendocument.text";
                break;
            }
            case "application/vnd.google-apps.spreadsheet":
            {
                convertedMimeType = "application/x-vnd.oasis.opendocument.spreadsheet";
                break;
            }
            case "application/vnd.google-apps.drawing":
            {
                convertedMimeType = "image/png";
                break;
            }
            case "application/vnd.google-apps.presentation":
            {
                convertedMimeType = "application/pdf";
                break;
            }
            default:
            {
                convertedMimeType = mimeType;
                googleDoc = false;
                break;
            }
        }

        MemoryStream stream = new MemoryStream();

        if (googleDoc)
        {
            var request = service.Files.Export(fileId, convertedMimeType);
            request.Download(stream);
        }
        else
        {
            service.Files.Get(fileId).Download(stream);
        }

        return File(stream.ToArray(), convertedMimeType, fileName);
}

You can get the MIME type of a file by using:

Google.Apis.Drive.v3.Data.File file = new Google.Apis.Drive.v3.Data.File(..);
string type = file.MimeType;

In the switch case I check if the MIME type is a Google Document MIME type. If it is, then I set the MIME type to the corresponding MIME type of the Google Document. If it is not a Google Document, you can use the MIME type you just got from the file. Google Drive won't change the MIME type if you upload a file that is not a Google Document.

Please not that the above sample is used in a ASP.NET MVC project.