php導入導出excel/csv
類庫：
"phpoffice/phpspreadsheet": "1.12",
1 導入excel/csv，結果array數組
private function importFromExcel($filePath){
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
$insert = [];
try {
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('Unknown data format'));
}
$currentSheet = $PHPExcel->getSheet(0); //讀取文(wén)件中(zhōng)的第一個工(gōng)作(zuò)表 $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号 $allRow = $currentSheet->getHighestRow(); //取得一共有(yǒu)多(duō)少行 $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$fields[] = $val;
}
}
$insert[] = $fields;
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$values[] = is_null($val) ? '' : $val;
}
$flag = false;
foreach ($values as $v) {
if (!empty($v)) $flag = true;
}
if ($flag) $insert[] = $values;
}
} catch (\Exception $exception) {
$this->error($exception->getMessage());
}
if (!$insert) {
$this->error(__('No rows were updated'));
}
return $insert;
}
2 導出CSV，不依賴庫
function exportToCvs($fileName = '', $headArr = [], $data = []) {
ini_set('memory_limit', '1024M'); //設置程序運行的内存 ini_set('max_execution_time', 0); //設置程序的執行時間,0為(wèi)無上限 ob_end_clean(); //清除内存 ob_start();
header("Content-Type: text/csv");
header("Content-Disposition:filename=" . $fileName . '.csv');
$fp = fopen('php://output', 'w');
fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
fputcsv($fp, $headArr);
$index = 0;
foreach ($data as $item) {
if ($index == 1000) { //每次寫入1000條數據清除内存 $index = 0;
ob_flush();//清除内存 flush();
}
$index++;
fputcsv($fp, $item);
}
ob_flush();
flush();
ob_end_clean();
exit();
}
3 導出excel
public function exportToExcel($title, $headers=[], $datas){
// Create new Spreadsheet object $spreadsheet = new Spreadsheet();
// Set document properties $spreadsheet->getProperties()->setCreator('syx')
->setLastModifiedBy('syx')
->setTitle($title)
->setSubject($title)
->setDescription('')
->setKeywords('')
->setCategory('');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet $worksheet = $spreadsheet->setActiveSheetIndex(0);
$worksheet->setTitle("數據");
$styleArray = array(
'font' => array(
'bold' => false,
'color' => array('rgb' => '000000'),
'size' => 12,
'name' => 'Verdana' ));
// Add some data 1 // $spreadsheet->setActiveSheetIndex(0) // ->setCellValue('A1', 'Hello') // ->setCellValue('B2', 'world!') // ->setCellValue('C1', 'Hello') // ->setCellValue('D2', 'world!'); // Add some data 2 // $worksheet->setCellValueByColumnAndRow(1, 1, 'Hello'); $line = 0;
if (!empty($headers)) {
$line++;
$col = 1;
foreach ($headers as $field => $value) {
$value = ' '.$value;
$worksheet->setCellValueByColumnAndRow($col, $line, $value);
$worksheet->getCellByColumnAndRow($col, $line)->getStyle()->applyFromArray($styleArray);
$col++;
}
}
foreach ($datas as $index => $item) {
$line++;
$col = 1;
foreach ($item as $field => $value) {
$value = ' '.$value;
$worksheet->setCellValueByColumnAndRow($col, $line, $value);
$worksheet->getCellByColumnAndRow($col, $line)->getStyle()->applyFromArray($styleArray);
$col++;
}
}
// Redirect output to a client’s web browser (Xlsx) 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 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}
over
留言