6. 单元格设置

设置单元格格式

1. 公式。（对于单元格设置计算公式仅支持行内）

$data['sheet1'] = [
  ['goodsName' => '半裙', 'price' => 1490, 'actualStock' => 2],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1]
];

$config['sheet1'] = [
  ['bindKey' => 'goodsName', 'columnName' => '商品名称'],
  ['bindKey' => 'price', 'columnName' => '售价'],
  ['bindKey' => 'actualStock', 'columnName' => '实际库存'],
  ['bindKey' => '={price}*{actualStock}', 'columnName' => '库存额'], //会自动转化为公式，所在行的 price * actualStock
];

2. 日期

$data['sheet1'] = [
  ['goodsName' => '半裙', 'price' => 1490, 'actualStock' => 2, 'date' => '2022-06-18'],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1, 'date' => '2022-06-19']
];

$config['sheet1'] = [
  ['bindKey' => 'goodsName', 'columnName' => '商品名称'],
  ['bindKey' => 'price', 'columnName' => '售价'],
  ['bindKey' => 'actualStock', 'columnName' => '实际库存'],
  ['bindKey' => 'date', 'columnName' => '日期', 'format'=> NumberFormat::FORMAT_DATE_YYYYMMDDSLASH],
];

3. 自定义格式

$data['sheet1'] = [
  ['goodsName' => '半裙', 'price' => 1490, 'actualStock' => 2],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1]
];

$config['sheet1'] = [
  ['bindKey' => 'goodsName', 'columnName' => '商品名称'],
  ['bindKey' => 'price', 'columnName' => '售价', 'format' => '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)'],
  ['bindKey' => 'actualStock', 'columnName' => '实际库存'],
  ['bindKey' => '={price}*{actualStock}', 'columnName' => '库存额'], //会自动转化为公式
];

自定义格式可以参考 execl->设置单元格格式->自定义

4. 超链接

将对应单元格value设置成数据，其中 text 字段为展示的值， hyperlink 为链接地址。

$data['sheet1'] = [
  ['goodsName' => ['text' => '半裙', 'hyperlink' => 'https://www.baidu.com/s?wd=半裙'], 'price' => 1490, 'actualStock' => 2],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1, 'hyperlink' => 'https://www.baidu.com/s?wd=半裙2']
];

5. 批注

批注的实现方式跟超链接类似，将对应单元格value设置成数组， 其中comment为批注内容

$data['sheet1'] = [
  ['goodsName' => ['text' => '半裙', 'comment' => 'this is comment'], 'price' => 1490, 'actualStock' => 2],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1, 'hyperlink' => 'https://www.baidu.com/s?wd=半裙2']
];

6. 图片

在data中图片列的值为路径，可以是网络图片(一般不建议导出大量网络图片)。

对于网络图片，用到了CURL批量请求下载到本地，渲染之后会删除。

在config中，对图片所在列，设置 drawing 属性。

drawing 为一个数组。 包括 name、x、y、w、h、remote五个字段。 分别表示图片名、横向偏移、纵向偏移、宽度、高度、是否是网络图片。

drawing 为空时取默认值 name = ''; x=10; y=10; w=80; h=80。

$data['sheet1'] = [
  ['goodsName' => '半裙', 'price' => 1490, 'actualStock' => 2, 'img' => __DIR__ . '/file/example.jpeg'],
  ['goodsName' => '半裙', 'price' => 1590, 'actualStock' => 1, 'img' => __DIR__ . '/file/example.jpeg']
];

$config['sheet1'] = [
  ['bindKey' => 'goodsName', 'columnName' => '商品名称' ],
  ['bindKey' => 'img', 'columnName' => '图片', 'drawing' => ['w' => 80, 'h' => '80'] ],
  ['bindKey' => 'price', 'columnName' => '售价', 'format' => NumberFormat::FORMAT_DATE_YYYYMMDDSLASH],
  ['bindKey' => 'actualStock', 'columnName' => '实际库存'],
  ['bindKey' => '={price}*{actualStock}', 'columnName' => '库存额'], //会自动转化为公式
];

#网络图片
$data['sheet1'] = [
  ['goodsName' => ['text' => '半裙', 'comment' => 'this is comment'], 'price' => 1490, 'actualStock' => 2, 'img' => ''],
  ['goodsName' => '半裙1', 'price' => 1590, 'actualStock' => 1, 'img'=> 'https://img2.baidu.com/it/u=128589838,2597987891&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=500'],
  ['goodsName' => '半裙', 'price' => 899, 'actualStock' => 2, 'img'=> 'https://img1.baidu.com/it/u=467548803,2897629727&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=500'],
];

$config['sheet1'] = [
  ['bindKey' => 'goodsName', 'columnName' => '商品名称', 'width' => 40 ],
  ['bindKey' => 'img', 'columnName' => '图片','width' => 30, 'drawing' => ['w' => 80, 'h' => 80, 'remote' => true]],
  ['bindKey' => 'price', 'columnName' => '售价', 'format' => NumberFormat::FORMAT_DATE_YYYYMMDDSLASH],
  ['bindKey' => 'actualStock', 'columnName' => '实际库存'],
];