Infinite Pole classification + commodity export Excel (THINKPHP5, data read unlimited pole, personal limit export level 5)

Source: Internet
Author: User
Tags chr ord

Category table field: ID (primary key), name (category name), PID (null for top level, NOT NULL for ancestor ID)

Product table: No,name,spec ... Fill in the form according to your own product

Association table: ID (primary key), Domelamp (category table ID), Domelamp_part (Product table ID)

HTML code

<button onclick= "Exportexcel ()" class= "btn Btn-danger" data-toggle= "dropdown" style= "margin-left:30px;" > </span> Export Data to xls</button>

JS Code

function Exportexcel () {
Location.href = "{: URL (' Shopmodel/exportexcel ')}";
}

PHP code

Class Shopmodelcontroller extends Basecontroller

{

Private $smallarr = Array ();
Private $classnumber = Array ();
Private $alllist = Array ();


/*
*
* View data format (Excel export)
*
* */

Public Function Exportexcelview () {
$filename = "After-Sales product data Format _". Date ("Y_m_d", Time ()). ". xls"; Name
$header = Array (' Class menu ', ' Level two menu ', ' Level three menu ', ' Level four menu ', ' Five level menu ', ' accessory name ', ' Accessory number ', ' specification ', ' Accessory unit ', ' accessory color ', ' accessory material ', ' Sales price ');
$data = [
[' Testing ', ' test '],
[' names ', ' name ']
];
Excelexport ($filename, $header, $data, ' format ');
}

/*
*
* Export all data to XLS
*
* */

Public Function Exportexcel () {

$data = $this->getlist ();
$filename = "After-Sales product data _". Date ("Y_m_d", Time ()). ". xls";
$header = Array (' Class menu ', ' Level two menu ', ' Level three menu ', ' Level four menu ', ' Five level menu ', ' accessory name ', ' Accessory number ', ' specification ', ' Accessory unit ', ' accessory color ', ' accessory material ', ' Sales price ');
Excelexport ($filename, $header, $data, ' allinfo ');

}


/*
*
* Export XLS query all accessories information
*
* */
Public Function Getalllist ($cid) {
$info = Db::name (' Domelampbind ')
->alias (' a ')
->join (' Domelampnode b ', ' A.domelamp = b.ID ')
->join (' Domelamppart c ', ' A.domelamp_part = C.no ')
->where (' A.domelamp ', ' in ', $cid)
->field (' C.code,c.name,c.spec,c.unit,c.type,c.category,c.price ')
->select ();
if (Isset ($this->number[$cid [' ContentList '])) {//Number of parts
$this->contentnumber[$cid] = count ($info);
}else{
$this->contentnumber[$cid]= count ($info);
}
foreach ($info as $k = = $v) {
$this->alllist[] = Array_merge ($this->smallarr[$cid], $v);
}
unset ($this->smallarr[$cid]);
}

/*
*
* Export XLS to get the lowest data
*
* */
Public Function Getexcelsmallarr ($pid = 0) {
if ($pid = = 0) {
$where = ' pid is null ';
}else{
$where = ' pid = '. $pid;
}
$arr = Db::name (' Domelampnode ')->where ($where)->select ();
if (empty ($arr)) {
$info = Db::name (' Domelampnode ')->where (' id ', $pid)->find ();

if ($pid! = 0) $this->smallarr[$info [' ID ']][' class ' [] = $info [' name '];
if (!isset ($this->classnumber[$info [' ID ']]) {//Category Quantity
$this->classnumber[$info [' id ']]=1;
}
$this->getallarr ($info [' id '], $info [' pid '];
$this->getalllist ($info [' id ']); Accessories Information
}else{
foreach ($arr as $k = = $v) {
$this->getexcelsmallarr ($v [' id ']);
}
}
}

/*
*
* Export XLS for full data
*
* */
Public Function Getallarr ($k, $id = 0) {
$info = Db::name (' Domelampnode ')->where (' id ', $id)->find ();
if (Isset ($this->classnumber[$k])) {//Category Quantity
$this->classnumber[$k]++;
}
Array_unshift ($this->smallarr[$k [' class '], $info [' name ']);
if ($info [' pid ']>0) {
$this->getallarr ($k, $info [' pid ']);
}
}

/*
*
* Export XLS Acquisition method (calling methods)
*
* */
Public Function GetList () {
$this->smallarr = Array ();
$this->getexcelsmallarr ();
return [' Max_classnumber ' = max ($this->classnumber), ' list ' = $this->alllist];
}


}


/*
* Export Excel Method Personal definition under common file
* Excel table export all data can be specified format
* data = Allinfo All categorical data export, modified format
* data = format for general data export, with a two-dimensional array
* EXCEL columns such as the number of ABC widths can be set according to the actual situation
* */
function Excelexport ($fileName = ", $headArr = [], $data = [], $format =") {

Vendor (' Phpexcel.phpexcel ');
$objPHPExcel = new \phpexcel ();

$objPHPExcel->getproperties ();

$key = Ord ("A"); Set up the table header

foreach ($headArr as $v) {

$colum = Chr ($key);

$objPHPExcel->setactivesheetindex (0)->setcellvalue ($colum. ' 1 ', $v);

$objPHPExcel->setactivesheetindex (0)->setcellvalue ($colum. ' 1 ', $v);

Set column width new plus
$objPHPExcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' C ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' E ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' F ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' G ')->setwidth (' 20 ');
$objPHPExcel->getactivesheet ()->getcolumndimension (' H ')->setwidth (' 20 ');

This line of code for the following comment is for the header to have a filter function, uncomment as needed

$objPHPExcel->getactivesheet ()->setautofilter ($objPHPExcel->getactivesheet () Calculateworksheetdimension ());

$key + = 1;

}

$column = 2;

$objActSheet = $objPHPExcel->getactivesheet ();

if ($format = = ' Allinfo ') {
$classMax = $data [' Max_classnumber '];
$list = $data [' list '];
foreach ($list as $k = = $row) {
$class = $row [' class '];
$number = count ($class);
if ($number < $classMax) {
$need = $classMax-$number;
for ($i =0; $i < $need; $i + +) {
Array_push ($class, ");
}
}
unset ($row [' class ']);

$list [$k] = Array_merge ($class, $row);

$span = Ord ("A");

foreach ($list [$k] as $keyName = = $value) {//Column write
$objActSheet->setcellvalue (Chr ($span). $column, $value);

$span + +;

}

$column + +;
}
}

if ($format = = ' format ') {
foreach ($data as $key = + $rows) {//Line write

$span = Ord ("A");

foreach ($rows as $keyName = + $value) {//Column write

$objActSheet->setcellvalue (Chr ($span). $column, $value);

$span + +;

}

$column + +;

}
}


$fileName = Iconv ("Utf-8", "gb2312", $fileName); Renaming a table

$objPHPExcel->setactivesheetindex (0); Set the activity Order index to the first table, so Excel opens this is the first table

Header (' Content-type:application/vnd.ms-excel ');

Header ("Content-disposition:attachment;filename= ' $fileName '");

Header (' cache-control:max-age=0 ');

$objWriter = \phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');

$objWriter->save (' php://output '); Files are downloaded through the browser

Exit ();

}


Infinite Pole classification + commodity export Excel (THINKPHP5, data read unlimited pole, personal limit export level 5)

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.