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

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)