phpExcel 操作示例

片段 1
片段 2

phpExcel 操作示例

<?php  
//写excel  
  
//Include class   
require_once(‘Classes/PHPExcel.php’);   
require_once(‘Classes/PHPExcel/Writer/Excel2007.php’);   
$objPHPExcel = new PHPExcel();  
  
/** 
* phpExcel导出excel 
* by www.jbxue.com 
*/  
  
//Set properties 设置文件属性   
$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”);   
$objPHPExcel->getProperties()->setLastModifiedBy(“Maarten Balliauw”);   
$objPHPExcel->getProperties()->setTitle(“Office 2007 XLSX Test Document”);  
$objPHPExcel->getProperties()->setSubject(“Office 2007 XLSX Test Document”);   
$objPHPExcel->getProperties()->setDescription(“Test document for Office 2007 XLSX, generated using PHP classes.”);   
$objPHPExcel->getProperties()->setKeywords(“office 2007 openxml php”);   
$objPHPExcel->getProperties()->setCategory(“Test result file”);  
  
//Add some data 添加数据   
$objPHPExcel->setActiveSheetIndex(0);   
$objPHPExcel->getActiveSheet()->setCellValue(‘A1′, ‘Hello’);//可以指定位置   
$objPHPExcel->getActiveSheet()->setCellValue(‘A2′, true);   
$objPHPExcel->getActiveSheet()->setCellValue(‘A3′, false);   
$objPHPExcel->getActiveSheet()->setCellValue(‘B2′, ‘world!’);   
$objPHPExcel->getActiveSheet()->setCellValue(‘B3′, 2);   
$objPHPExcel->getActiveSheet()->setCellValue(‘C1′, ‘Hello’);   
$objPHPExcel->getActiveSheet()->setCellValue(‘D2′, ‘world!’);   
//循环   
for($i = 1;$i<200;$i++) {   
$objPHPExcel->getActiveSheet()->setCellValue(‘A’ . $i, $i);   
$objPHPExcel->getActiveSheet()->setCellValue(‘B’ . $i, ‘Test value’);   
}   
//日期格式化   
$objPHPExcel->getActiveSheet()->setCellValue(‘D1′, time());   
$objPHPExcel->getActiveSheet()->getStyle(‘D1′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);  
  
//Add comment 添加注释   
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->setAuthor(‘PHPExcel’);   
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(‘PHPExcel:’);   
$objCommentRichText->getFont()->setBold(true);   
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(“
”);   
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(‘Total amount on the current invoice, excluding VAT.’);  
  
//Add rich-text string 添加文字 可设置样式   
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell(‘A18′) );   
$objRichText->createText(‘This invoice is ‘);   
$objPayable = $objRichText->createTextRun(‘payable within thirty days after the end of the month’);   
$objPayable->getFont()->setBold(true);   
$objPayable->getFont()->setItalic(true);   
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );   
$objRichText->createText(‘, unless specified otherwise on the invoice.’);  
  
//Merge cells 合并分离单元格   
$objPHPExcel->getActiveSheet()->mergeCells(‘A18:E22′);   
$objPHPExcel->getActiveSheet()->unmergeCells(‘A18:E22′);  
  
//Protect cells 保护单元格   
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needs to be set to true in order to enable any worksheet protection!   
$objPHPExcel->getActiveSheet()->protectCells(‘A3:E13′, ‘PHPExcel’);  
  
//Set cell number formats 数字格式化   
$objPHPExcel->getActiveSheet()->getStyle(‘E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);  
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(‘E4′), ‘E5:E13′ );  
  
//Set column widths 设置列宽度   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setAutoSize(true);   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(12);  
  
//Set fonts 设置字体   
$objPHPExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->setName(‘Candara’);   
$objPHPExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->setSize(20);   
$objPHPExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->setBold(true);   
$objPHPExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);   
$objPHPExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);  
  
//Set alignments 设置对齐   
$objPHPExcel->getActiveSheet()->getStyle(‘D11′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);   
$objPHPExcel->getActiveSheet()->getStyle(‘A18′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);   
$objPHPExcel->getActiveSheet()->getStyle(‘A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
$objPHPExcel->getActiveSheet()->getStyle(‘A3′)->getAlignment()->setWrapText(true);  
  
//Set column borders 设置列边框   
$objPHPExcel->getActiveSheet()->getStyle(‘A4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objPHPExcel->getActiveSheet()->getStyle(‘A10′)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objPHPExcel->getActiveSheet()->getStyle(‘E10′)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objPHPExcel->getActiveSheet()->getStyle(‘D13′)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);   
$objPHPExcel->getActiveSheet()->getStyle(‘E13′)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);  
  
//Set border colors 设置边框颜色   
$objPHPExcel->getActiveSheet()->getStyle(‘D13′)->getBorders()->getLeft()->getColor()->setARGB(‘FF993300′);   
$objPHPExcel->getActiveSheet()->getStyle(‘D13′)->getBorders()->getTop()->getColor()->setARGB(‘FF993300′);   
$objPHPExcel->getActiveSheet()->getStyle(‘D13′)->getBorders()->getBottom()->getColor()->setARGB(‘FF993300′);   
$objPHPExcel->getActiveSheet()->getStyle(‘E13′)->getBorders()->getRight()->getColor()->setARGB(‘FF993300′);  
  
//Set fills 设置填充   
$objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);   
$objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFill()->getStartColor()->setARGB(‘FF808080′);  
  
//Add a hyperlink to the sheet 添加链接   
$objPHPExcel->getActiveSheet()->setCellValue(‘E26′, ‘www.phpexcel.net’);   
$objPHPExcel->getActiveSheet()->getCell(‘E26′)->getHyperlink()->setUrl(‘http://www.phpexcel.net’);   
$objPHPExcel->getActiveSheet()->getCell(‘E26′)->getHyperlink()->setTooltip(‘Navigate to website’);   
$objPHPExcel->getActiveSheet()->getStyle(‘E26′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
  
//Add a drawing to the worksheet 添加图片   
$objDrawing = new PHPExcel_Worksheet_Drawing();   
$objDrawing->setName(‘Logo’);   
$objDrawing->setDescription(‘Logo’);   
$objDrawing->setPath(‘./images/officelogo.jpg’);   
$objDrawing->setHeight(36);   
$objDrawing->setCoordinates(‘B15′);   
$objDrawing->setOffsetX(110);   
$objDrawing->setRotation(25);   
$objDrawing->getShadow()->setVisible(true);   
$objDrawing->getShadow()->setDirection(45);   
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());  
  
//Play around with inserting and removing rows and columns   
$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);   
$objPHPExcel->getActiveSheet()->removeRow(6, 10);   
$objPHPExcel->getActiveSheet()->insertNewColumnBefore(‘E’, 5);   
$objPHPExcel->getActiveSheet()->removeColumn(‘E’, 5);  
  
//Add conditional formatting   
$objConditional1 = new PHPExcel_Style_Conditional();   
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);   
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);   
$objConditional1->setCondition(’0′);   
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);   
$objConditional1->getStyle()->getFont()->setBold(true);  
  
//Set autofilter 自动过滤   
$objPHPExcel->getActiveSheet()->setAutoFilter(‘A1:C9′);  
  
//Hide “Phone” and “fax” column 隐藏列   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setVisible(false);   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setVisible(false);  
  
//Set document security 设置文档安全   
$objPHPExcel->getSecurity()->setLockWindows(true);   
$objPHPExcel->getSecurity()->setLockStructure(true);   
$objPHPExcel->getSecurity()->setWorkbookPassword(“PHPExcel”);  
  
//Set sheet security 设置工作表安全   
$objPHPExcel->getActiveSheet()->getProtection()->setPassword(‘PHPExcel’);   
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled in order to enable any of the following!   
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);   
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);   
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);  
  
//Calculated data 计算   
echo ‘Value of B14 [=COUNT(B2:B12)]: ‘ . $objPHPExcel->getActiveSheet()->getCell(‘B14′)->getCalculatedValue() . “
”;  
  
//Set outline levels   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setOutlineLevel(1);   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setVisible(false);   
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setCollapsed(true);  
  
//Freeze panes   
$objPHPExcel->getActiveSheet()->freezePane(‘A2′);  
  
//Rows to repeat at top   
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);  
  
//Set data validation 验证输入值   
$objValidation = $objPHPExcel->getActiveSheet()->getCell(‘B3′)->getDataValidation();   
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );   
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );   
$objValidation->setAllowBlank(true);   
$objValidation->setShowInputMessage(true);   
$objValidation->setShowErrorMessage(true);   
$objValidation->setErrorTitle(‘Input error’);   
$objValidation->setError(‘Number is not allowed!’);   
$objValidation->setPromptTitle(‘Allowed input’);   
$objValidation->setPrompt(‘Only numbers between 10 and 20 are allowed.’);   
$objValidation->setFormula1(10);   
$objValidation->setFormula2(20);   
$objPHPExcel->getActiveSheet()->getCell(‘B3′)->setDataValidation($objValidation);  
  
//Create a new worksheet, after the default sheet 创建新的工作标签   
$objPHPExcel->createSheet(); www.jbxue.com  
$objPHPExcel->setActiveSheetIndex(1);  
  
//Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚   
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(‘&C&HPlease treat this document as confidential!’);   
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(‘&L&B’ . $objPHPExcel->getProperties()->getTitle() . ‘&RPage &P of &N’);  
  
//Set page orientation and size 方向大小   
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);  
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);  
  
//Rename sheet 重命名工作表标签   
$objPHPExcel->getActiveSheet()->setTitle(‘Simple’);  
  
//Set active sheet index to the first sheet, so Excel opens this as the first sheet  
$objPHPExcel->setActiveSheetIndex(0);  
  
//Save Excel 2007 file 保存   
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);   
$objWriter->save(str_replace(‘.php’, ‘.xlsx’, __FILE__));  
  
//Save Excel 5 file 保存   
require_once(‘Classes/PHPExcel/Writer/Excel5.php’);   
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);   
$objWriter->save(str_replace(‘.php’, ‘.xls’, __FILE__));  
  
//1.6.2新版保存   
require_once(‘Classes/PHPExcel/IOFactory.php’);   
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007′);   
$objWriter->save(str_replace(‘.php’, ‘.xls’, __FILE__));  
  
//读excel   
//Include class   
require_once(‘Classes/PHPExcel/Reader/Excel2007.php’);   
$objReader = new PHPExcel_Reader_Excel2007;  
  
$objPHPExcel = $objReader->load(“05featuredemo.xlsx”);  
  
//读写csv  
require_once(“05featuredemo.inc.php”);   
require_once(‘Classes/PHPExcel/Writer/CSV.php’);   
require_once(‘Classes/PHPExcel/Reader/CSV.php’);   
require_once(‘Classes/PHPExcel/Writer/Excel2007.php’);  
  
//Write to CSV format 写   
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);   
$objWriter->setDelimiter(‘;’);   
$objWriter->setEnclosure(”);   
$objWriter->setLineEnding(“
”);   
$objWriter->setSheetIndex(0);   
$objWriter->save(str_replace(‘.php’, ‘.csv’, __FILE__));  
  
//Read from CSV format 读   
$objReader = new PHPExcel_Reader_CSV();   
$objReader->setDelimiter(‘;’);   
$objReader->setEnclosure(”);   
$objReader->setLineEnding(“
”);   
$objReader->setSheetIndex(0);   
$objPHPExcelFromCSV = $objReader->load(str_replace(‘.php’, ‘.csv’, __FILE__));  
  
//Write to Excel2007 format   
$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);   
$objWriter2007->save(str_replace(‘.php’, ‘.xlsx’, __FILE__));  
  
//写html  
require_once(“05featuredemo.inc.php”);   
require_once(‘Classes/PHPExcel/Writer/HTML.php’);  
  
//Write to HTML format   
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);   
$objWriter->setSheetIndex(0);   
$objWriter->save(str_replace(‘.php’, ‘.htm’, __FILE__));  
  
//写pdf  
require_once(“05featuredemo.inc.php”);   
require_once(‘Classes/PHPExcel/IOFactory.php’);  
  
//Write to PDF format   
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘PDF’);   
$objWriter->setSheetIndex(0);   
$objWriter->save(str_replace(‘.php’, ‘.pdf’, __FILE__));   
//Echo memory peak usage   
echo date(‘H:i:s’) . ” Peak memory usage: ” . (memory_get_peak_usage(true) / 1024 / 1024) . ” MB
”;

示例 导出 excel 带图片

<?php

    exit;
    ini_set("memory_limit","512M");
    include "../framework/library/phpexcel/PHPExcel.php";
    include "./medoo.php";
    $SAVE_IMG = "";
    $table2 = new medoo(array(
        // 必须配置项
        'database_type' => 'mysql',
        'database_name' => '',
        'server' => '',
        'username' => '',
        'password' => '',
        'charset' => 'utf8',
        // 可选参数
        'port' => 3306,
        // 可选，定义表的前缀
        'prefix' => '',
        // 连接参数扩展, 更多参考 http://www.php.net/manual/en/pdo.setattribute.php
        'option' => array(
            PDO::ATTR_CASE => PDO::CASE_NATURAL
        )
    ));

    /*实例化excel类*/
    $excel = new PHPExcel();
    /*实例化excel图片处理类*/
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    /*设置文本对齐方式*/
    $excel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $excel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objActSheet = $excel->getActiveSheet();
    $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD');
    /*设置表头数据*/
    $tableheader = array('款号', '款名', '图片地址', '视频地址', '类型', '印记', '新款', '畅销', '供应商款号', '库存', '最低价', '最高价', '材质', '尺寸', '主石形状', '主石价', '主石描述', '金重', '主石数量', '主石重量', '副石数量', '副石重量', '颜色', '净度', '工费', '18k价格', 'pt950价格', '18K金重', '950金重');
    /*    填充表格表头*/
    for($i = 0;$i < count($tableheader);$i++) {
        $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
        /*设置font*/
        $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));
        $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setSize(12);
        $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setBold(true);
        /*设置下划线*/
        // $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
        /*设置字体颜色*/
        // $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
        // $excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
        /*设置表格宽度*/
        $objActSheet->getColumnDimension("$letter[$i]")->setWidth(20);
        $objActSheet->getColumnDimension("$letter[2]")->setWidth(30);
        $objActSheet->getColumnDimension("$letter[5]")->setWidth(60);
    }
    $tempImage = [];
    $data = $table2->query("SELECT count(*) AS total FROM `tablename` WHERE adduid = 1 LIMIT 1")->fetchAll();

    $total = intval($data[0]['total']);
    if( $total > 0 ){
        $bodyLine    = 2;
        $pageSize    = 20;
        $pageCount   = ceil( $total / $pageSize );
        $styleMode   = ['现货','定制','对戒','其它'];
        for( $index = 0; $index < $pageCount; $index ++ ){
            $database = $table2->select("tablename", "*", [ 'adduid'=> 1, 'LIMIT' => [ $index * $pageSize , $pageSize ] ]);
            foreach( $database as $keys => $vals ){
                $line = $keys + $bodyLine;
                /*设置表格高度*/
                $excel->getActiveSheet()->getRowDimension($line)->setRowHeight(50);
                $excel->getActiveSheet()->getStyle($line)->getFont()->setSize(12);
                /*设置表格格式*/
                $excel->getActiveSheet()->getStyle("$letter[2]$line")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
                $thumbHttp = $vals['best64'] == 1 ? 'http://www.aaa.com/imagespath/' : 'http://www.bbb.com/';

                $excel->getActiveSheet()->setCellValue( "$letter[0]$line" , $vals['style_no'] );
                $excel->getActiveSheet()->setCellValue( "$letter[1]$line" , $vals['style_name'] );
                if( $vals['style_thumb'] ){
                    $objDrawing = new PHPExcel_Worksheet_Drawing();
                    $images = files_save( $thumbHttp . $vals['style_thumb'] );
                    if( $images['path'] ){
                        $tempImage[] = $images['path'];
                        $objDrawing->setPath( $images['path'] );
                        $objDrawing->setWidth(20);
                        $objDrawing->setHeight(60);
                        $objDrawing->setOffsetX(50);
                        $objDrawing->setRotation(20);
                        $objDrawing->setCoordinates("$letter[2]$line");
                        $objDrawing->getShadow()->setVisible(true);
                        $objDrawing->getShadow()->setDirection(50);
                        $objDrawing->setWorksheet($excel->getActiveSheet());
                    }else{
                        $excel->getActiveSheet()->setCellValue("$letter[2]$line", "下载失败");
                        $excel->getActiveSheet()->getCell("$letter[2]$line")->getHyperlink()->setUrl($thumbHttp . $vals['style_thumb']);
                        $excel->getActiveSheet()->getCell("$letter[2]$line")->getHyperlink()->setTooltip("视频地址");
                        $excel->getActiveSheet()->getStyle("$letter[2]$line")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                }else{
                    $excel->getActiveSheet()->setCellValue( "$letter[2]$line" , '---' );
                }
                if( $vals['style_video'] ){
                    $excel->getActiveSheet()->setCellValue("$letter[3]$line", "Video");
                    $excel->getActiveSheet()->getCell("$letter[3]$line")->getHyperlink()->setUrl($vals['style_video']);
                    $excel->getActiveSheet()->getCell("$letter[3]$line")->getHyperlink()->setTooltip("视频地址");
                    $excel->getActiveSheet()->getStyle("$letter[3]$line")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }else{
                    $excel->getActiveSheet()->setCellValue( "$letter[3]$line" , '' );
                }

                $excel->getActiveSheet()->setCellValue( "$letter[4]$line" , $styleMode[ $vals['style_mode'] ] ? $styleMode[ $vals['style_mode'] ] : '---'  );
                $excel->getActiveSheet()->setCellValue( "$letter[5]$line" , intval($vals['style_sort']) );
                $excel->getActiveSheet()->setCellValue( "$letter[6]$line" , intval($vals['style_new']) );
                $excel->getActiveSheet()->setCellValue( "$letter[7]$line" , intval($vals['style_hot']) );
                $excel->getActiveSheet()->setCellValue( "$letter[8]$line" , $vals['factory_no'] );
                $excel->getActiveSheet()->setCellValue( "$letter[9]$line" , intval($vals['stocks']) );
                $excel->getActiveSheet()->setCellValue( "$letter[10]$line" , floatval($vals['MinPrice']) );
                $excel->getActiveSheet()->setCellValue( "$letter[11]$line" , floatval($vals['MaxPrice']) );
                $excel->getActiveSheet()->setCellValue( "$letter[12]$line" , $vals['material'] );
                $excel->getActiveSheet()->setCellValue( "$letter[13]$line" , $vals['GoodsSize'] );
                $excel->getActiveSheet()->setCellValue( "$letter[14]$line" , $vals['StoneType'] );
                $excel->getActiveSheet()->setCellValue( "$letter[15]$line" , floatval($vals['StonePrice']) );
                $excel->getActiveSheet()->setCellValue( "$letter[16]$line" , $vals['StoneTxt'] );
                $excel->getActiveSheet()->setCellValue( "$letter[17]$line" , $vals['goldWeight'] );
                $excel->getActiveSheet()->setCellValue( "$letter[18]$line" , $vals['StoneNum'] );
                $excel->getActiveSheet()->setCellValue( "$letter[19]$line" , $vals['MinStoneWeight'] );
                $excel->getActiveSheet()->setCellValue( "$letter[20]$line" , $vals['DeStoneNum'] );
                $excel->getActiveSheet()->setCellValue( "$letter[21]$line" , $vals['MinDeStoneWeight'] );
                $excel->getActiveSheet()->setCellValue( "$letter[22]$line" , $vals['Color'] );
                $excel->getActiveSheet()->setCellValue( "$letter[23]$line" , $vals['Clarity'] );
                $excel->getActiveSheet()->setCellValue( "$letter[24]$line" , floatval($vals['worksprice']) );
                $excel->getActiveSheet()->setCellValue( "$letter[25]$line" , floatval($vals['price_18']) );
                $excel->getActiveSheet()->setCellValue( "$letter[26]$line" , floatval($vals['price_pt']) );
                $excel->getActiveSheet()->setCellValue( "$letter[27]$line" , floatval($vals['goldWeight_18K']) );
                $excel->getActiveSheet()->setCellValue( "$letter[28]$line" , floatval($vals['goldWeight_pt950']) );
            }
            $bodyLine += count( $database );
        }
    }

    $write = new PHPExcel_Writer_Excel5($excel);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header("Content-Disposition:attachment;filename=buffer.xls");
    header("Content-Transfer-Encoding:binary");
    $write->save('php://output');

    foreach( $tempImage as $imagefiles ){
        is_file( $imagefiles  ) && @unlink( $imagefiles );
    }    function put_file_from_url_content($url, $savefile) {
        // 设置运行时间为无限制
        set_time_limit ( 60 );
        $url = trim ( $url );
        $curl = curl_init ();
        // 设置你需要抓取的URL
        curl_setopt ( $curl, CURLOPT_URL, $url );
        // 设置header
        curl_setopt( $curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt ( $curl, CURLOPT_HEADER, 1 );
        // 设置cURL 参数，要求结果保存到字符串中还是输出到屏幕上。
        curl_setopt ( $curl, CURLOPT_RETURNTRANSFER, 1 );
        // 运行cURL，请求网页
        $response = curl_exec ( $curl );
        $headerSize = curl_getinfo($curl, CURLINFO_HEADER_SIZE);
        $header = substr($response, 0, $headerSize);
        $body = substr($response, $headerSize);
        $error = curl_error($curl);
        // 关闭URL请求
        curl_close ( $curl );
        // 将文件写入获得的数据
        $write = @fopen ( $savefile, "w" );
        if ( $write == false ) { return false; }
        if ( @fwrite ( $write, $body ) == false ) { return false; }
        if ( @fclose ( $write ) == false ) { return false; }
        @fclose($savefile);
        return true;
    }
    function exit_file($url){
        $opts=array( 'http'=>array( 'method'=>'HEAD', 'timeout'=>2 ));
        @ file_get_contents($url,false,stream_context_create($opts));
        if ($http_response_header[0] == 'HTTP/1.1 200 OK') {
            return true;
        } else {
            return false;
        }
    }
    function files_save( $url ){
        if( !exit_file($url) ){ return false; }
        $targetPath = './uploadfiles';
        $tempFiles  = tempnam( sys_get_temp_dir() , 'temp_img');
        $saveState  = put_file_from_url_content( $url , $tempFiles );
        if( $saveState ){
            $info = imageInfo( $tempFiles );
            if( $info ){
                $info['path'] = $tempFiles;
            }
            return $info;
        }
        return false;
    }
    function imageInfo($img) {
        $imageInfo = @ getimagesize( $img );
        if ($imageInfo !== false) {
            $imageType = strtolower(substr(image_type_to_extension($imageInfo[2]), 1));
            $imageSize = filesize($img);
            $info = array(
                "width" => $imageInfo[0],
                "height" => $imageInfo[1],
                "type" => $imageType,
                "size" => $imageSize,
                "mime" => $imageInfo['mime']
            );
            return $info;
        } else {
            return false;
        }
    }