圣侠无双网游活动专题站

大家好，又见面了，我是你们的朋友全栈君。

1、安装

composer require phpoffice/phpspreadsheet

代码语言：javascript复制2、

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Style\Alignment;

use PhpOffice\PhpSpreadsheet\Style\Border;

use PhpOffice\PhpSpreadsheet\Style\Font;

use PhpOffice\PhpSpreadsheet\Style\Color;

use PhpOffice\PhpSpreadsheet\Style\Fill;

use PhpOffice\PhpSpreadsheet\IOFactory;代码语言：javascript复制3、数据库导出到excel表中

//导出测试

public function daochu()

{

$spreadsheet = new Spreadsheet();

$worksheet = $spreadsheet->getActiveSheet();

//设置工作表标题名称

$worksheet->setTitle('车型品牌');

//表头

//设置单元格内容

$worksheet->setCellValueByColumnAndRow(1, 1, '车型品牌表');

$worksheet->setCellValueByColumnAndRow(1, 2, 'ID');

$worksheet->setCellValueByColumnAndRow(2, 2, '品牌名');

$worksheet->setCellValueByColumnAndRow(3, 2, '图片');

$worksheet->setCellValueByColumnAndRow(4, 2, '状态');

//合并单元格

$worksheet->mergeCells('A1:D1');

//字体设置

$styleArray = [

'font' => [

'bold' => true, //字体加粗

'color' => [ 'rgb' => 'FF6A6A' ], //字体颜色

'strikethrough' => true, //删除线

'italic' => true, //倾斜

'underline' => Font::UNDERLINE_DOUBLE,

],

'alignment' => [

'horizontal' => Alignment::HORIZONTAL_CENTER,

],

];

//设置单元格样式

$worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

$worksheet->getStyle('A2:D2')->applyFromArray($styleArray)->getFont()->setSize(14);

//设置字体颜色

//$worksheet->getStyle('B2')->getFont()->getColor()->applyFromArray(['rgb' => 'C0FF3E']);

//背景色

//$worksheet->getStyle('B2')->getFill()->applyFromArray( [ 'fillType' => Fill::FILL_GRADIENT_LINEAR, 'rotation' => 0, 'startColor' => [ 'rgb' => '000000' ], 'endColor' => [ 'argb' => 'FFFFFFFF' ] ] );

//设置单元格 自动宽度显示

$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$rows = Db::name('cartype_logo')->select();

$len = Db::name('cartype_logo')->count();

$j = 0;

for ($i=0; $i < $len; $i++) {

$j = $i + 3; //从表格第3行开始

$worksheet->setCellValueByColumnAndRow(1, $j, $rows[$i]['logo_id']);

$worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['logo_name']);

$worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['logo_img']);

$worksheet->setCellValueByColumnAndRow(4, $j, $rows[$i]['status']);

}

$styleArrayBody = [

'borders' => [

'allBorders' => [

'borderStyle' => Border::BORDER_THIN,

'color' => ['argb' => '666666'],

],

],

'alignment' => [

'horizontal' => Alignment::HORIZONTAL_CENTER,

],

];

$total_rows = $len + 2;

//添加所有边框/居中

$worksheet->getStyle('A1:D'.$total_rows)->applyFromArray($styleArrayBody);

$filename = '品牌表.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="'.$filename.'"');

header('Cache-Control: max-age=0');

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');

}代码语言：javascript复制3、excel导入数据库表汇中

//导入测试

public function daoru()

{

$reader = IOFactory::createReader('Xls');

$reader->setReadDataOnly(TRUE);

$spreadsheet = $reader->load('C:\Users\Administrator\Desktop\logo.xls'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();

$highestRow = $worksheet->getHighestRow(); // 总行数

$lines = $highestRow - 1;

if ($lines <= 0) {

exit('Excel表格中没有数据');

}

$new = [];

for ($row = 2; $row <= $highestRow; ++$row) {

$new_logos = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //品牌名

$new_png = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //图片地址

$new[] = ['logo_name'=> $new_logos,'logo_img'=>$new_png];

}代码语言：javascript复制$new = [];

for ($row = 2; $row <= $highestRow; ++$row) {

$new_logos = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //品牌名

$new_png = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //图片地址

$new[] = ['logo_name'=> $new_logos,'logo_img'=>$new_png];

}代码语言：javascript复制foreach ($new as $k => $v) {

$data['logo_name'] = $v[0];

$data['logo_img'] = $v[1];

//var_dump($data); die;

Db::name('cartype_logo')->insert($data);

}echo ‘ok’;

}

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192586.html原文链接：https://javaforall.cn