Geek Answers Handbook

How to convert Excel file format to G-list by loading API disk?

I can download and convert CSV files using the Drive API without any problems, but when sending XLS (rendered using PEAR XLS-Writer) or XLSX (rendered using 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"

The MIME type "application / vnd.ms-excel" seems to work better than "application / vnd-excel"

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

When creating files using MS Excel and downloading them manually, it works great.

I can insert / update, upload / open files (everything is fine), but I want to convert them.

In all tests performed, I think the problem is with the file format created - or with the MIME type.

Any clues why the conversion might fail ...

"Has anyone ever converted a rendered XLS / XLSX?"

The question is basically this: what is the expected file format + MIME type to convert to gSheet?

I would take any response for generosity that provides a way to convert a multidimensional array to gSheet with multiple pages if it is (not yet) possible via the Drive API (certainly this would be the preferred way).

This is a question of the Google Drive API: there is no separate tag for this.

+8
php google-drive-sdk xlsx phpexcel
source share
3 answers

The fastest fast forward was rendering the array in Excel 2007 and simply installing the Google Drive application on desktops to access data. The table API cannot create documents; this must be done using the API Docs; Currently, the PHP API wrapper does not support both of these endpoints - this is how I can also review files. Timely effort: 1 hour.

Update: When rendering XLSX with Excel2007, the conversion fails. When I open the same file using MS-Excel and Save As, I can even convert it using the Drive API.

I noticed some things when unpacking files and comparing:

a) MS-Excel uses the values ​​"0/1" instead of "false / true"

b) The directory "xl / worksheets / _rels" has been deleted (perhaps useless?).

c) The line endings of XMLs have been converted from UNIX to DOS.

d) MS-Excel added all empty cells to XML - which PHPExcel did not write out.

e) Relations identifiers are biased ... is there some +3 calculation in the code ??

The problem is that the generated file format does not match the Drive API XML parser.

I'm still not quite sure what is wrong with the created file format, but fixing the processed XLSX file with XML files created from 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(); }

Cell formatting is not too correct, but conversion and preview work.

You can even skip some XML files from patches, and some others may be corrupted.

+1
source share

You can upload Excel documents to Google Drive using the Google Drive app or SDK, but I don’t think that Google provides any way to convert files.

Can I offer file conversion in Google Team? I have never seen the need for it personally, since Excel works fine.

0
source share

In the last part of the alternative approaches, you can simply use the spreadsheet API to directly write to the spreadsheet https://developers.google.com/google-apps/spreadsheets/ . This can give you a useful side effect of incremental updates.

Eg.

0
source share

More articles:


All Articles