Writing excel with title/caption in first two rows then header and then rows

[kcmakwana]

If the excel has some title/caption in first 2 rows, then column title(header) and then rows. I am unable to write captions in starting of excel Header can be used only once? i mean header is for column title then how to write caption, above header.

[marceloguiot]

@kcmakwana Could you show you code? I have a report that shows the company info and use the first two or three lines depending of the data, i think this can solve your problem. Sorry for my english is not my mother language.

[kcmakwana]

<?php
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");
$writer = new XLSXWriter();
$writer->setTitle('MY title');
$writer->setSubject('data sheet');
$writer->setAuthor('something@gmail.com');
$writer->setCompany('Some company name');
$writer->setDescription('This is a sample code by kcmakwana');
$writer->setTempDir(sys_get_temp_dir());//set custom tempdir

# input/output file names
$csvfile = 'sample.csv';
$sheet1='TestSheet';
$xlsxfilename='sample.xlsx';

# title of the exel sheet, should appear in first 3 rows
$caption_rows = array(
        array("This is title-My Company"),
        array("my office code"),
        array("My account code")
);

# Writing titles in first 3 rows
foreach($caption_rows as $caption_row)
       $writer->writeSheetRow($sheet1, $caption_row);

$header = array('Trans no'=>'string','Trans Date'=>'string','Debit'=>'#,##,###.00','Credit'=>'#,##,###.00','Name'=>'string');
# Writing column headings
$writer->writeSheetHeader($sheet1,$header, $header_options = ['widths'=>[12,12,12,12,30] ] );

# Reading data from sample.csv and writing rows of xlsx
if (($handle = fopen($csvfile , "r")) !== FALSE) {
     while (( $data = fgetcsv($handle, 5000, "\t")) !== FALSE) {
             $row_num++;
             # Converting below two columns as floating numbers for debit and credit columns
            $data[6] = (float)($data[6]);
            $data[7] = (float)$data[7];
            $writer->writeSheetRow($sheet1, $data,$col_options = ['widths'=>[12,12,12,12,30]]);
#       var_dump($data);
        }  // end of while for csv reading and writing rows to xlsx
        $writer->writeToFile($xlsxfilename);
       if (file_exists($xlsxfilename)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="'.basename($xlsxfilename).'"');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($xlsxfilename));

            readfile($xlsxfilename);
            exit;
        }
        else { echo "File does not exists: $xlsxfilename";}

}else {print "input file not found:$csvfile";}

?>

sample.csv (TAB delimited) input file as under:-

40526 03/02/2023 749737.00 0.00 Software maintenance 40527 03/02/2023 61399.00 0.00 Miscellaneous expences 40698 04/02/2023 1657996.00 0.00 Management expences 40706 05/02/2023 100963.00 346.00 Taxes 40708 06/02/2023 49529.00 0.00 Hardware purchase 40876 07/02/2023 18121.00 330.00 Interest on invenstment ~

@kcmakwana Could you show you code? I have a report that shows the company info and use the first two or three lines depending of the data, i think this can solve your problem. Sorry for my english is not my mother language.

[mk-j]

fixed in 07cc892d429e6757b97346e39e108bab2b87ffea on master