< go >.php export Excel (multiple methods)

Basically the exported files are divided into two types:
1: Excel format, this is not in the traditional sense of Excel file, just because Excel compatibility ability, can be opened correctly. After you modify this file and then save it, you will usually be prompted whether you want to convert to an Excel file.
Pros: Simple.
Disadvantage: It is difficult to generate a format, if you need to write the appropriate program for the import.
The 2:excel format, which corresponds to class Excel, produces files that are closer to the real Excel format.

If garbled in the export of Chinese, you can try to convert the string to gb2312, for example, the following $yourstr from Utf-8 to gb2312:
$YOURSTR = mb_convert_encoding ("gb2312", "UTF-8", $yourStr);

First, PHP import Excel

1: Still use Phpexcel, official website: http://www.codeplex.com/PHPExcel.

2: Using Php-excelreader,: Http://sourceforge.net/projects/phpexcelreader
Example:
<?php
Require_once ' excel/reader.php ';

Excelfile ($filename, $encoding);
$data = new Spreadsheet_excel_reader ();

Set output Encoding.
$data->setoutputencoding (' UTF8 ');

$data->read (' Jxlrwtest.xls ');


Error_reporting (e_all ^ e_notice);

for ($i = 1; $i <= $data->sheets[0][' numrows '); $i + +) {
for ($j = 1; $j <= $data->sheets[0][' numcols '); $j + +) {
echo "". $data->sheets[0][' cells ' [$i] [$j]. "", ";
}
echo "\ n";
}

?>

Summary PHP Export Excel PHP import Excel phpexcel instructions phpexcel user manual

Method One: Features, simple, worry, tab "\ T" user split the column in the same row, line break "\t\n" can open the next line, but also can automatically recognize the date format, currency format, and so on!

<?php
Header ("Content-type:application/vnd.ms-excel");
Header ("Content-disposition:attachment;filename=test_data.xls");
The output reads as follows:
echo "Name". " \ t ";
echo "Age". " \ t ";
echo "Education". " \ t ";
echo "\ n";
echo "Zhang San". " \ t ";
echo "25". " \ t ";
echo "Undergraduate". " \ t ";
?>
Dynamic version
<?
$sql = "SELECT * from". $ecs->table (' member '). " ";
$classres = $db->execute ($sql);
Header ("Content-type:application/vnd.ms-excel");
Header ("content-disposition:attachment; Filename=test_data.xls ");
Echo ' ID '. chr (9);
Echo ' Txtpeoclientcode '. chr (9);
Echo ' Jcompany '. chr (9);
Echo ' Fcompany '. chr (9);
Echo ' Ecompany '. chr (9);
Echo ' Rcompany '. chr (9);
Echo ' txtaddress '. chr (9);
Echo ' Txtcompanytel '. chr (9);
Echo ' Txtfax '. chr (9);
Echo ' Txtpostcode '. chr (9);
Echo Chr (13);
while ($classrow = $classres->fetchrow ())
{
Output to XLS
echo $classrow [' ID '].chr (9);
echo $classrow [' Txtpeoclientcode '].CHR (9);
echo $classrow [' Jcompany '].CHR (9);
echo $classrow [' Fcompany '].CHR (9);
echo $classrow [' Ecompany '].CHR (9);
echo $classrow [' Rcompany '].CHR (9);
echo $classrow [' Txtaddress '].CHR (9);
echo $classrow [' Txtcompanytel '].CHR (9);
echo $classrow [' Txtfax '].CHR (9);
echo $classrow [' Txtpostcode '].CHR (9);
Echo Chr (13);
}

Now with the Php-excel (http://code.google.com/p/php-excel/downloads/list), the code is simple:

Require (DirName (__file__). "./class-excel-xml.inc.php");
$sql = "SELECT * from". $ecs->table (' member '). " ";
$classres = $db->execute ($sql);

while ($classrow = $classres->fetchrow ())
{
$doc [] = Array ($classrow [' Txtpeoclientcode '], $classrow [' Jcompany '], $classrow [' Txtbillto ']);
}


$xls = new Excel_xml;
$xls->addarray ($doc);
$xls->generatexml ("mytest");

I use the utf-8, the result has garbled problem, put class-excel-xml.inc.php Utf8_encode ($v) replaced $v there is no garbled problem.


1: First recommendation Matchless Phpexcel, official website: http://www.codeplex.com/PHPExcel
Import and export, you can export office2007 format, and compatible with 2003.
The downloaded package has documentation and examples that you can study on your own.
To copy the paragraph example out:

Http://www.codeplex.com/PHPExcel

<?php
Ini_set ("Display_errors", 1);//Whether an error message is displayed
Ini_set (' include_path ', Ini_get (' include_path '). ';D: \ \php\\phpexcel 1.6.6\\tests\\classes\\ ');//Set this page to include a path

Include "classes/phpexcel.php";
Include "classes/phpexcel/writer/excel5.php";

Create an Excel
$objPHPExcel = new Phpexcel ();
$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);

$objWriter->save ("Xxx.xls");
?>

--------------------------------------------------------------------------------------
<?
To set the include path for the Phpexcel class library
Set_include_path ('. '). Path_separator. ' D:\workspace\biznaligy_eh\dev_src\includes\PHPExcel '. Path_separator. Get_include_path ());


Require_once ' phpexcel.php ';
Require_once ' phpexcel/writer/excel5.php '; For other low version xls

Require_once ' phpexcel/writer/excel2007.php '; For excel-2007 format

Create a Processing object instance
$objExcel = new Phpexcel ();

Create a file format to write to an object instance, uncomment
$objWriter = new Phpexcel_writer_excel5 ($objExcel); For other version formats
Or
$objWriter = new phpexcel_writer_excel2007 ($objExcel); For 2007 formats
$objWriter->setoffice2003compatibility (TRUE);


Set document basic Properties
$objProps = $objExcel->getproperties ();
$objProps->setcreator ("Zeal Li");
$objProps->setlastmodifiedby ("Zeal Li");
$objProps->settitle ("Office XLS Test Document");
$objProps->setsubject ("Office XLS Test Document, Demo");
$objProps->setdescription ("Test document, generated by Phpexcel.");
$objProps->setkeywords ("Office Excel Phpexcel");
$objProps->setcategory ("Test");
Sets the current sheet index for subsequent content operations.
It is generally only necessary to display a call when multiple sheet are used.
By default, Phpexcel automatically creates the first sheet to be set sheetindex=0
$objExcel->setactivesheetindex (0);

$objActSheet = $objExcel->getactivesheet ();

Sets the name of the currently active sheet
$objActSheet->settitle (' Test sheet ');


Set cell contents

Automatically determine cell content type by phpexcel based on incoming content
$objActSheet->setcellvalue (' A1 ', ' string content '); String content
$objActSheet->setcellvalue (' A2 ', 26); Numerical
$objActSheet->setcellvalue (' A3 ', true); Boolean value
$objActSheet->setcellvalue (' A4 ', ' =sum (A2:A2) '); Formula

Explicitly specifying a content type
$objActSheet->setcellvalueexplicit (' A5 ', ' 847475847857487584 ', phpexcel_cell_datatype::type_string);

Merge cells
$objActSheet->mergecells (' b1:c22 ');

Separating cells
$objActSheet->unmergecells (' b1:c22 ');

Set cell style

Set width
$objActSheet->getcolumndimension (' B ')->setautosize (true);
$objActSheet->getcolumndimension (' A ')->setwidth (30);

$objStyleA 5 = $objActSheet->getstyle (' A5 ');

Sets the number format for the contents of the cell.

If you use Phpexcel_writer_excel5 to generate content,
It is important to note that the const variable in the Phpexcel_style_numberformat class defines the
In various custom formats, other types can be used normally, but when the Setformatcode
For Format_number, the actual effect was not formatted as "0". Need
Modify the GETXF ($style) method in the source code of the Phpexcel_writer_excel5_format class,
Add one in front of if ($this->_biff_version = = 0x0500) {(Near line No. 363)
Line code:
if ($ifmt = = = ' 0 ') $ifmt = 1;

Format as Phpexcel_style_numberformat::format_number to avoid some large numbers
is displayed using scientific notation, with the following Setautosize method to let the contents of each line
Are all displayed according to the original content.
$objStyleA 5->getnumberformat ()->setformatcode (Phpexcel_style_numberformat::format_number);

Set font
$objFontA 5 = $objStyleA 5->getfont ();
$objFontA 5->setname (' Courier New ');
$objFontA 5->setsize (10);
$objFontA 5->setbold (TRUE);
$objFontA 5->setunderline (Phpexcel_style_font::underline_single);
$objFontA 5->getcolor ()->setargb (' FF999999 ');

Set alignment
$objAlignA 5 = $objStyleA 5->getalignment ();
$objAlignA 5->sethorizontal (phpexcel_style_alignment::horizontal_right);
$objAlignA 5->setvertical (Phpexcel_style_alignment::vertical_center);

Set border
$objBorderA 5 = $objStyleA 5->getborders ();
$objBorderA 5->gettop ()->setborderstyle (Phpexcel_style_border::border_thin);
$objBorderA 5->gettop ()->getcolor ()->setargb (' FFFF0000 '); Color
$objBorderA 5->getbottom ()->setborderstyle (Phpexcel_style_border::border_thin);
$objBorderA 5->getleft ()->setborderstyle (Phpexcel_style_border::border_thin);
$objBorderA 5->getright ()->setborderstyle (Phpexcel_style_border::border_thin);

Set Fill Color
$objFillA 5 = $objStyleA 5->getfill ();
$objFillA 5->setfilltype (phpexcel_style_fill::fill_solid);
$objFillA 5->getstartcolor ()->setargb (' ffeeeeee ');

Copies the style information from the specified cell.
$objActSheet->duplicatestyle ($objStyleA 5, ' b1:c22 ');

Add a picture
$objDrawing = new phpexcel_worksheet_drawing ();
$objDrawing->setname (' zealimg ');
$objDrawing->setdescription (' Image inserted by Zeal ');
$objDrawing->setpath ('./zeali.net.logo.gif ');
$objDrawing->setheight (36);
$objDrawing->setcoordinates (' C23 ');
$objDrawing->setoffsetx (10);
$objDrawing->setrotation (15);
$objDrawing->getshadow ()->setvisible (true);
$objDrawing->getshadow ()->setdirection (36);
$objDrawing->setworksheet ($objActSheet);

Add a new worksheet
$objExcel->createsheet ();
$objExcel->getsheet (1)->settitle (' Test 2 ');

Protect cells
$objExcel->getsheet (1)->getprotection ()->setsheet (true);
$objExcel->getsheet (1)->protectcells (' A1:c22 ', ' phpexcel ');

Output content

$outputFileName = "Output.xls";
to a file
$objWriter->save ($outputFileName);
Or
to the browser
Header ("Content-type:application/force-download");
Header ("Content-type:application/octet-stream");
Header ("Content-type:application/download");
Header (' Content-disposition:inline;filename= '. $outputFileName. ' ');
Header ("Content-transfer-encoding:binary");
Header ("last-modified:".) Gmdate ("D, D M Y h:i:s"). "GMT");
Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");
Header ("Pragma:no-cache");
$objWriter->save (' php://output ');
?>

< go >.php export Excel (multiple methods)