I need to use phpexcel to export excel tables in my recent work. Automatically store in a fixed location.
Previously exported excel using header natively.
However, I have not found any function that can be automatically stored in this method.
If you know, please leave a message below and learn together.
Let’s talk about header export first.
I am using thinkphp3.2 framework here.
/**
* Export excel tables
*/
public function actionPutExcel()
{
$admin = M('admin');
$dbbacklist = $admin->select();
$title = ["Serial Number","username","password","Login time"];
exportexcel($dbbacklist,$title);
}
/** Export data as an excel table
* @param $data A two-dimensional array with a structure like an array found from a database
* @param $title The first line title of excel, an array, if empty, there is no title
* @param $filename Downloaded filename
*/
function exportexcel($data=array(),$title=array(),$filename='report'){
ob_end_clean();
ob_start();
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/-excel");
header("Content-Disposition:attachment;filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");
//Export xls start if (!empty($title)){
foreach ($title as $k => $v) {
$title[$k]=iconv("UTF-8", "GB2312",$v);
}
$title= implode(" ", $title);
echo "$title
";
}
if (!empty($data)){
foreach($data as $key=>$val){
foreach ($val as $ck => $cv) {
$data[$key][$ck]=iconv("UTF-8", "GB2312", $cv);
}
$data[$key]=implode(" ", $data[$key]);
}
echo implode("
",$data);
}
}There is nothing to say about this, there are three parameters in total. Just pass it right.
Phpexcel export excel
Official website
Official download address
Put it where you can lead.
I'm using the laravel5.8 framework here
/**
* @name: Export excel
* @author: camellia
* @date: 2021-03-08
* @param: $data array Export data
* @param: $filename string file name
* @param: $tabhead array Single table header
* @param: $rowwidth array Single table per row width
* @param: $setHeader bool Whether to set the table header
* @param: $saveFile bool Whether to save the file
* @param: $sheetNameList array
*/
public function create_xls($data, $filename, $tabhead = [], $rowwidth = [],$setHeader=true,$sheetNameList=[],$saveFile=false)
{
ini_set('max_execution_time', '0');
$filename = str_replace('.xls', '', $filename) . '.xls';
$phpexcel = new \PHPExcel();
$phpexcel->getProperties()
->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$arrayLevel = $this->getArrayLevel($data);
if($arrayLevel > 2)
{
foreach($data as $key => $item)
{
//Create a new workspace (sheet) $phpexcel->createSheet();
$phpexcel->setactivesheetindex($key);
// Set sheet title $phpexcel->getActiveSheet()->setTitle($sheetNameList[$key]);
// Get the currently selected sheet object $objActSheet = $phpexcel->getActiveSheet();
if($setHeader)
{
$i = 1;
if($tabhead)
{
// Set the header foreach ($tabhead as $key => $val)
{
$phpexcel->getActiveSheet()->setCellValue($key, $val);
}
// Insert the file content into the table $i = 2;
}
/*Set width*/
foreach ($rowwidth as $key => $val)
{
$phpexcel->getActiveSheet()->getColumnDimension($key)->setWidth($val);
}
}
else
{
$i = 1;
}
foreach ($item as $value)
{
/* Excel file content */
$j = 'A';
foreach ($value as $value2)
{
$objActSheet->setCellValue($j . $i, $value2);
$j++;
}
$i++;
}
}
}
else
{
$phpexcel->getActiveSheet()->setTitle('Sheet1');
$phpexcel->setActiveSheetIndex(0);
$objActSheet = $phpexcel->getActiveSheet();
if($setHeader)
{
// Set the header foreach ($tabhead as $key => $val)
{
$phpexcel->getActiveSheet()->setCellValue($key, $val);
}
/*Set width*/
foreach ($rowwidth as $key => $val)
{
$phpexcel->getActiveSheet()->getColumnDimension($key)->setWidth($val);
}
// Insert the file content into the table $i = 2;
}
else
{
$i = 1;
}
foreach ($data as $value)
{
/* Excel file content */
$j = 'A';
foreach ($value as $value2)
{
$objActSheet->setCellValue($j . $i, $value2);
$j++;
}
$i++;
}
}
// $phpexcel->getActiveSheet()->fromArray($data);
header('Content-Type: application/-excel');
header("Content-Disposition: attachment;filename=$filename");
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objwriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
if($saveFile)
{
$objwriter->save('/usr/share/nginx/html/xxxx/xxxxx/xxxxxx/' . $filename); //This is automatically stored in the specified location $url = getenv('APP_URL'). "/xxxxx/xxxx/xxxxxx/". $filename;
return $url;
}
else
{
$objwriter->save('php://output'); //This is the output browser exit;
}
}
/**
* Returns the dimension of the array
* @author: camellia
* @date: 2021-03-08
* @param [array] $arr [array to judge]
* @return [number] [latitude number]
*/
public function getArrayLevel($arr)
{
$al = array(0);
function aL($arr, &$al, $level = 0)
{
if (is_array($arr)) {
$level++;
$al[] = $level;
foreach ($arr as $v) {
aL($v, $al, $level);
}
}
}
aL($arr, $al);
return max($al);
}The above example is what I am using myself. There are basically comments on important places. The corresponding parameter method comments are explained, and the corresponding parameter transmission is enough.
Phpexcel exports excel table functions with relatively diverse functions. But the speed is also relatively slow.
In the absence of much demand for functionality. It is better to use header, mainly because it is fast. When the data volume is large, withdrawals are particularly obvious.
There is a question that needs to be paid attention to. If the number in each cell is too long, it will be displayed as a scientific count.
The solution is very simple:
Add a space before the numeric string to make it a string
That's it:
$num = 123456789987456; $str = ''.$num;
Is it feasible to directly add quotes to $num to make it a string? I haven't tried it, if you are interested, you can try it.
That's it:
$num = string($num);
That's basically what exports are.
This is the end of this article about the detailed explanation of the method of exporting excel files using PHPExcel. For more related contents of PHPExcel exporting excel files, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!