“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!”
How does PHP import an Excel spreadsheet with images? How do I export the images to Excel? How do you style the Exported Excel to make it more beautiful? Start this article with these questions.
Excel is a common data of statistical tools, usually in some of the information platform in order to better implement paperless or on cloud, need to migration office data, migrated from the office computer platforms, or to download the data on the platform to the developers to use, will inevitably involve the import and export of data, The data format must be Excel.
Based on the actual development requirements, this paper summarizes the import and export of Excel in the development process, and the development framework involved:
- ThinkPHP 3.2
- phpExcel
In the deployment, the timeout or running time should be extended and the upload size limit should be increased for data with many images in Excel
Code repository: github.com/QuintionTan…
The import
Before importing data, you need to define the format of the imported data, and you must strictly follow the specified format program to parse the data correctly. The usual data import is just plain text data. This article will import data with pictures in Excel to cover the import requirements as much as possible.
The template
Templates are the basis of data import. Define a simple data template in the following format:
There are text and pictures. The first choice for importing data is to read the Excel file, so file upload is also required. After the file is uploaded successfully, the image column is detected first, and the code is directly read:
public function excel_import(){ $usedfor = empty($_GET['usedfor']) ? 'picture' : trim($_GET['usedfor']); $used_for = $usedfor; import('ORG.Net.UploadFile'); $upload = $this->_upload_init(new \Org\Net\UploadFile(),$usedfor); $attach = array(); $attachment = array(); $attach["success"] = 0; $info = ""; if(! $upload_error = $upload->getErrorMsg(); $upload_error = $upload->getErrorMsg(); $attach["msg"] = $upload_error; $info = $upload->getUploadFileInfo(); $info = $upload->getUploadFileInfo(); If (is_array($info)){$info = $info[0]; // PHPExcel class import(" org.util.phpExcel "); import("Org.Util.PHPExcel.Reader.Excel5"); import("Org.Util.PHPExcel.Reader.Excel2007"); import("Org.Util.PHPExcel.IOFactory.php"); $filePath = $info["savepath"] . $info["savename"]; $input_file_type = \PHPExcel_IOFactory::identify($filePath); $objExcel = new \PHPExcel(); $objReader = \PHPExcel_IOFactory::createReader($input_file_type); $objPHPExcel = $objReader->load($filePath); $objWorksheet = $objPHPExcel->getActiveSheet(); $data = $objWorksheet->toArray(); $attach_path = C('attach_path'); $subpath = date('YmdHm', time()); $attach_path." excel_img/".$attach_path. mkdirs($imageFileRealPath); $i = 0; $rebarRows = array(); Foreach ($objWorksheet->getDrawingCollection() as $img) {list($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates()); $imageFileName = uniqid(); try { switch($img->getExtension()) { case 'jpg': case 'jpeg': $imageFileName .= '.jpeg'; $source = imagecreatefromjpeg($img->getPath()); imagejpeg($source, $imageFileRealPath.$imageFileName,100); break; case 'gif': $imageFileName .= '.gif'; $source = imagecreatefromgif($img->getPath()); $width = imagesx($source); $height = imagesy($source); if (function_exists("imagecreatetruecolor")) { $newImg = imagecreatetruecolor($width, $height); $allocate = imagecolorallocate($newImg, $allocate, $allocate); $allocate = imagecolorallocate($allocate, $allocate, $allocate); imagecolortransparent($newImg,$color); Imagefill ($newImg, 0, 0, $color); ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } else { $newImg = imagecreate($width, $height); ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } imagejpeg($source, $imageFileRealPath.$imageFileName,100); break; case 'png': $imageFileName .= '.png'; $source = imagecreatefrompng($img->getPath()); $width = imagesx($source); $height = imagesy($source); if (function_exists("imagecreatetruecolor")) { $newImg = imagecreatetruecolor($width, $height); $allocate = imagecolorallocate($newImg, $allocate, $allocate); $allocate = imagecolorallocate($allocate, $allocate, $allocate); imagecolortransparent($newImg,$color); Imagefill ($newImg, 0, 0, $color); ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } else { $newImg = imagecreate($width, $height); ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } imagejpeg($newImg, $imageFileRealPath.$imageFileName,100); break; } $startColumn = $this->ABC2decimal($startColumn); $data[$startRow-1][$startColumn] = $imageFileRealPath . $imageFileName; } catch (\Throwable $th) { throw $th; } } $rowsData = array(); foreach ($data as $key => $rowData) { $serial = safty_value($rowData[0],0,'intval'); / / the first column number $title = safty_value ($rowData [1], ' ', 'trim); $logo_save_path = safty_value($rowData[2],'','trim'); $remark = safty_value($rowData[3],'','trim'); If ($serial >0 && $logo_save_path! =="" && $title! ==""){ array_push($rowsData,array( "serial"=>$serial, "title"=>$title, "logo_path"=>$logo_save_path, "remark"=>$remark )); $this->update_excel_data($rowsData); $this->update_excel_data($rowsData); $upload_result = array( "count" => count($rowsData), "success" => 1, "state"=>"SUCCESS" ); } else {$upload_result = array("message" => "upload failed!") , "success" => 0 ); } echo json_encode($upload_result); }Copy the codeThe following is the operation process, as follows:
Select file upload and import. When the export is successful, a prompt is displayed and the current list page is displayed.
The list after the export is successful:
At this point, the data import is complete.
Insufficient, the imported Excel file is not processed after the data import, so it is recommended to delete it
export
To export the format definition of Excel, you need to define the table header:
$first_cells = array (array (" serial ", "serial number"), array (" title ", "name"), array (" logo ", "logo"), array (" remark ", "description"));Copy the codeThe next step is to encapsulate the data in the format of the table header, as follows:
foreach ($excel_data as $key => $row_info) {
array_push($first_rows_data,array(
"serial"=>$row_info['serial'],
"title"=>$row_info['title'],
"logo"=>$row_info['logo_path'],
"remark"=>$row_info['remark']
));
}
Copy the codeAt this point, data encapsulation has been completed, the complete code is as follows:
public function export(){ $excel_detail = array( "author"=>"devpoint", "date"=>join(" ",$artifacts_full) ); $sheets = array(); $sheets = array(); // Excel table information, $first_cells = array(array("serial"," serial"), array("title"," name "), array("logo","logo"), Array ("remark"," description "); $excel_data = get_file_cache("excel_data"); $first_rows_data = array(); Foreach ($excel_data as $key => $row_info) {array_push($first_rows_data,array($array)); "serial"=>$row_info['serial'], "title"=>$row_info['title'], "logo"=>$row_info['logo_path'], "remark"=>$row_info['remark'] )); } array_push($sheets,array("title"=>" first_cells ", "rows"=>$first_rows_data)); $xlsName = "$xlsName "; $xlsName = $xlsName . date('YmdHis'); $this->exportExcel($xlsName,$sheets,$excel_detail); }Copy the codeThe exportExcel function writes data to Excel and defines the style of the table. The complete code is as follows:
protected function exportExcel($expTitle,$xlsSheets,$detail){
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Writer.Excel5");
import("Org.Util.PHPExcel.IOFactory.php");
$fileName = $expTitle;
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
// Excel列名称
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
foreach ($xlsSheets as $index => $sheet_info) {
$sheet_title = $sheet_info['title'];
if ($index>0){
// Excel默认已经建好的数据表，超过一张需要执行这里创建一个工作表
$newSheet = new \PHPExcel_Worksheet($objPHPExcel, $sheet_title); //创建一个工作表
$objPHPExcel->addSheet($newSheet);
} else {
$objPHPExcel->getActiveSheet($index)->setTitle($sheet_title);
}
$expCellName = $sheet_info['cells'];
$expTableData = $sheet_info['rows'];
$cellNum = count($expCellName);
$dataNum = count($expTableData);
$cellmerget = "";
$cellWidths = array();
$sheet_head_title = $sheet_title;
// 下面需要为每个工作表定义宽度
switch ($index) {
case 1: // 每张表的索引从 0 开始计算
$cellmerget = 'A1:E1';
$cellWidths=array(16,16,16,28,16);
break;
default:
$cellmerget = 'A1:D1';
$sheet_head_title = $sheet_title ;
$cellWidths=array(16,16,16,36);
break;
}
$activeSheet = $objPHPExcel->setActiveSheetIndex($index);
for($i=0;$i<$cellNum;$i++){
$currentCellName = $cellName[$i];
$activeSheet->getRowDimension(1)->setRowHeight(36);
$activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]);
$activeSheet->getStyle($currentCellName.'1')->getFont()->setSize(12)->setBold(true);
$activeSheet->getStyle($currentCellName.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
$activeSheet->mergeCells($cellmerget);//合并单元格
$activeSheet->setCellValue('A1', $sheet_head_title);
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(20);
$activeSheet->getRowDimension(1)->setRowHeight(50);
$styleThinBlackBorderOutline = array(
'borders' => array (
'outline' => array (
'style' => \PHPExcel_Style_Border::BORDER_MEDIUM, //设置border样式
'color' => array ('argb' => 'FF9b9b9b'), //设置border颜色
),
),
);
for($i=0;$i<$cellNum;$i++){
$currentCellName = $cellName[$i];
$activeSheet->getRowDimension(2)->setRowHeight(36);
$activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]);
$activeSheet->setCellValue($currentCellName.'2', $expCellName[$i][1]);
$activeSheet->getStyle($currentCellName.'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$activeSheet->getStyle($currentCellName.'2')->getFill()->getStartColor()->setARGB('FFc6efcd');
$activeSheet->getStyle($currentCellName.'2')->getFont()->setSize(12)->setBold(true);
$activeSheet->getStyle($currentCellName.'2')->applyFromArray($styleThinBlackBorderOutline);
$activeSheet->getStyle($currentCellName.'2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->freezePane($currentCellName.'3'); // 锁定表头，3 意味着锁定第3行上面的
}
switch ($index) {
case 1:
break;
default:
$start_row_index = 3; // 数据开始索引行
for($i1=0;$i1<$dataNum;$i1++){
$objPHPExcel->getActiveSheet()->getRowDimension($i1+3)->setRowHeight(60);
for($j1=0;$j1<$cellNum;$j1++){
if ($j1===2){
$logo_path = $expTableData[$i1][$expCellName[$j1][0]];
if ($logo_path!=="" && file_exists($logo_path)){
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($logo_path);
$objDrawing->setHeight(60);
$objDrawing->setWidth(60);
$objDrawing->setOffsetX(5);
$objDrawing->setOffsetY(5);
$objDrawing->setCoordinates($cellName[$j1].($i1+$start_row_index));
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
} else {
$objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index), "");
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true);
}
} else {
$objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index), $expTableData[$i1][$expCellName[$j1][0]]);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true);
}
}
}
break;
}
}
$objPHPExcel->setActiveSheetIndex(0);
header('pragma:public');
header('Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;name="'.$fileName.'.xlsx"');
header("Content-Disposition:attachment;filename=$fileName.xlsx"); // attachment新窗口打印inline本窗口打印
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
Copy the codeThe exported format is as follows:
Locking header
Locking table headers is a common function in Excel, which makes it easy for the user to check the data.
$activeSheet->freezePane($currentCellName.'3'); // 3 means to lock the number of rows above row 3
Copy the codeTable Border Style
The code above sets the border style of the table to \PHPExcel_Style_Border::BORDER_MEDIUM. There are 14 optional items to configure in phpExcel.
PHPExcel_Style_Border::BORDER_NONE;
PHPExcel_Style_Border::BORDER_THIN;
PHPExcel_Style_Border::BORDER_MEDIUM;
PHPExcel_Style_Border::BORDER_DASHED;
PHPExcel_Style_Border::BORDER_DOTTED;
PHPExcel_Style_Border::BORDER_THICK;
PHPExcel_Style_Border::BORDER_DOUBLE;
PHPExcel_Style_Border::BORDER_HAIR;
PHPExcel_Style_Border::BORDER_MEDIUMDASHED;
PHPExcel_Style_Border::BORDER_DASHDOT;
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT;
PHPExcel_Style_Border::BORDER_DASHDOTDOT;
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT;
PHPExcel_Style_Border::BORDER_SLANTDASHDOT;
Copy the code1. BORDER_NONE
The complete configuration is PHPExcel_Style_Border::BORDER_NONE, and it looks like this:
2. BORDER_THIN
\PHPExcel_Style_Border::BORDER_THIN
Copy the code
3. BORDER_MEDIUM
\PHPExcel_Style_Border::BORDER_MEDIUM
Copy the code
4. BORDER_DASHED
\PHPExcel_Style_Border::BORDER_DASHED
Copy the code
5. BORDER_DOTTED
\PHPExcel_Style_Border::BORDER_DOTTED
Copy the code
6. BORDER_THICK
\PHPExcel_Style_Border::BORDER_THICK
Copy the code
7. BORDER_DOUBLE
\PHPExcel_Style_Border::BORDER_DOUBLE
Copy the code
8. BORDER_HAIR
\PHPExcel_Style_Border::BORDER_HAIR
Copy the code
9. BORDER_MEDIUMDASHED
\PHPExcel_Style_Border::BORDER_MEDIUMDASHED
Copy the code
10. BORDER_DASHDOT
\PHPExcel_Style_Border::BORDER_DASHDOT
Copy the code
11. BORDER_MEDIUMDASHDOT
\PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT
Copy the code
12. BORDER_DASHDOTDOT
\PHPExcel_Style_Border::BORDER_DASHDOTDOT
Copy the code
13. BORDER_MEDIUMDASHDOTDOT
\PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT
Copy the code
14. BORDER_SLANTDASHDOT
\PHPExcel_Style_Border::BORDER_SLANTDASHDOT
Copy the code
The deployment of
In terms of deployment, the usual architecture is nginx + PHP-Fpm. For data import with many images in Excel, you need to increase the limit and timeout time of uploading files. A follow-up column, Docker for WEB Developers, will include PHP runtime mirroring.
The 413 Request Entity too Large error occurs when a file is uploaded. To solve this problem, add the following configuration to the nginx configuration:
client_max_body_size 2048m;
Copy the codeThe corresponding PHP configuration also needs to be modified. You need to modify php.ini:
upload_max_filesize = 2048M
post_max_size = 2048M
Copy the codeIn this case, the execution time is too short, and the configuration involved in nginx is:
fastcgi_connect_timeout 600;
Copy the codeThe www.conf PHP – FPM
request_terminate_timeout = 1800
Copy the codeIn my view, environment problems often occur in background development. The best way is to actually run an optimal configuration and make it into a Docker image, so as to ensure that environment migration or other occasions require, environment configuration can be quickly completed, and problems are not easy to occur.