PHPExcel - Formating cell and Number Format

  • Home
  • Content
  • PHPExcel - Formating cell and Number Format
Date Added: 14/01/2015
PHPExcel - Formating cell and Number Format

Formating Cell 

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);<!--more-->

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle('B2')
->getFill()->getStartColor()->setARGB('FFFF0000');
<div>

Number Format 

$objPHPExcel->getActiveSheet()->getStyle('A1')
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); 

For more Featore  :  https://github.com/PHPOffice/PHPExcel/blob/develop/Examples/05featuredemo.inc.php

<script src="https://gist.github.com/910370.js"></script>    

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Vikas Tharyani")
->setLastModifiedBy("Vikas Tharyani")
->setTitle($CompanyName)
->setSubject("General Leger Report")
->setDescription("Test")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");

//Add Data
$heading = sprintf("%s To %s",$FromDate,$ToDate);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('D1', $CompanyName);
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'GeneralLedger');
$objPHPExcel->getActiveSheet()->setCellValue('D3', $heading);
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'AccountId');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Opening Balance');
$objPHPExcel->getActiveSheet()->setCellValue('B4', $FromAccid);
$objPHPExcel->getActiveSheet()->setCellValue('b5', $OpeningBalance);
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Date');
$objPHPExcel->getActiveSheet()->setCellValue('C7', 'Particulars');
$objPHPExcel->getActiveSheet()->setCellValue('E7', 'VoucherType');
$objPHPExcel->getActiveSheet()->setCellValue('G7', 'VoucherNo');
$objPHPExcel->getActiveSheet()->setCellValue('I7', 'Debit');
$objPHPExcel->getActiveSheet()->setCellValue('K7', 'Credit');


//Add data using database
$i = 8;
$j = 1;
$querry7 = "select * from GLTRANSACTIONS where ACCOUNT_ID = 'G0003'";
$res7 = odbc_exec($conn,$querry7);
while(odbc_fetch_row($res7))
{
if($j%2 == 0)
{

$Refrenceno = sprintf('C%d',$i);
$RefrencenoVal = sprintf('D%d',$i);
$Refrencedate = sprintf('E%d',$i);
$RefrenceDateVal = sprintf('F%d',$i);
$chequeNo = sprintf('C%d',$i+1);
$chequeNoval = sprintf('D%d',$i+1);
$chequeDate = sprintf('E%d',$i+1);
$chequeDateVal = sprintf('F%d',$i+1);
$objPHPExcel->getActiveSheet()->setCellValue($Refrenceno, 'RefrenceNo');
$objPHPExcel->getActiveSheet()->setCellValue($RefrencenoVal,odbc_result($res7,'REF_NO'));
$objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'RefrenceDate');
$objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'REF_DATE'));
$objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'ChequeNo');
$objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'CHEQUE_NO'));
$objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'ChequeDate');
$objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'CHEQUE_DATE'));
$objPHPExcel->getActiveSheet()->getStyle($Refrenceno)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($RefrencenoVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($Refrencedate)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($RefrenceDateVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($chequeNo)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($chequeNoval)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($chequeDate)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($chequeDateVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$i = $i+2;
}
else
{
$cellindexcol1 = sprintf('A%d',$i);
$cellindexcol2 = sprintf('C%d',$i);
$cellindexcol3 = sprintf('E%d',$i);
$cellindexcol4 = sprintf('G%d',$i);
$cellindexcol5 = sprintf('I%d',$i);
$cellindexcol6 = sprintf('K%d',$i);
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol1, odbc_result($res7,'TR_DATE'));
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol2, odbc_result($res7,'TR_DESCRIPTION'));
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol3, odbc_result($res7,'VOUCHER_TYPE'));
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol4, odbc_result($res7,'TRANSACTION_ID'));
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol5, odbc_result($res7,'WITHDRAW_AMOUNT'));
$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol6, odbc_result($res7,'DEPOSIT_AMOUNT'));
//for alignment
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol4)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol5)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle($cellindexcol6)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
}

$j++;
$i++;
}
// Set column widths
echo date('H:i:s') . " Set column widths\n";
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(true);

// Merge cells
echo date('H:i:s') . " Merge cells\n";
$objPHPExcel->getActiveSheet()->mergeCells('D1:E1');


// Set fonts
echo date('H:i:s') . " Set fonts\n";
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

$objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);


// Set thin black border outline around column
$borderindex = sprintf('A1:K%d',$i);
echo date('H:i:s') . " Set thin black border outline around column\n";
$styleThinBlackBorderOutline = array(
'borders' => array(
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('argb' => 'FF000000'),
),
),
);
$objPHPExcel->getActiveSheet()->getStyle($borderindex)->applyFromArray($styleThinBlackBorderOutline);

// Set alignments
echo date('H:i:s') . " Set alignments\n";

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->setSheetIndex(0);

$objWriter->save(str_replace('.php', '.htm', __FILE__));
// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing files.\r\n";

Last Update: 16/12/2017 Posted by: müslüm ÇEN
Not Commented Yet !
Please login in order to comment . Login

Content Categories

Müslüm ÇEN

Müslüm ÇEN
PHP Developer, Founder

Webenfo software is incredibly Creative and Innovative.