How to export Excel file and set column width in native PHP?

How to accurately control column width is a common problem when native PHP exports Excel files. This article will explain in detail how to use PHP code to export Excel files and set column widths to solve the problem of inconsistent column widths of exported data.

For example, you need to export three columns of data "Alipay Account", "Name", and "Payment Amount" and set their column widths respectively. The column width setting cannot be achieved by directly using the fputcsv function. We need to use the PHPExcel library.

Improved method: Use PHPExcel library

The following code demonstrates how to set column width using the PHPExcel library:

 // Import the PHPExcel class library (please make sure that PHPExcel is installed correctly)
require_once 'Classes/PHPExcel.php'; // Replace with the actual path of the PHPExcel library// Create a PHPExcel object $objPHPExcel = new PHPExcel();

// Set column width (unit: character width)
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); // Alipay account $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); // Name $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10); // Payment amount// Set the header $title = array('Alipay account','name','Paid amount');
$objPHPExcel->getActiveSheet()->fromArray($title, null, 'A1');

// Fill in data $row = 2;
foreach($list as $val){
    $data = array(
        $val['alipay_acount'],
        $val['alipay_real_name'],
        $val['total_check_che']
    );
    $objPHPExcel->getActiveSheet()->fromArray($data, null, 'A'.$row);
    $row ;
}

// Save as Excel 2007 format (.xlsx)
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Article Information Statistics'.date('ymdHis').'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

This code first introduces the PHPExcel library, then creates the PHPExcel object, and sets the widths of the 'A', 'B', 'C' columns respectively using getColumnDimension() method. After that, the data is filled with fromArray() method, and finally the data is saved in xlsx format using Excel2007 writer and output.

Set column width in batches (optional)

If you need to set the column width in batches, you can use a loop:

 $cols = range('A', 'Z'); // Set the width of column A to Z foreach ($cols as $col) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth(15); // Set all column widths to 15
}

This loop can modify the range and width of the column as needed.

Summarize

By using the PHPExcel library, we can easily control the column width of the export Excel file, thereby achieving better data display results. Please make sure that the PHPExcel library is installed and configured correctly and adjust the require_once statement in the code according to the actual path. This method is more flexible than using fputcsv , which can better meet various Excel export needs.

The above is the detailed content of How to export Excel file and set column width in native PHP?. For more information, please follow other related articles on the PHP Chinese website!