网上说将单元格格式设置为文本格式就行了,但是我用excel导出功能时,刚开始设置B列为文本格式,结果导出后excel表数字还是不正常显示。
需要自己去手动重置才能正常显示,自己也不太清楚原因,琢磨后通过以下两种方式解决。
$objPHPExcel->getActiveSheet(0)->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
第一种方法:不知道是不是因为数据本身是字符串的原因,设置格式改为自定义数字类型后可以了。
$objPHPExcel->getActiveSheet(0)->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
第二种方法:在插入数据时设置格式。
$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValueExplicit('B'.($i + 1),$orders['order_list'][$i-1]['order_sn'],PHPExcel_Cell_DataType::TYPE_STRING);
以下是具体代码:
require_once ‘includes/Classes/PHPExcel.php’;
require_once ‘includes/Classes/PHPExcel/Writer/Excel5.php’;
require_once ‘includes/Classes/PHPExcel/Writer/Excel.php’;
require_once ‘includes/Classes/PHPExcel/IOFactory.php’;
$objPHPExcel = new PHPExcel ();$objPHPExcel->getProperties ()->setCreator ( "Maarten Balliauw" )->setLastModifiedBy ( "Maarten Balliauw" )->setTitle ( "Office XLSX Test Document" )->setSubject ( "Office XLSX Test Document" )->setDescription ( "Test document for Office XLSX, generated using PHP classes." )->setKeywords ( "office openxml php" )->setCategory ( "Test result file" );$objPHPExcel->getActiveSheet(0)->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A1', "订单日期" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'B1', "订单编号" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'C1', "公司部门" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'D1', "采购员" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'E1', "金额" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'F1', "支付方式" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'G1', "支付状态" );$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'H1', "备注" );$filter['select_list'] = $_REQUEST['select_list'];$filter['price_format'] = 'no';$orders = get_user_orders_listTable($_SESSION['b_id'],$user_id, $filter, $page);for($i=1 ; $i <$orders['record_count']+2 ; $i++){$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A' . ($i + 1), $orders['order_list'][$i-1]['order_time']);$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'B' . ($i + 1), $orders['order_list'][$i-1]['order_sn']);$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'C' . ($i + 1), $orders['order_list'][$i-1]['dep_name']);$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'D' . ($i + 1), $orders['order_list'][$i-1]['user_name']);if($orders['order_list'][$i-1]['orderType'] == 1){$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'E' . ($i + 1), strip_tags($orders['order_list'][$i-1]['total_fee']));}else{$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'E' . ($i + 1), strip_tags($orders['order_list'][$i-1]['back_money']));}$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'F' . ($i + 1), strip_tags($orders['order_list'][$i-1]['pay_name']));if($orders['order_list'][$i-1]['orderType'] == 1){$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'G' . ($i + 1), strip_tags($orders['order_list'][$i-1]['pay_status']));}$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'H' . ($i + 1), $orders['order_list'][$i-1]['postscript']);}$objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'E' . ($i+2), '订单总金额:'.$orders['order_list_amount']);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'A' )->setWidth (20);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'B' )->setWidth (20);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'C' )->setWidth (10);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'D' )->setWidth (20);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'E' )->setWidth (10);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'F' )->setWidth (30);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'G' )->setWidth (10);$objPHPExcel->setActiveSheetIndex ( 0 )->getColumnDimension ( 'H' )->setWidth (10);$filename = "按订单统计导出订单".date ( "Y-m-d" ) . ".xls";header ( "Pragma: public" );header ( "Expires: 0" );header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );//header ( "Content-Type: application/force-download" );//header ( "Content-Type: application/octet-stream" );//header ( "Content-Type: application/download" );header('Content-Type: application/vnd.ms-excel');header ( "Content-Disposition: attachment;filename=$filename " );header ( "Content-Transfer-Encoding: binary" );$objPHPExcel->setActiveSheetIndex ( 0 )->getPageSetup ()->setOrientation ( PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE );$objPHPExcel->setActiveSheetIndex ( 0 )->getPageSetup ()->setPaperSize ( PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4 );$objWriter = PHPExcel_IOFactory::createWriter ( $objPHPExcel, 'Excel5' );$objWriter->save ( 'php://output' );exit;