It believe that Excel is stripping leading 0s. I was told that updating the column format to text during the export will fix this and to modify Excel output (coming from "ProofAndTracking actionIpromoteuAutomation" file, yet I can't find this file or how to access where the excel formatting code is generated).
I've never worked with Excel. I tried this, but it seems to be a local fix: http://excelribbon.tips.net/T010262_Handling_Leading_Zeros_in_CSV_Files.html Could someone point me in the right direction to start?
I've researched these answers How to stop the leading 0's from being stripped off when exporting to excel from a datatable? and Export Excel : Avoid stripping the leading zeros
Thanks in advance!
Here is the function I believe is causing the problem, but not sure yet how to format columns to text here. /* * format and send order and tracking info (via Excel spreadsheet) for the day for ipromoteu (150837) */
public function actionIpromoteuAutomation() {
$ordersGroup1 = $this->getIpromoteuProof();
$ordersGroup2 = $this->getIpromoteuProofHistory();
$orders = array_merge($ordersGroup1, $ordersGroup2);
$fileName = 'Hit Promo Order Tracking ' . date('m-d-Y');
$this->_export($orders, $fileName);
$fileName = $fileName . '.xls';
$toIPROMOTEU = array(//email to be sent to HR when request is submitted
'body' => 'Attached, please find the Excel spreadsheet containing'
. ' order details for orders on ' . date('m/d/Y') . '.',
'from_email' => 'donotreply@hitpromo.net',
'from_name' => 'Hit Promotional Products',
'subject' => 'Order Tracking Information - ' . date('m/d/Y'),
'to_emails' => 'orders@ipromoteu.com',
'attachments' => array(array('path' => Yii::app()->basePath . '/../tmp/' . $fileName, 'filename' => $fileName)),
);
Hit::email((object) $toIPROMOTEU);
$this->logForDeveloper('Order records sent to iPromoteU for '. date('m/d/Y'));
}
private function _export($data, $fileName, $format = 'excel', $output = false) {
// get a reference to the path of PHPExcel classes
$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');
// Turn off our yii library autoload
spl_autoload_unregister(array('YiiBase', 'autoload'));
include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
if ($format == 'excel') {
// Set properties
$objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)
->setLastModifiedBy(Yii::app()->user->name)
->setTitle('Order Tracking Report');
}
$styleArray = array(
'font' => array(
'bold' => true,
'underline' => true,
)
);
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
for ($rowCounter = 0; $rowCounter < sizeof($data); $rowCounter++) {
$sheet->getStyle("A" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$sheet->getStyle("B" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$sheet->getStyle("C" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$sheet->setCellValue("A" . ($rowCounter + 1), date('m/d/Y', strtotime($data[$rowCounter]['date'])));
//$sheet->setCellValue("A".($rowCounter+1), $data[$rowCounter]['date']);
if ($data[$rowCounter]['fedex_tracking'] != '')
$sheet->setCellValue("B" . ($rowCounter + 1), $data[$rowCounter]['fedex_tracking']);
else
$sheet->setCellValue("B" . ($rowCounter + 1), $data[$rowCounter]['other_tracking']);
$sheet->setCellValue("C" . ($rowCounter + 1), $data[$rowCounter]['sales_order_number']);
$sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
$sheet->setCellValue("A1", "DATE")
->setCellValue("B1", "TRACKING")
->setCellValue("C1", "PO NUMBER")
->getStyle("A1:C1")->applyFromArray($styleArray);
////Set the column widths
$sheet->getColumnDimension("A")->setWidth(25);
$sheet->getColumnDimension("B")->setWidth(25);
$sheet->getColumnDimension("C")->setWidth(25);
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Order Tracking Report');
// Set active sheet index to the first sheet,
// so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$fileName = $fileName . ($format == 'excel' ? '.xls' : '.csv');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$path = Yii::app()->basePath . '/../tmp/' . $fileName;
$objWriter->save($path);
// Once we have finished using the library, give back the
// power to Yii...
spl_autoload_register(array('YiiBase', 'autoload'));
}
public function getIpromoteuProof() {
$date = date('Ymd');
$db2params = Yii::app()->params['db2params'];
$db = Zend_Db::factory('Db2', $db2params);
$select = $db->select()
->from(
// table
array('t' => 'WBPIPRAE'),
// columns
array(
'sales_order_number' => 'TRIM(WAESPO#)',
),
// schema
$db2params['schemas']['hitdta'])
->joinLeft(
// table
array('f' => 'MFD1MD'), 'CONCAT(TRIM(WAEORD#),RIGHT(TRIM(MDORDR),3))=TRIM(MDORDR)',
// columns
array(
'fedex_tracking' => 'TRIM(MDFTRK)',
'other_tracking' => 'TRIM(MDFBRC)',
'date' => 'TRIM(MDUPDT)',
),
// schema
$db2params['schemas']['varfil'])
->where("MDUPDT = '" . $date . "'")
->where("WAEIVKY='150837'")
->order('sales_order_number')
->distinct(true);
$stmt = $db->query($select);
$orders = $stmt->fetchAll();
return $orders;
}
