Is there any way to read and modify an excel file using laravel excel?

Question

I am trying to import an excel file with 150+ sheets inside it, then by checking the excel sheet name, I want to replace the data inside or add new rows.

Initially I thought of using view to manually make all the design myself and just export them but it would be time consuming.

This is what I currently have.


<?php

namespace App\Http\Controllers;

use App\Imports\FirstSheetImport;
use App\Imports\WorkingPaperImport;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class WorkingPaperExportController extends Controller
{
    public function getExcelAndRewrite(Request $request)
    {
        $import = new FirstSheetImport('EA1');

        $modifiedData = $import->getModifiedData();

        return $modifiedData;
    }
}

<?php

namespace App\Imports;

use App\Exports\TestExportEA1;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Events\AfterImport;
use PhpOffice\PhpSpreadsheet\IOFactory;

class FirstSheetImport implements ToCollection, WithHeadingRow
{
    protected $sheetName;
    protected $sheetIndex;
    protected $modifiedData = [];

    public function __construct($sheetName)
    {
        $this->sheetName = $sheetName;
    }

    public function collection(Collection $collection)
    {
        //
    }

    protected function getFilePath()
    {
        // Provide the path to your Excel file
        return storage_path('/import/uptodateworkingpaper.xlsx');
    }

    protected function hasHeadingRow()
    {
        // Return true or false based on your needs
        return true;
    }

    public function getModifiedData()
    {
        $spreadsheet = IOFactory::load($this->getFilePath());
        $sheetNames = $spreadsheet->getSheetNames();

        $sheetIndex = array_search($this->sheetName, $sheetNames);

        $this->sheetIndex = $sheetIndex;
        if ($sheetIndex !== false) {
            $worksheet = $spreadsheet->getSheet($sheetIndex);
            $rows = $worksheet->toArray();

            return $rows;
        }
        // return $this->sheetIndex;
    }
}

With the functions I have now., I am able to get the correct worksheet data but I am not sure where to go from there. Not even sure if this is a good way.

Any help or suggestions is appreciated.

PHP 7.3.33 Laravel Framework 8.75.0

You could use a package like DOMPdf or just use PHP's fgetcsv() and fputcsv() methods — geertjanknapen
– geertjanknapen, Commented Dec 22, 2023 at 10:13

Peram · Accepted Answer · 2023-12-28 07:49:32Z

If anyone else needs help, this is working for me.

<?php

namespace App\Http\Controllers;

use App\Imports\FirstSheetImport;
use App\Imports\WorkingPaperImport;
use App\Models\Project;
use App\Models\WorkSheet;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as WriterXlsx;

class WorkingPaperExportController extends Controller
{
    public function projectWorkingPaper(Request $request)
    {
        $data = $request->only('project_id');

        $rules = [
            'project_id' => 'required|exists:project_has_schedules,project_id',
        ];

        $validator = Validator::make($data, $rules);

        if ($validator->fails()) {
            return response()->json(['message' => $validator->errors()], 400);
        }

        $project = Project::with('masterSchedules')->find($data['project_id']);

        $activeSchedules = [];

        foreach ($project['masterSchedules'] as $schedule) {
            $activeSchedules[] = $schedule['id'];
        }

        $workSheetSymbols = [];

        foreach ($activeSchedules as $schedule_id) {
            $groupedSchedules = WorkSheet::where('schedule_id', $schedule_id)->get();

            foreach ($groupedSchedules as $individialSchedule) {
                $workSheetSymbols[] = $individialSchedule['symbol'];
            }
        }

        if ($workSheetSymbols) {

            // read the excel file here
            $templatePath = storage_path('app/template/workingpaper.xlsx');

            $reader = new ReaderXlsx();

            $spreadsheet = $reader->load($templatePath);

            $sheetNames = $spreadsheet->getSheetNames();

            $sheetIndex = array_search($workSheetSymbols[0], $sheetNames);                                                                                                                                                                                                                                           

            if ($sheetIndex !== false) {
                // get the desired worksheet
                $worksheet = $spreadsheet->getSheetByName($workSheetSymbols[0]);
                // add/change values accordingly
                $worksheet->setCellValue('Q10', '500250');

                $writer = new WriterXlsx($spreadsheet);

                $newFileName = 'hello.xlsx';
                $savePath = storage_path("app/workingpaper/{$newFileName}");
                $writer->save($savePath);

                return response()->download($savePath);
            }
        } else {
            return response()->json(['message' => 'No schedules found!'], 500);
        }
    }
}

Collectives™ on Stack Overflow

Is there any way to read and modify an excel file using laravel excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related