Databases
StackOverflow2013
Postsdbo
POCreating big Excel sheets programmatically
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating big Excel sheets programmatically
    primarykey
    data
    text
    <p>We are using OpenPyxl to export MySQL content to Microsoft Excel in XSLX format</p> <p><a href="https://bitbucket.org/ericgazoni/openpyxl/overview" rel="nofollow">https://bitbucket.org/ericgazoni/openpyxl/overview</a></p> <p>However, the amount of data we are dealing with is big. We are running to out of memory situation. Tables may contain up to 400 columns in 50000+ rows. Even the files are big, they are not that big that Microsoft Excel or OpenOffice should have problems with them. We are assuming our issues mainly stem from the fact that Python keeps XML DOM structure in memory in not efficient enough manner.</p> <p>EDIT: Eric, the author of OpenPyxl, pointed out that there is an option to make OpenPyxl write with fixed memory usage. However, this didn't solve our problem completely, as we still have issues with raw speed and something else taking up too much memory in Python.</p> <p>Now we are looking for more efficient ways to create Excel files. With Python preferably, but if we cannot find a good solution we might want to look other programming languages as well.</p> <p>Options, not in any specific order, include </p> <p>1) Using OpenOffice and PyUno and hope their memory structures are more efficient than with OpenPyxl and the TCP/IP call bridge is efficient enough</p> <p>2) Openpyxl uses xml.etree. Would Python lxml (libxml2 native extension) be more efficient wit XML memory structures and is it possible to replace xml.etree directly with lxml drop-in e.g. with monkey-patching? (later the changes could be contributed back to Openpyxl if there is a clear benefit)</p> <p>3) Export from MySQL to CSV and then post-process CSV files directly to XSLX using Python and file iteration</p> <p>4) Use other programming languages and libraries (Java)</p> <p>Pointers:</p> <p><a href="http://dev.lethain.com/handling-very-large-csv-and-xml-files-in-python/" rel="nofollow">http://dev.lethain.com/handling-very-large-csv-and-xml-files-in-python/</a></p> <p><a href="http://enginoz.wordpress.com/2010/03/31/writing-xlsx-with-java/" rel="nofollow">http://enginoz.wordpress.com/2010/03/31/writing-xlsx-with-java/</a></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMike Pennington
    1. USMikko Ohtamaa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreating big Excel sheets programmatically
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCreating big Excel sheets programmatically
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCreating big Excel sheets programmatically
      1. This table or related slice is empty.
      1. VTUpMod
    1. COdoes it have to be xlsx, are you doing custom formatting or something? if it was just a straight csv dump from mysql, excel can open those quite happily without needing the conversion step
      singulars
      1. POCreating big Excel sheets programmatically
      1. USbumperbox
    2. COcustom formatting needed, CSV loses dates and other complex columns
      singulars
      1. POCreating big Excel sheets programmatically
      1. USMikko Ohtamaa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload