Export dhtmlxGrid to Excel

| Comments (70)

UPDATE: Use the links below to download the newest version of the export packages:

 
Having developed a way to export grid data to an external format, we found it hard to stop at just PDF.

We have been often asked about the possibility of exporting dhtmlxGrid to MS Excel format. (What could be more natural than transporting data from one kind of spreadsheet to another?) Until now, the only way to export grid data to Excel was going through CSV format, which was causing the loss of information about grid structure and visual appearance.

The Grid-to-Excel service allows you to save the current state of the grid into the .xls file, keeping the grid structure and visual settings. So if the grid looks like this on the page:

Grid-to-Excel. HTML page.

Grid-to-Excel. HTML page.

The result in Excel will look like:

Grid-to-Excel. MS Excel document.

Grid-to-Excel. MS Excel document.

You can see a working example here.

To install the Grid-to-Excel service locally, you need to download this package, which contains two folders:

./server – copy this folder onto the server (PHP 5.x required)
./client – copy files from this folder to the codebase of the grid

After that, to enable Grid-to-Excel functionality, you should add the following code to the page with dhtmlxGrid:

             <script src="./codebase/dhtmlxgrid_export.js">
             scheduler.toExcel ("http://some.com/server/generate.php");

Note that the http path is the path to the script of the export service.

So now you can enjoy a ready solution to easily export data from dhtmlxGrid to Excel spreadsheet. For the configuration details, you may also check the documentation.

Comments

  1. Amol Patil May 3, 2010 at 12:50 pm

    This feature is really cool and much awaited as this is a very basic requirement in almost every application.
    One improvement that I would like to suggest which will improve the user friendlyness of this feature and application
    -> “It will be grate to have a way to automatically close the new window that gets opened on this event”

  2. Amol Patil May 3, 2010 at 12:59 pm

    Also it will be gr8 if generate.php is can be hosted on dhtmlx.com for excel export, the same way it is done for export to pdf / print

  3. Harsh Choudhary May 3, 2010 at 6:25 pm

    I tried this feature. Everything works fine, except that the excel file exported says its is corrupt when I try to open in excel. I am using 2010 , dont know if that makes a difference. any help would be greatly appreciated.

    • ivan May 4, 2010 at 1:41 pm

      Harsh, on our side everything works fine. If you still encountering any problems with grid-to-Excel, please either post this question to the Forum or open a support ticket (if you have the PRO license).

      • Pratham April 12, 2012 at 9:47 am

        firstly Dhtmlx is superb for Data export.
        well i am trying to export to excel in using dhtmlx java jsp but when i am trying to export more then 2000 data its not working with 5 column but when i do with 4 column then it works fine..
        please help out of this.
        is ther any data type specification becase i am writing xml from resultset.

  4. Scott May 11, 2010 at 1:39 am

    Would be great if there was a java-based server.

    • ivan May 11, 2010 at 5:05 pm

      Unfortunately we don’t plan to make a Java-based version of this functionality in the near future.

  5. mark May 28, 2010 at 9:09 am

    Would be great if there was a c#-based server.

  6. Manuel May 30, 2010 at 3:17 am

    How can i change the output file name, grom grid.xls to another?

    • ivan May 31, 2010 at 3:52 pm

      Manuel, in current version name of file is hardcoded , there is no way to change it dynamically.

    • Siddharth Kushwah November 20, 2015 at 11:39 am

      yes you can change excel file name dynamic.for this you send file name in library.
      mygrid.toExcel(public/dhtmlxgrid/codebase/grid-excel-php/generate.php?filename=’+filename);
      and then some change in library

  7. Nestor May 31, 2010 at 7:51 am

    I can’t see or open the Demo file with IE 8, all is OK with Firefox 3.6

    • ivan May 31, 2010 at 3:53 pm

      Nestor, the problem can be caused by used security settings. Files of excel are counted as active content, so IE can block their download.
      You can set:
      Tools -> Internet options -> Security -> Custom level -> Downloads -> Automatic prompting for files download -> Enabled

  8. Ricardo May 31, 2010 at 9:21 pm

    And what about microsoft office 2003?

    • ivan June 1, 2010 at 1:06 pm

      It works for MS office 2003 as well.

  9. Dale June 2, 2010 at 5:50 pm

    Hi, I would have loved this feature about 6 months ago. I had to build my own in ASP. Tried this one and everytime I click to convert to excel it opens a new window with a file download security warning. When I accept it at the top of the browser window it comes does nothing and I can see the same grid but in a new page. I end up with many pages open and no excel document. Obviously a security issue but surely one most my customers would encounter. Any ideas? Thanks.

    • Inga June 2, 2010 at 7:41 pm

      The problem can be caused by security settings in IE. Files of excel are counted as active content, so IE can block their download. Try to set:
      Tools -> Internet options -> Security -> Custom level -> Downloads -> Automatic prompting for files download -> Enabled

  10. Kenneth Chen June 19, 2010 at 7:38 am

    i tried the example on windows 2003 R2 English version + IIS 6 + PHP 5.3.2(php-5.3.2-nts-Win32-VC9-x86.msi) + fastCGI (fcgisetup_1.5_rtw_x86.msi) and have problems.
    the downloaded excel file is corrupted. The excel program prompted to fix it, then the data is there yet the layout is gone. Please help and thanks.

    • ale March 10, 2011 at 7:52 pm

      it’s a php version problem. I make it work only with 5.2.17. I tested php version 5.1.16 & 5.3.1 & 6.0 without success.

      • monty June 18, 2011 at 12:52 am

        I think you are right.. I updated my server and the export to excel feature broke with generating a corrupted xls file. I have been going *crazy* looking for a solution and was about to give up.. Calling the remote dhtmlx service works.. but I am senging data that should not leave the firewall.

        I didnt want to downgrade my PHP release to 5.2.17, and I do have access to a tomcat installation on the host. I was able to plug in the java version, make the tweak to the web page to call the java url and VOILA!.. works fine.

        Whew!

  11. ivan June 21, 2010 at 12:33 pm

    Kenneth, please post your question on the Forum and specify the version of Exel that you use to open generated files.

    • Kenneth Chen June 21, 2010 at 2:11 pm

      Will do. thanks a lot :)

  12. Cyril June 24, 2010 at 11:14 pm

    Hi guys, excellent work! When do you think the issues will be resolved, most specifically exporting a grid with a header containing select_filters which is also exported but not correctly. This is a problem on the PDF export. Also, I have a grid with subgroup which isn’t exported into excel/pdf either. Are you planning new releases of these libraries soon?

    • Stanislav June 28, 2010 at 12:31 pm

      Currently we have not plans to support export of grid with sub-grids
      As for filter-in-header problem – you can update client side code from
      the next post

  13. Harsh Choudhary July 9, 2010 at 7:41 am

    Hi Is there anyway, to exclude the hidden columns of the grid in the excel file? Right now when I do export to excel, it also includes the column which are hidden in the excel. Please suggest.

    • ivan July 15, 2010 at 2:00 pm

      We plan to add the possibility to exclude the hidden columns in the near future. Unfortunately now this feature is not supported yet.

      • Nishadh May 5, 2014 at 8:36 am

        Is there anyway, to exclude the hidden columns of the grid in the excel file? Right now when I do export to excel, it also includes the column which are hidden in the excel. Please suggest Harsh Choudhary,Ivan. Thanks

        • Stanislav May 6, 2014 at 5:52 pm

          In latest version ( dhtmlxGrid 3.6 ) hidden columns are excluded from xml and pdf export ( columns hidden by setColumnHidden API )

  14. Candra Arie Nugrahanto July 15, 2010 at 11:36 am

    Hi…How to import excel files to MySQL database using dhtmlxGrid?

    • ivan July 15, 2010 at 1:57 pm

      dhtmlxConnectors v1.0, which will be a part of the upcoming version 2.6, will provide the possibility to load data in the grid directly from the Excel file.

  15. jonathan July 26, 2010 at 11:29 pm

    Hi, thanks for this very exciting addition.

    I don’t see footers in the exported file, and there isn’t even a “footerPrint” function in the code, they way you have rowPrint and headerPrint. Can footers be exported?

    Also, headers aren’t wrapping. This isn’t as big a deal in Excel as it is in PDF, where the user really can’t edit it.

    • ivan July 27, 2010 at 6:00 pm

      1. Export of footer is not supported right now. We plan to add this feature soon.
      2. Will be fixed in the near future.

  16. rofel August 24, 2010 at 9:39 am

    the exporting of the grid didn’t complete when there’s a large of data.(like 2000 rows)

    is there any way to prolong the execution time of the script.

    i already set_time_timelimit(0) in the generate.php but the scripts stop mostly 10 secs.

    • ivan August 24, 2010 at 12:34 pm

      Possibly some other configuration setting of server prevents long script execution, maybe max memory limit or something similar.

      • Jazzy August 29, 2010 at 1:42 am

        I do have the same problem. But I guess it is in fact of dynamic loading of rows. If the grid is fresh only 100rows are loaded. The export only has 100rows. But if you scroll down the grid and load more than 100rows, maybe 1000. Than the export has 1000rows. Is there a way to export all rows even if the gridis fresh.

  17. philipp September 7, 2010 at 1:19 pm

    Nice feature,

    but I had to change in generate.php:

    $xmlString = stripslashes($_POST[‘grid_xml’]);

    to

    $xmlString = stripslashes($_POST[‘mycoolxmlbody’]);

    make it work.
    I downloaded from the link above.

    greetings, philipp

  18. Jay February 17, 2011 at 5:36 am

    What is the minimum version PHP needed for the Export to Excel/PDF feature from grid?

    • ivan February 17, 2011 at 1:36 pm

      It’s PHP 5.x.

  19. nathan March 25, 2011 at 2:47 pm

    I generate xml string using java template and form a grid in JSP using that xml sting.The excel export doesn’t launch when any one of grid cell contain “degree symbol” . Otherwise it’s working fine. if any one have idea for this problem?

    • ivan March 25, 2011 at 8:24 pm

      Are you using utf-8 for the grid’s page or some different encoding?
      If you are using UTF-8 – please provide a sample of problematic xml data.

  20. Francisco June 14, 2011 at 11:44 pm

    I cant make it work on IE8, when i trigger the generator IE8 just opens a new pop up and thats all .. done … but no excel…….. if i open the error log its a perfect xml with the data.

  21. Adriano June 15, 2011 at 7:10 pm

    Hi. I want to export all colum in excel (also in pdf) But, when i set a column hidden, it appear with a width=2. How to show these comum with their actual size ?

    • Ivan June 20, 2011 at 7:18 pm

      Adriano, please try to use the latest files of the export service: http://www.dhtmlx.com/blog/?p=553

      If you still have any problems, please submit your questions to the forum: http://forum.dhtmlx.com/
      Registration is free. Answers are provided by support team and community.

  22. Karvesh June 27, 2011 at 4:54 pm

    Hello
    I tried exporting my grid to excel. But it just seems to stop half way because there seems to be too many records, exactly 260 records. When i try for a smaller set, say 50, it works fine.. Is it any issue with my web server? Do i have to increase connection time out or something like that?

  23. Abhishek July 6, 2011 at 3:15 pm

    Is there any way to generate excel through dhtmlx tree grid.
    The excel to have rows collapsable, in the same way as in the tree grid.

    • ivan July 7, 2011 at 9:22 pm

      Unfortuantely it is not supported in the current version of the export script.

  24. Ayman July 17, 2011 at 4:02 pm

    I am recieving this error when converting to pdf
    Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 6291456 bytes) in E:\Marketing Web Folder\ATL-Web-Management\to_pdf\server\tcpdf\unicode_data.php on line 16485

    and when converting to excell I get a blank page

    Can anyone help me with this issue?

    • Stanislav July 21, 2011 at 1:01 pm

      You need to increase memory limit setting in php.ini

  25. Mauro Nieva September 8, 2011 at 8:38 pm

    Exists dhtmlxGrid-to-Excel for .NET??
    you could send some example?

  26. china wchaowu September 13, 2011 at 12:18 pm

    Would be great if there was a c#-based server.

  27. William February 14, 2012 at 8:21 pm

    Please… I need the server configuration (grid to excel) for java, but i don’t find in blog or site. Someone could post.

  28. Marina March 15, 2012 at 5:36 pm

    Privet, thanks for the great tool. I was wondering whether it is possible to save information entered by users in the grid into a relational, highly normalized, fully populated MySQL database through HTML form submission? (click a button and send the grid with its input values to the server to be parsed and inserted/updated in a MySQL database) Spasibo! :)

  29. Sayan July 15, 2012 at 4:45 pm

    I have similar post on excel export using jXLS, the main advantage of JXLS is uses templates which makes excel design formatting extremely easy,have a look

    http://fundapass.blogspot.in/2012/07/this-method-is-getting-called-from-ajax.html

    • Dhivya June 12, 2013 at 10:45 am

      I am able to export to PDF/Excel using the DHTML Libraries. But the header and columns contents are not wrapping. Is there any feature to wrap the text in (Export->PDF/Excel) .NET version?

  30. Umaid Naeem June 16, 2016 at 3:44 pm

    I want To export data to Excel File From my Node.js Application Please Help me through it

  31. Umaid Naeem June 17, 2016 at 11:31 am

    I want to export Grid Data into excel file through Node.js couldn’t find any thing helpful. Please help me through it.

Leave a Reply