早睡早起,方能养生
Sleep early rise early, way to keep healthy

PHPOffice导出xlsx表格文件

super
2023-12-04 18:06
views 540

把一些bug改了,现在7.4版本能直接使用

 

文件下载

 

代码:

 

function excel_column_str($key) {
    $array = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ');
    return $array[$key];
}

function excel_column($key, $columnnum = 1) {
    return excel_column_str($key) . $columnnum;
}

function exportXlsx($data = [], $config = []) {

	$title = isset($config['title']) ? $config['title'] : 'data';

	require_once './phpexcel1.8/PHPExcel.php';

	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

	// Set document properties
	$objPHPExcel->getProperties()
		->setCreator("Maarten Balliauw")
		->setLastModifiedBy("Maarten Balliauw")
		->setTitle("Office 2007 XLSX Test Document")
		->setSubject("Office 2007 XLSX Test Document")
		->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
		->setKeywords("office 2007 openxml php")
		->setCategory("Test result file");


	// Add some data
	$sheet = $objPHPExcel->setActiveSheetIndex(0);

	// 设置表头
	$rowNum = 1;
	foreach ($config['column'] as $key => $column) {
        $sheet->setCellValue(excel_column($key, $rowNum), $column['title']);

        if (!empty($column['width'])) {
            $sheet->getColumnDimension(excel_column_str($key))->setWidth($column['width']);
        }
    }
	++$rowNum;

    // 设置数据
	$len = count($config['column']);
    foreach ($data as $row) {
        $i = 0;
        while ($i < $len) {
            $value = (isset($row[$config['column'][$i]['field']]) ? $row[$config['column'][$i]['field']] : ' ');
            if($config['column'][$i]['field']=='bankcard') { // 特殊优化,例如是银行卡号,根据自己的业务字段名自行更改
                //"b$i", chunk_split("123456789 ",4," ")
                $sheet->setCellValue(excel_column($i, $rowNum), "".$value);
            } else {
                $sheet->setCellValue(excel_column($i, $rowNum),"".$value);
            }
            ++$i;
        }
        ++$rowNum;
    }

    // 设置行高
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(24);

	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle($title);


	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);

	// Redirect output to a client’s web browser (Excel2007)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
	header('Cache-Control: max-age=0');
	// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');

	// If you're serving to IE over SSL, then the following may be needed
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
}

 

使用:

 

$data = [
	[
		'name' => '小王',
		'age' => 22,
		'gender' => '男'
	],
	[
		'name' => '小美',
		'age' => 23,
		'gender' => '女'
	],
];

exportXlsx($data, [
	'title' => 'excel title',
	'column' => [
		['title' => '姓名', 'field' => 'name', 'width' => 20],
		['title' => '年龄', 'field' => 'age', 'width' => 20],
		['title' => '性别', 'field' => 'gender', 'width' => 20],
	]
]);


分享
0 条讨论
top