[FIXED] How to choose PHPExcel graph type in detail?

Issue

Following the official example on generating line graph, I am able to create a line graph. However, when I use my own set of data, changing only the data source with all other parts exactly the same as the official example, somehow PHPExcel decide to choose "Stacked line with marker" instead of the "Line with marker".

This is the type of the graph generated. (Stacked line with marker)

This is the type I want, the the default type before I change the data.

How to set the line graph type in the code?

Solution

Line #93 within the example code you provided at https://github.com/PHPOffice/PHPExcel/blob/1.8/Examples/33chartcreate-line.php is where you change how the plots are grouped.

All you need to do is change it FROM THIS:

PHPExcel_Chart_DataSeries::GROUPING_STACKED

TO THIS:

PHPExcel_Chart_DataSeries::GROUPING_STANDARD

Here is a good resource to find out about the other options for plotting: http://synergica.co/tr/Documentation/API/PHPExcel_Chart/PHPExcel_Chart_DataSeries.html

Answered By - lthrhx

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] How to keep the cell references from another sheet updated when adding new rows with PHPExcel

Issue

After using insertNewRowBefore method from the PHPExcel 1.8.0 library, the cell references defined in the active sheet (i.e. sheet1) are correctly updated. However the cell references from the other sheets (e.g. sheet1!A10) remain unchanged.

Is there a way to update them as well?

Solution

No there isn't.

The logic that updates the cell references in the same worksheet is heavily intensive and slow, and we implemented a trade-off rather than execute it for all worksheets, adding a very big overhead. It can be executed manually using an adaptation of the logic from the PHPExcel_ReferenceHelper::insertNewBefore() and related methods

Answered By - Mark Baker

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How to get the font name of the excel cell

Issue

I am iterating all cells of the excel sheet and printing out the values. Same time i also want to print the font name they are using. Is there any function to get the specific cell font name in phpexcel ? Thanks. below is the code snippet

 include('lib/phpexcel/Classes/PHPExcel/IOFactory.php');

  //Use whatever path to an Excel file you need.
  $inputFileName = 'text.xlsx';

  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
  } catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . 
        $e->getMessage());
  }

  $sheet = $objPHPExcel->getSheet(0);
  $highestRow = $sheet->getHighestRow();
  $highestColumn = $sheet->getHighestColumn();

  foreach ($sheet->getRowIterator() as $row) {
  echo '<tr>' . "\n";
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); 
  foreach ($cellIterator as $cell) {
// Is there any similar $cell->getFont() function ?? which will echo"time new roman" 
    echo '<td>' .$cell->getValue(). '</td>' . "\n";
  }

  echo '</tr>' . "\n";
}
  ?>

Solution

The font is an aspect of the style of a cell; so you need to get the style details for the cell, and read the font information from that:

$cell->getStyle()
    ->getFont()
    ->getName();

Note that you can also get font size, italic, bold, underlining, super/subscript, strikethru, and the font colour in a similar manner.... the font object holds more information than simply the font name.

Answered By - Mark Baker

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How to verify the password in PHPExcel generated ".xls" file?

Issue

I am generating excel files with extension .xls using PHPExcel library. The excel file is generating. I am using password protection for the document and made only some fields editable. I am doing an Export Import mechanism. It is perfectly working now and I need to add some modification.

My question is

Is it possible to verify the password I given to protect the document? So that I can check it at the time of import

For example

If I protect the document using

$sheet -> getProtection() -> setPassword('MyPassword');

Is there any function like below for checking the password?

$newsheet -> getProtection() -> verifyPassword('MyPassword');

Any help could be appreciated.

Solution

You should be able to use

$hash = $sheet->getProtection()->getPassword(); // returns a hash
$valid = ($hash === PHPExcel_Shared_PasswordHasher::hashPassword($password));

if($valid) {
    //
}

Answered By - Tom Regner

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How to write Concatenate url nd text in phpexcel

Issue

I am printing some url which is coming dynamic in phpexcel

$sheet->setCellValue('M'.($results+2),($result['headline']).$result['url']);

but the output is like this Govt to start Air India roadshows in Singapore this weekhttp://www.windowtonews.com/news.php?id=288115

How can i write so that link comes on text with hyperlink

Solution

You could do this in three steps:

• set cell value
• set datatype of this cell to string2
• set url link to this text value

$sheet->setCellValue('M'.($results+2),$result['headline']);
$sheet->getCell('M'.($results+2))->setDataType(PHPExcel_Cell_DataType::TYPE_STRING2);
$sheet->getCell('M'.($results+2))->getHyperlink()->setUrl(strip_tags($result['url']));

In one line it would looks like:

$sheet->setCellValueExplicit('M'.($results+2), $result['headline'], PHPExcel_Cell_DataType::TYPE_STRING2, TRUE)
      ->getHyperlink()
      ->setUrl(strip_tags($result['url']));

setCellValueExplicit() - similar to getCell(), if setted to true returns the cell instead of the sheet (similar to getActiveSheet()).

Answered By - Aksen P

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to write php excel code in phpexcel format and print dynamic label?

Issue

I have a dynamic cell coming from a database. How can I print it into Phpexcel format?

$out_table = '';
$k=0;
    mysql_data_seek($query_result1,0);
    while($row1=mysql_fetch_array($query_result1,MYSQL_ASSOC)){
            $out_table .= "<th valign=\"top\">".$labels[$k]." (value1)</th>";
            $out_table .= "<th valign=\"top\">".$labels[$k]." (value2)</th>";
            $k++;   
    }

    echo $out_table;
    //print_r($out_table);die;
}

Solution

I strongly suggest you to use PHPSPREEDSHEET the evolution of PHPEXCEL, here's an example:

require 'DB.php';
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as xlsx; // Instead PHPExcel_Writer_Excel2007
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing as drawing; // Instead PHPExcel_Worksheet_Drawing
use PhpOffice\PhpSpreadsheet\IOFactory as io_factory; // Instead PHPExcel_IOFactory


$query = 'SELECT * FROM ? WHERE ?';
$result = $mysqli->query($query);

$objPHPExcel    =   new Spreadsheet();


$objPHPExcel->setActiveSheetIndex(0);
//NAME WORKSHEET
$objPHPExcel->getActiveSheet()->setTitle("Test");


$objPHPExcel->getActiveSheet()
    ->getPageSetup()
    ->setOrientation(PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

$objPHPExcel->getActiveSheet()
    ->getPageSetup()
    ->setPaperSize(PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);

$objPHPExcel->getActiveSheet()->SetCellValue('A12', 'ROW1');
$objPHPExcel->getActiveSheet()->SetCellValue('B12', 'ROW2');

$rowCount   =   1;

}
while($row  =   $result->fetch_assoc()){


      //BORDER  
      $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->applyFromArray($styleArray);
      $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->applyFromArray($styleArray);


      //VALUE
      $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['row1']);
      $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['row2']);


    $rowCount++; //ADD COUNT 
}

$objWriter = io_factory::createWriter($objPHPExcel, 'Xlsx');

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=EXAMPLE.xlsx");
header("Content-Transfer-Encoding: binary ");

ob_end_clean();
ob_start();
$objWriter->save('php://output');
exit;   

Answered By - Simone Rossaini

Answer Checked By - Terry (PHPFixing Volunteer)

Read More

[FIXED] How do I use PHPExcel to read data from an Excel file?

Issue

I want to read data from an Excel file in PHP so that I can process the data and insert it into a DB.
Looking around SO, it looks like PHPExcel is the premier library for this task.

I went to the PHPExcel GitHub page (https://github.com/PHPOffice/PHPExcel), but I cannot figure out how to actually use the library. There are a ton of example files and none of the ones I looked at seem to match the simple use case I'm looking for.
Furthermore, I cannot even figure out which files from the GitHub page I even need to download and what the folder structure for the include file(s) needs to be.

As such, given the way the GitHub page linked above is structured now (for v1.8), what files do I need to download and what is a simple code example that allows me to provide a path to an Excel file and read the data from the file?

Solution

Mark Baker was extremely helpful in guiding me to the right answer. I don't use Composer with PHP (I should probably learn), but given that, in order to get this to work I went to the GitHub page for PHPExcel (https://github.com/PHPOffice/PHPExcel), clicked the green Clone and download button, and then the Download ZIP link.

After unzipping the file, I got a folder called PHPExcel-1.8. I moved that folder to the same folder as both the Excel file I wanted to read (in my code below test.xlsx) and the PHP file that has the code below.

The key to getting it to work was inputting the correct path to the IOFactory.php file. It may seem simple to some, but it was tripping me up.

Given the above and Mark Baker's comments, the following code worked perfectly for me (note the commented parts):

<?php

  //Had to change this path to point to IOFactory.php.
  //Do not change the contents of the PHPExcel-1.8 folder at all.
  include('PHPExcel-1.8/Classes/PHPExcel/IOFactory.php');

  //Use whatever path to an Excel file you need.
  $inputFileName = 'test.xlsx';

  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
  } catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . 
        $e->getMessage());
  }

  $sheet = $objPHPExcel->getSheet(0);
  $highestRow = $sheet->getHighestRow();
  $highestColumn = $sheet->getHighestColumn();

  for ($row = 1; $row <= $highestRow; $row++) { 
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
                                    null, true, false);

    //Prints out data in each row.
    //Replace this with whatever you want to do with the data.
    echo '<pre>';
      print_r($rowData);
    echo '</pre>';
  }

Answered By - HartleySan

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More