How to set Column Width in a generated Excel spreadsheet as HTML?

Question

How to set Column Width in a generated Excel spreadsheet as HTML?

I need to convert an HTML table and generate the file in excel. How to set the width of columns in excel? I create the HTML file using PHP and export using:

header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"{$arquivo}\"" );
header ("Content-Description: PHP Generated Data" );

The issue is that I can't set the column width formatting in excel. In CSS the tab is correct but how to format the column widths in excel?

5

Author: Marcelo Aymone, 2014-02-17

Source

2 answers

score 2 · Answer 1

As I understand it, you want to open an HTML inside Excel.

Instead of worrying about setting the width through PHP, why don't you start for a macro in Excel?

I've used the macro below a lot. I adapted it for you.

See The Adjust Column Width command. It will not set specific widths, but will optimize the widths in general.

Sub Macro2()
'
' Macro2 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
               "URL;file:///C:/test.html" _
    , Destination:=Range("$C$3"))
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
End Sub

Credit to mcbranco who helped me at the Microsoft forum in 2011. http://goo.gl/KxGxwl

score 2 · Answer 2

As suggested by @bfavaretto's comment, you are just forcing the browser to suggest that the best way to load a file is with excel. In case you want to set the column width in this way, you can create a xls and rename it to a zip, then just open the xml and check its properties.

However, it might be interesting to take a look at the library PHPExcel, which is a well-known library for handling xls files. In the case of PHPExcel, just use the command to set the width, as can be seen in the following example:

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(0.54);

If you want the width to be set automatically, you can use:

$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);