Tag: excel

PHP读取EXCEL文件

一条评论

2010 年 12 月 09 日 at 上午 10:46分类:PHP | WEB开发

这是自己写的一个基于PHPExcel操作excel文件的类
PHPExcel:点击下载
首先是读取excel文件的操作:

<?php
error_reporting(E_ALL);

date_default_timezone_set ('Asia/Shanghai');

/** PHPExcel 类文件*/
require_once 'PHPExcel.php';
/*
 * 输出到页面上的EXCEL
 */ 
class Excel
{   
   private $cellArray = array(
                        1=>'A', 2=>'B', 3=>'C', 4=>'D', 5=>'E',
                        6=>'F', 7=>'G', 8=>'H', 9=>'I',10=>'J',
                        11=>'K',12=>'L',13=>'M',14=>'N',15=>'O',
                        16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',
                        21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',
                        26=>'Z',
                        27=>'AA', 28=>'AB', 29=>'AC', 30=>'AD', 31=>'AE',
                        32=>'AF', 33=>'AG', 34=>'AH', 35=>'AI',36=>'AJ',
                        37=>'AK',38=>'AL',39=>'AM',40=>'AN',41=>'AO',
                        42=>'AP',43=>'AQ',44=>'AR',45=>'AS',46=>'AT',
                        47=>'AU',48=>'AV',49=>'AW',50=>'AX',51=>'AY',
                        52=>'AZ', 53=>'BA', 54=>'BB', 55=>'BC', 56=>'BD', 57=>'BE',
                        58=>'BF', 59=>'BG', 60=>'BH', 61=>'BI', 62=>'BJ', 63=>'BK', 64=>'BL')
/**
     * 读取Excel
     * @param  Excel文件名称 
     * @param  返回数据的键名 
     * @return data   
     */ 
    function read($fileName,$rows='')
    {   
        //$fileName      = "ExcelFile/MyExcel.xlsx";
        $objReader     = new PHPExcel_Reader_Excel2007();
        $objPHPExcel   = $objReader->load("$fileName");
        $sheet         = $objPHPExcel->getActiveSheet();
        $highestRow    = $sheet->getHighestRow();           // 取得总行数  
        $highestColumn = $sheet->getHighestColumn();       // 取得总列数D
        
        $rowMin = array_search($highestColumn,$this->cellArray); //根据返回的总列数D 返回对用的KEY
        
        for($i = 2;$i<=$highestRow;$i++)                   //循环总行数
        {   
            for($a = 1;$a<=$rowMin;$a++)                   //循环总列数 
            {     
                 if(empty($rows))
                 {
                    $data[$i][$a] = $sheet->getCell($this->cellArray[$a].$i)->getValue();      
                 }
                 else
                 {
                    $data[$i][$rows[$a-1]] = $sheet->getCell($this->cellArray[$a].$i)->getValue();         
                 }
                    
            }  
        }
        return $data;
    }
}

使用方法:

<?php
 require_once "Excel.php";
 $excel = new Excel();
 $data = $excel->read('MyExcel.xlsx');
 print_r($data);          //返回的是一个二维数组

php生成excel文件的操作

没有评论

2010 年 12 月 08 日 at 下午 4:52分类:PHP | WEB开发

php生成excel文件的操作的原理文件代码

<?
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=test.xls");
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
?> 

用php生成excel文件的操作的原理备份一个数据表,生成excel文件

<?php
$DB_Server = "localhost";
$DB_Username = "mydowns";
$DB_Password = "";
$DB_DBName = "mydowns";
$DB_TBLName = "user";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn@#t connect.");
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn@#t select database.");
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=mydowns.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
$now_date = date('Y-m-d H:i');
$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";
$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn@#t select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());
echo ("$title\n");
$sep = "\t";
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
echo ("\n");
$i = 0;
while($row = mysql_fetch_row($result))
{
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
echo (trim($schema_insert));
echo "\n";
$i++;
}
return (true);
?>

将导出的excel文件改成csv文件,然后执行以下操作 就可以导入数据

<?php      
$fname = $_FILES['MyFile']['name'];      
$do = copy($_FILES['MyFile']['tmp_name'],$fname);      
if ($do)       
{       
    echo"导入数据成功<br>";       
} else {      
    echo "";       
}      
?>      
<form ENCTYPE="multipart/form-data" ACTION="<?php echo"".$PHP_SELF.""; ?>" METHOD="POST">       
    <p>导入CVS数据  <input NAME="MyFile" TYPE="file"> <input VALUE="提交" TYPE="submit">       
    </p>      
</form>       
<?      
error_reporting(0);      
//导入CSV格式的文件      
$connect=mysql_connect("localhost","a0530093319","123456") or die("could not connect to database");      
mysql_select_db("a0530093319",$connect) or die (mysql_error());      
$fname = $_FILES['MyFile']['name'];      
$handle=fopen("$fname","r");      
while($data=fgetcsv($handle,10000,","))                 
{      
    $q="insert into test (code,name,date) values ('$data[0]','$data[1]','$data[2]')";      
    mysql_query($q) or die (mysql_error());      
                                           
}      
fclose($handle);      
?>