Read
<?php
Header ("Content-type:text/html;charset=utf-8");
Include ' classes/phpexcel.php ';
Include ' classes/phpexcel/iofactory.php ';
function Readxls ($file, $type) {
$xlsReader = Phpexcel_iofactory::createreader ($type);
$xlsReader->setreaddataonly (TRUE);
$xlsReader->setloadsheetsonly (TRUE);
$sheets = $xlsReader->load ($file);
$content = $sheets->getsheet (0)->toarray (); Read the first worksheet (note number starting from 0) If reading multiple can do a loop 0,1,2,3 ....
Get the two-dimensional array, where each decimal group is a row of Excel table content containing data for each column of this row
return $content;
}
$type = ' Excel2007 '; Set the type of Excel to resolve Excel5 (2003 or later) or Excel2007
$type = ' Excel5 ';
$content = Readxls (' Data.xls ', $type);
Echo ' <pre> ';
Var_dump ($content);
Echo ' </pre> ';
Output Write
<?php
Header (' Content-type:text/html;charset=utf-8 ');
Include ' phpexcel_1.8.0_doc/classes/phpexcel.php ';
Include ' phpexcel_1.8.0_doc/classes/phpexcel/iofactory.php ';
Set_time_limit (0);
function Write_xls ($data =array (), $title =array (), $filename = ' report ') {
$objPHPExcel = new Phpexcel ();
$objPHPExcel->setactivesheetindex (0);
$cols = ' abcdefghijklmnopqrstuvwxyz ';
Set Title
For ($i =0, $length =count ($title), $i < $length; $i + +) {
$objPHPExcel->getactivesheet ()->setcellvalue ($cols {$i}. ' 1 ', $title [$i]);
}
Set the heading style (you can decide whether you want it as required)
$titleCount = count ($title);
$r = $cols {0}. ' 1 ';
$c = $cols {$titleCount}. ' 1 ';
$objPHPExcel->getactivesheet ()->getstyle ("$r: $c")->applyfromarray (
Array
' Font ' = = Array (
' Bold ' = True
// ),
' Alignment ' = Array (
' Horizontal ' = Phpexcel_style_alignment::horizontal_right,
// ),
' Borders ' = Array (
' Top ' = Array (
' Style ' = Phpexcel_style_border::border_thin
// )
// ),
' Fill ' = = Array (
' Type ' = Phpexcel_style_fill::fill_gradient_linear,
' Rotation ' = 90,
' StartColor ' = Array (
' Argb ' = ' ffa0a0a0 '
// ),
' EndColor ' = Array (
' Argb ' = ' FFFFFFFF '
// )
// )
// )
// );
$i = 0;
foreach ($data as $d) {//here with foreach, supports associative arrays and numeric indexed arrays
$j = 0;
foreach ($d as $v) {//here with foreach, supports associative arrays and numeric indexed arrays
$objPHPExcel->getactivesheet ()->setcellvalue ($cols {$j}. ( $i +2), $v);
$j + +;
}
$i + +;
}
Generate 2003excel format xls files for direct download
/* Header (' content-type:application/vnd.ms-excel ');
Header (' Content-disposition:attachment;filename= '. $filename. '. XLS "');
Header (' cache-control:max-age=0 ');
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');
$objWriter->save (' php://output '); * *
Generate 2007excel formatted XLS file to the specified path
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel2007 ');
$objWriter->save (' a.xlsx ');
}
$arr =array ();
mysql_connect (' localhost ', ' root ', ');
mysql_query (' Set names UTF8 ');
mysql_query (' use test ');
$sql = ' Select Id,name,pid from shop where ID <= 1000 ';
$res =mysql_query ($sql);
while ($ret =mysql_fetch_assoc ($res))
{
$arr []= $ret;
}
$arr _type=array (' Product ID ', ' name ', ' brand ID ');
Write_xls ($arr, $arr _type, ' report ');
Phpexcel Read Output operation