How do I set the width of columns in an Excel spreadsheet generated as HTML?

Question

How do I set the width of columns in an Excel spreadsheet generated as HTML?

Navigation

#1 by (2 votes)
#2 by (2 votes)

5

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 not set the formatting of column widths in excel. In CSS the tab is correct but how do you format the column widths in excel?

php excel html-table

asked by anonymous 17.02.2014 / 21:13

2 answers

How to filter, select and count data in a pandas.DataFrame? WPF - The SelectedItem property of the ComboBox is not working correctly

score 2 · Answer 1

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

Rather than worrying about setting the width through PHP, why do not you go for a macro in Excel?

I've used the macro extensively. I fit it for you.

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

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 on the Microsoft forum in 2011. link

score 2 · Answer 2

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

However, you might want to take a look at the PHPExcel library, which is a well-known library for file manipulation xls . 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);