使用phpExcel导出xls数据分享


使用phpExcel导出xls数据,将需要导出的数据格式成类似如下数组:

 Array (     [财务] => Array         (             [0] => Array                 (                     [0] => Array                         (                             [width] => 6                             [value] => ID                             [align] => center                         )                      [1] => Array                         (                             [width] => 10                             [value] => 日期                             [align] => center                         )                      [2] => Array                         (                             [width] => 8                             [value] => 收入/支出                             [align] => left                         )                      [3] => Array                         (                             [width] => 10                             [value] => 金额                             [align] => right                         )                  )              [1] => Array                 (                     [0] => 1                     [1] => 2020-04-21                     [2] => 支出                     [3] => 20.00                 )              [2] => Array                 (                     [0] => 2                     [1] => 2020-04-21                     [2] => 支出                     [3] => 27.38                 )          )      [计划] => Array         (             [0] => Array                 (                     [0] => Array                         (                             [width] => 6                             [value] => ID                             [align] => center                         )                      [1] => Array                         (                             [width] => 20                             [value] => 标题                             [align] => left                         )                      [2] => Array                         (                             [width] => 6                             [value] => 重要程度                             [align] => center                         )                      [3] => Array                         (                             [width] => 30                             [value] => 内容                             [align] => left                         )                  )              [1] => Array                 (                     [0] => 2                     [1] => 测试计划                     [2] => 正常                     [3] => 内容                 )          )  )

导出方法代码:

 /**  * 使用 phpExcel 导出 Excel  * @param array $data 内容  * @param string $fileName 文件名  * @throws PHPExcel_Exception  * @throws PHPExcel_Reader_Exception  * @throws PHPExcel_Writer_Exception  */ function exportExcel($data = [], $fileName = '账单导出数据.xls') {     if (empty($data)) die('内容不能为空!');     if (empty($fileName)) die('文件名不能为空!');      // 设置页面等待时间     set_time_limit(60);     // 不限制内存     ini_set('memory_limit', -1);     // 引入库     require APP_ROOT . 'phpExcel/PHPExcel.php';      $phpExcel = new PHPExcel();     // 所有单元格居中     //$phpExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);      $i = 0;     // 外循环产生每一项 Sheet     foreach ($data as $key => $lists) {         // 创建新的工作空间 sheet         if ($i > 0) {             $phpExcel->createSheet();         }          $phpExcel->setActiveSheetIndex($i);         // 给 Sheet 设置名字         $phpExcel->getActiveSheet()->setTitle($key);          // 内容         $line = 1;         // 外循环产生每一列         foreach ($lists as $item) {             // 内循环产生每一行             foreach ($item as $k => $value) {                 $columnName = PHPExcel_Cell::stringFromColumnIndex($k);                  if ($line == 1) {                     if (isset($value['width'])) {                         $phpExcel->getActiveSheet()->getColumnDimension($columnName)->setWidth($value['width']);                     }                      if (isset($value['align'])) {                         $phpExcel->getActiveSheet()->getStyle($columnName)->getAlignment()->setHorizontal($value['align']);                     }                      $value = isset($value['value']) ? $value['value'] : (is_string($value) ? $value : '');                 }                  // 数字列转换为字母列 如:27变为AA                 $phpExcel->getActiveSheet()->setCellValue($columnName . $line, $value);             }             $line++;         }         $i++;     }      $fileExt = substr($fileName, strrpos($fileName, '.'));     $fileName = substr($fileName, 0, strrpos($fileName, '.'));     $fileName = $fileName . '-' . date('Y-m-d H-i-s') . $fileExt;      header('Pragma: public');     header('Expires: 0');     header('Cache-Control: must-revalidate, post-check=0, pre-check=0');     header('Content-Type: application/force-download');     header('Content-Type: application/vnd.ms-execl');     header('Content-Type: application/octet-stream');     header('Content-Type: application/download');     header('Content-Disposition: attachment; filename=' . iconv('UTF-8', 'UTF-8', $fileName));     header('Content-Transfer-Encoding: binary');     $objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');     $objWriter->save('php://output');     exit; }

发表评论

电子邮件地址不会被公开。 必填项已用*标注