Adjust column width when exporting excel file

Question

Adjust column width when exporting excel file

I am trying to export data from Mysql to an excel file 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 problem is, how can I set the width of the auto-width columns?

+4

php excel laravel

Mossen Oct 7 '15 at 10:01

source share

1 answer

Alex · Answer 1 · 2015-10-11T21:49:18+0000

You do not need a huge library to export excel. You can create an excel file almost genuine only with the correct one metain the html file.

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

<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 I'm using is below. Based on what is written there, most likely, this code will not work in earlier versions of MS Office. Please note that this is a type of blade.

<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>

Hide result

, ( colspan rowspan) ( , text-center text-right).

:

$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
]);

, .

Adjust column width when exporting excel file

More articles: