phpexcel to html only a portion

Question

I'm working on phpexcel. The File I'm working on is from tally export file. I have to read this file and save the data to each user .

For example from this file I need only A2:G10 as HTML/TABLE. So i can display to the particular member (Adv. Chandra Mogan) individually.

I NEED A TABLE FOR ONLY A PORTION OF THE SHEET

What I have done so far:

protected function doExcelUpdate() {
    $inputFileName = $this->getParameter('temp_directory') . '/file.xls';
    if (!file_exists($inputFileName)) {
        $this->addFlash('sonata_flash_error', 'File: not found in temp directory');
        return;
    }

    $this->addFlash('sonata_flash_info', 'File: exist');
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch (Exception $e) {
        $this->addFlash('sonata_flash_error', 'Error in PHPExcel');
        return;
    }

    $sheet = $objPHPExcel->getSheet(0);
    if (!$sheet) {
        $this->addFlash('sonata_flash_error', 'Error in reading sheet');
        return;
    }

    $objPHPExcel->getSheet(0)
        ->getStyle('A1:G10')
        ->getProtection()
        ->setLocked(
            PHPExcel_Style_Protection::PROTECTION_PROTECTED
        );

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
    $objWriter->setSheetIndex(0);
    $objWriter->save($this->getParameter('temp_directory') . '/output.html');        
}

A1:G10 is not locked. Entire sheet is printed.

Answer 1

First point: "Locking" doesn't change the sheet size, or set a "view window"; it protects parts of the sheet from being edited.

Second point: "Locking" is a feature of Excel, and supported for excel writers, but not for HTML.

Third point: there is no direct mechanism to write only part of a worksheet.

---

As a suggestion, you might create a new blank worksheet, and then copy the data/style information from the cell range that you want in your your main worksheet to that new worksheet starting from cell A1; then send that worksheet to the HTML Writer.

Answer 2

You can't achive this using locked or hidden cells when you use HTML writer. You can use a workaround creating a new worksheet and after adding the portion you want to display.

For mantain style on new worksheet (like font, color, border) you must extract it for each cell from the orginal worksheet and apply to the copied cells. The same thing is for merged cells in the old worksheet. Your function doExcelUpdate() should be like this:

protected function doExcelUpdate()
{
    $inputFileName = $this->getParameter('temp_directory').'/file.xls';
    if (!file_exists($inputFileName)) {
        $this->addFlash('sonata_flash_error', 'File: not found in temp directory');

        return;
    }

    $this->addFlash('sonata_flash_info', 'File: exist');
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $originalPHPExcel = $objReader->load($inputFileName);
    } catch (Exception $e) {
        $this->addFlash('sonata_flash_error', 'Error in PHPExcel');

        return;
    }

    $originalSheet = $originalPHPExcel->getSheet(0);
    if (!$sheet) {
        $this->addFlash('sonata_flash_error', 'Error in reading sheet');

        return;
    }

    // Get the data of portion you want to output
    $data = $originalSheet->rangeToArray('A1:G11');

    $newPHPExcel = new PHPExcel;
    $newPHPExcel->setActiveSheetIndex(0);
    $newSheet = $newPHPExcel->getActiveSheet();

    $newSheet->fromArray($data);

    // Duplicate style for each cell from original sheet to the new sheet
    for ($i = 1; $i < 11; $i++) {
        for ($j = 0; $j <= 6; $j++) {
            $style = $originalSheet->getStyleByColumnAndRow($j, $i);
            $newSheet->duplicateStyle($style, PHPExcel_Cell::stringFromColumnIndex($j).(string)$i);
        }
    }

    // Merge the same cells that are merged in the original sheet
    foreach ($originalSheet->getMergeCells() as $cells) {
        $inRange = false;
        foreach (explode(':', $cells) as $cell) {
            $inRange = $originalSheet->getCell($cell)->isInRange('A1:G11');
        }

        // Merge only if in range of the portion of file you want to output
        if ($inRange) {
            $newSheet->mergeCells($cells);
        }
    }

    $objWriter = PHPExcel_IOFactory::createWriter($newPHPExcel, 'HTML');
    $objWriter->save($this->getParameter('temp_directory').'/output.html');
}

Answer 3

UP TO NOW FOR THE WORK TO BE COMPLETED, I HAVE DONE THIS,

private function doExcelUpdate() {
    $inputFileName = $this->getParameter('temp_directory') . '/file.xls';
    $synopsis = PHPExcel_IOFactory::load($inputFileName)->getSheet(0);
    $column = $synopsis->getHighestColumn();
    $row = $synopsis->getHighestRow();
    $this->cleanUserPayment();
    $this->doExcelUpdateTable($synopsis, $column, $row);
    $this->deleteExcelFile();
}

private function cleanUserPayment() {
    $em = $this->getDoctrine()->getManager();
    $classMetaData = $em->getClassMetadata('AppBundle\Entity\UserPayment');
    $connection = $em->getConnection();
    $dbPlatform = $connection->getDatabasePlatform();
    $connection->beginTransaction();
    try {
        $connection->query('SET FOREIGN_KEY_CHECKS=0');
        $q = $dbPlatform->getTruncateTableSql($classMetaData->getTableName());
        $connection->executeUpdate($q);
        $connection->query('SET FOREIGN_KEY_CHECKS=1');
        $connection->commit();
    } catch (\Exception $e) {
        $connection->rollback();
    }
}

private function doExcelUpdateTable($synopsis, $column, $row) {
    set_time_limit(300);
    $t = [];
    for ($r = 1; $r <= $row; $r++) {
        for ($c = "A"; $c <= $column; $c++) {
            $cell = $synopsis->getCell($c . $r)->getFormattedValue();
            if ($cell == 'Ledger:') {
                $t[] = $r;
            }
        }
    }
    $t[] = $row+1;
    $numItems = count($t);
    $i = 0;
    $em = $this->getDoctrine()->getManager();
    foreach ($t as $key => $value) {
        if (++$i != $numItems) {
            $up = new UserPayment();
            $up->setName($synopsis->getCell('B' . $value)->getFormattedValue());
            $up->setMessage($this->doExcelUpdateTableCreate($synopsis, $column, $value, $t[$key + 1]));
            $em->persist($up);
             // $this->addFlash('sonata_flash_error', 'Output: ' . $synopsis->getCell('B' . $value)->getFormattedValue() . $this->doExcelUpdateTableCreate($synopsis, $column, $value, $t[$key + 1]));
        }
    }
    $em->flush();
    $this->addFlash('sonata_flash_success', "Successfully updated user bills. Total data updated::" . count($t));
}

private function doExcelUpdateTableCreate($synopsis, $column, $rowS, $rowE) {
    $mr = NULL;
    $x = 0;
    $alphas = range('A', $column);
    $oneTable = '<table border="1">';
    for ($r = $rowS; $r < $rowE; $r++) {
        $oneTable .= "<tr>";
        for ($c = "A"; $c <= $column; $c++) {
            if ($x > 0) {
                $x--;
                continue;
            }
            $mr = NULL;
            $x = 0;
            $cell = $synopsis->getCell($c . $r);
            $cellVal = $cell->getFormattedValue();
            if ($cellVal == NULL) {
                $cellVal = "&nbsp;";
            }
            $cellRange = $cell->getMergeRange();
            if ($cellRange) {
                $mr = substr($cellRange, strpos($cellRange, ":") + 1, 1);
                $upto = array_search($mr, $alphas);
                $x = ($upto - array_search($c, $alphas));
                $oneTable .= "<td colspan=" . ($x + 1) . " style='text-align:right;'>" . $cellVal . "</td>";
            } else {
                $oneTable .= "<td>" . $cellVal . "</td>";
            }
        }
        $oneTable .= "</tr>";
    }
    $oneTable .= "</table>";
    return $oneTable;
}
private function deleteExcelFile() {
    $filesystem = new \Symfony\Component\Filesystem\Filesystem();
    $filesystem->remove($this->getParameter('temp_directory') . '/file.xls');
}