How do I append to separate excel file

public function UploadExcelData(Request $request)
{
\Log::info('Uploading file:', ['file' => $request->file('file')]);

//Disabled for now, this cant detect xlsx file correctly, causing it to always fail uploading

/* $request->validate([
'file' => 'required|mimes:xlsx,xls',
]);

*/
$filePath = 'excel/UnitTracker.xlsx';

if (!Storage::disk('public')->exists($filePath)) {
return response()->json(['message' => 'UnitTracker.xlsx file not found.'], 404);
}

$path = Storage::disk('public')->path($filePath);
$existingData = Excel::toArray([], $path);


$uploadedFile = $request->file('file');
$uploadedData = Excel::toArray([], $uploadedFile);


if (empty($uploadedData) || empty($uploadedData[0])) {
return response()->json(['message' => 'Uploaded Excel file contains no data.'], 400);
}

/*
Appended Column
0 = Machine Type (A)
1 = Manufacturer (B)
2 = Model (C)
3 = Type (D)
4 = Serial Number (E)
27 = Customer Name (AB)
67 = Working Hour (BP)
68 = Actual Working Hour (BQ)
120 = Period From (DQ)
121 = Period To (DR)
5 = Customer Machine No (F)
61 = SMR[H] (BJ)
94 = Fuel Consumption [L/H] (CQ)
96 = Idling Hour Ratio[%] (CS)
72 = E Mode In Actual Working Hour (CA)
*/
$columnsToAppend = [0, 1, 2, 3, 4, 67, 68, 120, 121, 27, 5, 61, 94, 96, 72];
$sheets = [];

foreach ($uploadedData as $sheetIndex => $sheet) {
foreach ($sheet as $rowIndex => $row) {
$currentSheetIndex = $rowIndex;
$newRowData = [];
foreach ($columnsToAppend as $colIndex) {
$value = isset($row[$colIndex]) ? $row[$colIndex] : null;

// Convert Working Hour (67) and Actual Working Hour (68) to dates
if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value)) {
$newRowData[] = \Carbon\Carbon::createFromFormat('m/d/Y', $value)->toDateString();

} else {
$newRowData[] = $value;
}
}

if (isset($existingData[$currentSheetIndex])) {
$sheetData = $existingData[$currentSheetIndex];
} else {
$sheetData = [];
}

$sheets[$currentSheetIndex] = new DataExport($sheetData, [$newRowData]);
}
}

$multiSheetExport = new RowHandlerExport($sheets);


Excel::store(new RowHandlerExport($sheets), $filePath, 'public');

$originalName = $uploadedFile->getClientOriginalName();
\Log::info('Data appended from file: ' . $originalName);

return response()->json(['message' => 'Data appended successfully.'], 200);
}


So I was trying to append the excel data basically like this File 1 row 1 is appended to sheet 1 row 1. File 2 row 1 is appended to sheet 1 row 2. File 1 row 2 is appended to sheet 2 row 1. File 2 row 2 is appended to sheet 2 row 2.

it works here, but rn it's only for a single file, I need to be able to append this to a new excel file based on the customer name (row 27) in excel

How do I append it to different excel file, I'm kinda clueless on this

Continue reading...