export_demo.php 5.7 KB

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