phpexcel takes time with writer used as html

Topics: User Forum
Dec 5, 2010 at 11:20 AM
Edited Dec 5, 2010 at 11:20 AM

I have around 100 records which are coming from the database

i have used writer as html , it's taking so much time in writing to html

please provide some solutions

Dec 5, 2010 at 11:25 AM

here is the code

// 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";

Coordinator
Dec 5, 2010 at 11:52 AM
Edited Dec 5, 2010 at 11:55 AM

There are dozens of threads here indicating that PHPExcel is slow, and yes! it will take time to write to any format, including HTML. These threads also indicate that we are working on performance, speeding up the execution of code as well as reducing the memory required to execute. You can find details of our current progress in this comparison of speed/memory usage between different versions of PHPExcel.

Some of these threads also give advice on how to improve performance, for example by setting styles for a range of cells rather than for every individual cell, and/or setting an array of style definitions and using applyFromArray() rather than setting every style feature individually, or using PHPExcel's fluent interface.

$objPHPExcel->getActiveSheet()->getStyle('D1:D3')->getFont()->setName('Arial');

or

 

 

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

or
$styleArray = array( 'font' => array( 'name' => 'Arial',
                                      'size' => 12,
                                      'bold' => true,
                                      'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE,
                                      'color' => array( 'argb' => PHPExcel_Style_Color::COLOR_BLUE)
                                    ),
                   );
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);
 
Dec 6, 2010 at 6:09 AM

thanks for the reply

I have modified the code, now it's working fine