生成并下载excel文件

生成excel文件

        <?php
    /*
     * session_start();
     * if(!$_SESSION['login'])
     * {
     * echo "you can not get the file";
     * exit;
     * }
     *
     * error_reporting(E_ALL);
     * //date_default_timezone_set('Europe/London');
     * /** PHPExcel
     */
    include "../lib/phpexcel/PHPExcel.php";

    $order_no = $_POST['order_no'];

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel(); // 实例化PHPExcel类，类似于在桌面上新建一个Excel表格

    // Set properties 设置属性
    $objPHPExcel->getProperties()
        ->setCreator("")
        ->setLastModifiedBy("")
        ->setTitle("采购清单")
        ->setSubject("O")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
    $objPHPExcel->getActiveSheet()
        ->getRowDimension('A1')
        ->setRowHeight(30); // 设置行高度
    $objPHPExcel->getActiveSheet()->mergeCells('A1:K1'); // 合并单元格
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1')
        ->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置水平居中
                                                                      // $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                                                                      // $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FFCCFF');

    // $objPHPExcel->getActiveSheet()->getStyle('A2:k2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:k2')->getFill()->getStartColor()->setARGB('ffffa0');
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('A2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('B2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('C2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('D2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('E2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('F2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('G2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('H2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('I2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('J2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
    $objPHPExcel->getActiveSheet()
        ->getStyle('K2')
        ->getFont()
        ->setBold(true); // 设置是否加粗
                         // $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(50);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setAutoSize(true);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('K')
        ->setWidth(20);
    // "设置表格列宽";
    // Add some data

    // 表头
    // 给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '订单详细')
        ->setCellValue('A2', '二维码')
        ->setCellValue('B2', '订单号')
        ->setCellValue('C2', '专案号')
        ->setCellValue('D2', '成品料号')
        ->setCellValue('E2', '物料编码')
        ->setCellValue('F2', '物料名称')
        ->setCellValue('G2', '规格描述')
        ->setCellValue('H2', '数量')
        ->setCellValue('I2', '单位')
        ->setCellValue('J2', '供应商')
        ->setCellValue('K2', '单体产品序列号');

    include "../config.php";

    // 数据库连接
    // $host = "localhost";
    // $username = "root";
    // $password = "mysql#112";
    // $database = "";

    // $db = mysql_connect($host, $username, $password);
    // mysql_select_db($database,$db); //选择数据库，这里为"ywcl"。
    // mysql_query("SET NAMES UTF8"); //设定编码方式为UTF8

    $sqlgroups = 'SELECT barcode,project_code,fg_part_no,material_part_no,material_name,mat_parameter,qty,unit,vendor,serial_number 
    from x_orderlist 
    where order_no=\'' . $order_no . '\'';
    $resultgroups = mysql_query($sqlgroups);
    $numrows = mysql_num_rows($resultgroups); // 行数
    if ($numrows > 0) {
        $count = 2;
        while ($data = mysql_fetch_array($resultgroups)) {
            $count += 1;
            $l1 = "A" . "$count";
            $l2 = "B" . "$count";
            $l3 = "C" . "$count";
            $l4 = "D" . "$count";
            $l5 = "E" . "$count";
            $l6 = "F" . "$count";
            $l7 = "G" . "$count";
            $l8 = "H" . "$count";
            $l9 = "I" . "$count";
            $l10 = "J" . "$count";
            $l11 = "K" . "$count";
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue($l1, $data['barcode'])
                ->setCellValue($l2, $order_no)
                ->setCellValue($l3, $data['project_code'])
                ->setCellValue($l4, $data['fg_part_no'])
                ->setCellValue($l5, $data['material_part_no'])
                ->setCellValue($l6, $data['material_name'])
                ->setCellValue($l7, $data['mat_parameter'])
                ->setCellValue($l8, $data['qty'])
                ->setCellValue($l9, $data['unit'])
                ->setCellValue($l10, $data['vendor'])
                ->setCellValue($l11, $data['serial_number']);
        }
    }

    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('采购明细');

    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean(); // 清除缓冲区,避免乱码

    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel;charset=utf-8');
    header('Content-Disposition: attachment;filename="采购明细清单.xls"'); // attachment新窗口打印inline本窗口打印
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // Excel5为xls格式，excel2007为xlsx格式
    $objWriter->save('php://output');
    exit();
    echo "yes";
    ?>