β

用 PHPExcel 导入导出 Excel 和 csv 文件

刘勇的BLOG 391 阅读

下载最新的 PHPExcel

在项目中引入 PHPExcel:

require('PHPExcel/PHPExcel.php');

导出:

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

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Yong Liu")
            ->setLastModifiedBy("Yong Liu")
            ->setTitle('export')
            ->setSubject('export demo')
            ->setDescription('export ')
            ->setKeywords("excel csv")
            ->setCategory('file');

        // Add data
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'column1');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'column2');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', 'column3');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', 'column4');
        ...

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('export demo');

        // 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 (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="export demo.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        // Redirect output to a client’s web browser (Excel5)
        /*
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        */

        // Redirect output to a client’s web browser (csv)
        /*
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')
        ->setDelimiter(',')
        ->setEnclosure('"')
        ->setLineEnding("\r\n")
        ->setSheetIndex(0);
        */

        $objWriter->save('php://output');
        exit;

导入:

// fox xlsx
        $inputFileType = 'Excel2007';
        // fox xls
        // $inputFileType = 'Excel5';
        // fox csv
        // $inputFileType = 'CSV';
        $path = $_FILES['upload_file']['tmp_name'];
        $reader = PHPExcel_IOFactory::createReader($inputFileType);
        $excelObj = $reader->load($path);
        // $result 为导入内容的数组
        $result = $excelObj->getActiveSheet()->toArray(null,true,true,true);

操作过程中可能会出现如下错误:

Unknown codepage: 10008
PHPExcel_Shared_CodePage::NumberToName(%d)
PHPExcel/Shared/CodePage.php        98        break()

原因是 NumberToName() 方法中没有 10008 对应的情况处理,所以会抛出“Unknown codepage: 10008”异常。

解决方法:

在 NumberToName() 方法中加入 10008 对应的处理:

case 10008: return 'MAC';
作者:刘勇的BLOG
走走看看,寻寻觅觅
原文地址:用 PHPExcel 导入导出 Excel 和 csv 文件, 感谢原作者分享。

发表评论