PHP Import Export Excel code _php tips

I. Import

The import needs to use the component that can read Excel, also has the good component on the net, here share I use: Download extract code: Vxyn. (Note that two files have a reference relationship)

<?php
//Incoming file name
function import_to_db ($filename) {
  require_once ' reader.php ' of Excel to be imported;
  $data = new Spreadsheet_excel_reader ();   Create an object that reads Excel
  $data->setoutputencoding (' utf-8 ');  Sets the character encoding output after reading the Excel content
  $data->read ("data/excel/{$filename}.xls");
  $db = mysql_connect (' localhost ', ' username ', ' password ') or Die ("could not connect to database."); Connect database  
  mysql_query ("Set names ' Uft8 '");//Output Chinese  
  mysql_select_db (' database name ');//Select Database  
  error_reporting (e_ all ^ e_notice);
  for ($i = 1; $i <= $data->sheets[0][' numrows '; $i + +) {
    echo $data->sheets[0][' cells '] [$i] [Number of columns];
    Here you can insert the value of the corresponding column in each row into the database, such as:/
    *
    $sql = Insert Table name values (corresponding ...);
    mysql_query ($sql);
    Can be added error judgement/ 
  } 
?>    

In summary, the insert operation can be done by reading the value of the corresponding column in each row in the table $data->sheets[0][the row [column].

Two. Export

Exports can easily export a table file using the MIME protocol without relying on any component. You can export Excel by setting the header in the following format while the browser downloads

Header (' content-type:text/html; Charset=utf-8 ');
Header ("Content-type:application/vnd.ms-excel;charset=utf-8"); APPLICATION/VND.MS-EXCEL Specifies output
header for Excel format ("content-disposition:filename= table file name. xls");//output table name

The complete code is as follows:

<?php 
header (' content-type:text/html; Charset=utf-8 ');
Header ("Content-type:application/vnd.ms-excel;charset=utf-8"); 
Header ("content-disposition:filename= table file name. xls");
$conn = mysql_connect ("localhost", "root", "Database Password") or Die ("Cannot connect to the database");
mysql_select_db ("Database name", $conn);
mysql_query ("Set names ' UTF-8 '");
$sql = "SELECT * from table name where condition";

$result =mysql_query ($sql);
echo "Table Head 1\t header 2\t header 3\n";
while ($row =mysql_fetch_array ($result)) {
  echo $row [0]. " \ t ". $row [1]." \ t ". $row [2]." \ n ";
}
? >

In fact, \ t is a change, \ n is a line break. Set up a link to this PHP file in a Web page, and the browser will automatically save the stream as an Excel file when clicked.

Phpexcel is a PHP class library that is used to manipulate office Excel documents, based on Microsoft's OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats

Export class

Phpexcel class to export Excel, while doing some streamlining of phpexcel, can basically meet the function of data export Excel

The code is as follows:

<?php//loading into Phpexcel class require './phpexcel/phpexcel.php ';
Create an instance of an Excel object $objPHPExcel = new Phpexcel ();
Set the basic properties of the document $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 calls when multiple sheet are used.
By default, Phpexcel automatically creates the first sheet set sheetindex=0 $objPHPExcel->setactivesheetindex (0);
Sets the name of the current active sheet $objActSheet = $objPHPExcel->getactivesheet ();
$objActSheet->settitle (' Test sheet ');
Set cell content www.jb51.net//The data here can be read from the database and then recycled $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);
Set headers header information 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 ');

Ways to import Excel:

Code:

<?php
//Load Phpexcel class
include (DirName (__file__). /phpexcel/phpexcel.php ');
$OBJ = new Phpexcel_reader_excel5 ();
$OBJ->setreaddataonly (true);
Read the Demo.xls file
$phpExcel = $Obj->load (__file__). ' /output.xls ');
Gets the current activity sheet
$objWorksheet = $phpExcel->getactivesheet ();
Gets the number of rows
$highestRow = $objWorksheet->gethighestrow ();
Gets the number of columns
$highestColumn = $objWorksheet->gethighestcolumn ();
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);
Cyclic output data www.jb51.net
$data = Array ();
for ($row = 1; $row <= $highestRow + + $row) {for
 ($col = 0; $col < $highestColumnIndex; + + $col) {
 $val = $o Bjworksheet->getcellbycolumnandrow ($col, $row)->getvalue ();
 $data [$row] [$col] = Trim ($val)
 ;
}
Echo ' <pre> ';
Print_r ($data);
Echo ' </pre> ';

But this approach is flawed, the old version of Excel has a data ceiling, up to 65536 rows of data, then we can not use Excel to achieve large data export, but I do not expect a few have so my data, of course, if you can consider using a CSV to operate

The above mentioned is the entire content of this article, I hope you can enjoy.