Removerow not working when loading an Excel file

Topics: Developer Forum, User Forum
Jul 30, 2008 at 12:51 PM
Hi everyone! :-)

I need to load a Excel 2007 file, change it a bit, put some data in it and finally convert it to an Excel xls file.

I am currently trying to change this Excel 2007 file and especially remove some rows. I tried the 05featuredemo.php example it works fine. But when I am trying to do it in my file, it doesn't work at all and show this error ->

Fatal error: Uncaught exception 'Exception' with message 'Column string index can not be empty.' in C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\Cell.php:491 Stack trace: #0 C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\ReferenceHelper.php(451): PHPExcel_Cell::columnIndexFromString('') #1 C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\ReferenceHelper.php(422): PHPExcel_ReferenceHelper->_updateSingleCellReference('A', 'A28', 0, -4) #2 C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\ReferenceHelper.php(401): PHPExcel_ReferenceHelper->_updateCellRange('A:Q', 'A28', 0, -4) #3 C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\ReferenceHelper.php(259): PHPExcel_ReferenceHelper->updateCellReference('A:Q', 'A28', 0, -4) #4 C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\Worksheet.php(1417): PHPExcel_ReferenceHelper->insertNewBefore('A28', 0, -4, Object(PHPExcel_Worksheet)) #5 C:\xampp\htdocs\LMS1\strategic_database\pages\newexport2.php(118): PHPExcel_Worksheet->removeRow(2 in C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\Cell.php on line 491

I spend the all morning looking where is the error and I think I found where:

When I am using this command -> $objPHPExcel = new PHPExcel(); instead of loading the file like that -> $objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load("../files/testtemplate4.xlsx"); There is no error but of course my file is not loaded.

Can you give me a hand on this?
Thanks a lot!
Jul 31, 2008 at 9:18 AM
I tried to hide the rows, it is working. But I would like to delete them.
Can someone give me a hand to delete these rows without any problems... Can it be a bug of the PHPExcel classes?
Thanks a lot!
Aug 4, 2008 at 8:53 AM
Does someone has any ideas about my problem?
Thank you very much!
Aug 4, 2008 at 12:56 PM
I think you may get better response if you provide the whole script together with the Excel 2007 file you are reading. It may not be easy to tell what the problem is with only the error message. Unfortunately it is not possible to attach files to discussion posts, but please try to find some other upload location you can point to.
Aug 4, 2008 at 8:20 PM
Thanks a lot for your answer, koyama! :)
I will copy my code tomorrow when I got in the office. Personally, I do not think providing the Excel file will bring much but I will try to upload it somewhere tomorrow.
Have a good day!
Aug 5, 2008 at 9:08 AM
Edited Aug 5, 2008 at 9:10 AM
Hi everyone,

Here is a part of my code:
 (I wrote [...] when I think you do not need this part to understand where is the error, there is also a lot less code to read for you! :)


/** Error reporting */

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** Include different files from the library to read and write **/
include 'PHPExcel/IOFactory.php';
//include 'PHPExcel.php';
include 'PHPExcel/Reader/Excel2007.php';
//include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Writer/Excel5.php';

if (!file_exists("../files/template.xlsx")) {
    exit("Can not find the template file!");
/*load a excel2003 file works fine but do not load the formatting so we are not using this solution
//$objReader = PHPExcel_IOFactory::createReader('Excel5');
//$objPHPExcel = $objReader->load("../files/testtemplate3.xls");*/

/////////////////////////////////////////////////////////////////////////////Read and load the template file////////////////////
//load a Excel2007 template
//$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("../files/testtemplate4.xlsx");
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load("../files/template.xlsx");

// Create a first sheet, representing sales data

// Set properties
$objPHPExcel->getProperties()->setCreator("Strategic Database");
$objPHPExcel->getProperties()->setLastModifiedBy("Strategic Database");
$objPHPExcel->getProperties()->setTitle("Strategic Database");
$objPHPExcel->getProperties()->setSubject("Strategic Database");
$objPHPExcel->getProperties()->setDescription("Strategic Database");
$objPHPExcel->getProperties()->setKeywords("Strategic Database");
$objPHPExcel->getProperties()->setCategory("Strategic Database");

// Play around with inserting and removing rows and columns    <---TEST: THIS IS NOT WORKING
/*$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(24, 4);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 9);

//hide a row <--that works

////////////////////////////////////////////Write something into the template file//////////////////////////////////////


//Display the unit name and code
$rq_structure="SELECT * FROM structure WHERE structure_id='$structure_id' ORDER BY structure_id LIMIT 1";
$res_structure=mysql_query($rq_structure) or die(mysql_error());       
$objPHPExcel->getActiveSheet()->setCellValue('H2', $structure_name_db.' ('.$country_code_db.')');
$objPHPExcel->getActiveSheet()->setCellValue('M2', $structure_code_db);


//write some data from the database <--not working
//$objPHPExcel->getActiveSheet()->insertNewRowBefore(1, 10);
//$objPHPExcel->getActiveSheet()->removeRow(6, 10);


//Display the comment
$rq_comment="SELECT comment_content FROM comment WHERE comment_structure_id='$structure_id' ORDER BY comment_id LIMIT 1";
$res_comment=mysql_query($rq_comment) or die(mysql_error());       
$replacement_list=array('&nbsp;','</p>','<br />','<br/>','<br>','<p>');
$objPHPExcel->getActiveSheet()->setCellValue('C224', $comment_db);

////////////////////////////////////////////////////////////////////////////////////////Save the file////////////////////////////

/*Save the template file as Excel 2003 and not 2007          <--THIS IS NOT WORKING NEITHER, I HAVE NOT IDEA WHY....
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

And the file->

I hope this will help! :-) Thanks a lot!
Have a good day!
Aug 5, 2008 at 5:35 PM
Thanks for providing more details. I now see what you mean although I couldn't reproduce your error message. When I tried to do what you are doing the resulting output was messed up.

I think that there are perhaps 2-3 bugs associated with this problem. I just filed one of them here:

A second bug may be one involving removeColumn() in conjuction with merged cells, but I'm not sure. I say let's have a look at it again when the issue above is resolved.

In the mean time, I recommend that you avoid using "Theme colors" for backgrounds, text, etc. Instead pick colors from the "Standard colors" or "More colors..." (located at the bottom of the color picker) when you create your template in Microsoft Office Excel. Why? Because only those are currently supported by Excel2007 reader. If you use "Theme colors" they will be lost when you read the template.
Aug 7, 2008 at 12:40 PM
Thanks Koyama for your answer and your efficiency!
I will have a look regularly to this issue...
I will also try to find a solution tomorrow or next week about the colors. Thanks for your advice. I will read more about it.
Have a good day!