以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');