How to set the width of columns in a generated Excel spreadsheet as HTML?

Asked

Viewed 2,591 times

5

I need to convert an HTML table and generate the file in excel. How to set column width 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 point is that I cannot define the column width formatting in excel. In CSS the tab is correct but how to format column widths in excel?

php excel html-table

  • I think there is no way, just generating a native XLS file. Already tried to export pure HTML or CSV to see what scrolls?

  • yes, even then it loses formatting.

  • In fact you are not exporting to Excel, you are only forcing the browser to suggest that the file be opened by Excel. Maybe you need to generate a real Excel file, using HTML there are several formatting limitations (but I’m not sure that this is one of them).

  • knows some api type Worksheet.php to do this?

2 answers

2

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

However, it might be interesting for you to take a look at the library Phpexcel, 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);

2

From what I understand, you want to open an HTML inside Excel.

Instead of worrying about setting the width through PHP, because you don’t part to a macro in Excel?

I have already used enough macro below. I adapted for you.

See the Adjust Column Width command. It will not set specific widths, but will optimize 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 for mcbranco who helped me in the Microsoft forum in 2011. http://goo.gl/KxGxwl

Browser other questions tagged php excel html-table

You are not signed in. Login or sign up in order to post.