Search code examples
phplaravelphpspreadsheetlaravel-excel

Copying excel column and inserting into new array

Problem:

What I am trying to achieve is to copy a selected range of cells from one excel spreadsheet and insert it into a newly generated one using laravel-excel and phpspreadsheet libraries. So far, the code that I've got, sort of does that but not idealy.

Excel::load($file, function($reader)
{
    $activeSheet = $reader->getActiveSheet();
    $this->data = $activeSheet->rangeToArray(
        'A1:A27',  // The worksheet range that we want to retrieve
        NULL,      // Value that should be returned for empty cells
        true,      // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        true,      // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        true       // Should the array be indexed by cell row and cell column
    );
});

// Create new file.
$newExport = Excel::create('Filename', function($excel) {
    $excel->sheet('Sheetname', function($sheet) {
        $sheet->fromArray($this->data, null, 'B1', true);
    });
});

// Export newly created file.
$newExport->export('xlsx');

The problem is that it also inserts column name into a first cell (0 on screenshot, as I had indexing turned off, with indexing on, it would insert A) , as you can see on a screenshot bellow.

Actual result: Actual result

Expected result: enter image description here

Stuff I have tried:

  • Tried to turn off indexing inside rangeToArray method.
  • Add column name into ignore list (2nd parameted in fromArray()), but that's not pragmatic, as I would end up adding every single column name into ignore list, moreover, it inserts blank value into first column and starts from B2 cell.

If anyone could give me some ideas how to resolve this case, that would be great.

Thanks!

Solution

  • The answer to the case is following...

    Read the documentation for the library that you're actually using, rather than the one it's based on.

    The reason why it was adding column headings, was due to laravel-excel library fromArray method default property values, which has heading generation enabled by default.

    $sheet->fromArray($this->data, null, 'B1', true);

    change to

    $sheet->fromArray($this->data, null, 'B1', true, false);

    Accepted parameters:

    fromArray($source, $nullValue, $startCell, $strictNullComparison, $headingGeneration).