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[] = CarbonCarbon::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