save multiple files

Topics: Developer Forum, User Forum
Jun 24, 2010 at 6:04 AM

Dear All,

I'm trying to write data into two excel files or multiple excel files from mysql with looping method.

I'm doing something like:

#!/usr/bin/php -q

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

set_include_path(get_include_path() . PATH_SEPARATOR . '/home/chemonk/sinau/Classes');

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

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

/* PHPExcel_Cell_AdvanceValueBinder required for this sample */
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

include 'connect.php';

$style = array(
'font' => array(
'name' => 'Arial',
'size' => 10,
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
),
);

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("Ponco Nugrah Wibowo");
$objPHPExcel->getProperties()->setLastModifiedBy("IDMSCoins");

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:AF1')->applyFromArray($style);

$objPHPExcel->getActiveSheet()->setCellValue('A1','Record Status');
$objPHPExcel->getActiveSheet()->setCellValue('B1','Container Number');
$objPHPExcel->getActiveSheet()->setCellValue('C1','Size Type');
$objPHPExcel->getActiveSheet()->setCellValue('D1','Shipping Line (Carrier)');
$objPHPExcel->getActiveSheet()->setCellValue('E1','Full/Empty Indicator');
$objPHPExcel->getActiveSheet()->setCellValue('F1','Activity Code');
$objPHPExcel->getActiveSheet()->setCellValue('G1','Activity Date Time');
$objPHPExcel->getActiveSheet()->setCellValue('H1','Port code (Activity)');
$objPHPExcel->getActiveSheet()->setCellValue('I1','Terminal Code');
$objPHPExcel->getActiveSheet()->setCellValue('J1','To Place');
$objPHPExcel->getActiveSheet()->setCellValue('K1','To Depot');
$objPHPExcel->getActiveSheet()->setCellValue('L1','Service Code');
$objPHPExcel->getActiveSheet()->setCellValue('M1','Vessel Code');
$objPHPExcel->getActiveSheet()->setCellValue('N1','Voyage');
$objPHPExcel->getActiveSheet()->setCellValue('O1','Bound');
$objPHPExcel->getActiveSheet()->setCellValue('P1','Place of Origin Code (UN Code)');
$objPHPExcel->getActiveSheet()->setCellValue('Q1','Port of Loading Code (UN Code)');
$objPHPExcel->getActiveSheet()->setCellValue('R1','Port of Discharge Code');
$objPHPExcel->getActiveSheet()->setCellValue('S1','Destination Code (UN Code)');
$objPHPExcel->getActiveSheet()->setCellValue('T1','Container Condition Code');
$objPHPExcel->getActiveSheet()->setCellValue('U1','Booking Number');
$objPHPExcel->getActiveSheet()->setCellValue('V1','Gross Weight');
$objPHPExcel->getActiveSheet()->setCellValue('W1','Seal Number');
$objPHPExcel->getActiveSheet()->setCellValue('X1','Mode of Transport Code');
$objPHPExcel->getActiveSheet()->setCellValue('Y1','Created User');
$objPHPExcel->getActiveSheet()->setCellValue('Z1','Remarks');
$objPHPExcel->getActiveSheet()->setCellValue('AA1','Flag Status');
$objPHPExcel->getActiveSheet()->setCellValue('AB1','Supplier Ref');
$objPHPExcel->getActiveSheet()->setCellValue('AC1','Sub lease Ref');
$objPHPExcel->getActiveSheet()->setCellValue('AD1','Scale In Ref');
$objPHPExcel->getActiveSheet()->setCellValue('AE1','Scale Out Ref');
$objPHPExcel->getActiveSheet()->setCellValue('AF1','Soc');

$SQLin = "SELECT
cp.CPOPR, cp.CRNO AS Container_No, DATE_FORMAT(cp.CPITGL, '%d/%m/%Y') AS Tgl,
cp.CPITERM, SEC_TO_TIME(TIME_TO_SEC(cp.CPIJAM) - 1800) AS trfetime, cp.CPIJAM AS Jam,
SEC_TO_TIME(TIME_TO_SEC(cp.CPIJAM) + 300) AS sntrtime,
sur.SVCOND, cp.CPIDELIVER, cp.CPIREMARK, cr.RETYPE, cr.RETFROM
FROM idms_coins.coins_container_process cp
LEFT JOIN idms_coins.tblcontainer con ON con.CRNO = cp.CRNO
LEFT JOIN idms_coins.coins_container_repo cr ON cr.REORDERNO=cp.CPIORDERNO
LEFT JOIN idms_coins.coins_survey sur ON sur.CPID = cp.CPID
LEFT JOIN idms_coins.tblcontainer_code cc ON cc.CCCODE = con.CCCODE
LEFT JOIN idms_coins.tbldepo dep ON dep.DPCODE = cp.CPDEPO
WHERE
$Subdepo
$Oprcon
$TanggalIN
cp.CPOPR <>'' AND cp.CRNO IS NOT NULL
ORDER BY cp.CPOPR, cp.CPIJAM";

//die($SQLin);

$resultsIN = mysql_query($SQLin,$link);


function in($objPHPExcel,$resultsIN,$style,$port,$terminal) {

$i = 2;

while ($rowIN = mysql_fetch_array($resultsIN, MYSQL_ASSOC)){


$range = "A$i:AF$i";
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($style);
$objPHPExcel->setActiveSheetIndex()
->setCellValue('A'.$i, "0" )
->setCellValue('B'.$i, $rowIN["Container_No"])
->setCellValue('F'.$i, "RCVC");
->setCellValue('G'.$i, $rowIN["Tgl"]." ".$rowIN["Jam"])
->setCellValue('H'.$i, $port)
->setCellValue('I'.$i, $terminal)
->setCellValue('AF'.$i, "N");

$i++;

}
// Save file

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('MTCON_JKT_RCVC.xls');

$objPHPExcel->setActiveSheetIndex()->setCellValue('F'.$i, "SNTR");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('MTCON_JKT_SNTR.xls');

}
in($objPHPExcel,$resultsIN,$style,$port,$terminal);
?>

There are differences in the contents of the data in column F between MTCON_JKT_RCVC.xls with MTCON_JKT_SNTR.xls.I want to change the content in column F. From RCVC become SNTR.

Please let me know any suggestions you have.

Thank You.