export_demo.php 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152
  1. <?php
  2. require_once(__DIR__ . '/../vendor/autoload.php');
  3. use Pxlswrite\DB\DB;
  4. use Pxlswrite\Pxlswrite;
  5. use Pxlswrite\WebSocket\WebSocketClient;
  6. $time = time();
  7. //实例化pxlswrite
  8. $fileObj = new Pxlswrite(['path' => __DIR__ . '/uploads']);
  9. //实例化WebSocketClient--需要推送进度才实例化
  10. $pushHandle = new WebSocketClient('ws://192.168.18.192:9502', $_GET['fd']);
  11. //创建excel文件
  12. $fileObj->fileName('1234.xlsx');
  13. //定义样式
  14. $style = [
  15. 'align' => [Pxlswrite::FORMAT_ALIGN_CENTER, Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER],//对齐 [x,y]
  16. 'border' => Pxlswrite::BORDER_SLANT_DASH_DOT,//单元格边框
  17. 'background' => Pxlswrite::COLOR_RED,//单元格背景色
  18. 'fontColor' => Pxlswrite::COLOR_BLUE,//字体颜色
  19. 'fontSize' => 30,//字体大小
  20. 'font' => 'FontName',//设置字体 字体名称,字体必须存在于本机
  21. 'number' => '#,##0',//数字格式化
  22. 'bold' => true,//粗题
  23. 'strikeout' => false,//文本删除线
  24. 'wrap' => true,//文本换行
  25. 'italic' => true,//斜体
  26. ];
  27. //定义字段
  28. $field = [
  29. 'id' => ['name' => 'title'],
  30. 'c1' => ['name' => 'age'],
  31. 'c2' => ['name' => 'year'],
  32. 'c3' => ['name' => 'kk'],
  33. 'c4' => ['name' => 'll'],
  34. 'c5' => ['name' => 'aa', 'callback' => 'myFormat']//callback 回调处理格式化值 可以是函数/对象方法
  35. ];
  36. $orderField = [
  37. 'order'=>['name'=>'订单号'] ,
  38. 'time'=>['name'=>'下单时间'],
  39. 'username'=>['name'=>'用户名'],
  40. 'address'=>['name'=>'地址'],
  41. 'phone'=>['name'=>'手机号'],
  42. 'itemnumer'=>['name'=>'子订单号'],
  43. 'productname'=>['name'=>'商品名称'],
  44. 'amount'=>['name'=>'数量'],
  45. 'mark'=>['name'=>'备注'],
  46. ];
  47. //注意:设置行与行/列与列样式 交集范围会覆盖;行样式优先于列样式
  48. /**
  49. * 一般数据导出demo
  50. */
  51. //$filePath = $fileObj->field($field)//设置字段&表格头
  52. // ->setDataByGenerator('generateData', ['c1', 'c2'], ['align' => [Pxlswrite::FORMAT_ALIGN_CENTER, Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]], $pushHandle)//设置数据 回调生成器方法获取数据,$pushHandle 用于推送,可不传
  53. // ->setRow('A1:A3', 80, $style)//设置范围行样式 80行高
  54. // ->setColumn('A:F', 20, ['background' => Pxlswrite::COLOR_GRAY, 'align' => [Pxlswrite::FORMAT_ALIGN_CENTER, Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]])//设置范围列样式 20列宽
  55. // ->setRow('A1', 50, ['background' => Pxlswrite::COLOR_PINK, 'align' => [Pxlswrite::FORMAT_ALIGN_CENTER, Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]])//设置指定某一行样式
  56. // ->setColumn('F:F', 60, ['background' => Pxlswrite::COLOR_YELLOW])//指定某一列样式
  57. // ->mergeCells('A1:C1', 'Merge cells', ['align' => [Pxlswrite::FORMAT_ALIGN_CENTER, Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]])//合并单元格
  58. // ->defaultFormat(['align'=>[Pxlswrite::FORMAT_ALIGN_CENTER,Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]])//全局默认样式
  59. // ->output();//输出excel文件到磁盘
  60. /**
  61. * 订单数据导出demo
  62. // */
  63. //$filePath = $fileObj
  64. // ->defaultFormat(['align'=>[Pxlswrite::FORMAT_ALIGN_CENTER,Pxlswrite::FORMAT_ALIGN_VERTICAL_CENTER]])//全局默认样式
  65. // ->field($orderField)//设置字段&表格头
  66. // ->setOrderData('generateOrderData',['order','time'],[],$pushHandle)
  67. // ->setColumn('A:B',50)
  68. // ->setColumn('F:F',50)
  69. // ->output();//输出excel文件到磁盘
  70. //单元格插入文本
  71. //for ($index = 0; $index < 10; $index++) {
  72. // $fileObj->insertText($index, 0, 'viest');
  73. // $fileObj->insertText($index, 1, 10000, '#,##0');
  74. //}
  75. //$filePath = $fileObj->output();
  76. $memory = floor((memory_get_peak_usage()) / 1024 / 1024) . "MB";#10M 22S
  77. $execute_time = time() - $time . 's';
  78. //同步下载
  79. //$fileObj->download($filePath);
  80. //ajax请求返回下载地址
  81. echo json_encode(['code' => 1, 'msg' => '导出完毕', 'url' => '/download.php?file=' . $filePath, 'data' => ['memory' => $memory, 'excute_time' => $execute_time]]);
  82. //数据生成器--封装模拟数据获取的方法
  83. function generateData()
  84. {
  85. $db = DB::getInstance();
  86. $step = 10000;
  87. for ($i = 0; $i < 10000; $i = $i + $step) {
  88. yield $db->get_records_sql("select * from sheet1 limit {$i},{$step}", null, PDO::FETCH_ASSOC);
  89. }
  90. }
  91. //格式化字段值
  92. function myFormat($v, $values)
  93. {
  94. return $v . '自定义格式化-' . $values['id'];
  95. }
  96. //数据生成器--获取订单数据,数据格式如下,字段数量名称不限,只支持一个item数组
  97. //$data = [
  98. // [
  99. // 'order'=>'20200632555' ,
  100. // 'time'=>'2020-06-30 12:30:10',
  101. // 'username'=>'张三',
  102. // 'address'=>'成都',
  103. // 'phone'=>'17756891562',
  104. // 'item'=> [
  105. // [
  106. // 'itemnumer'=>'2020515',
  107. // 'productname'=>'商品1',
  108. // 'mark'=>'备注',
  109. // ],
  110. // ],
  111. // ]
  112. //];
  113. function generateOrderData(){
  114. for ($i=0;$i<100;$i++){
  115. $order = [];
  116. $range = mt_rand(0,5);
  117. for($k = 0;$k<$range;$k++){
  118. $orderId = date('YmdHis').rand(1000,9999);
  119. $range2 = mt_rand(1,5);
  120. $item = [];
  121. for($j = 0;$j<$range2;$j++){
  122. $item[] = [
  123. 'itemnumer'=>$orderId,
  124. 'productname'=>'商品'.rand(10,99),
  125. 'amount'=>'1',
  126. 'mark'=>'备注',
  127. ];
  128. }
  129. $order[] = [
  130. 'order'=>$orderId ,
  131. 'time'=>date('Y-m-d H:i:s'),
  132. 'username'=>'张三',
  133. 'address'=>'成都',
  134. 'phone'=>'17756891562',
  135. 'item'=> $item,
  136. ];
  137. }
  138. yield $order;
  139. }
  140. }