php导出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');