php的phpExcel相关操作

最新推荐文章于 2025-04-09 09:15:39 发布

爱吃榴莲的橙子

最新推荐文章于 2025-04-09 09:15:39 发布

阅读量894

点赞数

分类专栏： php 文章标签： php postman elementui

本文链接：https://blog.youkuaiyun.com/u011477914/article/details/108471231

版权

php 专栏收录该内容

31 篇文章

订阅专栏

这篇博客介绍了如何使用PHPExcel库在PHP中进行Excel文件操作，包括将列的字母编号转换为数字和反之，以及如何在Excel中插入图片。此外，还展示了读取上传的Excel文件数据并导出新的Excel文件，其中包含自动换行和图片显示的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、将列的字母转成数字序号使用,代码如下:

$phpExcel = new PHPExcel();
//$array需要导出的数组
foreach($array as $k => $value){
    $title_col = PHPExcel_Cell::stringFromColumnIndex($k); // 从0开始 A-Z,AA,AB...
    $phpExcel->getActiveSheet()->setCellValue($title_col."1",$value);//标题行  A1-Z1,AA1,AB1...
}

二、将列的数字序号转成字母使用,代码如下:

PHPExcel_Cell::columnIndexFromString('AA');

phpExcel中添加图片显示

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('产品图片');
$objDrawing->setDescription('产品图片');
$objDrawing->setPath(FILE_UPLOAD.$image); //图片的绝对地址
$objDrawing->setHeight(200);
$objDrawing->setCoordinates('G' . $m);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

自动换行

$objPHPExcel->getActiveSheet()->getStyle('A' . $m)->getAlignment()->setWrapText(true);//自动换行

/**
 * 根据上传的excel文件导出相应的excel数据
 */
set_time_limit(0);
if (!empty($_FILES['exl']['name'])) {
    //上传目录
    $uplode_url = FILE_UPLOAD . "document";//文件夹路径
    //判断大的文件夹路径是否存在
    if(!file_exists($uplode_url)){
        //创建文件夹
        mkdir($uplode_url,0700,true);
    }

    //上传初始化配置参数
    $config['upload_path'] = $uplode_url;
    $config['allowed_types'] = 'xls|xlsx';
    $this->load->library('upload', $config);
    if ($this->upload->do_upload('exl')) {
        $firmware_note = $this->upload->data();
        $phpexcel = PHPExcel_IOFactory::load($firmware_note['full_path']);
        $exlData = $phpexcel->getActiveSheet()->toArray(null, true, true, true);
        $tmp_array = array();
        foreach ($exlData as $key => $values) {
            $tmp_child_array = array();
            foreach ($values as $key1 => $value1) {
                $tmp_child_array[] = $value1;
            }
            $tmp_array[] = $tmp_child_array;
        }

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '产品名称')
            ->setCellValue('B1', '简要描述')
            ->setCellValue('C1', '产品描述')
            ->setCellValue('D1', '产品图片')
 
        foreach ($tmp_array as $key => $value) {
            //第一行跳过
            if ($key == 0) {
                continue;
            }

            $m = $key + 1 ;
            $product_name = $value[0];
            $objPHPExcel->getActiveSheet()->getStyle('A' . $m)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $m)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $m)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $m)->getAlignment()->setWrapText(true);

            $where = "is_show = 1 and language_id = 0 and find_in_set('{$product_name}', product_name))";
            $product_info = $this->product->get_row($where, "product_id,product_name,product_content,product_description,product_image");
            if ($product_info) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . ($m), html_escape_decode($product_info['product_name']))
                    ->setCellValue('B' . ($m), html_escape_decode($product_info['product_description']))
                    ->setCellValue('C' . ($m), html_escape_decode($product_info['product_content']));

                $where2 = ["is_show" => 1, "language_id" => 0, "product_id" => $product_info['product_id']];
                $image = $this->productImage->get_all($where2, "image");
                if ($image) {
                    $a = array_column($image,'image_image');
                    $b = implode("\r\n",$a);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($m), $b);
                }else{
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($m), $product_info['product_image']);
                }
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle('Simple');
        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="product.xls"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');

        //Excel 2003版：列数最大256(IV，2的8次方)列，行数最大65536(2的16次方)行；
        //Excel 2007版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；
        //Excel 2013版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
    }
}