PHP Import Export Excel table Picture (GO)

When I write Excel, I used the Pear library, also used the pack to press the head, also those who use smarty, such as the simple substitution of XML also used, CSV, not to mention. Oh. (COM does not speak, this can be read too much, I also wrote the use of WPS, such as word generation and other articles)
But in the reading, only used one, specifically what forget, to go back to the code.
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);
Several methods are listed below. Include PHP export excel,php import excel,php generate Excel instance

PHP Export Excel

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:

PHP code

Copy the code code as follows:
GetProperties ()->setcreator ("Maarten Balliauw");
$objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw");
$objPHPExcel->getproperties ()->settitle ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setdescrīption ("Test document for Office-XLSX, generated using PHP classes.");
$objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP");
$objPHPExcel->getproperties ()->setcategory ("Test result file");

ADD some data
echo Date (' H:i:s '). "Add some data\n";
$objPHPExcel->setactivesheetindex (0);
$objPHPExcel->getactivesheet ()->setcellvalue (' a1′, ' Hello ');
$objPHPExcel->getactivesheet ()->setcellvalue (' b2′, ' world! ');
$objPHPExcel->getactivesheet ()->setcellvalue (' c1′, ' Hello ');
$objPHPExcel->getactivesheet ()->setcellvalue (' d2′, ' world! ');

Rename Sheet
echo Date (' H:i:s '). "Rename sheet\n";
$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 File
echo Date (' H:i:s '). "Write to Excel2007 format\n";
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);
$objWriter->save (Str_replace ('. php ', '. xlsx ', __file__));

Echo Done
echo Date (' H:i:s '). "Done writing file.\r\n";


2, the use of Pear Spreadsheet_excel_writer class
: Http://pear.php.net/package/Spreadsheet_Excel_Writer
This class relies on the Ole,:http://pear.php.net/package/ole
It is important to note that the exported Excel file format is older, and the modified save will prompt you to convert to an updated format.
But you can set the format, very powerful.
PHP code

Copy the code code as follows:
Send (' Test.xls ');

Creating a worksheet
$worksheet =& $workbook->addworksheet (' My first worksheet ');

The actual data
$worksheet->write (0, 0, ' Name ');
$worksheet->write (0, 1, ' age ');
$worksheet->write (1, 0, ' John Smith ');
$worksheet->write (1, 1, 30);
$worksheet->write (2, 0, ' Johann Schmidt ');
$worksheet->write (2, 1, 31);
$worksheet->write (3, 0, ' Juan Herrera ');
$worksheet->write (3, 1, 32);

Let ' s send the file
$workbook->close ();
?>

3: Generate an XML or HTML file that conforms to the Excel specification using Smarty
Support format, very perfect export scheme. However, the nature of the derivative is the XML file, if used to import it needs to be processed separately.
For more information, please see the home of the help. Another article: http://www.bkjia.com/jingyan/excelsmarty.html
It is important to note that if the number of table rows exported is uncertain, it is best to delete "ss:expandedcolumncount=" 5″ss:expandedrowcount= "21″" in the template.
4, using the Pack function to print the analog Excel format of the segmentation symbol, which is closer to the standard format of Excel, with office2003 modified to save, will not pop up hints, recommend this method.
The disadvantage is no format.

PHP code


Copy the code code as follows:
Send Header
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/octet-stream");
Header ("Content-type:application/download");;
Header ("Content-disposition:attachment;filename=test.xls");
Header ("Content-transfer-encoding:binary");
XLS Data Cell

Xlsbof ();
Xlswritelabel (1,0, "My Excel line One");
Xlswritelabel (2,0, "My Excel Line:");
Xlswritelabel (2,1, "Hello everybody");

Xlseof ();

function Xlsbof () {
Echo Pack ("Ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
Return
}
function xlseof () {
Echo Pack ("ss", 0x0a, 0x00);
Return
}
function Xlswritenumber ($Row, $Col, $Value) {
Echo Pack ("Sssss", 0x203, $Row, $Col, 0x0);
Echo Pack ("D", $Value);
Return
}
function Xlswritelabel ($Row, $Col, $Value) {
$L = strlen ($Value);
Echo Pack ("Ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
Echo $Value;
Return
}
?>


However, the author in the 64-bit Linux system used in the failure, the segmentation symbol has all become garbled.

5, the use of tabs, newline character method tab "\ T" user split the column in the same row, line break "\t\n" can open the next line.

Copy the code code as follows:
Header ("Content-type:application/vnd.ms-execl");
Header ("content-disposition:attachment; Filename=myexcel.xls ");
Header ("Pragma:no-cache");
Header ("Expires:0″");
/*first line*/
echo "Hello". " \ t ";
echo "World". " \ t ";
echo "\t\n";

/*start of second line*/
echo "This was second line". " \ t ";
echo "Hi,pretty girl". " \ t ";
echo "\t\n";
?>

6. Using COM to export Excel tables
If your PHP can open a COM module, you can use it to export Excel files


PHP code


Copy the code code as follows:

$filename = "C:/spreadhseet/test.xls";

$sheet 1 = 1;

$sheet 2 = "Sheet2″;

$excel _app = new COM ("Excel.Application") or Die ("Do not Connect");

Print "Application name: {$excel _app->application->value}\n";

Print "Loaded version: {$excel _app->application->version}\n";

$Workbook = $excel _app->workbooks->open ("$filename") or Die ("Do not Open $filename $Workbook");

$Worksheet = $Workbook->worksheets ($sheet 1);

$Worksheet->activate;

$excel _cell = $Worksheet->range ("C4″");

$excel _cell->activate;

$excel _result = $excel _cell->value;

Print "$excel _result\n";

$Worksheet = $Workbook->worksheets ($sheet 2);

$Worksheet->activate;

$excel _cell = $Worksheet->range ("C4″");

$excel _cell->activate;

$excel _result = $excel _cell->value;

Print "$excel _result\n";

#To Close all instances of Excel:

$Workbook->close;

Unset ($Worksheet);

Unset ($Workbook);

$excel _app->workbooks->close ();

$excel _app->quit ();

unset ($excel _app);

?>

7.PHP through the Phpexcel class to export Excel, while the phpexcel did some streamlining, basically can meet the data export Excel function.
Files: http://pan.baidu.com/share/link?shareid=1345826295&uk=2332350821
The code is as follows:


Copy the code code as follows:
Load Phpexcel class
Require './phpexcel/phpexcel.php ';

Create an instance of an Excel object
$objPHPExcel = new Phpexcel ();

Set document basic Properties
$objProps = $objPHPExcel->getproperties ();
$objProps->setcreator ("Lao Mao");
$objProps->setlastmodifiedby ("Lao Mao");
$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
$objPHPExcel->setactivesheetindex (0);

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

Set cell contents
The data here can be read from the database and then processed in a loop
$objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' A1 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' A2 ', ' A2 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' A3 ', ' A3 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' A4 ', ' A4 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' A5 ', ' A5 ');

$objPHPExcel->getactivesheet ()->setcellvalue (' B1 ', ' B1 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' B2 ', ' B2 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' B3 ', ' B3 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' B4 ', ' b4 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' B5 ', ' B5 ');

$objPHPExcel->getactivesheet ()->setcellvalue (' C1 ', ' C1 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' C2 ', ' C2 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' C3 ', ' C3 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' C4 ', ' C4 ');
$objPHPExcel->getactivesheet ()->setcellvalue (' C5 ', ' C5 ');

Output document
$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);

Headers header information is set and output to browser
Header (' Content-type:application/vnd.ms-excel ');
Header ("content-disposition:attachment; Filename=demo.xls ");
Header (' cache-control:max-age=0 ');
$objWriter->save (' php://output ');

Save to a location
$objWriter->save (DirName (__file__). '/demo.xls ');


PHP Import into Excel

1: Still use Phpexcel, official website: http://www.codeplex.com/PHPExcel.
2: Using Php-excelreader,: http://sourceforge.net/projects/phpexcelreader Example:


PHP code

Copy the code code as follows:
? 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"; }?>

Here's how to import Excel through PHP:

Files: http://pan.baidu.com/share/link?shareid=1345826295&uk=2332350821
Code:


Copy the code code as follows:
Load Phpexcel class
Include (DirName (__file__). ' /phpexcel/phpexcel.php ');

$OBJ = new Phpexcel_reader_excel5 ();
$OBJ->setreaddataonly (TRUE);

Read Demo.xls file
$phpExcel = $Obj->load (dirname (__file__). /output.xls ');

Get Current Activity sheet
$objWorksheet = $phpExcel->getactivesheet ();

Get row Count
$highestRow = $objWorksheet->gethighestrow ();

Get Number of columns
$highestColumn = $objWorksheet->gethighestcolumn ();
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);

Cyclic output data
$data = Array ();
for ($row = 1; $row <= $highestRow; + + $row) {
for ($col = 0; $col < $highestColumnIndex; + + $col) {
$val = $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ();
$data [$row] [$col] = Trim ($val);
}
}

Echo '

‘;
Print_r ($data);
Echo '

‘;


PHP build excel| easy to use powerful PHP Excel class library


Magento's order export Excel function, found this PHP Excel class: Phpexcel.

Phpexcel is a powerful MS Office Excel document Generation class library based on Microsoft's OpenXML, support for general xls,excel2007,pdf,csv,html, etc.

Official website: http://phpexcel.codeplex.com/


Compare the big bar of a class library, the official latest version has jumped to 7. More than M big. Found a civil lite version of 1. Multi-M.

Here are some tips and hints for how to use this PHP Excel class library. From the Internet.


Copy the code code as follows:
To set the include path for the Phpexcel class library
Set_include_path ('. '). Path_separator.
' D:\Zeal\PHP_LIBS '. Path_separator.
Get_include_path ());

/**
* If you use Excel5, the content of the output should be GBK encoded.
*/
Require_once ' phpexcel.php ';

Uncomment
Require_once ' phpexcel/writer/excel5.php '; For other low version xls
Or
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 contents '); 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 ("Expires:mon, Jul 1997 05:00:00 GMT");
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 ');


This PHP Excel class library is now the best to see the features.
Transfer from http://www.bcty365.com/content-10-1031-1.html

PHP Import Export Excel table Picture (GO)