FAQ Database Discussion Community


Wrong reference to cell in PHPExcel

phpexcel
I'm developing importer based on PHPExcel library. This piece of code saves the cells for later usage. foreach ($this->importModel->currentAttributeNames as $columnLabel => $attribute) { $cell = $this->_sheet->getCell($columnLabel . $row->getRowIndex()); $this->importModel->importModelAttributes[$attribute]->setDefaultValueCell($cell); } $this->_sheet contains current sheet which is valid PHPExcel_Worksheet object. Saved cells contain valid PHPExcel_Cell object. Later I try to...

PHPExcel setActiveSheetIndex not working properly

indexing,default,phpexcel,sheet,first-time
I'm using PHPExcel to make export xls with 2 worksheets. When I open the generated xls file , it open directly the second worksheet, even that I've declared on the code that generates the first worksheet : $objPHPExcel->setActiveSheetIndex(0) This happened after I've added the following lines in the code that...

Slice array then merg the contents to form single arrays in php

php,arrays,phpexcel
My array result returns Many different arrays, I want to slice the array based on a number x, then combine the results to give me one full outcome.. for example, say the array is of size 8 i want to slice it down to 2 size 4's then combine the...

Color issues using PHPExcel

php,phpexcel
I'm experiencing issues setting a background color in PHPExcel. I have done this many many times and am wondering if this is a bug in PHPExcel itself. The code I use is $objPHPExcel->getActiveSheet()->getStyle('D17')->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor()->setRGB('ff0000'); I am trying to set the following hex colour a87ac6 this is a shade of...

PHPExcel search/return all rows containing perticular value in cells

javascript,php,rows,phpexcel,cells
I tried to find a way to just get a particular rows using PHPExcel. After searching on the internet, I cannot figure it out on my own. What I want to do is to list all the rows that have a "I1/027" as a column content. For example: Hours |...

Object Class could not be converted to string error

php,phpexcel
im trying to import my database values in my index.xlsx worksheet so i have written an code that uses phpExcel to import it Index.php <?php require_once 'E:\Xampp\htdocs\Excel\Classes\phpExcel.php'; $con = mysql_connect('localhost','root',''); mysql_select_db('khader',$con); $sql = "SELECT * FROM person"; $result = mysql_query($sql); $filename = "index.xlsx"; $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader ->setReadDataOnly(true); $objReader ->$objReader->load($filename);...

How to generate a big Excel file with php?

php,phpexcel
Let me explain my problem, I have to generate automatically Excel file, the Excel file contains 15.000 to 50.000 rows and 75 columns. It is obtained using a join and formulas in Excel (68 Excel formulas, there are IF, IFERROR, COUNTIF ...). So I opted for the library PHPExcel, it...

PHPExcel for loop timeout

php,yii,phpexcel
I have this code where I want to set from 3 to 333 to have a certain row height at every 30 interval, meaning at row 3, 33, 63, 93 up till 333: for ($i=3; $i<340; $i+30){ $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(57); } But when I click on controller actionGenerate(), it doesn't generate and...

Error while generating an Excel file

php,excel,phpexcel
I use the library PHPExcel for generating ".xlsx" files. The file is generated, but when I try to open it I get an error: Excel found some unreadable content. Do you want to fix the content? When I click on "yes" it opens my file with the content I want....

PHPExcel : Can we make use of it in our comercial web server [closed]

licensing,phpexcel
Can I make use of PHPExcel software for my commercial web site - https://phpexcel.codeplex.com/license I am not sure of all terms defined in the URL...

retrieving data from excel and showing in the page

javascript,php,jquery,ajax,phpexcel
Hi guys I have a excel sheet that I want to pull some info and publish it... it looks like so far I have done putting them into option tab the company names like drop down menu. I want to do the selected ones information like the one in the...

PHPExcel download file

php,angularjs,phpexcel,export-to-excel
I wanted to download an excel file generated by using PHPExcel. I followed the code from PHPExcel Force Download Issue and to no avail. After I log the data receive, my console only shows full of these symbols �.However, when I change the $objWriter->save('php://output'); to $objWriter->save('filename.xlsx'); then it just download...

PHPExcel: Set row height in pixels according to image

php,phpexcel
is there a way to set the row height according to the height of an image? Example: I put an image (320x480 pixel) on D12 and now i want the row #12 height to fit the image (480 pixel). Thanks!...

Using phpexcel to export MySql to excel

php,mysql,excel,phpexcel
Hi everyone I have created a program to export MySQL data to excel but at some point I am getting the error the time I am trying to open the excel file. The error: The file you are trying to open is in a different format,than specified by the file...

PHPExcel to bold text in between HTML markups and

javascript,php,jquery,html5,phpexcel
HI folks ive been trying to figure out a problem that's been haunting me for some time now and original started this thread thread PHPExcel - How to replace text using preg_replace. I was going about this all the wrong way and Mark gave me some tips on where to...

PHPExcel Get cells ignoring related sheets

php,phpexcel,import-from-excel
I have a .xlsx file with two sheets and I want to extract to PHP the second sheet, just like it is. The problem is that a column from this sheet is related to the first one, so the results I get when I print the extracted values, are some...

Get value from a certain row in a column

php,excel,phpexcel
I currently have the below code that will loop through the excel file and return rows with missing data. the output is as below: - Row number: 37 - Missing : in cell B37 - Missing : in cell D37 What I would like to return is the following -...

PHPExcel : graph working on LibreOffice but not on Excel

php,excel,phpexcel
I'm currently using PHPExcel (v1.8.0) to generate an xlxs file with datas and graph. It works well when I open the file on LibreOffice, but in Excel it throws and error : "Excel found unreadable content in .." When I "repair" the file in Excel, I have my datas but...

changing alignment of Y-Axis in PHPEXCEL

php,charts,phpexcel
Following is my code to generate the Chart using PHPEXCEL: $categories = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C'.$old_constant.':$C'.$new_constant); $values = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E'.$old_constant.':$E'.$new_constant); $series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping array(0), // plotOrder array(), // plotLabel array($categories), // plotCategory array($values) //...

PHPExcel calculate formula cell and format currency

php,phpexcel
Now I'm reading an excel file and showing it on a html table. like this: <?php function load_table(){ require_once('Classes/PHPExcel.php'); $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load("SampleData.xlsx"); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); // e.g. 10 $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex =...

What causes PHPExcel to use so much memory when reading a file while using a chunked filter?

php,memory,phpexcel,centos6.5
Like many others, I have been struggling with PHPExcel memory usage when reading a file (to convert it to MySQL). Of course I have tried the usual things mentioned in various places and have been able to increase memory efficiency by at least 40%. This includes using a custom chunked...

PHPExcel Clear Sheet

php,excel,phpexcel,sheet
How do I clear the sheet with PHPExcel? Something like Sheet.ClearContents in VBA. All I could find was delete rows and columns and it's not working for me. Thanks...

Problems with column array (PHPExcel)

php,arrays,phpexcel
Sorry, I searched but didn't find any solution to my problem. I created an array that I want to export with PHPExcel, in one column (tableauBK): if ($tableauBK[$k]<>"" && $tableauQ[$k]<>"") { $tableauBL[$k]=$tableauBK[$k]*$tableauQ[$k]; } I can read the array without problems: foreach ($tableauBL as $subtab) { echo $subtab; } But I...

PHPExcel cannot load spreadsheet with formula

php,composer-php,phpexcel,import-from-excel
When trying to create a reader for a file, I get some strange errors. I was using the dev-master branch, then switched to 1.8.1 but that didn't seem to make any difference therefore I'm thinking it's the spreadsheet that's the problem. There are quite a lot of formulas used in...

PHPExcel based function RATE() returning incorrect value

php,excel,phpexcel,financial
The newest version, found here: https://phpexcel.codeplex.com/SourceControl/latest#trunk/Classes/PHPExcel/Calculation/Financial.php of the RATE() function is not working for me. I created a new PHP page, cut out that specific function and tested it but with a big difference in output to Excel. <?php define('FINANCIAL_MAX_ITERATIONS', 128); define('FINANCIAL_PRECISION', 1.0e-08); function RATE($nper, $pmt, $pv, $fv = 0.0,...

PHPExcel write rows as is , without any calculation / styling

php,phpexcel
Is there a way to tell PHPExcel to just write rows supplied from an array, without doing any calculation / apply styling / any other thing it does while writing OR when using fromArray ? Need this for performance. $inputFileName = 'client_files/sample.xlsx'; $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); $objPHPExcel->getSheet(0)->setCellValue('D2', '@' . $user ....

How to check if a cell has a bottom border with Phpexcel

php,excel,if-statement,phpexcel
How to check if a cell has a bottom border using PHPexcel? I am working on a very funky template the sales force of my company have assembled. The lines could go on and on inside the "Why?" block so that is why I need to check for that last...

Information coming up as duplicate Columns phpexcel, mySql

php,mysql,phpexcel
I am retrieving information from the database and exporting it to excel using PHPExcel. The retrieving of the information seems to work fine but Columns duplicate in the excel sheet. I use the following method to retrieve information: public function monthlyKm($startDate, $endDate, $userid) { $query = "SELECT a.`travelDate`,MIN(a.`openning`) AS minimum,...

How remove BOM mark from download file?

php,phpexcel,byte-order-mark
I have this script to let the user download file: header('Content-Encoding: UTF-8'); header("Content-Type: application/vnd.ms-excel; charset=UTF-8"); header("Content-Disposition: attachment; filename=qa_report.xlsx"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); //echo excel file....; exit; the file always have a BOM marker, how cant i remove the BOM marker?...

Where to put the PHPExcel Library in CakePHP 3.0

phpexcel,cakephp-3.0
I would like to know in which folder should I put/install the PHPExcel Library in my CakePHP 3.0 app. Thanks !...

passing one variable from one page to another for pdf generation

php,pdf,phpexcel,tcpdf
I have a page that I have used to retrieve some excel data written in php... by using phpexcel... this part provides me company information echo '<form action="final.php" method="post">'; echo "<table border='1'>"; for ($rowcount = $rowCompanyInfoStart; $rowcount <= $rowCompanyInfoEnd; $rowcount++) { //$data = $objWorksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']); $rangeCoordinates =...

Can PHPExcel add a trend line on a line chart?

php,charts,phpexcel
At the moment of this writing the current version of PHPExcel is version 1.8.0. I know how to make a chart and embed it in a worksheet, I basically followed the provided example and modified it to create a line chart with multiple lines / data series based on a...

Chrome is exporting wrong “file extension” instead of xls

php,mysql,google-chrome,phpexcel
i am new to phpexcel, so I use the code attached to export data from database to xls file. It works fine in any other browser, except Chrome, where the file downloaded have no "extension" instead xls. It is something wrong with my code or it's a browser setting that...

Removing sheet from Excel 2005 using PHP

php,phpexcel
I want to delete sheets from an Excel2005/Excel5 file using PHP. I am using PHPExcel-1.7.9. I am using the following code to delete Excel2007 files as follows $exceltype="Excel2007"; $excel = PHPExcel_IOFactory::createReader($exceltype); $excel = $excel->load("ABC.xlsx"); $count = $excel->getSheetCount(); for($i = 0; $i < $count; $i++) { $excel->removeSheetByIndex(0); } When I use...

PHPExcel unexpected float behavior

php,phpexcel
I'm create a new PHPExcel object into one of my projects and whats odd is how some float functions behave after the library is included. Take for example the round function, it will not round after PHPExcel is included. echo round(90.00 + 9.71, 2); // outputs 99.71 $sheet = new...

Store PHPExcel files in Amazon S3

php,amazon-web-services,amazon-s3,phpexcel
I have an application that creates a lot of Excel files, which I would like to store in Amazon S3 for later use. $objPHPExcel = PHPExcel_IOFactory::load('template.xlsx'); $objPHPExcel->getActiveSheet()->getCell('A1')->setValue('SomeVal'); // ... $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('filename.xlsx'); This is the code I usually use to save the files, but I dont't want to...

phpExcel : Failed to load resource: net::ERR_CONNECTION_RESET

php,phpexcel
I actualy use phpExcel to get an excel file that i recover from the user with an <input type='file'> and after i convert this excel file in csv file. My script work and the csv file is generate. The problem is after my script finish the page never stop to...

Write text between merged cells using phpExcel

phpexcel
I have that code from phpExel. $objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:I1'); This code make some blank space between column A1 to I1. Just want to add text in the center blank space(A1:I1). I am trying this code: $objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:I1','add some text here...'); but this code does not work. Can anyone help me please?

Zend_mail send duplicate attachment

phpexcel,zend-mail
I am using phpexcel library with zend framwork. I want to send mail to user with excel file attachment, all are working good but mail send with two attachment one is duplicate, i dont know why. here is my function which is used to export excel and send it to...

Row 2 is out of range (2 - 1) laravel-excel

php,laravel-5,phpexcel,phpexcelreader,laravel-excel
I am trying to import an excel file using laravel-excel so I used this algorithm $result = Excel::load('public/uploads/test.xlsx',function($reader){ })->get(); but I get the following error : PHPExcel_Exception in RowIterator.php line 125: Row 2 is out of range (2 - 1)...

PHPExcel file “corrupt” when Unicode in data

excel,unicode,phpexcel
I've been using PHPExcel successfully for months now. Now I have users that are entering Unicode into their data, and now their reports are not opening in Excel. The exact error that Excel 2010 gives is: Excel found unreadable content in 'blahblahblah.xlsx'. Do you want to recover the contents of...

phpExcel - Excel found unreadable content when use formula?

formula,phpexcel
I wrote this code but when I open excel file I had error "Excel found unreadable content". Here is my code $i=8 $objPHPExcel->getActiveSheet()->setCellValue('F'.$i,'=D.$i*E.$i'); $i++; Where is my mistake? Thank you!...

PHPExcel error: Undefined index [duplicate]

php,mysql,phpexcel
This question already has an answer here: PHP: “Notice: Undefined variable” and “Notice: Undefined index” 11 answers Reference - What does this error mean in PHP? 28 answers I want to export mysql table called "lokasi" in to excel but these are the errors I am getting: Notice: Undefined...

Phpexcel how to delete after upload?

php,html,database,phpexcel
I've began using phpexcel and have managed to upload an excel file, read it, and insert it into my database but I've noticed that the file I've read was still present. I was under the impression that the file was only temporary. How would I go about deleting the uploaded...

PHP exporting data to CSV in multiple sheets

php,phpexcel,fputcsv
Each day I run a cron where it finds data that has not been pulled/exported yet (via a column "exported" that contains a boolean) and this works fine. However, now I'd like to do a loop for each of the results and stick them in to the exported CSV in...

PHPExcel Chart won't work when downloading directly

php,charts,phpexcel
Here is my code... its identical to the example code... except for the end, where I changed it to download directly from the browser instead of creating a file on the server. When I download the excel this is all I see (no chart): <?php /** Error reporting */ error_reporting(E_ALL);...

PHPExcel moving from coordinated cell

php,phpexcel
is there a way, how I can move from coordinated cell in excel to next, e.g. on the right side? Example: I have located value, that I'm looking for in excel with PHP , let's say it's has coordinates A3 (I have value, that contains it). How can I move...

Can PHPExcel add a sheet chart (as opposed to embedded chart)?

php,charts,phpexcel
At the moment of this writing the current version of PHPExcel is version 1.8.0. I know how to make a chart and embed it in a worksheet, I basically followed the provided example. Excel allows to either embed a chart into a worksheet or otherwise make a chart sheet. A...

PHPExcel takes unreasonably long to create spreadsheet

php,phpexcel
I am using PHPExcel to generate some pretty hefty spreadsheets on the fly for our users. This seems to work fine until we start to get up to medium sized spreadsheets. I have user that is trying to export a spreadsheet that is about 6000 rows with 11 columns and...

Read only one row of a big Excel file with PHPExcel

php,excel,performance,phpexcel
I'm using PHPExcel library to read data from Excel files. My file is about 5mb, 70 columns and 20000 rows big. The code for loading the file is: $sheetnames = array('Classification'); $excelFile = Yii::app()->basePath . '/categories/'. $region .'.xlsx'; $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); $objReader->setLoadSheetsOnly($sheetnames); $objPHPExcel = $objReader->load($excelFile); The Excel file has...

'The filename 062014.xlsx is not recognised as an OLE file'

php,html,excel,phpexcel
I am working on a complex program that deals with Excel, so I am using PHPExcel to search and edit the Excel file from a browser. My problem comes in the editing portion of the program, so I wrote a basic program to edit the existing Excel page. It seems...

When reading an XLS file using PHP Excel, how can I detect if a cell is bold?

php,excel,formatting,phpexcel
I have an Excel 2007 xlsx document with "BC/B22--" in cell L2. It is in bold, and coloured red. Using PHPExcel (v1.8.0, 2014-03-02) I want to detect whether the cell is bold or not, but I can only get at the value "BC/B22--", and no styling info. This is my...

How to use print ready functionality in PHPExcel library

php,printing,phpexcel
I am using PHPExcel library for spreadsheet operations. I am to apply print ready functionality. Does this functionality exist?