Column Widths with Background formatting

Topics: Developer Forum
Dec 16, 2011 at 4:46 PM

I am developing an application that outputs a spreadsheet report and I have set auto column widths. This works fine except for all the columns that I have set background colors on; the widths are twice what they should be. The background colors are set conditional on the values in the cells, but I didn't use the PHPExcel conditional formatting because I already had a complex system I had developed for conditional formatting of HTML versions of the reports I'm developing. I am not able to provide a link to the file, but I could provide a screen shot if someone were to provide an email for that purpose.

Below is my method for constructing the body of the report spreadsheet:

//ITERATE THROUGH THE DATA AND DISPLAY WITH COLOR FORMATTING IN APPROPRIATE PLACES
	protected function excel_table_body(){
		$n=3;
		$h = $this->row_count;
		$this->body_top = $this->row_count;		
		$this->get_color_set();
		//ITERATE THE ROWS OF DATA
		foreach ($this->data_rows as $row){
			$col='A';
			$ind = 0;
			//ITERATE THROUGH EACH ROW AND SET BACKGROUND COLOR IN COLUMN REQUIRING BG COLOR
			foreach($row as $val){
				$val = $this->split_long_field($val);//CHECK IF VALUE IS LONGER THAN MAX COLUMN WIDTH 
				//IF STRING IS LONGER THAN MAX COL WIDTH, SPLIT INTO LINES OF CORRECT LENGTH WITH \n AT END
				//SET PHPExcel WRAP TEXT METHOD FOR THAT CELL
				if($this->split_flag == 1){ 
					$this->split_flag = 0;
					$this->ExP->getActiveSheet()->getCell($col . $h)->setValue($val);
					$this->ExP->getActiveSheet()->getStyle($col . $h)->getAlignment()->setWrapText(true);
					}
				else{ //POPULATE CELL WITH ORIGINAL STRING
					$this->ExP->getActiveSheet()->setCellValue($col . $h, $val);
					}
				//CHECK IF THIS COLUMN NEEDS BG COLOR FORMATTING - IF SO, GET THE COLOR AND SET THE FORMATTING
				if(isset($this->conditional_bg[$ind]) && $this->conditional_bg[$ind] == '1'){
					$val = $this->remove_pct($val);
					$color = $this->set_excel_color($val);
					$this->ExP->getActiveSheet()->getStyle($col . $h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)															   ->getStartColor()->setARGB($color);
					}
				$col++;
				$ind++;
				}				
			$h++;
			$this->row_count++;			
			}
		}

And this is the snippet that sets the automatic column sizing:

foreach($this->col_letters as $column){
			//COLUMN WIDTHS SET AUTOMATICALLY ACCORDING TO WIDEST ROW CELL PER COLUMN 
			$this->ExP->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
			
		}
Is there any way to get these columns to adhere to the appropriate column widths when using Auto Size?