Home > Backend Development > PHP Problem > How to achieve different colors in the same column in Excel in php

How to achieve different colors in the same column in Excel in php

PHPz
Release: 2023-04-21 09:31:30
Original
1121 people have browsed it

Excel is a spreadsheet processing software widely used in office software. It has powerful functions, flexible use and supports many operations. When using Excel to process table data, "different colors in the same column" are often used to distinguish different data to facilitate analysis and viewing.

In Excel, using different colors in the same column requires manual formatting, which is often tedious and time-consuming. By using PHP to write programs, you can automatically set different colors in the same column with one click, improving work efficiency and reducing error rates.

First, let’s take a look at the process of manually setting different colors for the same column in Excel.

① First select the column to be set;

② Click "Conditional Formatting", "New Rule", and "Use formulas to determine the cells to be formatted";

③ Enter the expression to set the conditions in "Formula";

④ Select the color according to the expression and set the format;

⑤ Click "OK" to save the settings.

The above is the basic process of manually setting different colors for the same column. Although it is more cumbersome, it only needs to be completed once and it can be applied to the entire worksheet.

Next, let’s take a look at how to use PHP to automate the setting of different colors for the same column.

In PHP, we can use the PHPExcel extension library to operate Excel files. The library can read and write Excel files and supports formatting and content of cells.

First, you need to introduce the PHPExcel library in PHP, and then use the PHPExcel_IOFactory class to open the Excel file. The code example is as follows:

require_once 'Classes/PHPExcel.php'; // 引入PHPExcel库

$excelObj = PHPExcel_IOFactory::load('test.xlsx'); // 打开Excel文件
$sheet = $excelObj->getActiveSheet(); // 获取当前工作表格
Copy after login

Then, we need to set each cell by traversing the columns. The code example is as follows:

for($i=1; $i<=$maxRow; $i++){
    $cellValue = $sheet->getCellByColumnAndRow($colIndex, $i)->getValue();

    // 判断单元格内容是否满足某些条件
    if($cellValue > 50) {
        $styleArray = array(
            'font' => array(
                'bold' => true,
                'color' => array('rgb' => 'FF0000')
            )
        );
        $sheet->getStyle($colName.$i)->applyFromArray($styleArray); // 设置单元格格式
    }
    else {
        $styleArray = array(
            'font' => array(
                'bold' => true,
                'color' => array('rgb' => '0000FF')
            )
        );
        $sheet->getStyle($colName.$i)->applyFromArray($styleArray); // 设置单元格格式
    }
}
Copy after login

In the above code, $maxRow represents the maximum number of rows of the current column, $colIndex represents the index value of the current column (starting from 0), and $colName represents the alphabetical name of the current column. When traversing each cell, set different font colors based on whether the cell content meets certain conditions.

Finally, we need to save the modified Excel file. The code example is as follows:

$excelWriter = PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
$excelWriter->save('new_test.xlsx');
Copy after login

The above are the complete steps to use PHP to automatically realize the setting of different colors in the same column in Excel.

In summary, using PHP to write programs to set different colors in the same column of Excel can greatly improve work efficiency and reduce error rates. At the same time, the PHPExcel extension library also has other powerful functions that can meet other needs in Excel table processing.

The above is the detailed content of How to achieve different colors in the same column in Excel in php. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template