PHP从MySql转换为Excel电子表格自动调整列 [英] PHP convert from MySql to Excel Spreadsheet Autosize Columns

问题描述

目前，我正在使用以下代码从MySql数据库将租户数据导出到 Excel 电子表格中。

I am currently exporting tenant data from my MySql database into an Excel spreadsheet, using the code below.

<?php
    require_once('../../../includes/config.inc.php'); 

    require_once(MYSQL);

    $query = "SELECT * FROM tenant";        //Define the SQL query

    if ($result = $dbc->query($query))
    {
        $file_type = "vnd.ms-excel";
        $file_ending = "xls";

        header("Content-Type: application/$file_type");
        header("Content-Disposition: attachment; filename=tenant_data.$file_ending");
        header("Pragma: no-cache");
        header("Expires: 0");

        $seperator = "\t";  //define separator (defines columns in excel)

        $field_count = $result->field_count;

        for ($for_count = 1; $for_count < $field_count; $for_count++)   //start of printing column names as names of MySQL fields
        {
            $result->field_seek($for_count);

            $field_info = $result->fetch_field();

            $field_name = $field_info->name;

            $field_name_title = str_replace("_", " ", "$field_name");

            $field_array[] = $field_name;

            echo $field_name_title . $seperator; 
        }

        $result->field_seek(0);

        print("\n");        //end of printing column names

        while($row = $result->fetch_assoc())    //start while loop to get data
        {
            $schema_insert = "";

            for ($col_count = 0; $col_count < $field_count - 1; $col_count++)
            {
                $current_field = $field_array[$col_count];

                if ($row[$current_field] != "")
                {
                    $schema_insert .= "$row[$current_field]" . $seperator;
                }
                else
                {
                    $schema_insert .= "" . $seperator;
                } 
            }

            $schema_insert = str_replace($seperator . "$", "", $schema_insert);

            $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);            //Replaces \n or \r with a space

            $schema_insert .= "\t";

            print(trim($schema_insert));

            print "\n";
        }
    }
?>

代码目前有效，但是我有两个问题：

The code currently works, however I have two problems:

1）我希望电子表格中的列自动调整到创建内容的大小。

1) I want the columns in the spreadsheet to autosize to the size of the content on creation.

2）在数据中，有一个电话字段，其中数字趋于从0开始。结果，0趋于在数据库中被错过，我认为列需要指定为String。

2) In the data, there is a Phone field, where numbers tend to start with 0. As a result, the 0 tends to get missed off in the database, and I think the column needs specifying as a String.

有谁知道如何做这些事情？我不想使用PHPExcel，因为它似乎是一个巨大的过度复杂，当这是我想做的所有。

Does anyone know how to do either of these things? I don't want to use PHPExcel as it seems to be a massive overcomplication when this is all I want to do.

推荐答案

如果没有任何答案，我最终决定只使用 PHPExcel 类。

In the absence of any answer, I eventually decided just to use the PHPExcel class.

这篇关于PHP从MySql转换为Excel电子表格自动调整列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！