导出excel

[php]代码库

    /** Error reporting */
            error_reporting(E_ALL & ~E_NOTICE);

            date_default_timezone_set('Europe/London');
            /** PHPExcel */
            require_once '../export/phpexcel/Classes/PHPExcel.php';

            // Create new PHPExcel object
            $objPHPExcel = new PHPExcel();

            $objPHPExcel->getProperties()->setCreator("meilang")
                ->setLastModifiedBy("meilang")
                ->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");

            // Add some data
            $objPHPExcel->setActiveSheetIndex(0)
                ->SetCellValue('A1', '订单号')
                ->SetCellValue('B1', '会员名称')
                ->SetCellValue('C1', '状态')
                ->SetCellValue('D1', '数量')
                ->SetCellValue('E1', '商品名称')
                ->SetCellValue('F1', '提货点')
                ->SetCellValue('G1', '总计')
                ->SetCellValue('H1', '生成日期')
                ->SetCellValue('I1', '姓氏')
                ->SetCellValue('J1', '姓名')
                ->SetCellValue('K1', '快递公司')
                ->SetCellValue('L1', '地址1')
                ->SetCellValue('M1', '地址2')
                ->SetCellValue('N1', '快递号')
                ->SetCellValue('O1', '手机号码')
                ->SetCellValue('P1', '邮编')
                ->SetCellValue('Q1', '会员留言')
                ->SetCellValue('R1', '地区/省');


            $i = 2;
            foreach ($results as $r) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->SetCellValue('A' . $i, $r['order_id'])
                    ->SetCellValue('B' . $i, $r['customer_name'])
                    ->SetCellValue('C' . $i, $r['name'])
                    ->SetCellValue('D' . $i, $r['product_nums'])
                    ->SetCellValue('E' . $i, $r['pname'])
                    ->SetCellValue('F' . $i, $r['addresstype'])
                    ->SetCellValue('G' . $i, $r['total'])
                    ->SetCellValue('H' . $i, $r['date_added'])
                    ->SetCellValue('I' . $i, $r['firstname'])
                    ->SetCellValue('J' . $i, $r['lastname'])
                    ->SetCellValue('K' . $i, $r['shipping_method'])
                    ->SetCellValue('L' . $i, $r['payment_address_1'])
                    ->SetCellValue('M' . $i, $r['payment_address_2'])
                    ->SetCellValue('N' . $i, $r['shipping_num'])
                    ->SetCellValue('O' . $i, $r['payment_phone'])
                    ->SetCellValue('P' . $i, $r['payment_postcode'])
                    ->SetCellValue('Q' . $i, $r['liuyan'])
                    ->SetCellValue('R' . $i, $r['payment_zone']);

                $i++;

            }
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7.86);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(27.5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13.14);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(18.29);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13.67);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(14);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(46);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(23.67);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(14.83);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(26);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25);

            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
            // 水平方向上两端对齐
            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment:: HORIZONTAL_CENTER);
            // 垂直方向上中间居中
            $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment:: VERTICAL_CENTER);
            // 固定第一行
            $objPHPExcel->getActiveSheet()->freezePane('A2');
            // 设置默认字体、大小
            $objPHPExcel->getDefaultStyle()->getFont()->setName('Times New Roman');
            $objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
            // 设置第一行填充颜色
            $objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->setFillType(PHPExcel_Style_Fill:: FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->getStartColor()->setARGB('888888');
            $objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color:: COLOR_WHITE);
            //*********************** 画出单元格边框 *****************************
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        //'style' => PHPExcel_Style_Border::BORDER_THICK,// 边框是粗的
                        'style' => PHPExcel_Style_Border:: BORDER_THIN,// 细边框
                        'color' => array('argb' => 'ADADAD'),
                    ),
                ),
            );
            $objPHPExcel->getActiveSheet()->getStyle('A1:R' . ++$i)->applyFromArray($styleArray);// 这里就是画出从单元格 A5 到Si 的边框
            //*********************** 画出单元格边框结束 *****************************
            // Rename sheet
            $objPHPExcel->getActiveSheet()->setTitle('订单报表');

            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $objPHPExcel->setActiveSheetIndex(0);

            // Redirect output to a client's web browser (Excel5)
            ob_end_clean();
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="订单报表.xls"');
            header('Cache-Control: max-age=0');
            header('Content-type:application/vnd.ms-excel');


            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');