Unable to Add new column in excel file

Topics: Developer Forum, Project Management Forum, User Forum
Sep 25, 2009 at 8:49 PM

Hy , I woluld like to add new column after once i create xls file but i getting error like this Microsoft office excel - Compatibility checker so how to i solve this problum

 

Developer
Sep 25, 2009 at 10:36 PM

Do you have some code showing what you are trying to do?

Sep 26, 2009 at 12:03 AM

i bale to succesfully create xls file and i also able to save this file on my machine  after that i want to open this file and add same column as per my requirement that time i getting this error

in this file i generate a invoice , In the  header part i include one image from A column to G and my all the invoice matter i print below this image now after succesffully save my invoice i would like to add more column after G that time ai cant able to save this file and also cant able to giving proper formatting

Developer
Sep 26, 2009 at 12:11 AM

This is strange. This kind of bug has not been reported before. Are you trying to add that extra column using Microsoft Office Excel?

You will need to show us the Excel file so we can reproduce the error. Can you upload it somewhere, or you can send it to me: (erik at phpexcel dot net)

Sep 28, 2009 at 2:05 PM

Hy , Koyama

See Below Code which i used for the Generate Invoice Which data comes from the data base In this invoice i able to add data on the g column but cant able to draw the line on H20 to H26 so please reply me asap 

<?
/** Error reporting */
error_reporting(E_ALL);

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

/** PHPExcel */
require_once 'PHPExcel.php';

/** PHPExcel_RichText */
require_once 'PHPExcel/RichText.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add  A HEADER IMAGE
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath();
//$objDrawing->setHeight(86);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:G4');
//$objPHPExcel->getActiveSheet()->mergeCells('A5:G5');

//SET TEXT ON A6 SELL
$objPHPExcel->getActiveSheet()->setCellValue('A6', '');
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);

// INVOICE HEADER IN THE MIDDLE
$objPHPExcel->getActiveSheet()->mergeCells('A7:G7');
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'INVOICE');
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// ADDRESS OF PTS ON LEFT SIDE
$objPHPExcel->getActiveSheet()->setCellValue('A8', ' ');
$objPHPExcel->getActiveSheet()->setCellValue('A9', ',');
$objPHPExcel->getActiveSheet()->setCellValue('A10', '');
$objPHPExcel->getActiveSheet()->setCellValue('A11', '');
$objPHPExcel->getActiveSheet()->setCellValue('A12', '');
$objPHPExcel->getActiveSheet()->setCellValue('A13', );
$objPHPExcel->getActiveSheet()->getStyle('A13')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A9')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A10')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A11')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A12')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A13')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A9')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A11')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A12')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A13')->getFont()->setSize(10);

// INVICE# AND INVOICE PERIOD DATE AND ITS BORDER
$objPHPExcel->getActiveSheet()->setCellValue('E11', 'INVOICE#');
$objPHPExcel->getActiveSheet()->setCellValue('E12', 'DATE');
$objPHPExcel->getActiveSheet()->mergeCells('F11:G11');
$objPHPExcel->getActiveSheet()->mergeCells('F12:G12');
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->setCellValue('F12',$dSDate.'  TO  '.$dEDate);
$objPHPExcel->getActiveSheet()->getStyle('F12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G12')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F12')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G12')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// ADDRESS OF VENDOR
$objPHPExcel->getActiveSheet()->setCellValue('A15', 'TO');
$objPHPExcel->getActiveSheet()->getStyle('A15')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A15')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->setCellValue('B15',);
$objPHPExcel->getActiveSheet()->getStyle('B15')->getFont()->setName('Arial');

$objPHPExcel->getActiveSheet()->setCellValue('B17', );
$objPHPExcel->getActiveSheet()->getStyle('B17')->getFont()->setName('Arial');

$objPHPExcel->getActiveSheet()->setCellValue('B18', 'FAX: '');
$objPHPExcel->getActiveSheet()->getStyle('B18')->getFont()->setName('Arial');

// INVOICE AND DUE DATE
$objPHPExcel->getActiveSheet()->setCellValue('F17', 'INVOICE');
$objPHPExcel->getActiveSheet()->setCellValue('G17', 'DUE DATE');
$objPHPExcel->getActiveSheet()->setCellValue('G18',);

$objPHPExcel->getActiveSheet()->getStyle('F17')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F17')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F17')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F17')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('G17')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F18')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F18')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F18')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F18')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G18')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G18')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G18')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('F17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('F17')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('F17')->getFill()->getStartColor()->setARGB('FF808000');
$objPHPExcel->getActiveSheet()->getStyle('G17')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getFill()->getStartColor()->setARGB('FF808000');

$objPHPExcel->getActiveSheet()->getStyle('F17')->getFont()->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('F17')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('F17')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getFont()->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('G17')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('G17')->getFont()->setBold(true);

// INVOICE HEADER
$objPHPExcel->getActiveSheet()->mergeCells('B20:D20');
$objPHPExcel->getActiveSheet()->setCellValue('A20', 'ITEM');
$objPHPExcel->getActiveSheet()->setCellValue('B20', 'NAME');
$objPHPExcel->getActiveSheet()->setCellValue('E20', 'VISITS');
$objPHPExcel->getActiveSheet()->setCellValue('F20', 'RATE');
$objPHPExcel->getActiveSheet()->setCellValue('G20', 'AMOUNT');

$objPHPExcel->getActiveSheet()->getStyle('A20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('A20')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('A20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B20')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('B20')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('B20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('E20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('F20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('G20')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A20')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getFill()->getStartColor()->setARGB('FF808000');
$objPHPExcel->getActiveSheet()->getStyle('B20')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B20')->getFill()->getStartColor()->setARGB('FF808000');
$objPHPExcel->getActiveSheet()->getStyle('E20')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getFill()->getStartColor()->setARGB('FF808000');
$objPHPExcel->getActiveSheet()->getStyle('F20')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getFill()->getStartColor()->setARGB('FF808000');
$objPHPExcel->getActiveSheet()->getStyle('G20')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getFill()->getStartColor()->setARGB('FF808000');

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13);

$objPHPExcel->getActiveSheet()->getStyle('A20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('B20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D20')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('E20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('F20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('G20')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G20')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

//  Draw the grid from here
$all_rows = $db->fetch_all_array($sql);
$count=count($all_rows);
$i=1;
$rono=22;
$NetTotal=0;

foreach($all_rows as $row){

$VBorderSrart=$rono-1;
$VBorderEnd=$rono+1;

$objPHPExcel->getActiveSheet()->mergeCells('B'.$VBorderSrart.':D'.$VBorderSrart);
$objPHPExcel->getActiveSheet()->mergeCells('B'.$VBorderEnd.':D'.$VBorderEnd);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rono,$i);
$objPHPExcel->getActiveSheet()->getStyle('A'.$rono)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('B'.$rono.':D'.$rono);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$rono, $row['vServiceName']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$rono,$row['tCount']);
$objPHPExcel->getActiveSheet()->getStyle('E'.$rono)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$rono,$row['dServiceRate']);
$objPHPExcel->getActiveSheet()->getStyle('F'.$rono)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
$amount=$row['tCount']*$row['dServiceRate'];

$NetTotal=$NetTotal+$amount;
$objPHPExcel->getActiveSheet()->setCellValue('G'.$rono,$amount);
$objPHPExcel->getActiveSheet()->getStyle('G'.$rono)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

// drow the right side border of grid
$objPHPExcel->getActiveSheet()->getStyle('A'.$VBorderSrart)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A'.$VBorderSrart)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D'.$VBorderSrart)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E'.$VBorderSrart)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F'.$VBorderSrart)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$VBorderSrart)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getStyle('A'.$VBorderEnd)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A'.$VBorderEnd)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D'.$VBorderEnd)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E'.$VBorderEnd)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F'.$VBorderEnd)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$VBorderEnd)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$i++;
$rono++;
}

// drow the bottom border
$objPHPExcel->getActiveSheet()->getStyle('A'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('F'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$VBorderEnd)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$net=$VBorderEnd+1;
$objPHPExcel->getActiveSheet()->getStyle('G'.$net)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$net)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$net)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G'.$net)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->setCellValue('F'.$net,'TOTAL');
$objPHPExcel->getActiveSheet()->getStyle('F'.$net)->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('F'.$net)->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('F'.$net)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F'.$net)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->setCellValue('A'.$net,'THANK YOU FOR YOUR BUSINESS!');
$objPHPExcel->getActiveSheet()->getStyle('A'.$net)->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A'.$net)->getFont()->setName('Garamond');
$objPHPExcel->getActiveSheet()->getStyle('A'.$net)->getFont()->setBold(true);

$nets=$net+2;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$nets,.');
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setName('Garamond');

$ROW=$nets+1;

$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A'.$ROW) );
$objRichText->createText('Make all checks payable to');
$objPayable = $objRichText->createTextRun(' same name');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

// net total
$objPHPExcel->getActiveSheet()->setCellValue('G'.$net,$NetTotal);
$objPHPExcel->getActiveSheet()->getStyle('G'.$net)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Invoice');

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

// redirect output to client browser
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=ServiceInvoice.xls");
header("Content-Transfer-Encoding: binary ");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>

Developer
Sep 28, 2009 at 2:41 PM

>> In this invoice i able to add data on the g column but cant able to draw the line on H20 to H26 so please reply me asap

Sorry, I am still trying to understand your question. Are you trying to insert data in column H ? Can you point at the lines in the code that are not working as expected?

Developer
Sep 28, 2009 at 3:04 PM

Perhaps you may also like to know that PHPExcel 1.7.0 supports a more compact syntax for styling cells. It is especially easier to style borders.

Instead of this:

$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E12')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

You can do like this:

$objPHPExcel->getActiveSheet()->getStyle('E11:E12')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

If you look in the documentation, you will find more shorthand methods for styling borders.

Sep 29, 2009 at 7:38 AM
koyama wrote:

>> In this invoice i able to add data on the g column but cant able to draw the line on H20 to H26 so please reply me asap

Sorry, I am still trying to understand your question. Are you trying to insert data in column H ? Can you point at the lines in the code that are not working as expected?

 Thanks Kayoma For Suggeistion for the border.

Now consider as per my code i have therapist.xls file in this file i want to add one column and dosome formatting in thia file which i have created by PHPEXCEL

My Invoice Created upto G column now i want to add some data on H20 to H26, now icant understand what happen but i able to add data on this given column range now i would like to add thin line on right side of this H20 to H26 but Just on the H20 i able to add right side thin line rest of cell i cant add right side thin line for this i used office 2007.

and also if u seen in my code want to add rich text formatting but its also cant working

$objPHPExcel->getActiveSheet()->setCellValue('A'.$nets,'Should you have any questions, please contact samaj at the above given number.');
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setName('Garamond');

$ROW=$nets+1;

$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A'.$ROW) );
$objRichText->createText('Make all checks payable to');
$objPayable = $objRichText->createTextRun(' my name .');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

Sep 29, 2009 at 7:38 AM
koyama wrote:

>> In this invoice i able to add data on the g column but cant able to draw the line on H20 to H26 so please reply me asap

Sorry, I am still trying to understand your question. Are you trying to insert data in column H ? Can you point at the lines in the code that are not working as expected?

 Thanks Kayoma For Suggeistion for the border.

Now consider as per my code i have therapist.xls file in this file i want to add one column and dosome formatting in thia file which i have created by PHPEXCEL

My Invoice Created upto G column now i want to add some data on H20 to H26, now icant understand what happen but i able to add data on this given column range now i would like to add thin line on right side of this H20 to H26 but Just on the H20 i able to add right side thin line rest of cell i cant add right side thin line for this i used office 2007.

and also if u seen in my code want to add rich text formatting but its also cant working

$objPHPExcel->getActiveSheet()->setCellValue('A'.$nets,'Should you have any questions, please contact samaj at the above given number.');
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A'.$nets)->getFont()->setName('Garamond');

$ROW=$nets+1;

$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A'.$ROW) );
$objRichText->createText('Make all checks payable to');
$objPayable = $objRichText->createTextRun(' my name .');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

Developer
Sep 29, 2009 at 4:00 PM

>> i would like to add thin line on right side of this H20 to H26 but Just on the H20 i able to add right side thin line rest of cell

Can you try this:

$objPHPExcel->getActiveSheet()->getStyle('H20:H26')->getBorders()->getRight()->applyFromArray(
    array(
        'style' => PHPExcel_Style_Border::BORDER_THICK,
        'color' => array(
            'rgb' => 'FF0000',
        ),
    )
);

>> want to add rich text formatting but its also cant working

Rich-Text has unfortunately not been implemented for PHPExcel_Writer_Excel5. You may vote for this:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=8916

Sep 30, 2009 at 5:41 AM

Sorry Koyama,

But My Actual Problum is like this i successfully created myfile.xls and save in my computer after the save this file i open it and want to add column and format it. dont want add column programatically but i want add manully column in myfile.xls file in this file i cant able to formatting H20 to H26 Column and Give Right thin border

 

Developer
Oct 1, 2009 at 2:58 AM

Ok, I had misunderstood the issue. This is strange. Can you upload the file somewhere? Or send the file to (erik at phpexcel dot net). I will see if I can figure out the problem.

Developer
Oct 3, 2009 at 10:38 PM

Thanks, I have received the file. You noted that you were using change set 28233 (July 25, 2009)

The bug where the style dialog could not be launched was discovered July 28, 2009

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10181

Try to upgrade to latest source code, I think it will disappear:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx