PHP Excel Laravel 模板下载,设置下拉选择框

laravel PHP Excel 自定义模板下拉框设置

1.由于Excel限制字符长度255.所有需要将下拉框设置放在另一个sheet中,然后再下拉框中设置属性读取另一个sheet中的值
2.上代码:

  1. /**
  2. * Description: 下载固定的模板,并设置下拉选项值,突破255限制
  3. * Author: WangSx
  4. * DateTime: 2020-04-20 20:27
  5. * @param $file_name 导出的文件名
  6. * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
  7. * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
  8. */
  9. public function exports_file($file_name)
  10. {
  11. @ob_clean();
  12. // 固定模板地址
  13. $file= base_path().'/storage/app/file/talents.xlsx';
  14. /** @var Xlsx $objRead */
  15. $objRead = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
  16. /* 建立excel对象
  17. */
  18. $obj = $objRead->load($file,'utf-8');
  19. # 处理岗位数据过多,Excel下拉框255字符限制,选择使用另一个sheet进行赋值
  20. try {
  21. // 选择第二个sheet
  22. $sheet2 = $obj->getSheet(1);
  23. } catch (\Exception $exception) {
  24. // 一般报错,说明没有第二个sheet,创建一个并设置名称
  25. $sheet2 = $obj->createSheet(1);
  26. $sheet2->setTitle('Sheet2');
  27. }
  28. // 设置起始行数
  29. $sheet2_cell_start_p = 1114;
  30. // 设置默认结束行数
  31. $sheet2_cell_end_p = 111100;
  32. // 下拉选项的值
  33. $position = [
  34. 1,2,3,4,5,6,7,8,9
  35. ];
  36. if (!empty($position)) {
  37. foreach ($position as $k => $v) {
  38. $sheet2_cell_end_p = $k + $sheet2_cell_start_p;
  39. $cell = "W" . $sheet2_cell_end_p; // 设置W列
  40. $sheet2->setCellValue($cell, $v);
  41. }
  42. // 设置完后将 W列集体隐藏
  43. $sheet2->getColumnDimension("W")->setVisible(false);
  44. }
  45. // 起始行
  46. $sheet2_cell_start_r = 1114;
  47. // 结束行
  48. $sheet2_cell_end_r = 111100;
  49. $recruitment = [
  50. "德玛西亚",
  51. "火女",
  52. "黄忠",
  53. "赵子龙",
  54. "张飞"
  55. ];
  56. if (!empty($recruitment)) {
  57. foreach ($recruitment as $k => $v) {
  58. $sheet2_cell_end_r = $k + $sheet2_cell_start_r;
  59. $cell = "X" . $sheet2_cell_end_r;
  60. $sheet2->setCellValue($cell, $v);
  61. }
  62. $sheet2->getColumnDimension("X")->setVisible(false);
  63. }
  64. /* 获取指定的sheet0表 */
  65. $currSheet = $obj->getSheet(0);
  66. for ($i = 0; $i < 1000; $i++) {
  67. $sheet2_cell_start_ps = "W" . "$" . $sheet2_cell_start_p;
  68. $sheet2_cell_end_ps = "W" . "$" . $sheet2_cell_end_p;
  69. $objValidation1 = $currSheet->getCell('E' . ($i + 4))->getDataValidation(); //从第二行开始有下拉样式
  70. $objValidation1->setType('list')
  71. ->setErrorStyle('information')
  72. ->setAllowBlank(false)
  73. ->setShowInputMessage(true)
  74. ->setShowErrorMessage(true)
  75. ->setShowDropDown(true)
  76. ->setErrorTitle('输入的值有误')
  77. ->setError('您输入的值不在下拉框列表内.')
  78. ->setPromptTitle('')
  79. ->setPrompt('')
  80. ->setFormula1("Sheet2!\${$sheet2_cell_start_ps}:\${$sheet2_cell_end_ps}");
  81. $sheet2_cell_start_rs = "X" . "$" . $sheet2_cell_start_r;
  82. $sheet2_cell_end_rs = "X" . "$" . $sheet2_cell_end_r;
  83. $objValidation2 = $currSheet->getCell('F' . ($i + 4))->getDataValidation(); //从第二行开始有下拉样式
  84. $objValidation2->setType('list')
  85. ->setErrorStyle('information')
  86. ->setAllowBlank(false)
  87. ->setShowInputMessage(true)
  88. ->setShowErrorMessage(true)
  89. ->setShowDropDown(true)
  90. ->setErrorTitle('输入的值有误')
  91. ->setError('您输入的值不在下拉框列表内.')
  92. ->setPromptTitle('')
  93. ->setPrompt('')
  94. ->setFormula1("Sheet2!\${$sheet2_cell_start_rs}:\${$sheet2_cell_end_rs}");
  95. $objValidation3 = $currSheet->getCell('B' . ($i + 4))->getDataValidation(); //从第二行开始有下拉样式
  96. $objValidation3->setType('list')
  97. ->setErrorStyle('information')
  98. ->setAllowBlank(false)
  99. ->setShowInputMessage(true)
  100. ->setShowErrorMessage(true)
  101. ->setShowDropDown(true)
  102. ->setErrorTitle('输入的值有误')
  103. ->setError('您输入的值不在下拉框列表内.')
  104. ->setPromptTitle('')
  105. ->setPrompt('')
  106. ->setFormula1('"男,女"');
  107. }
  108. $writer = new Xlsx($obj);
  109. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
  110. header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');//告诉浏览器输出浏览器名称
  111. header('Cache-Control: max-age=0');
  112. $writer->save('php://output');
  113. @ob_flush();
  114. @flush();
  115. exit;
  116. }


评论 0

发表评论

Top