PHPExcel auto size column width

If a column is set to AutoSize, PHPExcel attempts to calculate the column width based on the calculated value of the column (so on the result of any formulae), and any additional characters added by format masks such as thousand separators.

By default, this is an estimated width: a more accurate calculation method is available, based on using GD, which can also handle font style features such as bold and italic; but this is a much bigger overhead, so it is turned off by default. You can enable the more accurate calculation using

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

However, autosize doesn't apply to all Writer formats... for example CSV. You don't mention what writer you're using.

But you also need to identify the columns to set dimensions:

foreach(range('B','G') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
        ->setAutoSize(true);
}

$objPHPExcel->getActiveSheet()->getColumnDimension() expects a column ID.

$objPHPExcel->getActiveSheet()->getColumnDimensions() will return an array of all the defined column dimension records; but unless a column dimension record has been explicitly created (perhaps by loading a template, or by manually calling getColumnDimension()) then it won't exist (memory saving).

A general idea of what you could do:

Create a button e.g.

<a href="#" id="export">export to Excel</a>

Then in jquery you have to create something like:

var grid = $(".grid.digital_edit").loadGrid({...}); //or similar - what you did to load the data into the grid

$('#export').click(function() {
    $.ajax({
        url: "export_to_excel.php", // the url of the php file that will generate the excel file
        data: grid.getData(), //or similar - based on the grid's API
        success: function(response){
            window.location.href = response.url;
        }
    })

});

The file export_to_excel.php will contain the code that generates the excel file:

1. This is where you'll initiate the PHPExcel class and create a file e.g. new_excel.xls
2. In your response array the $response['url'] will contain the absolute url to the newly created file. (http://www.example.com/files/new_excel.xls)

It may sound too complex, but try to separate your goals and achieve one at a time. E.g.

1. Create the button.
2. Then try to make a simple AJAX call when hitting the button.
3. Then create your export_to_excel.php file and try to work with the PHPExcel class.
4. Create a sample excel file based on the tutorials found.
5. Create an excel file based on your own data, but hard-coded in the php file.
6. Create the correct AJAX call that sends the wanted data to a php file.
7. Catch the correct AJAX call.
8. Pass the data from the AJAX call to the PHPExcel class.
9. Create the excel file.
10. Send back the url to the excel file.
11. Redirect the user to the url of the excel file.

EDIT

To help you a bit more: You need only one PHP script/file. The same one will receive the AJAX call from the javascript file, will generate the excel file and will return/respond the file url to the javascript file(in that order). A simplified example would be:

<?php
//export_to_excel.php

$data = $_POST['data']; // get the data from the AJAX call - it's the "data: grid.getData()" line from above

//... format the received data properly for the PHPExcel class

// later on in the same file:
$xls = new PHPExcel();
$xls->loadData($formattedData); //I assume that there is a similar loadData() method
$xls->exportToFile('/vaw/www/example.com/public/files/new_excel.xls'); // I assume that there is an exportToFile() method

$response = array(
    'success' => true,
    'url' => 'http://www.example.com/files/new_excel.xls'
);

header('Content-type: application/json');

// and in the end you respond back to javascript the file location
echo json_encode($response);

And then in javascript you display the file with this line

window.location.href = response.url; //response.url is $response['url'] from the PHP script

You can do it with AutoFit:

 Columns("A:B").EntireColumn.AutoFit