生成具有最长执行时间的大型Excel文件

Question

hi I am using PHPExcel library to write large data in excel file

my header:

$objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A5', 'Sr. No.')
                     ->setCellValue('B5', 'Invoice No')
                     ->setCellValue('C5', 'Invoice Date')
                     ->setCellValue('D5', 'Zone')
                     ->setCellValue('E5', 'Customer Code')
                     ->setCellValue('F5', 'Customer Description')
                     ->setCellValue('G5', 'Item Code')
                     ->setCellValue('H5', 'Item Description')
                     ->setCellValue('I5', 'Picklist No.')
                     ->setCellValue('J5', 'Picklist Date')
                     ->setCellValue('K5', 'Start Serial No')
                     ->setCellValue('L5', 'End Serial No')
                     ->setCellValue('M5', 'Quantity')
                     ->setCellValue('N5', 'LR No.')
                     ->setCellValue('O5', 'LR Date')
                     ->setCellValue('P5', 'Transporter');

I am setting each cell to text format using- i need this since my records are having large numeric string and i want them as text only

$i=2;
for($s=$dd['start_serial_no'];$s<=$dd['end_serial_no'];$s++)
    {

    $objsheet->getStyle('A'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('B'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('C'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('D'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('E'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('F'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('G'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('H'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

    $objsheet
    ->getStyle('I'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('J'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('K'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('L'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('M'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('N'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('O'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        $i++;
    }

then i am adding values to them using

$i=2;    
for($s=$dd['start_serial_no'];$s<=$dd['end_serial_no'];$s++)
        {

            if($dd['record_status']=='Active')
            {

                $objPHPExcel->setActiveSheetIndex(0)
                          ->setCellValue('A'.$i, $i-5)
                          ->setCellValue('B'.$i, $dd['invoice_no'])
                          ->setCellValue('C'.$i, date("d/m/Y",strtotime($dd['invoice_date'])))
                          ->setCellValue('D'.$i, $dd['zone_name'])
                          ->setCellValue('E'.$i, $dd['cus_code'])
                          ->setCellValue('F'.$i, $dd['cus_desc'])
                          ->setCellValue('G'.$i, $dd['item_code'])
                          ->setCellValue('H'.$i, $dd['item_name'])
                          ->setCellValue('I'.$i, $dd['mrnpicklist_date'])
                          ->setCellValue('J'.$i, date("d/m/Y",strtotime($dd['mrnpicklist_date'])))
                          ->setCellValue('K'.$i, $s)
                          ->setCellValue('L'.$i, $dd['quantity'])
                          ->setCellValue('M'.$i, $dd['lr_no'])
                          ->setCellValue('N'.$i, date("d/m/Y",strtotime($dd['lr_date'])))
                          ->setCellValue('O'.$i, $dd['transporter_name']);

                $i++;
            }

    }

then resize cell

foreach(range('A','O') as $columnID) {
            $objsheet->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

p.s.$dd['end_serial_no']-$dd['start_serial_no'] >50000 recordes

Its taking around 400 sec to write 50000 records

Can anybody tell me how to reduce execution time.

Answer 1

To start

Get rid of

$i=2;
for($s=$dd['start_serial_no'];$s<=$dd['end_serial_no'];$s++)
    {

    $objsheet->getStyle('A'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objsheet
    ->getStyle('B'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

    ....

    $objsheet
    ->getStyle('O'.$i)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

    $i++;
}

and replace it with a style setting applied to the whole range of cells in one go

$objsheet->getStyle('A2:O'.($dd['end_serial_no'] - $dd['start_serial_no'] + 1))
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

---

But if you have large serial numbers in the values, you might also be better off setting those cells using setCellValueExplicit() rather than setCellValue() and not assuming that setting a cell format code to TEXT will automatically force values to be stored as strings.... it won't, that's the job of setCellValueExplicit().... then you don't even need to set a format mask