php中通用的excel导出方法实例

1

2

3

4

5

6

7

8

//AAA转换

public static function toAAA($dec)

{

 if ($dec < 0) return '';

 $y = $dec % 26;

 $x = floor($dec / 26);

 return self::toAAA($x - 1) . chr($y + 65);

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

//导出xls

public static function exportXls($array)

{

 set_time_limit(0);

 include(Url::to('@vendor/moonland/phpexcel/PHPExcel.php'));

 include(Url::to('@vendor/moonland/phpexcel/PHPExcel/Writer/Excel2007.php'));

 $titles = $array['titles'];

 $fields = $array['fields'];

 $list = $array['list'];

 $name = $array['name'];

 $count = count($titles);

 $keys = [];//A=>chr(65)

 foreach ($titles as $k => $v) {

 $keys[] = self::toAAA($k);

 }

 $objPHPExcel = new \PHPExcel();

 $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

 $objPHPExcel->setActiveSheetIndex(0);

 $activeSheet = $objPHPExcel->getActiveSheet();

 $activeSheet->setTitle($name);

 $activeSheet->getStyle("A1:{$keys[$count-1]}1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

 $activeSheet->mergeCells("A1:{$keys[$count-1]}1");

 $activeSheet->setCellValue('A1', $name);

 //设置title,样式

 foreach ($titles as $key => $title) {

 $activeSheet->setCellValue($keys[$key] . '2', $title);

 $activeSheet->getColumnDimension($keys[$key])->setWidth(20);

 $activeSheet->getRowDimension(($key + 1))->setRowHeight(18);

 }

 $i = 3;

 foreach ($list as &$item) {

 foreach ($keys as $k => $v) {

  $val = isset($item[$fields[$k]]) ? $item[$fields[$k]] . ' ' : ' ';

  $activeSheet->setCellValue($v . $i, $val);

 }

 $i++;

 }

 $fileName = $name . "_" . date('Y_m_d_His') . '.xlsx';

 header("Cache-Control: public");

 header("Pragma: public");

 header("Content-type:application/vnd.ms-excel");

 header("Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName));

 header('Content-Type:APPLICATION/OCTET-STREAM');

 ob_clean();

 ob_start();

 $objWriter->save('php://output');

 ob_end_flush();

}

//AAA转换

public static function toAAA($dec)

{

 if ($dec < 0) return '';

 $y = $dec % 26;

 $x = floor($dec / 26);

 return self::toAAA($x - 1) . chr($y + 65);

}