详解PhpSpreadsheet设置单元格

程序
2 617
caixiaoxu
2018-09-01

PhpSpreadsheet提供了丰富的API接口,可以设置诸多单元格以及文档属性,包括样式、图片、日期、函数等等诸多应用,总之你想要什么样的Excel表格,PhpSpreadsheet都能做到。

在调试设置时,确保引入了正确的文件并实例化。

  1. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  2. $spreadsheet = new Spreadsheet();
  3. $worksheet = $spreadsheet->getActiveSheet();
字体

第1行代码将A7至B7两单元格设置为粗体字,Arial字体,10号字;第2行代码将B1单元格设置为粗体字。

  1. $spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')
  2. ->setSize(10);;
  3. $spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
颜色

将文字颜色设置为红色。

  1. $spreadsheet->getActiveSheet()->getStyle('A4')
  2. ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
图片

可以将图片加载到Excel中。

  1. $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
  2. $drawing->setName('Logo');
  3. $drawing->setDescription('Logo');
  4. $drawing->setPath('./images/officelogo.jpg');
  5. $drawing->setHeight(36);
列宽

将A列宽度设置为30(字符)。

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);

如果需要自动计算列宽,可以这样:

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

设置默认列宽为12。

$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
行高

设置第10行行高为100pt。

$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);

设置默认行高。

$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
对齐

将A1单元格设置为水平居中对齐。

  1. $styleArray = [
  2. 'alignment' => [
  3. 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
  4. ],
  5. ];
  6. $worksheet->getStyle('A1')->applyFromArray($styleArray);
合并

将A18到E22合并为一个单元格。

$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
拆分

将合并后的单元格拆分。

$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');
边框

将B2至G8的区域添加红色边框。

  1. $styleArray = [
  2. 'borders' => [
  3. 'outline' => [
  4. 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
  5. 'color' => ['argb' => 'FFFF0000'],
  6. ],
  7. ],
  8. ];
  9. $worksheet->getStyle('B2:G8')->applyFromArray($styleArray);
工作表标题

设置当前工作表标题。

$spreadsheet->getActiveSheet()->setTitle('Hello');
日期时间

设置日期格式。

  1. $spreadsheet->getActiveSheet()
  2. ->setCellValue('D1', '2018-06-15');
  3. $spreadsheet->getActiveSheet()->getStyle('D1')
  4. ->getNumberFormat()
  5. ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);
换行

使用\n进行单元格内换行,相当于(ALT+"Enter")。

  1. $spreadsheet->getActiveSheet()->getCell('A4')->setValue("hello\nworld");
  2. $spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true);
超链接

将单元格设置为超链接形式。

  1. $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.helloweba.net');
  2. $spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.helloweba.net');
使用函数

使用SUM计算B5到C5之间单元格的总和。其他函数同理:最大数(MAX),最小数(MIN),平均值(AVERAGE)。

  1. $spreadsheet->getActiveSheet()
  2. ->setCellValue('B7', '=SUM(B5:C5)');
设置文档属性

可以设置Excel文档属性。

  1. $spreadsheet->getProperties()
  2. ->setCreator("Helloweba") //作者
  3. ->setLastModifiedBy("Yuegg") //最后修改者
  4. ->setTitle("Office 2007 XLSX Test Document") //标题
  5. ->setSubject("Office 2007 XLSX Test Document") //副标题
  6. ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //描述
  7. ->setKeywords("office 2007 openxml php") //关键字
  8. ->setCategory("Test result file"); //分类

此外,除了提供丰富的Excel文件处理接口外,PhpSpreadshee还提供了CSV,PDF,HTML以及XML等文件处理接口。

更多使用设置请参照官网文档:https://phpspreadsheet.readthedocs.io/en/stable/

文件转自:https://www.helloweba.net/php/564.html


回帖
  • 操作手册些的有点短小~实际上phpspreadsheet的功能非常强大并不止操作手册那么少,我也不是很懂为什么他的操作手册写的这么渣,有人说还没有稳定版,我也不是很清楚,或许这就是不受欢迎的原因。但实际上讲,这个库实际上还是很值得一用,毕竟用好了就跟你打开excel在操作是一样的。至于怎么用,除了看操作手册和百度之外,有一些功能,手册没有百度找不到,就要自己猜它跟那些函数比较像,然后手册看它怎么调用的,接着自己去找那个类,看里面是怎么写的,然后再去调用~过程可能会耗点时间,自己权衡吧~


    0 回复
  • public function index()
    {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    //水平居中样式
    $styleArray_horizontal_center = [
    'alignment' => [
    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    ];

    //分散对齐
    $styleArray_horizontal_distributed = [
    'alignment' => [
    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_DISTRIBUTED,
    ],
    ];

    //边框
    $styleArray_border = [
    'borders' => [
    'allBorders' => [
    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
    'color' => ['argb' => '000000'],
    ],
    ],
    ];

    $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
    $sheet->setCellValue('A1', '表二:');
    $spreadsheet->getActiveSheet()->mergeCells('F2:L2');

    $spreadsheet->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(22);
    $sheet->setCellValue('F2', '医疗仪器设备单机效益明细表');

    $time = date('Y 年 m 月 d 日',time());
    $spreadsheet->getActiveSheet()->mergeCells('O2:S2');
    $spreadsheet->getActiveSheet()->getStyle('O2')->applyFromArray($styleArray_horizontal_center)->getFont()->setSize(12);
    $sheet->setCellValue('O2', '统计日期:'.$time);

    $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(30);

    $spreadsheet->getActiveSheet()->mergeCells('A3:B3');
    $spreadsheet->getActiveSheet()->getStyle('A3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('A3', '设备编号:');

    $spreadsheet->getActiveSheet()->mergeCells('C3:D3');
    $spreadsheet->getActiveSheet()->getStyle('C3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('C3', '060701231204');

    $spreadsheet->getActiveSheet()->mergeCells('E3:F3');
    $spreadsheet->getActiveSheet()->getStyle('E3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('E3', '设备名称:');

    $spreadsheet->getActiveSheet()->mergeCells('G3:L3');
    $spreadsheet->getActiveSheet()->getStyle('G3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('G3', '超声多普勒胎儿监护仪');

    $spreadsheet->getActiveSheet()->mergeCells('M3:N3');
    $spreadsheet->getActiveSheet()->getStyle('M3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('M3', '品牌型号:');

    $spreadsheet->getActiveSheet()->mergeCells('O3:S3');
    $spreadsheet->getActiveSheet()->getStyle('O3')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('O3', '西门子Acuson SC2000型');

    $spreadsheet->getActiveSheet()->getRowDimension('4')->setRowHeight(30);

    $spreadsheet->getActiveSheet()->mergeCells('A4:B4');
    $spreadsheet->getActiveSheet()->getStyle('A4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('A4', '所在科室:');

    $spreadsheet->getActiveSheet()->mergeCells('C4:D4');
    $spreadsheet->getActiveSheet()->getStyle('C4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('C4', '妇产科');

    $spreadsheet->getActiveSheet()->mergeCells('E4:F4');
    $spreadsheet->getActiveSheet()->getStyle('E4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('E4', '设备原值:');

    $spreadsheet->getActiveSheet()->mergeCells('G4:H4');
    $spreadsheet->getActiveSheet()->getStyle('G4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('G4', number_format("3600000",2));

    $spreadsheet->getActiveSheet()->mergeCells('M4:N4');
    $spreadsheet->getActiveSheet()->getStyle('M4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('M4', '使用日期:');

    $spreadsheet->getActiveSheet()->mergeCells('O4:S4');
    $spreadsheet->getActiveSheet()->getStyle('O4')->getFont()->setBold(true)->setSize(14);
    $sheet->setCellValue('O4', '2010.11.05');

    $spreadsheet->getActiveSheet()->mergeCells('A5:A6');
    $spreadsheet->getActiveSheet()->getStyle('A5')->applyFromArray($styleArray_horizontal_center)->applyFromArray($styleArray_horizontal_distributed)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('A5', '月份');

    $spreadsheet->getActiveSheet()->mergeCells('B5:B6');
    $spreadsheet->getActiveSheet()->getStyle('B5')->applyFromArray($styleArray_horizontal_center)->applyFromArray($styleArray_horizontal_distributed)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('B5', '月收入');

    $spreadsheet->getActiveSheet()->mergeCells('C5:L5');
    $spreadsheet->getActiveSheet()->getStyle('C5')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('C5', '月支出');

    $spreadsheet->getActiveSheet()->getStyle('C6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('C6', '材料费');

    $spreadsheet->getActiveSheet()->getStyle('D6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('D6', '折旧费');

    $spreadsheet->getActiveSheet()->getStyle('E6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('E6', '人员费');

    $spreadsheet->getActiveSheet()->getStyle('F6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('F6', '维保费');

    $spreadsheet->getActiveSheet()->getStyle('G6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('G6', '水电费');

    $spreadsheet->getActiveSheet()->getStyle('H6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('H6', '管理费');

    $spreadsheet->getActiveSheet()->getStyle('I6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('I6', '场地费');

    $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
    $spreadsheet->getActiveSheet()->getStyle('J6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('J6', '利息支出');

    $spreadsheet->getActiveSheet()->getStyle('K6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('K6', '其他');

    $spreadsheet->getActiveSheet()->getStyle('L6')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('L6', '合计');

    $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(7);
    $spreadsheet->getActiveSheet()->mergeCells('M5:M6');
    $spreadsheet->getActiveSheet()->getStyle('M5')->applyFromArray($styleArray_horizontal_center)->applyFromArray($styleArray_horizontal_distributed)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('M5', '月工作量');

    $spreadsheet->getActiveSheet()->mergeCells('N5:N6');
    $spreadsheet->getActiveSheet()->getStyle('N5')->applyFromArray($styleArray_horizontal_center)->applyFromArray($styleArray_horizontal_distributed)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('N5', '维修停机天数');

    $spreadsheet->getActiveSheet()->mergeCells('O5:O6');
    $spreadsheet->getActiveSheet()->getStyle('O5')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('O5', '月利润');

    $spreadsheet->getActiveSheet()->mergeCells('P5:P6');
    $spreadsheet->getActiveSheet()->getStyle('P5')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('P5', '使用率');

    $spreadsheet->getActiveSheet()->mergeCells('Q5:Q6');
    $spreadsheet->getActiveSheet()->getStyle('Q5')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('Q5', '完好率');

    $spreadsheet->getActiveSheet()->mergeCells('R5:R6');
    $spreadsheet->getActiveSheet()->getStyle('R5')->applyFromArray($styleArray_horizontal_center)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('R5', '阳性率');

    $spreadsheet->getActiveSheet()->mergeCells('S5:S6');
    $spreadsheet->getActiveSheet()->getStyle('S5')->applyFromArray($styleArray_horizontal_center)->applyFromArray($styleArray_horizontal_distributed)->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('S5', '月资产利润率');

    for ($i = 7 ; $i < 19 ; $i++){
    $time = $i-6;
    $sheet->setCellValue('A'.$i, '2018.'.$time);
    $sheet->setCellValue('B'.$i, rand('140000','180000'));
    $sheet->setCellValue('C'.$i, rand('1000','2000'));
    $sheet->setCellValue('D'.$i, rand('1000','2000'));
    $sheet->setCellValue('E'.$i, rand('1000','2000'));
    $sheet->setCellValue('F'.$i, rand('1000','2000'));
    $sheet->setCellValue('G'.$i, rand('1000','2000'));
    $sheet->setCellValue('H'.$i, rand('1000','2000'));
    $sheet->setCellValue('I'.$i, rand('1000','2000'));
    $sheet->setCellValue('J'.$i, rand('1000','2000'));
    $sheet->setCellValue('K'.$i, rand('1000','2000'));
    $SUM = '=SUM(C'.$i.':K'.$i.')';
    $sheet->setCellValue('L'.$i, $SUM);
    $sheet->setCellValue('M'.$i, rand('2000','3000'));
    $sheet->setCellValue('N'.$i, rand('0','5'));
    $a = '=B'.$i.'-L'.$i.')';
    $sheet->setCellValue('O'.$i, $a);

    //百分比格式
    $spreadsheet->getActiveSheet()->getStyle('P'.$i)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $spreadsheet->getActiveSheet()->getStyle('Q'.$i)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $spreadsheet->getActiveSheet()->getStyle('R'.$i)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);

    $sheet->setCellValue('P'.$i, rand('60','80')/100);
    $sheet->setCellValue('Q'.$i, rand('65','85')/100);
    $sheet->setCellValue('R'.$i, rand('30','60')/100);
    $b = '=O'.$i.'/'.'3600000';
    $spreadsheet->getActiveSheet()->getStyle('S'.$i)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $sheet->setCellValue('S'.$i, $b);
    }
    $spreadsheet->getActiveSheet()->getStyle('A19:S19')->getFont()->setBold(true)->setSize(12);
    $sheet->setCellValue('A19', '总计');
    $sheet->setCellValue('B19', '=SUM(B7:B18)');
    $sheet->setCellValue('C19', '=SUM(C7:C18)');
    $sheet->setCellValue('D19', '=SUM(D7:D18)');
    $sheet->setCellValue('E19', '=SUM(E7:E18)');
    $sheet->setCellValue('F19', '=SUM(F7:F18)');
    $sheet->setCellValue('G19', '=SUM(G7:G18)');
    $sheet->setCellValue('H19', '=SUM(H7:H18)');
    $sheet->setCellValue('I19', '=SUM(I7:I18)');
    $sheet->setCellValue('J19', '=SUM(J7:J18)');
    $sheet->setCellValue('K19', '=SUM(K7:K18)');
    $sheet->setCellValue('L19', '=SUM(L7:L18)');
    $sheet->setCellValue('M19', '=SUM(M7:M18)');
    $sheet->setCellValue('N19', '=SUM(N7:N18)');
    $sheet->setCellValue('O19', '=SUM(O7:O18)');
    $spreadsheet->getActiveSheet()->getStyle('P19')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $spreadsheet->getActiveSheet()->getStyle('Q19')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $spreadsheet->getActiveSheet()->getStyle('R19')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $spreadsheet->getActiveSheet()->getStyle('S19')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    $sheet->setCellValue('P19', '=AVERAGE(P7:P18)');
    $sheet->setCellValue('Q19', '=AVERAGE(Q7:Q18)');
    $sheet->setCellValue('R19', '=AVERAGE(R7:R18)');
    $sheet->setCellValue('S19', '=AVERAGE(S7:S18)');

    //全部加边框
    $spreadsheet->getActiveSheet()->getStyle('A5:S19')->applyFromArray($styleArray_border);


    $spreadsheet->getActiveSheet()->mergeCells('A20:C20');
    $spreadsheet->getActiveSheet()->getStyle('A20')->applyFromArray($styleArray_horizontal_center)->getFont()->setSize(12);
    $sheet->setCellValue('A20', '填表人: 黄丹');

    $spreadsheet->getActiveSheet()->mergeCells('H20:J20');
    $spreadsheet->getActiveSheet()->getStyle('H20')->applyFromArray($styleArray_horizontal_center)->getFont()->setSize(12);
    $sheet->setCellValue('H20', '审核人: 吴柳梅');

    $spreadsheet->getActiveSheet()->mergeCells('O20:R20');
    $spreadsheet->getActiveSheet()->getStyle('O20')->applyFromArray($styleArray_horizontal_center)->getFont()->setSize(12);
    $sheet->setCellValue('O20', '设备部门负责人: 陈国敏');


    $writer = new Xlsx($spreadsheet);
    $writer->save('./out/helloworld.xlsx');

    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    }


    0 回复