EzDevInfo.com

PHPExcel and Text Wrapping

First, I'm very new to PHPExcel. I know that this line of code will make the cell text-wrap:

$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);

'D1' being the chosen cell.

Instead of using this code for every cell I need wrapped, is there a way to make the entire Excel Worksheet automatically wrap everything?

Or is there a better practice technique to use for specified columns?

how to use phpexcel to read data and insert into database?

I have a php application where I want to read data from excel, Insert into database and then generate pdf reports for specific users. I searched over the internet a lot but nothing specific given about both things. if someone could provide a tutorial or something, that would be a real help.

Set Background cell color in PHPExcel

How to set specific color to active cell when creating XLS document in PHPExcel?

phpexcel to download

hello i am new to phpexcel, and i was wondering if there is some way send the excel i have created to the clients download without saving it on my server or to delete it right after he downloads it

i am trying to create an "export button" on a page that will give the user a "pop-up" with the excel that he wants that i have just created.

now after i create the table i do :

$objXLS->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
$objXLS->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);

$objXLS->getActiveSheet()->setTitle('Test Stats');

$objXLS->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objXLS, 'Excel5');
$objWriter->save(__DIR__."/test1.xls");

but that saves it to my server

thank you

PHPExcel Column Loop

How can I do a loop which based on Excel worksheet columns? I found (and used) WorksheetIterator, RowIterator and CellIterator but nothing about columns.

PHPExcel how to get column index from cell

PHPExcel $cell->getColumn() returns 'A', 'B', 'C', ...

which is the best way to get the integer (0, 1, 2, ...) from the cell.

This function doesn't exist.

$colIndex = $cell->getColumnIndex();

So what is the alternative withoput converting chr to ascii ?

how to make New lines in a cell using phpexcel

i have problem with php excel,

i want to make new line in one cell but i can't, i have tried using \n or <br /> but itsn't work. this my code:

$objPHPExcel->getActiveSheet()->setCellValue('H5', 'Hello\nWorld'); // i need this show in two line
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

fyi: my format excel is xls not xlsx. many thanks :)

Alternative for PHP_excel

Is there any alternative for PHP_excel which can "Export to XLSX/XLS" file in a customized format?

This is a General Reference question for the php tag

PHP excel - data looping?

I have an array of arrays of data.

so the basic format is

$sheet = array(
  array(
    'a1 data',
    'b1 data',
    'c1 data',
    'd1 data',
  ),
  array(
    'a2 data',
    'b2 data',
    'c2 data',
    'd2 data',
  ),
  array(
    'a3 data',
    'b3 data',
    'c3 data',
    'd3 data',
  )
);

When I am passed the array I have no idea how many columns or rows there will be. What I want to do is using php excel create an excel sheet out of the array.

from what I have seen, the only way to set data is to use $objPHPExcel->getActiveSheet()->setCellValue('A1', $value);

So my question is

How would you loop over the cells?

remembering that there could be say 30 columns and say 70 rows which would be AD70 So, how do you loop that?

Or is there a built in function to turn an array to a sheet?

How do I read sheet two of an xlsx file with PHPExcel?

I know how to read my xlsx spreadsheet and loop through the first sheet.

It has 5 sheets and I am having trouble getting to any other than the first.

Here is the code I am using which was straight from the documentation. You can see I tried to utilize setActiveSheet, but that threw the error Call to undefined method PHPExcel::setActiveSheet().

Code:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("cmt_school_data.xlsx");
//$objPHPExcel->setActiveSheet(1);
$objWorksheet = $objPHPExcel->getActiveSheet();

echo '<table border=1>' . "\n";

foreach ($objWorksheet->getRowIterator() as $row) {

  echo '<tr>' . "\n";

  $cellIterator = $row->getCellIterator();

  // This loops all cells, even if it is not set.
  // By default, only cells that are set will be iterated.
  $cellIterator->setIterateOnlyExistingCells(false);

  foreach ($cellIterator as $cell) {
    echo '<td>' . $cell->getValue() . '</td>' . "\n";
  }

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

}

echo '</table>' . "\n";

php_zip does not exist in php 5.3.5

I'm in the process of using PHPExcel. One of the requirements is that I need to enable php_zip.

(...) if you need PHPExcel to handle .xlsx or .ods files you will need the zip extension...

I'm using PHP v5.3.5. and in my php.ini file, I have the following lines:

;extension=php_xmlrpc.dll
;extension=php_xsl.dll
;extension=php_zip.dll

If I remove the ; in the last line, and restart Wampserver, I get the following error message:

PHP Startup: Unable to load dynamic library 'I:/wamp/.../ext/php_zip.dll' - The specified module could not be found.

Reading the web, many says that as of PHP 5.3.0, php_zip is built-in.

If it is built then why is this line still in the configuration?
Do I get the error message because since it's built in, the file has been removed?

PS. Sorry if this is in the wrong forum, but not sure where else to put it.

How to get the number of columns of worksheet as integer (28) instead of Excel-letters ("AB")?

Given:

$this->objPHPExcelReader = PHPExcel_IOFactory::createReaderForFile($this->config['file']);
$this->objPHPExcelReader->setLoadSheetsOnly(array($this->config['worksheet']));
$this->objPHPExcelReader->setReadDataOnly(true);
$this->objPHPExcel = $this->objPHPExcelReader->load($this->config['file']);

I can iterate through the rows like this but it is very slow, i.e. in a 3MB Excel file with a worksheet that has "EL" columns, it takes about 1 second per row:

foreach ($this->objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row)
{
    $dataset = array();
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach ($cellIterator as $cell)
    {
        if (!is_null($cell))
        {
            $dataset[] = $cell->getCalculatedValue();
        }
    }
    $this->datasets[] = $dataset;
}

When I iterate like this, it it significantly faster (approx. 2000 rows in 30 seconds), but I will have to convert the letters e.g. "EL" to a number:

$highestColumm = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); // e.g. "EL"
$highestRow = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

$number_of_columns = 150; // TODO: figure out how to get the number of cols as int
for ($row = 1; $row < $highestRow + 1; $row++) {
    $dataset = array();
    for ($column = 0; $column < $number_of_columns; $column++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
    }
    $this->datasets[] = $dataset;
}

Is there a way to get the highest column as an integer (e.g. "28") instead of in Excel-styled letters (e.g. "AB")?

PHPExcel runs out of 256, 512 and also 1024MB of RAM

I don't understand it. The XSLX table is about 3MB large yet even 1024MB of RAM is not enough for PHPExcel to load it into memory?

I might be doing something horribly wrong here:

function ReadXlsxTableIntoArray($theFilePath)
{
    require_once('PHPExcel/Classes/PHPExcel.php');
    $inputFileType = 'Excel2007';
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($theFilePath);
    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
    $arrayData = $arrayOriginalColumnNames = $arrayColumnNames = array();
    foreach($rowIterator as $row){
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
        if(1 == $row->getRowIndex ()) {
            foreach ($cellIterator as $cell) {
                $value = $cell->getCalculatedValue();
                $arrayOriginalColumnNames[] = $value;
                // let's remove the diacritique
                $value = iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $value);
                // and white spaces
                $valueExploded = explode(' ', $value);
                $value = '';
                // capitalize the first letter of each word
                foreach ($valueExploded as $word) {
                    $value .= ucfirst($word);
                }
                $arrayColumnNames[] = $value;
            }
            continue;
        } else {
            $rowIndex = $row->getRowIndex();
            reset($arrayColumnNames);
            foreach ($cellIterator as $cell) {
                $arrayData[$rowIndex][current($arrayColumnNames)] = $cell->getCalculatedValue();
                next($arrayColumnNames);
            }
        }
    }
    return array($arrayOriginalColumnNames, $arrayColumnNames, $arrayData);
}

The function above reads data from an excel table to an array.

Any suggestions?

At first, I allowed PHP to use 256MB of RAM. It was not enough. I then doubled the amount and then also tried 1024MB. It still runs out of memory with this error:

Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 50331648 bytes) in D:\data\o\WebLibThirdParty\src\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 688

Fatal error (shutdown): Allowed memory size of 1073741824 bytes exhausted (tried to allocate 50331648 bytes) in D:\data\o\WebLibThirdParty\src\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 688

How to open an Excel file with PHPExcel for both reading and writing?

I'm using the PHPExcel library, and I'm creating xls objects either for writing or for reading:

PHPExcel_IOFactory::createReaderForFile('file.xlsx')
PHPExcel_IOFactory::createWriter('Excel2007')

How can I open an XLSX file for reading and writing?

PHPExcel - How to make part of the text bold

How do you create a bold cell value using PHPExcel? I know I can use \n to add a carriage return within the text, but is there some kind of way to bold part of cell value? I also have tried using html formatting such as <b> or <strong> but it did not work.