kk Blog —— 通用基础


date [-d @int|str] [+%s|"+%F %T"]
netstat -ltunp

PHPExcel防止大数以科学计数法显示

在使用PHPExcel来进行数据导出时，常常需要防止有些数字(如手机号、身份证号)以科学计数法显示，我们可以采用下面的方式来解决:

setCellValueExplicit第三个参数用PHPExcel_Cell_DataType::TYPE_STRING

1

setCellValueExplicit('A1', 13211223344, PHPExcel_Cell_DataType::TYPE_STRING);

PHP_XLSXWriter 导出excel

PHP_XLSXWriter.tar

http://www.lynnk.cn/post/49.html

https://github.com/mk-j/PHP_XLSXWriter

https://blog.csdn.net/qq_41049126/article/details/89532403

https://learnku.com/articles/43135

相比于PHPExcel，PHP_XLSXWriter是一个小而强悍的Excel读写插件，它并没有PHPExcel功能丰富，但是它导出速度非常快，非常适合于数据量特别大，报表格式不是很复杂的导出需求

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);
 
//设置 header，用于浏览器下载
$filename = "example.xlsx";
header('Content-disposition: attachment; filename="' . XLSXWriter::sanitize_filename($filename) . '"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
 
# 表格样式
$styles1 = array(
    'font'         => 'Arial',
    'font-size'    => 12,
    'font-style'   => 'bold',  #bold, italic, underline, strikethrough or multiple ie: 'bold,italic'
    'color'        => '#333',
    'fill'         => '#fff',  # 背景填充
    'halign'       => 'center',  # 水平位置 general, left, right, justify, center
    'border'       => 'left,right,top,bottom', # 边界，默认是虚线，最好和border-style一起用 left, right, top, bottom, or multiple ie: 'top,left'
    'border-style' => 'thin',  # 边框样式，要先设置border thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
    'border-color' => '#333',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
    'valign'       => 'center', # 垂直位置 bottom, center, distributed
    'height'       => 50,  # 行高
    // 'collapsed'       => true,  # 未知
    // 'hidden'       => true,  # 隐藏行
);
 
# 每列标题头
$header = array(
    'created'     => 'date',
    'product_id'  => 'integer',
    'quantity'    => '#,##0.00', #价格 #,##0.00表示小数位两个，减少或增加改变长度
    'amount'      => 'price',
    'description' => 'string',
    'tax'         => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
);
 
# 列样式
$col_options = [
    'widths'       => [20, 30, 20, 40, 40],  # 宽度
    'auto_filter'  => true,  # 筛选
    // 'freeze_rows' => 2,  # 冻结
    // 'freeze_columns' => 2,  # 冻结
    // 'suppress_row' => true,
];
 
# 表数据
$rows = array(
    array('2015-01-01', '1', '-50.5', '2010-01-01 23:00:00', '2012-12-31 23:00:00', '=D2'),
    array('2003', '=B1', '23.5', '2010-01-01 00:00:00', '2012-12-31 00:00:00', '=D2*0.05'),
);
 
$writer = new XLSXWriter();
 
$writer->setTitle('标题');
$writer->setSubject('主题');
$writer->setAuthor('作者名字');
$writer->setCompany('公司名字');
$writer->setKeywords('关键字');
$writer->setDescription('描述');
$writer->setTempDir('临时目录');
# 合并单元格，第一行的大标题
$writer->markMergedCell('Sheet1', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 5);
# 每列标题头
$writer->writeSheetHeader('Sheet1', $header, $col_options);
 
# 表数据行插入
foreach ($rows as $row) {
    $writer->writeSheetRow('Sheet1', $row, $styles1);
}
 
#统计行数 返回行数
$writer->countSheetRows('Sheet1');
 
# 输出文档
$writer->writeToStdOut();
// $writer->writeToFile('example.xlsx');
// echo $writer->writeToString();  #没什么卵用
// $writer->log('错误信息');  # 控制台输出错误信息  数据支持数组、字符串
exit(0);
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

代码实现:
//writer 类
$writer = new XLSXWriter();
// 文件名
$filename = "example.xlsx";
// 设置 header，用于浏览器下载
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

// 导出的数据
$string = array (
0 =>
array (
    'payc_bill_time' => '2017-07-12 16:40:44',
    'payt_received_date' => '2017-07-12',
    'ci_name' => ' 租金 ',
    'payt_num' => 'YRZB(2012)A0047',
    'payt_scsr_name' => ' 李巧红 ',
    'payt_received' => '300.00',
    'paytd_type' => ' 现金 ',
    'emp_name' => ' 郑振标 ',
),
1 =>
array (
    'payc_bill_time' => '2017-07-12 16:39:55',
    'payt_received_date' => '2017-07-12',
    'ci_name' => ' 租金 ',
    'payt_num' => 'YRZB(2012)A0046',
    'payt_scsr_name' => '22222',
    'payt_received' => '45.00',
    'paytd_type' => ' 现金 ',
    'emp_name' => ' 郑振标 ',
)
);
// 每列的标题头
$title = array (
    0 => ' 开单时间 ',
    1 => ' 收款时间 ',
    2 => ' 开票项目 ',
    3 => ' 票据编号 ',
    4 => ' 客户名称 ',
    5 => ' 实收金额 ',
    6 => ' 收款方式 ',
    7 => ' 收款人 ',
);
// 工作簿名称
$sheet1 = 'sheet1';

// 对每列指定数据类型，对应单元格的数据类型
foreach ($title as $key => $item){
    $col_style[] = $key ==5 ? 'price': 'string';
}

// 设置列格式，suppress_row: 去掉会多出一行数据；widths: 指定每列宽度
$writer->writeSheetHeader($sheet1, $col_style, ['suppress_row'=>true,'widths'=>[20,20,20,20,20,20,20,20]] );
// 写入第二行的数据，顺便指定样式
$writer->writeSheetRow ($sheet1, ['xxx 财务报表 '],
    ['height'=>32,'font-size'=>20,'font-style'=>'bold','halign'=>'center','valign'=>'center']);

/ 设置标题头，指定样式 /
$styles1 = array ('font'=>' 宋体 ','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee',
    'halign'=>'center', 'border'=>'left,right,top,bottom');
$writer->writeSheetRow($sheet1, $title,$styles1);
// 最后是数据，foreach 写入
foreach ($data as $value) {
    foreach ($value as $item) { $temp[] = $item;}
    $rows[] = $temp;
    unset($temp);
}
$styles2 = ['height'=>16];
foreach($rows as $row){
    $writer->writeSheetRow($sheet1, $row,$styles2);
}

// 合并单元格，第一行的大标题需要合并单元格
$writer->markMergedCell($sheet1, $start_row=0, $start_col=0, $end_row=0, $end_col=7);
// 输出文档
$writer->writeToStdOut();
exit(0);
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

require_once 'tools/PHP_XLSXWriter/xlsxwriter.class.php';

public function export_xls($dir, $name, $data, $fields, $coms = null, $dstyle = null)
{
	$writer = new \XLSXWriter();
	$sheet1 = 'sheet1';

	$n = 0;
	$i = 0;
	$col_style = array();
	foreach ($fields as $field) {
		$title = isset($coms[$i]) ? $coms[$i] : ((in_array('bm', $fields) and $field == 'zgxz') ? 'lb' : $field);
		if (in_array($field, ['gwjb', 'xj']))
			$col_style[$title] = 'string';
		else
			$col_style[$title] = 'string';
		$i ++;
	}
	$col_options = [
		'widths' => [6,28,24,30,12,10],
		'suppress_row' => true,
		'freeze_rows' => 3, // 表头锁定
	];
	$writer->writeSheetHeader($sheet1, $col_style, $col_options);

	$data_style = array();
	$data_style['border'] = 'left,right,top,bottom';
	$data_style['border-style'] = 'thin';
	foreach ($data as $row) {
		$tmp = array();
		foreach ($fields as $field)
			if (isset($row[$field]))
				$tmp[] = $row[$field];
			elseif (in_array($field, ['gwjb', 'xj']))
				$tmp[] = '0';
			else
				$tmp[] = '';
		$writer->writeSheetRow($sheet1, $tmp, $data_style);
		$n += 1;
	}

	$path = "/var/www/html/downloads/{$dir}/";
	if (!is_dir($path))
		mkdir($path);
	$filename = "{$path}/{$name}.xlsx";
	$writer->writeToFile($filename);
	return ['filename' => $filename, 'row' => $n, 'col' => count($fields), 'size' => filesize($filename)];
}

PhpSpreadsheet的简单使用

composer.tar

https://www.cnblogs.com/woods1815/p/11372007.html

https://www.e-learn.cn/topic/3761556

由于PHPExcel已经不再维护，PhpSpreadsheet是PHPExcel的下一个版本。PhpSpreadsheet是一个用纯PHP编写的库，并引入了命名空间，PSR规范等。

安装

1

composer require phpoffice/phpspreadsheet

GitHub下载： https://github.com/PHPOffice/PhpSpreadsheet

读写

1
2
3

require_once 'composer/vendor/autoload.php';

其他基本和PHPExcel一样

PHPExcel 读写

PHPExcel.tar

https://www.cnblogs.com/cyfblogs/p/10115541.html

https://blog.csdn.net/beyond__devil/article/details/53457849

http://www.thinkphp.cn/code/2143.html

https://blog.csdn.net/nizaiwoan/article/details/88635315

读

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

require_once  'tools/PHPExcel/Classes/PHPExcel.php';
require_once  'tools/PHPExcel/Classes/PHPExcel/IOFactory.php';


public function readExcelOne($filename, $fullname, $fullpath, $type, $kqny)
{
	$ret = 0;
	if (!file_exists($fullpath)) {
		die('no file!');
	}

	$readfile = $fullpath;

	if ($type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
		$objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$objReader->setLoadSheetsOnly('月汇总');
		$objReader->setReadDataOnly(true);
		$objPHPExcel = $objReader->load($fullpath, 'utf-8');
	} elseif ($type == 'application/vnd.ms-excel') {
		$objReader = PHPExcel_IOFactory::createReader('Excel5');
		$objReader->setLoadSheetsOnly('月汇总');
		$objReader->setReadDataOnly(true);
		//$objPHPExcel = $objReader->load($fullpath, 'utf-8');
		$objPHPExcel = $objReader->load($readfile, 'utf-8');
	} elseif ($type == 'text/csv') {
		# TODO
	} else {
		die('文件类型错误');
	}

	$n = 0;
	//while ($n < $objPHPExcel->getSheetCount())
	{
		$sheet = $objPHPExcel->getSheetByName('月汇总');

		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumn = $sheet->getHighestColumn(); // 取得总列数

		//echo 'sheet: ' . $n . ', 行数 = '. (string)($highestRow-1) . ', 列数 = '. (string)$highestColumn . ' <br>';

		/*
		if ($highestColumn > 'H')
			$highestColumn = 'H';
		if ($highestRow < 2 || $highestColumn != 'H') {
			die('文件至少两行，至少8列');
		}
		*/

		$j = 1;
		$str = '';
		for ($k = 'A'; $k <= $highestColumn; $k++) {
			$str .= '"' . $sheet->getCell("$k$j")->getValue() . '"';//读取单元格
		}
		//echo $str . ' <br /><br />';
		if ($str != '"事件类型""控制器名称""卡号""持卡人""所属部门""事件时间""门禁点名称""事件源"') {
//                die('列的顺序固定为：事件类型 控制器名称 卡号 持卡人 所属部门 事件时间 门禁点名称 事件源');
		}
		$n = $n + 1;
	}

	$n = 0;
//        while ($n < $objPHPExcel->getSheetCount())
	{
		$sheet = $objPHPExcel->getSheetByName('月汇总');
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumn = $sheet->getHighestColumn(); // 取得总列数

		$filerow = 0;
		$inbmh = '';
		$empty_r = 0;
		for($j = 5; $j <= $highestRow; $j ++) {
			$kv = ['no' => 1,
				'zgh' => 2,
				'xm' => 3,
				'bz' => 25];
			$args = array();
			foreach ($kv as $k => $v) {
				$vv = chr(ord('A') - 1 + $v);
				try {
					$args[$k] = (string)$sheet->getCell("$vv$j")->getCalculatedValue(); //读取单元格
				} catch (Exception $e) { // 可能链接外部文件
					$args[$k] = (string)$sheet->getCell("$vv$j")->getValue(); //读取单元格
				}
			}
			if (strlen($args['zgh']) < 4 or !is_numeric($args['zgh'])) {
				$empty_r ++;
				if ($empty_r > 100)
					break;
				continue;
			}
			$empty_r = 0;

			$filerow++;
		}
		$n = $n + 1;
	}
	return $ret;
}

写

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

public function export_xls2($dir, $name, $data, $fields, $coms = null, $colstyle = null, $sheetname = null)
{
	// 文件名和文件类型
	$path = "/var/www/html/downloads/{$dir}/";
	if (!is_dir($path))
		mkdir($path);
	$filename = "{$path}/{$name}.xlsx";
	$filetype = "xlsx";

	$obj = new PHPExcel();

	// 以下内容是excel文件的信息描述信息
	$obj->getProperties()->setCreator(''); //设置创建者
	$obj->getProperties()->setLastModifiedBy(''); //设置修改者
	$obj->getProperties()->setTitle(''); //设置标题
	$obj->getProperties()->setSubject(''); //设置主题
	$obj->getProperties()->setDescription(''); //设置描述
	$obj->getProperties()->setKeywords('');//设置关键词
	$obj->getProperties()->setCategory('');//设置类型

	// 设置当前sheet
	$obj->setActiveSheetIndex(0);

	$sheet = $obj->getActiveSheet();

	// 设置当前sheet的名称
	if ($sheetname != null)
		$sheet->setTitle($sheetname);

	// 列标
	$list = array();
	for ($i = 0; $i < count($fields); $i ++) {
		$A = (int)($i / 26);
		$B = (int)($i % 26);
		$L = ($A > 0) ?  chr($A+64).chr($B+65) : chr($B+65);
		array_push($list, $L);

		$field = $fields[$i];
		$title = isset($coms[$i]) ? $coms[$i] : ((in_array('bm', $fields) and $field == 'zgxz') ? 'lb' : $field);
		// 填充第一行数据
		$sheet->setCellValue($L . '1', $title);

		if ($colstyle == null)
			$sheet->getStyle($L)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
		else {
		//    if ($colstyle[$i] == '0')
		//        $sheet->getStyle($L)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
			if ($colstyle[$i] == '1')
				$sheet->getStyle($L)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
		}
	}
/*
	// 设置列宽
	$sheet->getColumnDimension('A')->setWidth(40);
	$sheet->getColumnDimension('B')->setWidth(10);
*/

	$n = 1;
	foreach ($data as $row) {
		$n = $n + 1;
		for ($i = 0; $i < count($fields); $i ++) {
			$field = $fields[$i];
			if ($colstyle == null)
				$sheet->setCellValueExplicit($list[$i] . ($n), $row[$field]); // PHPExcel_Cell_DataType::TYPE_STRING);
			else {
				if ($colstyle[$i] == '0')
					$sheet->setCellValue($list[$i] . ($n), $row[$field]); //, PHPExcel_Cell_DataType::TYPE_NUMERIC);
				if ($colstyle[$i] == '1')
					$sheet->setCellValueExplicit($list[$i] . ($n), $row[$field], PHPExcel_Cell_DataType::TYPE_STRING);
			}
		}
	}

	// 导出
	ob_clean();
	if ($filetype == 'xls') {
		/*
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="' . $filename);
		header('Cache-Control: max-age=1');
		$objWriter = new PHPExcel_Writer_Excel5($obj);
		$objWriter->save('php://output');
		*/
		$objWriter = new PHPExcel_Writer_Excel5($obj);
		$objWriter->save($filename);
	} elseif ($filetype == 'xlsx') {
		/*
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="' . $filename);
		header('Cache-Control: max-age=1');
		$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
		$objWriter->save('php://output');
		*/
		$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
		$objWriter->save($filename);
	}
	return ['filename' => $filename, 'row' => $n-1, 'col' => count($fields), 'size' => filesize($filename)];
}

python读写Excel，优化PHPExcel

PHPExcel getCalculatedValue() 可能很慢，一次需要30ms。预先有python处理后就很快了

1
2

yum install python-pip
pip install xlrd==1.2.0 xlwt

https://www.cnblogs.com/caesar-id/p/11802440.html#top

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

#coding:utf-8
import xlrd
import xlwt
import sys

if len(sys.argv) != 3:
	print 'python export.py xlsx sheet'
	exit(1)

filename = sys.argv[1]
sheetname = sys.argv[2]

data = xlrd.open_workbook(filename)
#table = data.sheet_by_index(0)
table = data.sheet_by_name(sheetname.decode('utf-8'))

workbook = xlwt.Workbook(encoding = 'utf-8')
worksheet = workbook.add_sheet(sheetname)

for rn in range(table.nrows):
	line = table.row_values(rn)
	j = 0
	for v in line:
		worksheet.write(rn, j, line[j])
		j = j + 1

workbook.save((filename+'.xls'))