PHP导出到Excel表格 解决数字不正常显示问题_phpexcel导出excel的数字 被隐藏 解决方案

网上说将单元格格式设置为文本格式就行了，但是我用excel导出功能时，刚开始设置B列为文本格式，结果导出后excel表数字还是不正常显示。

需要自己去手动重置才能正常显示，自己也不太清楚原因，琢磨后通过以下两种方式解决。

$objPHPExcel->getActiveSheet(0)->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
1

第一种方法：不知道是不是因为数据本身是字符串的原因，设置格式改为自定义数字类型后可以了。

$objPHPExcel->getActiveSheet(0)->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
1

第二种方法：在插入数据时设置格式。

 $objPHPExcel->setActiveSheetIndex ( 0 )->setCellValueExplicit('B'.($i + 1),$orders['order_list'][$i-1]['order_sn'],PHPExcel_Cell_DataType::TYPE_STRING);
1

以下是具体代码：

require_once ‘includes/Classes/PHPExcel.php’;
require_once ‘includes/Classes/PHPExcel/Writer/Excel5.php’;
require_once ‘includes/Classes/PHPExcel/Writer/Excel2007.php’;
require_once ‘includes/Classes/PHPExcel/IOFactory.php’;

            $objPHPExcel = new PHPExcel ();
            $objPHPExcel->getProperties ()->setCreator ( "Maarten Balliauw" )->setLastModifiedBy ( "Maarten Balliauw" )->setTitle ( "Office 2007 XLSX Test Document" )->setSubject ( "Office 2007 XLSX Test Document" )->setDescription ( "Test document for Office 2007 XLSX, generated using PHP classes." )->setKeywords ( "office 2007 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64