问题

它相信Excel正在剥离前导0.我被告知在导出期间将列格式更新为文本将解决这个问题并修改Excel输出(来自“ProofAndTrackationIpromoteuAutomation”文件,但我找不到这个文件或如何访问生成excel格式代码的地方).

我从未使用过Excel.我尝试过这个,但它似乎是一个本地修复: http://excelribbon.tips.net/T010262_Handling_Leading_Zeros_in_CSV_Files.html 有人能指出我正确的方向开始吗?

我研究了这些答案如何在从数据表导出excel时阻止前导0被剥离? Export Excel:避免剥离前导零
2001年12月31日终了的两年期收入和支出及准备金和基金结余变动报表 提前致谢!

这是我认为导致问题的函数,但还不确定如何将列格式化为texthere.

 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' => '[email protected]',
        'from_name' => 'Hit Promotional Products',
        'subject' => 'Order Tracking Information - ' . date('m/d/Y'),
        'to_emails' => '[email protected]promoteu.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;
}
 

  最佳答案

好的,Excel可以创建csv列作为text,但问题是导入.

当Excel打开csv文件时,它将任何看起来像数字的东西转换为数字,剥离前导零.

首先确保Excel按照应有的方式导出它,在文本编辑器中打开文件并检查前导零存在。

您可能需要更改文件的导入方式.而不是只双击csv文件,它们需要通过数据导入向导运行:

ios – 从text’导入’

From Text

sql – 选择分隔

Next

确保逗号为ticked

Comma separator

javascript – 选择列作为文本

Select text

这应该解决另一方面的问题。

另一种选择是在数字之前添加一个单一的postrophe ',但是如果他们使用宏/软件与文件交互,这可能会破坏它.

编辑

在查看PHP代码(我无法测试)后,您可能可以更改行:

 $sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
 

至:

 $sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode('000000000000'); // With however many zero's you need to keep
 

但是,当您导出时,这可能仍然会被删除,因为它在技术上仍然是一个数字,格式将被删除。

  相同标签的其他问题

excelcsvdataformatautocorrect