Use PHPExcel in php to Operate excel (xls) files,

Source: Internet
Author: User

Use PHPExcel in php to Operate excel (xls) files,

Reading Chinese xls, csv files will have problems, find the information online, found PHPExcel class library easy to use, official website address: http://phpexcel.codeplex.com/

1. Read the content of the xls file

The Code is as follows: Copy code

<? Php
// Write content to the xls file
Error_reporting (E_ALL );
Ini_set ('display _ errors ', TRUE );
Include 'classes/PHPExcel. php ';
Include 'classes/PHPExcel/IOFactory. php ';
// $ Data: the body of the xls file
// $ Title: The content title of the xls file
// $ Filename: exported file name
// $ Data and $ title must be UTF-8; otherwise, FALSE values are written.
Function write_xls ($ data = array (), $ title = array (), $ filename = 'report '){
$ ObjPHPExcel = new PHPExcel ();
// Set document attributes. garbled characters are generated when Chinese characters are set...
// $ ObjPHPExcel-> getProperties ()-> setCreator ("yunshu ")
//-> SetLastModifiedBy ("yunshu ")
//-> SetTitle ("Product URL export ")
//-> SetSubject ("Product URL export ")
//-> SetDescription ("Product URL export ")
//-> SetKeywords ("Product URL export ");
$ ObjPHPExcel-> setActiveSheetIndex (0 );

$ Cols = 'abcdefghijklmnopqrstuvwxy ';
// Set www.111cn.net title
For ($ I = 0, $ length = count ($ title); $ I <$ length; $ I ++ ){
// Echo $ cols {$ I}. '1 ';
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ I}. '1', $ title [$ I]);
}
// Set the title Style
$ 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 (
'Arg' => 'ffa0a0'
),
'Endcolor' => array (
'Arg' => 'ffffffff'
)
)
)
);

$ I = 0;
Foreach ($ data as $ d) {// use foreach here. It supports associating arrays and Numerical index arrays.
$ J = 0;
Foreach ($ d as $ v) {// use foreach here. It supports associating arrays and Numerical index arrays.
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ j}. ($ I + 2), $ v );
$ J ++;
}
$ I ++;
}
// Generate an xls file in 2003excel format
Header ('content-Type: application/vnd. ms-excel ');
Header ('content-Disposition: attachment; filename = "'.w.filename.'.xls "');
Header ('cache-Control: max-age = 0 ');

$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save ('php: // output ');
}
$ Array = array (
Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '),
Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '),
Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '),
Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '),
Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '),
);
Write_xls ($ array, array ('item id', 'Supplier name', 'brand', 'item name', 'url'), 'report ');

?>

2. Write content to the xls file

The Code is as follows: Copy code
<? Php
// Obtain database data (mysqli preprocessing)
$ Config = array (
'Db _ type' => 'mysql ',
'Db _ host' => 'localhost ',
'Db _ name' => 'test ',
'Db _ user' => 'root ',
'Db _ pwd' => 'root ',
'Db _ port' => '123 ',
);
Function getProductIdByName ($ name ){
Global $ config;
$ Id = false;

$ Mysqli = new mysqli ($ config ['db _ host'], $ config ['db _ user'], $ config ['db _ pwd'], $ config ['db _ name']);
If (mysqli_connect_error () {// compatible with <php5.2.9 OO way: $ mysqli-> connect_error
Die ("connection failed, error code:". mysqli_connect_errno (). "error message:". mysqli_connect_error ());
}
// Set the database connection encoding. Do not forget to set it.
$ Mysqli-> set_charset ("gbk ");
// The encoding of Chinese characters must be consistent with that of the database. If no encoding is set, the result is null.
$ Name = iconv ("UTF-8", "gbk // IGNORE", $ name );
If ($ mysqli_stmt = $ mysqli-> prepare ("select id from 137_product where name like? ")){
$ Mysqli_stmt-> bind_param ("s", $ name );
$ Mysqli_stmt-> execute ();
$ Mysqli_stmt-> bind_result ($ id );
$ Mysqli_stmt-> fetch ();
$ Mysqli_stmt-> close ();
}
$ Mysqli-> close ();
Return $ id; // obtain the gbk code (same as the database code)
}
$ Id = getProductIdByName ('% Chennai bathroom inner toilet seat % ');
Var_dump ($ id );
?>

OK...

 

Related content
  • 2014.05.28PHPEXCEL import an excel table to generate an array
  • Example of 2014.04.16thinkphp calling phpexcel to export an excel file to a local file
  • 2014.04.11thinkphp import excel using phpexcel
  • 2014.04.09ThinkPHP use PHPExcel to import and export Excel files
  • 2014.03.19PHP use the PHPExcel class to import and export excel
  • 2013.11.24PHP use PHPExcel to export an Excel example
  • 2013.08.13PHPExcel instance program for generating and reading Excel files
  • Export the Excel instance code using PHPExcel in 2013.08.13Yii
  • 2013.05.06PHPExcel reads excel and imports mysql database code
  • 2013.05.03php excel operation class phpExcel usage Introduction



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.