<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter;
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;


require __DIR__ . '/../Header.php';

$inputFileName = __DIR__ . '/sampleData/test1.xlsx';
$secondFile = __DIR__ . '/sampleData/test2.xlsx';
$helper->log('Loading file ' . pathinfo($inputFileName, PATHINFO_BASENAME) . ' using IOFactory to identify the format');
$helper->log('Loading file ' . pathinfo($secondFile, PATHINFO_BASENAME) . ' using IOFactory to identify the format');
$spreadsheet = IOFactory::load($inputFileName);
$spreadsheet2 = IOFactory::load($secondFile);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
$sheetData2 = $spreadsheet2->getActiveSheet()->toArray(null, true, true, true);
$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()->setTitle('test3');
foreach ($sheetData as $key => $sheet) {
	$spreadsheet->getActiveSheet()->setCellValue('A'.$key, $sheet['A']);
	$spreadsheet->getActiveSheet()->setCellValue('B'.$key, $sheet['B']);
	$spreadsheet->getActiveSheet()->setCellValue('C'.$key, $sheet['C']);
	$spreadsheet->getActiveSheet()->setCellValue('D'.$key, $sheet['D']);
	$spreadsheet->getActiveSheet()->setCellValue('E'.$key, $sheet['E']);
	$spreadsheet->getActiveSheet()->setCellValue('F'.$key, $sheet['F']);
	$spreadsheet->getActiveSheet()->setCellValue('G'.$key, $sheet['G']);
	$spreadsheet->getActiveSheet()->setCellValue('H'.$key, $sheet['H']);
	$spreadsheet->getActiveSheet()->setCellValue('I'.$key, $sheet['I']);
	$spreadsheet->getActiveSheet()->setCellValue('K'.$key, $sheet['K']);
	foreach ($sheetData2 as $sheet2) {
		if($key > 1){
			if ((int)$sheet2['D'] == (int)$sheet['B']) {
				$spreadsheet->getActiveSheet()->setCellValue('J'.$key, $sheet2['C']);
			}
		}
	}
}
$helper->write($spreadsheet, 'test3.xlsx');