mk-j / PHP_XLSXWriter

Lightweight XLSX Excel Spreadsheet Writer in PHP
MIT License
1.84k stars 664 forks source link

Option to have row-specific format #276

Closed grandehombre closed 1 year ago

grandehombre commented 4 years ago

(Many thanks to the author for providing us with this very useful library!)

There are times when I need to override the format of a certain column, based on that column's value in the current row. For example, I define 'postcode' as 'integer' at the column level. However, some rows can have non-numeric postcodes. In such cases, I want to be able to override the format_type and set it to 'integer'.

This can be achieved with the following mods to writeSheetRow()

/* was   foreach ($row as $v) {   */

/* is */      foreach ($row as $k => $v) {
            $number_format = $sheet->columns[$c]['number_format'];
            $number_format_type = $sheet->columns[$c]['number_format_type'];
// rest of changes start here
            if (key_exists($k, $row_options)) {
                if (key_exists('number_format', $row_options[$k])) {
                    $number_format = $row_options[$k]['number_format'];
                }
                if (key_exists('number_format_type', $row_options[$k])) {
                    $number_format_type = $row_options[$k]['number_format_type'];
                }
            }**
// changes end here

            $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle($number_format, json_encode(isset($style[0]) ? $style[$c] : $style));
            $this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx);
            $c++;
        }

The caller simply needs to provide the extra formatting info, as follows ('// changes start here' marks the relevant code. The rest is for context)

            $qry = self::getDatatable()->get();
            if (count($qry) > 0) {
                // get the field name list
                $projects = $qry->toArray();
                $contactFieldList = [
                    'address1' => ['type' => 'string', 'heading' => 'Address']
                    , 'city' => ['type' => 'string', 'heading' => 'City']
                    , 'state' => ['type' => 'string', 'heading' => 'State']
                    , 'postcode' => ['type' => 'string', 'heading' => 'Postcode']
                    , 'country' => ['type' => 'string', 'heading' => 'Country']
                ];

                $writer = new XLSXWriter();
                $sheetName = 'Sheet1';

                $styles1 = array('font' => 'Arial', 'font-size' => 10, 'font-style' => 'bold', 'fill' => '#eee', 'halign' => 'center', 'border' => 'left,right,top,bottom');
                $headerRow = [];
                foreach ($contactFieldList as $k => $v) {
                    $headerRow[$v['heading']] = $v['type'];
                }
                $writer->writeSheetHeader($sheetName, $headerRow, $styles1);

                $row = [];
                foreach ($projects as $r) {
                    foreach ($contactFieldList as $k => $v) {
                        $row[$k] = $r[$k];
                    }

// changes start here
// instead of just calling
//                     $writer->writeSheetRow($sheetName, $row, $opts);
// do this
                    if (!is_numeric($r['postcode'])) {
                        $opts =  ['postcode' => ['number_format_type' => 'string', 'number_format' => '']];
                    } else {
                        $opts =  ['postcode' => ['number_format_type' => 'integer', 'number_format' => '']];
                    }
                    $writer->writeSheetRow($sheetName, $row, $opts);
                }

                $writer->writeToFile($xlsName);
            }