CODEWITHSUNDEEP
phpexcellaravel
Mossen
Mossen

Reputation: 604

Adjust column width when exporting an Excel file

I am trying to export data from Mysql to an excel file by using this code:

    $stories = Story::all();
    $header = 'Name' . "\t" . 'Email' . "\t" . 'Title' . "\t" . 'Created At' . "\t" . 'Story';
    $xsl = $header . "\n";
    foreach ($stories as $story)
    {
        $row = '';
        $row .= '"' . str_replace('"', '""', stripslashes($story->name )) . '"' . "\t";
        $row .= '"' . str_replace('"', '""', stripslashes($story->email)) . '"' . "\t";
        $row .= '"' . str_replace('"', '""', stripslashes($story->title)) . '"' . "\t";
        $row .= '"' . str_replace('"', '""', stripslashes($story->created_at)) . '"' . "\t";
        $row .= '"' . str_replace('"', '""', stripslashes($story->story)) . '"' . "\t";

        $xsl .= trim($row) . "\n";
    }

    $xsl = str_replace("\\t", "", $xsl);

    return Response::make($xsl)->header('Content-type', 'application/vnd.ms-excel')->header('Content-disposition', "attachment;filename=Stories [as of].xls");

The issue is how is it possible to give the columns auto width?

Upvotes: 4

Views: 2517

Answers (1)

Alex
Alex

Reputation: 810

You do not need a huge library to export excel. You can build almost genuine excel file just by using the proper meta in a html file.

You will have to include in your header something like this:

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
    <meta charset="UTF-8">
<!--[if gte mso 9]>
<xml>
    <x:ExcelWorkbook>
        <x:ExcelWorksheets>
            <x:ExcelWorksheet>
                <x:Name>Sheet 1</x:Name>
                <x:WorksheetOptions>
                    <x:Print>
                        <x:ValidPrinterInfo/>
                    </x:Print>
                </x:WorksheetOptions>
            </x:ExcelWorksheet>
        </x:ExcelWorksheets>
    </x:ExcelWorkbook>
</xml>
<![endif]-->
...

The full code snipped I'm actually using is below. Based on what it's written there, it is likely this code will not work on earlier versions of MS Office. Please note that this is a Blade view.

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
    <meta charset="UTF-8">
    <!--[if gte mso 9]>
    <xml>
        <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                <x:ExcelWorksheet>
                    <x:Name>Sheet 1</x:Name>
                    <x:WorksheetOptions>
                        <x:Print>
                            <x:ValidPrinterInfo/>
                        </x:Print>
                    </x:WorksheetOptions>
                </x:ExcelWorksheet>
            </x:ExcelWorksheets>
        </x:ExcelWorkbook>
    </xml>
    <![endif]-->
</head>

<body>
<table>
    @if(!empty($thead))
        <thead>
        {!! $thead !!}
        </thead>
    @endif
    @if(!empty($tbody))
        <tbody>
        {!! $tbody !!}
        </tbody>
    @endif
    @if(!empty($tfoot))
        <tfoot>
        {!! $tfoot !!}
        </tfoot>
    @endif
</table>
</body>
</html>

The trick here is you can take advantages of various features like columns and rows merging (with colspan and rowspan) and many more (it's working with alignment classes like text-center and text-right).

You can force downloading the file with a code in your controller like that:

$fileName = "export.xls";

$data = View::make('export.excel.table', [
    'thead' => $thead,
    'tbody' => $tbody,
    'tfoot' => $tfoot,
]);

return Response::make($data, 200, [
    'Content-type'        => 'application/excel',
    'Content-Type'        => 'application/excel',
    'Content-Disposition' => 'attachment; filename=' . $fileName
]);

Hope that helps.

Upvotes: 1

Related Questions