How to convert Excel File-Format to G-Sheet by Drive API upload?

Question

I can upload and convert CSV files by Drive API without any problem, but when sending XLS (rendered with PEAR XLS-Writer) or XLSX (rendered with PHPExcel) the Drive API complains:

"Error calling POST https://www.googleapis.com/upload/drive/v2/files?convert=true&uploadType=multipart&key=123: (500) Internal Error"

MIME type "application/vnd.ms-excel" apparently works better than "application/vnd-excel"

When I write out XLSX with content-type "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" it's quite the same ... it gets stuck when loading the preview, when convert=true.

When creating files with MS Excel and uploading them manually it works fine.

I can insert/update, download/open the files (all fine) - but I want to convert them.

With all the tests performed I guess the problem is related to the created file-format - or MIME type.

Any hints why the conversion might fail...

"Did ever someone managed to convert rendered XLS/XLSX?"

The question basically is: What is the expected file-format + MIME-type in order to convert to gSheet?

I'd accept any answer for the bounty, which provides a way to convert a multi-dimensional array to gSheet with several pages, in case it's not (yet) possible via the Drive API (for sure that would be the preferred way).

This is a Google Drive API question - there's just no individual tag for this available.

Some clarifications:- 1. Are you saying that exactly the same file can be upload/converted through the web interface, but fails 50 via API? If so, check that your Base64 encoding is correct. 2. You can upload without conversion. What happens if you try to "convert to google docs" in the web interface? — pinoyyid
if i upload by web-browser with conversion - it works, converting files that have been saved over the API won't convert (progress-bar gets stuck); I'm using the google-api wrapper for PHP. — Martin Zeitler

syslogic syslogic · Accepted Answer · 2012-09-09T03:14:34

Most fast-forward was to render array to Excel 2007 and just install Google Drive App on the Desktops for accessing the data. Spreadsheets API can't create documents, need to do that with Docs API; Currently the API wrapper for PHP not supports both of these endpoints - like this I can revision the files as well. Timely effort: 1h.

Update: When I render XLSX with Excel2007 the conversion fails. When I open the very same file with MS-Excel and "Save As" - I can convert it even over the Drive API.

I noticed some things when unzipping the files and compared:

a) MS-Excel uses values '0/1' instead of 'false/true'

b) Directory 'xl/worksheets/_rels' got removed (maybe useless?).

c) The line-endings of the XML´s were converted from UNIX to DOS.

d) MS-Excel added all the empty cells to the XML - which PHPExcel didn't write out.

e) the relation IDs appear to be shifted ... there some +3 calculation in code??

The problem is, that the generated file-format is not matching the Drive API's XML-parser.

I'm still not exactly sure what is wrong with the generated file-format, but patching the rendered XLSX file with XML files natively created by MS-Excel makes it work:

/* how-to patch the generated XLSX: */

$zip = new ZipArchive();
$zip->open($xlsx_path);
$zip->extractTo($export_dir.'temp');
$zip->close();

$this->deleteDir($export_dir.'temp/xl/worksheets/_rels');

$files =array(
   '_rels/.rels',
   'docProps/app.xml',
   'docProps/core.xml',
   'xl/workbook.xml',
   'xl/_rels/workbook.xml.rels',
   'xl/theme/theme1.xml'
);
foreach($files as $file){
   copy($export_dir.'template/'.$file, $export_dir.'temp/'.$file);
}

unlink($xlsx_path);

if($zip->open($xlsx_path, ZIPARCHIVE::CREATE)) {
$source = str_replace('\\', '/', realpath($export_dir.'temp'));
if(is_dir($source) === true) {
    $files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($source), RecursiveIteratorIterator::SELF_FIRST);
    foreach ($files as $file) {
        $file = str_replace('\\', '/', $file);
        if(in_array(substr($file, strrpos($file, '/')+1), array('.', '..')) ){continue;}
        $file = realpath($file);
        if(is_dir($file) === true){$zip->addEmptyDir(str_replace($source . '/', '', $file . '/'));}
        else if(is_file($file) === true){
              $zip->addFromString(str_replace($source . '/', '', $file), file_get_contents($file));
           }
        }
    }
    else if(is_file($source) === true) {
       $zip->addFromString(basename($source), file_get_contents($source));
    }
    $zip->close();
}

The cell formatting isn't too proper - but the conversion & preview is working.

Can even skip some XML files from patching, some others make it break.

How to convert Excel File-Format to G-Sheet by Drive API upload?

3 Answers