Expire and Write to Excel

Topics: Developer Forum, Project Management Forum, User Forum
Apr 8, 2010 at 1:48 PM

Hi,

  I have an invoice with 3500 lines. When I generate the xls file, the program expire and send me an error saying that it expire. I'm kind of surprise that it takes that long to generate 3600 lines.

 Any idea how I can get around that problem ?

Also, I want to create an excel sheet without opening excel, is it possible ?

Thanks

Real

Apr 9, 2010 at 1:57 AM

realdrouin,

There are many potential reasons why it could be taking so long to generate the lines...

How are you populating the data? How are you iterating line by line and/or cell by cell? With no code snippets, it could be anything!

What do you mean you want to create an Excel sheet without opening it?

 

Apr 9, 2010 at 2:04 AM

Here is the code

 

$query = "SELECT * FROM SMtbl_Invoice_details WHERE invoicenumber='".$_REQUEST["invoice"]."' ORDER BY state, city";
  $result=odbc_exec($conn, $query);
 
  while(odbc_fetch_row($result))
  {  
    $line = $line + 1;
   
    $hours = fntonbr(odbc_result($result,"HoursBilled"));
    if ($hours == 0) $hours = fntonbr(odbc_result($result,"MonthsBilled"));
 
    $rate = fntonbr(odbc_result($result,"ServiceHourly"));
    if ($rate == 0) $rate = fntonbr(odbc_result($result,"ServiceMonthly"));   
   
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$line, odbc_result($result,"storenumber"))
            ->setCellValue('B'.$line, trim(odbc_result($result,"city")).", ".trim(odbc_result($result,"state")))
            ->setCellValue('C'.$line, fndate(odbc_result($result,"ServiceDate")))
            ->setCellValue('D'.$line, odbc_result($result,"DisplayBillName"))
            ->setCellValue('E'.$line, $hours)
            ->setCellValue('F'.$line, $rate)
            ->setCellValue('G'.$line, odbc_result($result,"LineServiceAmount"))
            ->setCellValue('H'.$line, odbc_result($result,"LineSalesTaxAmount"))
            ->setCellValue('I'.$line, odbc_result($result,"LineTotalAmount"));  
           
    $tot1 = $tot1 + $hours;
    $tot2 = $tot2 + odbc_result($result,"LineServiceAmount");
    $tot3 = $tot3 + odbc_result($result,"LineSalesTaxAmount");
    $tot4 = $tot4 + odbc_result($result,"LineTotalAmount");        

  }

  $line = $line + 1;
            
  $objPHPExcel->getActiveSheet(0)
            ->setCellValue('D'.$line, "TOTAL:")
            ->setCellValue('E'.$line, $tot1)
            ->setCellValue('G'.$line, $tot2)
            ->setCellValue('H'.$line, $tot3)
            ->setCellValue('I'.$line, $tot4);
  $objPHPExcel->getActiveSheet()->getStyle('D'.$line)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);           
  $objPHPExcel->getActiveSheet()->getStyle('D'.$line.':I'.$line)->getFont()->setBold(true);

  $objPHPExcel->getActiveSheet()->getStyle('F2:I'.$line)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

  $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
  $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
  $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(13); 
  $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(5);
  $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
  $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
  $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10); 

  $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
  $objPHPExcel->getActiveSheet()->getStyle('E1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
   
$invoicenumber = 'inv '.$invdate.' '.$custname.' '.$billname.'.xls';

//$invoicenumber = 'inv.xls';    
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$invoicenumber.'"');
header('Cache-Control: max-age=0');

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

Apr 9, 2010 at 2:13 AM

About creating an excel sheet without opening it, well I have a "strange" user (he's 71). He wants the new system to be indentical to the current one...

In the current application, he is entering the data in a Foxpro application. When he is done entering the data, he click on generate, the program leave and go back to the main menu. However before going back to the main menu, he must wait for the application to generate the excel sheet.

Then he go to excel and open the newly created file.

I told him that the new program will generate the excel sheet and open it in excel. He doesn't want that. He doesn't want to be force to go in excel. He wants to start himself excel and option the file. That annoy me but he is paying, so I cannot change him.

Therefore, how can I create an excel sheet, without opening excel ? Can I save automatically the excel sheet  or something like that

Thanks

Real