How To add some informations above the header

[amidia]

Can we create several rows of headers?

[mk-j]

The purpose of headers is to set the column types of the following rows.

[amidia]

what i meant was. some informations strings above the header. such company name, document number etc.

[mk-j]

You could trying calling writeSheetRow() several times before calling writeSheetHeader() if that helps.

[amidia]

still doesn't work. any other suggestion @mk-j ?

[michaelpporter]

Call writeSheetHeader with suppress_rows. Then writeSheetRow() the address, then writeSheetRow the header data with formatting.

[amidia]

@michaelpporter it's can work. but since the header is rendered by writeSheetRow(). the data cannot be formated. any suggestion?

[michaelpporter]

something like this.

$header = array("string","string","string","string","string");
$headerdata = array("Name","Address","City","State","Postal Code");
$styles1 = array( 'font'=>'Arial','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee', 'halign'=>'center', 'border'=>'left,right,top,bottom');

$preheader = array(
    array('', 'Acme Corp.', '', '', ''),
    array('', '123 Main', 'Anytown', 'US', '50010'),
    array('', '(123) 234-234', '', '', ''),
);
$rows = array(
    array(100, 200, 300, 400, 500),
    array(100, 200, 300, 400, 500),
    array(110, 210, 310, 410, 510),
);
$writer->writeSheetHeader($sheet1, $header, $suppress_header_row = true);
foreach($preheader as $row) {
	$writer->writeSheetRow($sheet1, $row);
    }
$writer->writeSheetRow('Sheet1', $headerdata, $styles1 );
foreach($rows as $row) {
	$writer->writeSheetRow($sheet1, $row);
    }

[amidia]

$header = array("string","string","string","string","string","integer"=>"#,##0"); //when i add this format
$headerdata = array("Name","Address","City","State","Postal Code","Cost"); //and this header
$styles1 = array( 'font'=>'Arial','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee', 'halign'=>'center', 'border'=>'left,right,top,bottom');

            $preheader = array(
                array('', 'Acme Corp.', '', '', ''),
                array('', '123 Main', 'Anytown', 'US', '50010'),
                array('', '(123) 234-234', '', '', ''),
            );
            $rows = array(
                array(100, 200, 300, 400, 500, 450000), //this values
                array(100, 200, 300, 400, 500, 75000),
                array(110, 210, 310, 410, 510, 50000),
            );
            $writer->writeSheetHeader($sheet1, $header, $suppress_header_row = true);
            foreach($preheader as $row) {
                $writer->writeSheetRow($sheet1, $row);
            }
            $writer->writeSheetRow('Sheet1', $headerdata, $styles1 );
            foreach($rows as $row) {
                $writer->writeSheetRow($sheet1, $row);
            }

the rows data formated correctly but the "Cost" header displayed "0"

can we make the format header applied only under the $writer->writeSheetHeader()?

[michaelpporter]

if your columns will contain mixed content then you have to use GENERAL

$header = array("string","string","string","string","string","integer"=>"GENERAL");

The other option is to extented this class and make a new header function.

class XLSXWriterHeaderRow extends XLSXWriter
{
// other code
public function writeSheetHeaderWithRow($sheet_name, array $header_types, $suppress_row = false, $row=1)
	{
		if (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name]))
			return;
		self::initializeSheet($sheet_name);
		$sheet = &$this->sheets[$sheet_name];
		$sheet->columns = $this->initializeColumnTypes($header_types);
		if (!$suppress_row)
		{
			$header_row = array_keys($header_types);
			$sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . $row . '">');
			foreach ($header_row as $c => $v) {
				$cell_style_idx = $this->addCellStyle( 'GENERAL', $style_string=null );
				$this->writeCell($sheet->file_writer, 0, $c, $v, $number_format_type='n_string', $cell_style_idx);
			}
			$sheet->file_writer->write('</row>');
			$sheet->row_count++;
		}
		$this->current_sheet = $sheet_name;
	}
// other code

Then:

$writer = new XLSXWriterHeaderRow();
// etc.

[gnanet]

Short question, based on the above sample of @michaelpporter i did my own:

class XLSXWriterHeaderMultiRow extends XLSXWriter

i made a simple extend to write multiple rows above header,but as soon as i do

        $writer = new XLSXWriterHeaderMultiRow();
        if ( $writer ) { echo print_r($writer, true)."\n"; }

        $writer->setAuthor('myauthor');

I see only the object "XLSXWriterHeaderMultiRow" printed by print_r, but never get to setAuthor, and the class destructs,and execution is halted (CLI). I may oversee something in my child-class, but i cannot find the problem. That also means i get not even to the part, where i could call my function in the child class

[gnanet]

A side note to my question above: if i simply paste my custom writeSheetHeaderMultiRows() function below writeSheetHeader() the custom function is working as expected.

And there is nothing special in the custom function, it is not more, than a combination of a "foreach-wrapped" writeSheetRow() and writeSheetHeader() minimaly customized to handle the row count properly.

I can only think about either some basic mistake i make in the child class, or there is some part inside the main class, that prevents me to simply add one custom function.

[ArunShree]

$header = array(' '=>'string', ' '=>'string'); $styles1 = array('widths'=>[40,40]); $styles2 = array('font'=>'Arial','font-size'=>14); $styles3 = array('font'=>'Arial','font-size'=>10); $writer->writeSheetHeader('Sheet1', $header, $styles1); $writer->writeSheetRow('Sheet1', ['some heading above heading'],$styles2); $writer->markMergedCell('Sheet1', $start_row=0, $start_col=0, $end_row=0, $end_col=2); $writer->writeSheetRow('Sheet1', ['column 1 name','column 2 name'],$styles3);

below this you can put your db render loop or whatever row data you want .The only drawback is , this will leave an empty row at the very top.This is what i did after searching a lot and found noting online. I dont know whether this is a proper way.But it worked for me.

[mk-j]

this should be fixed now.

[kegs85]

@mk-j if I want to set the col widths AND set something above the actual column headings, I need to add a suppressed header row (with widths and correct qty of header labels) in the first row, is that correct?

If I dont include the header labels/types in the suppressed row, the width is not applied, and if I dont include the suppressed row at all the width is not applied.

e.g. Header Row (suppressed) Content line 1 Content line 2 Header Row (same as #1 but not suppressed)

p.s. loving the speed of production - lightspeed compared to PHPSpreadsheet!

[sayid]

您好，我已经收到您的信件，将尽快回复您。