单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列2007以上版本是16384列。xlswriterxlswriter - PHP 高性能 Excel 扩展功能类似phpspreadsheet。它能够处理非常大的文件同时保持内存使用率非常低。/** * 使用php扩展导出文件大数据方案 * * param $fileName * param $header * param $list * throws \Exception */ public function xlsWriter($fileName, $header, $list) { if (!extension_loaded(xlswriter)) { throw new \Exception(请先安装php的xlswriter扩展); } $config [path $this-_path]; $excel new \Vtiful\Kernel\Excel($config); //WPS需要关闭 zip64否则打开文件可能报文件损坏该问题已反馈给WPS修复进度未知。第三个参数 False 即为关闭 ZIP64 $fileObject $excel-constMemory($fileName, NULL, false); $fileHandle $fileObject-getHandle(); $format new \Vtiful\Kernel\Format($fileHandle); $boldStyle $format-bold()-toResource(); $data []; foreach ($list as $info) { $row []; foreach ($header as $key $title) { $row[] $info[$key]; } $data[] $row; } $fileObject-freezePanes(1, 0)-setRow(A1, 20, $boldStyle) //固定表头设置样式 -header(array_values($header)) -data($data)-output(); } // 读取文件 $data $excel-openFile(text.xlsx)-openSheet()-getSheetData(); 0.000 #,##0 #,##0.00 0.00 // 数字格式化 $format new \Vtiful\Kernel\Format($fileHandle); $numberStyle $format-number(#,##0)-toResource(); $filePath $fileObject-header([name, balance]) -data([ [wjx, 100000] ]) -setColumn(B:B, 10, $numberStyle) // 应用样式 -output(); #插入公式文本 $excel new \Vtiful\Kernel\Excel($config); $freeFile $excel-fileName(free.xlsx) -header([name, money]); for($index 1; $index 10; $index) { $freeFile-insertText($index, 0, viest); $freeFile-insertText($index, 1, 10); } $freeFile-insertText(12, 0, Total); $freeFile-insertFormula(12, 1, SUM(B2:B11)); $freeFile-output();IDE Helpercomposer require viest/php-ext-xlswriter-ide-helper:dev-master导出100万行数据单行27列数据类型均为字符串单个字符串长度为19普通模式耗时 29S内存只需 2083MB固定内存模式仅需 52S内存仅需 1MB导入100万行数据单行1列数据类型为INT全量模式耗时 3S内存仅 558MB游标模式耗时 2.8S内存仅 1MBwget https://pecl.php.net/get/xlswriter-1.3.6.tgz tar -zxvf xlswriter-1.3.6.tgz cd xlswriter-1.3.6 phpize ./configure --enable-reader make make install [xlswriter] extensionxlswriter.sophpspreadsheetphp spreadsheet是phpexcel的下一个版本。它打破了兼容性大大提高了代码基础质量名称空间、PSR兼容性、使用最新的PHP语言功能等。因为所有的努力都转移到了phpspreadsheetphpexcel将不再被维护。对phpexcel、补丁和新功能的所有贡献都应该以phpspreadsheet主分支为目标。//创建一个处理对象实例 $spreadsheet new Spreadsheet(); $spreadsheet-setActiveSheetIndex(0); $objActSheet $spreadsheet-getActiveSheet();由于Office2003兼容性包中的错误打开Xlsx电子表格时可能会出现一些小问题主要与公式计算有关。您可以使用以下代码启用Office2003兼容性$writer new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); $writer-setOffice2003Compatibility(true); $writer-save(05featuredemo.xlsx);//设置文档基本属性 $spreadsheet-getProperties() -setCreator(Helloweba) //作者 -setLastModifiedBy(Yuegg) //最后修改者 -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); //分类//设置当前的sheet索引用于后续的内容操作。一般只有在使用多个sheet的时候才需要显示调用。 //缺省情况下PHPExcel会自动创建第一个sheet被设置SheetIndex0 $spreadsheet-setActiveSheetIndex(0); //创建新的工作标签 $newSheet $spreadsheet-createSheet(); //设置当前活动sheet的名称 $objActSheet-setTitle(测试Sheet);设置单元格格式为文本//公式 $objActSheet-setCellValue(A4,SUM(A2:A2)); $objActSheet-setCellValue(K6, F6G6H6); //显式指定内容类型 $objActSheet-setCellValueExplicit(A5,8757584,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING); //使用\n进行单元格内换行 $value Hello World! . PHP_EOL . Next Line; $sheet-setCellValue(A1, $value); $sheet-getStyle(A1)-getAlignment()-setWrapText(true);//合并单元格 $objActSheet-mergeCells(B1:C22); //分离单元格 $objActSheet-unmergeCells(B1:C22); //插入行或列 $objActSheet-insertNewRowBefore(6, 10); $objActSheet-removeRow(6, 10); $objActSheet-insertNewColumnBefore(E, 5); $objActSheet-removeColumn(E, 5);默认设置默认字体大小宽度高度对齐方式//worksheet默认style设置(和默认不同的需单独设置) $spreadsheet-getDefaultStyle()-getFont()-setName(华文仿宋)-setSize(12); $alignment $spreadsheet-getDefaultStyle()-getAlignment(); $alignment-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); $alignment-setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); //默认列宽 $objActSheet-getDefaultColumnDimension()-setWidth(15); //默认行宽 $objActSheet-getDefaultRowDimension()-setRowHeight(20); //设置宽度 $objActSheet-getColumnDimension(B)-setAutoSize(true); $objActSheet-getColumnDimension(A)-setWidth(30); //第10行高度 $objActSheet-getRowDimension(10)-setRowHeight(100); $objActSheet-getRowDimension(10)-setRowHeight($objActSheet-getDefaultRowDimension()-getRowHeight());设置单元格缩进$objActSheet-getStyle(A7)-getAlignment()-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT)-setIndent(2);//日期格式化 $spreadsheet-getActiveSheet()-setCellValue(D1, 2018-06-15); $spreadsheet-getActiveSheet()-getStyle(D1) -getNumberFormat() -setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); //设置格式为FORMAT_NUMBER避免某些大数字被使用科学记数方式显示配合下面的setAutoSize方法可以让每一行的内容 //都按原始内容全部显示出来。 $objStyleA5 $objActSheet-getStyle(A5); $objStyleA5-getNumberFormat()-setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00); //货币格式化 $objStyleA5-getNumberFormat()-setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objActSheet-getStyle(J11)-getNumberFormat()-setFormatCode(¥#,##0.00); //设置对齐方式 $objAlignA5$objStyleA5-getAlignment(); $objAlignA5-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT); $objAlignA5-setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); //设置边框 $objBorderA5$objStyleA5-getBorders(); $objBorderA5-getTop()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getTop()-getColor()-setARGB(FFFF0000);//边框color $objBorderA5-getBottom()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getLeft()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getRight()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); //从指定的单元格复制样式信息. $objActSheet-duplicateStyle($objStyleA5,B1:C22);设置CELL背景色//设置字体 $objActSheet-getStyle(B1:I2)-getFont()-setName(Candara);//字体 $objActSheet-getStyle(B1)-getFont()-setSize(20);//大小 $objActSheet-getStyle(B1)-getFont()-setBold(true);//加粗 $objActSheet-getStyle(B1)-getFont()-setUnderline(Font::UNDERLINE_SINGLE);//下划线 $objActSheet-getStyle(B1)-getFont()-getColor()-setARGB(Color::COLOR_WHITE); $objActSheet-getStyle(B1)-getFont()-setSuperscript(true);//设置上标 $objActSheet-getStyle(B1)-getFont()-setSubscript(true);//设置下标 //设置CELL背景色 $objActSheet-getStyle(A1:I2)-getFill() -setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) -getStartColor()-setARGB(FFFFFF); //添加图片 $drawing new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing-setName(Logo); $drawing-setDescription(Logo); $drawing-setPath(./images/officelogo.jpg); $drawing-setHeight(36); $drawing-setCoordinates(A1); /*设置图片要插入的单元格*/ $drawing-setWorksheet($objActSheet); //显示网格线: $objPHPExcel-getActiveSheet()-setShowGridlines(true); //Add a hyperlink to the sheet 添加链接 $objActSheet-setCellValue(E26, www.phpexcel.net); $objActSheet-getCell(E26)-getHyperlink()-setUrl(http://www.phpexcel.net); $objActSheet-getCell(E26)-getHyperlink()-setTooltip(Navigate to website); $objActSheet-getStyle(E26)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //Add conditional formatting $objConditional1 new PHPExcel_Style_Conditional(); $objConditional1-setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS); $objConditional1-setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN); $objConditional1-setCondition(0); $objConditional1-getStyle()-getFont()-getColor()-setARGB(PHPExcel_Style_Color::COLOR_RED); $objConditional1-getStyle()-getFont()-setBold(true); //Set autofilter 自动过滤 $objActSheet-setAutoFilter(A1:C9); //设置文档安全 打开需要密码Excel2007生效 $objPHPExcel-getSecurity()-setLockWindows(true); $objPHPExcel-getSecurity()-setLockStructure(true); $objPHPExcel-getSecurity()-setWorkbookPassword(PHPExcel); //设置工作表安全 全部只读 $objActSheet-getProtection()-setPassword(password);//设置保护密码 $objActSheet-getProtection()-setSheet(true);// This should be enabled in order to enable any of the following! $objActSheet-protectCells(A1:C22); //Set outline levels $objActSheet-getColumnDimension(E)-setOutlineLevel(1); $objActSheet-getColumnDimension(E)-setVisible(false); $objActSheet-getColumnDimension(E)-setCollapsed(true);//设置工作表的页面方向和大小 $spreadsheet-getActiveSheet()-getPageSetup() -setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE); $spreadsheet-getActiveSheet()-getPageSetup() -setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4); //页面设置缩放选项 $spreadsheet-getActiveSheet()-getPageSetup()-setFitToWidth(1); $spreadsheet-getActiveSheet()-getPageSetup()-setFitToHeight(0); //页边距 $spreadsheet-getActiveSheet()-getPageMargins()-setTop(1); $spreadsheet-getActiveSheet()-getPageMargins()-setRight(0.75); $spreadsheet-getActiveSheet()-getPageMargins()-setLeft(0.75); $spreadsheet-getActiveSheet()-getPageMargins()-setBottom(1); //指定打印区域 $spreadsheet-getActiveSheet()-getPageSetup()-setPrintArea(A1:E5,G4:M20); //PhpSpreadsheet可以在页面顶部/左侧重复特定的行/单元格。以下代码是如何在特定工作表的每个打印页面上重复第1至5行的示例 $spreadsheet-getActiveSheet()-getPageSetup()-setRowsToRepeatAtTopByStartAndEnd(1, 5);在单元格中添加评论要将注释添加到单元格请使用以下代码。下面的示例向单元格E11添加注释$spreadsheet-getActiveSheet() -getComment(E11) -setAuthor(Mark Baker); $commentRichText $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(PhpSpreadsheet:); $commentRichText-getFont()-setBold(true); $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(\r\n); $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(Total amount on the current invoice, excluding VAT.);单元格部分文字加粗、放大、添加颜色//创建一个富文本对象 $richText new \PhpOffice\PhpSpreadsheet\RichText\RichText(); $richText-createText(铁扇公主); //需要改变大小或颜色的文字内容 $payable $richText-createTextRun(芭蕉妹妹); $payable-getFont()-setBold(true); $payable-getFont()-setItalic(true); $payable-getFont()-setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN ) ); $richText-createText(牛魔王); $spreadsheet-getActiveSheet()-getCell(A18)-setValue($richText);//显示隐藏列$objPHPExcel-getActiveSheet()-getColumnDimension(C)-setVisible(true);$objPHPExcel-getActiveSheet()-getColumnDimension(D)-setVisible(false);//显示隐藏行$objPHPExcel-getActiveSheet()-getRowDimension(10)-setVisible(false);做软件中难免会遇到数据导入的功能而数据导入要生成一个模板模板中有些字段是需要固定那几种的下拉菜单就是EXCEL中的数据有效性简单研究一下下面把源码贴出来$objValidation $objActSheet-getCell(A1)-getDataValidation(); //这一句为要设置数据有效性的单元格 $objValidation - setType(PHPExcel_Cell_DataValidation::TYPE_LIST) - setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION) - setAllowBlank(false) - setShowInputMessage(true) - setShowErrorMessage(true) - setShowDropDown(true) - setErrorTitle(输入的值有误) - setError(您输入的值不在下拉框列表内.) - setPromptTitle(设备类型) - setFormula1(列表项1,列表项2,列表项3);列数字与字母的相互转化超过第26个字母Z有bugprint_r(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString(D));//echo 4 echo \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4) // echo E//phpexcel默认是没有冻结的下面是冻结列。定二则定全部$sheet-freezePane(A1);$sheet-freezePane(B1);冻结行$sheet-freezePane(D1);$sheet-freezePane(D2);不能再次定义A,b,c否则列冻结被替代public function freezePaneByColumnAndRow($pColumn 0, $pRow 0){$this-freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);}public function unfreezePane() {$this-freezePane();}$worksheet-setInputEncoding(UTF-8);//$freeze $sheet-getFreezePane();批量设置单元格格式$styleArray [ font [ bold true, ], borders [ //上下左右画线 allBorders [borderStyle \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], ], alignment [ horizontal \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, //横向居中 vertical \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, //纵向居中 wrap true, //自动换行 indent 2, //缩进2个字符 ], fill [ fillType \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR, rotation 90, startColor [ argb FFA0A0A0, ], endColor [ argb FFFFFFFF, ], ], ]; $spreadsheet-getActiveSheet()-getStyle(A1:J35)-applyFromArray($styleArray);设置外边框$styleArray [ borders [ outline [ borderStyle \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, color [argb FFFF0000], ], ], ]; $objActSheet-getStyle(B2:G8)-applyFromArray($styleArray);获得公式计算值$workbook-getActiveSheet()-getCell(G1)-getCalculatedValue();获得日期的格式化数值$workbook-getActiveSheet()-getCell(G2)-getFormattedValue();导出phpexcel大数据导出,数据追加19万行分20个sheet, 1万行是一个sheet,第一次到1个sheet数据其他数据追加到excel,分19次覆盖对应的sheet先用 PhpExcel 建立reader再load文件这样打开已经存在的文档然后再建立writer将reader中的数据都复制过来再用 PhpExcel 进行数据修改再以load的文件名覆盖保存。$reader new PHPExcel_Reader_Excel2007; $workbook $reader-load(document.xlsx); $workbook-getActiveSheet()-getSecurity()-setWorkbookPassword(your password); $workbook-getActiveSheet()-getCell(D1)-getValue(); $workbook-getActiveSheet()-setCellValueByColumnAndRow(1, 5, PhpSpreadsheet);//A5的值 $cellValue $spreadsheet-getActiveSheet()-getCellByColumnAndRow(1, 5)-getValue();//第2列第5行B5方法二分批导出导出多个文件,通过程序将所有文件加入压缩包使用phpspreadsheet进行导出excel的时候遇到了内存溢出的问题官方提供了memory saving的解决方案官方文档中提供了APC、redis和memcache的缓存方案。//关闭公式计算预处理 $writer new Xlsx($spreadsheet); $writer-setPreCalculateFormulas(false);切割excel大文件导入导出大数据导出分片查询每次读取1000条数据追加到导入文件或者生成新文件// 示例 $inputFileType Xls; $inputFileName ./sampleData/example2.xls; /** Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter */ class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { private $startRow 0; private $endRow 0; /** Set the list of rows that we want to read */ public function setRows($startRow, $chunkSize) { $this-startRow $startRow; $this-endRow $startRow $chunkSize; } public function readCell($column, $row, $worksheetName ) { // Only read the heading row, and the configured rows if (($row 1) || ($row $this-startRow $row $this-endRow)) { return true; } return false; } } $reader \PhpOffice\PhpSpreadsheet\IOFactory::createReader(Xlsx); $perpage 10000; $page 0; $chunkFilter new ChunkReadFilter(); $reader-setReadFilter($chunkFilter); while (true) { $startRow 2 $perpage * $page; $chunkFilter-setRows($startRow, $perpage); $spreadsheet $reader-load($filePath); $sheet $spreadsheet-getActiveSheet(); $sheetData $sheet-toArray(null, true, true, true); $sheetData array_slice($sheetData, $startRow - 1, $perpage); if (empty($sheetData)) { break; } $this-importDatas($sheetData); //业务处理函数 $page; }节省内存PhpSpreadsheet在工作表中平均每个单元格使用约1k因此大型工作簿可以迅速用尽可用内存。默认情况下PhpSpreadsheet将所有单元格对象保留在内存中。单元缓存提供了一种机制使PhpSpreadsheet可以将单元对象维护在较小的内存或非内存中例如在磁盘上在APCu中内存缓存或Redis中。这使您可以减少大型工作簿的内存使用量尽管以访问单元数据的速度为代价。要求将软件包放入您的项目中composer require cache/simple-cache-bridge cache/redis-adapter$client new \Redis(); $client-connect(127.0.0.1, 6379); $pool new \Cache\Adapter\Redis\RedisCachePool($client); $simpleCache new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool); \PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);读取CSV内容$reader \PhpOffice\PhpSpreadsheet\IOFactory::createReader(Csv); $reader-setReadDataOnly(true); $spreadsheet $reader-load(/mnt/d/文档/test.csv); $data $spreadsheet-getActiveSheet()-toArray(); $headers array_shift($data); //剔除表头读取XLSX内容?php class ExcelReader { private static $rowStart 7; private static $colStart 3; /** * 获取起始行. */ public static function getStartRow(): int { return self::$rowStart; } /** * 获取起始列. */ public static function getStartCol(): int { return self::$colStart; } /** * param string $destFile 文件路径 * param string $extensionName 扩展名 * param array $headerIndexArr [3 name, 4 mobile]; * * throws Exception */ public static function readExcelData(string $destFile, string $extensionName, array $headerIndexArr): array { // 验证文件路径和扩展名 if (!file_exists($destFile)) { throw new Exception(文件不存在: {$destFile}); } if (!is_readable($destFile)) { throw new Exception(文件不可读: {$destFile}); } if (!in_array(strtolower($extensionName), [xls, xlsx])) { throw new Exception(不支持的文件扩展名: {$extensionName}); } // 创建 Reader 对象 $readerType xls strtolower($extensionName) ? Excel5 : Excel2007; $objReader PHPExcel_IOFactory::createReader($readerType); $objReader-setReadDataOnly(true); try { $objPHPExcel $objReader-load($destFile); $workSheet $objPHPExcel-getSheet(0); $highestRow $workSheet-getHighestRow(); $highestColumn $workSheet-getHighestColumn(); $highestColumnIndex PHPExcel_Cell::columnIndexFromString($highestColumn); // 检查文件是否为空 if ($highestRow self::$rowStart || $highestColumnIndex self::$colStart) { return []; } $excelData []; for ($row self::$rowStart; $row $highestRow; $row) { $rowData []; for ($col self::$colStart; $col $highestColumnIndex; $col) { if (array_key_exists($col, $headerIndexArr)) { $cellValue trim((string) $workSheet-getCellByColumnAndRow($col, $row)-getValue()); $rowData[$headerIndexArr[$col]] $cellValue; } } if (!empty($rowData)) { $excelData[] $rowData; } } return $excelData; } catch (Exception $e) { throw new Exception(读取 Excel 文件时出错: .$e-getMessage()); } } /** * 设置起始行和列. * * param int $startRow 起始行号 * param int $startCol 起始列号 * * throws InvalidArgumentException 如果参数无效 */ protected static function setStartRowAndCol(int $startRow, int $startCol): void { if ($startRow 0 || $startCol 0) { throw new InvalidArgumentException(起始行和列必须大于等于0); } // 设置属性 self::$rowStart $startRow; self::$colStart $startCol; } }将xls文件转换为CSV?php $reader new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadsheet $reader-load($tmpFile); //CSV保存 $writer new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); $writer-setUseBOM(true); $writer-setDelimiter(,); $writer-setEnclosure(); $writer-setLineEnding(\r\n); $writer-setSheetIndex(0); $writer-save($csvFile); ?获取csv文件最大行数/** * 获取csv文件最大行数 * param $filePath * return int */ public function getCsvFileMaxRows($filePath) { $row 0; if (($handle fopen($filePath, r)) ! FALSE) { while (fgetcsv($handle)) { $row; } fclose($handle); } return $row; }
phpspreadsheet excel导入导出
单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列2007以上版本是16384列。xlswriterxlswriter - PHP 高性能 Excel 扩展功能类似phpspreadsheet。它能够处理非常大的文件同时保持内存使用率非常低。/** * 使用php扩展导出文件大数据方案 * * param $fileName * param $header * param $list * throws \Exception */ public function xlsWriter($fileName, $header, $list) { if (!extension_loaded(xlswriter)) { throw new \Exception(请先安装php的xlswriter扩展); } $config [path $this-_path]; $excel new \Vtiful\Kernel\Excel($config); //WPS需要关闭 zip64否则打开文件可能报文件损坏该问题已反馈给WPS修复进度未知。第三个参数 False 即为关闭 ZIP64 $fileObject $excel-constMemory($fileName, NULL, false); $fileHandle $fileObject-getHandle(); $format new \Vtiful\Kernel\Format($fileHandle); $boldStyle $format-bold()-toResource(); $data []; foreach ($list as $info) { $row []; foreach ($header as $key $title) { $row[] $info[$key]; } $data[] $row; } $fileObject-freezePanes(1, 0)-setRow(A1, 20, $boldStyle) //固定表头设置样式 -header(array_values($header)) -data($data)-output(); } // 读取文件 $data $excel-openFile(text.xlsx)-openSheet()-getSheetData(); 0.000 #,##0 #,##0.00 0.00 // 数字格式化 $format new \Vtiful\Kernel\Format($fileHandle); $numberStyle $format-number(#,##0)-toResource(); $filePath $fileObject-header([name, balance]) -data([ [wjx, 100000] ]) -setColumn(B:B, 10, $numberStyle) // 应用样式 -output(); #插入公式文本 $excel new \Vtiful\Kernel\Excel($config); $freeFile $excel-fileName(free.xlsx) -header([name, money]); for($index 1; $index 10; $index) { $freeFile-insertText($index, 0, viest); $freeFile-insertText($index, 1, 10); } $freeFile-insertText(12, 0, Total); $freeFile-insertFormula(12, 1, SUM(B2:B11)); $freeFile-output();IDE Helpercomposer require viest/php-ext-xlswriter-ide-helper:dev-master导出100万行数据单行27列数据类型均为字符串单个字符串长度为19普通模式耗时 29S内存只需 2083MB固定内存模式仅需 52S内存仅需 1MB导入100万行数据单行1列数据类型为INT全量模式耗时 3S内存仅 558MB游标模式耗时 2.8S内存仅 1MBwget https://pecl.php.net/get/xlswriter-1.3.6.tgz tar -zxvf xlswriter-1.3.6.tgz cd xlswriter-1.3.6 phpize ./configure --enable-reader make make install [xlswriter] extensionxlswriter.sophpspreadsheetphp spreadsheet是phpexcel的下一个版本。它打破了兼容性大大提高了代码基础质量名称空间、PSR兼容性、使用最新的PHP语言功能等。因为所有的努力都转移到了phpspreadsheetphpexcel将不再被维护。对phpexcel、补丁和新功能的所有贡献都应该以phpspreadsheet主分支为目标。//创建一个处理对象实例 $spreadsheet new Spreadsheet(); $spreadsheet-setActiveSheetIndex(0); $objActSheet $spreadsheet-getActiveSheet();由于Office2003兼容性包中的错误打开Xlsx电子表格时可能会出现一些小问题主要与公式计算有关。您可以使用以下代码启用Office2003兼容性$writer new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); $writer-setOffice2003Compatibility(true); $writer-save(05featuredemo.xlsx);//设置文档基本属性 $spreadsheet-getProperties() -setCreator(Helloweba) //作者 -setLastModifiedBy(Yuegg) //最后修改者 -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); //分类//设置当前的sheet索引用于后续的内容操作。一般只有在使用多个sheet的时候才需要显示调用。 //缺省情况下PHPExcel会自动创建第一个sheet被设置SheetIndex0 $spreadsheet-setActiveSheetIndex(0); //创建新的工作标签 $newSheet $spreadsheet-createSheet(); //设置当前活动sheet的名称 $objActSheet-setTitle(测试Sheet);设置单元格格式为文本//公式 $objActSheet-setCellValue(A4,SUM(A2:A2)); $objActSheet-setCellValue(K6, F6G6H6); //显式指定内容类型 $objActSheet-setCellValueExplicit(A5,8757584,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING); //使用\n进行单元格内换行 $value Hello World! . PHP_EOL . Next Line; $sheet-setCellValue(A1, $value); $sheet-getStyle(A1)-getAlignment()-setWrapText(true);//合并单元格 $objActSheet-mergeCells(B1:C22); //分离单元格 $objActSheet-unmergeCells(B1:C22); //插入行或列 $objActSheet-insertNewRowBefore(6, 10); $objActSheet-removeRow(6, 10); $objActSheet-insertNewColumnBefore(E, 5); $objActSheet-removeColumn(E, 5);默认设置默认字体大小宽度高度对齐方式//worksheet默认style设置(和默认不同的需单独设置) $spreadsheet-getDefaultStyle()-getFont()-setName(华文仿宋)-setSize(12); $alignment $spreadsheet-getDefaultStyle()-getAlignment(); $alignment-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); $alignment-setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); //默认列宽 $objActSheet-getDefaultColumnDimension()-setWidth(15); //默认行宽 $objActSheet-getDefaultRowDimension()-setRowHeight(20); //设置宽度 $objActSheet-getColumnDimension(B)-setAutoSize(true); $objActSheet-getColumnDimension(A)-setWidth(30); //第10行高度 $objActSheet-getRowDimension(10)-setRowHeight(100); $objActSheet-getRowDimension(10)-setRowHeight($objActSheet-getDefaultRowDimension()-getRowHeight());设置单元格缩进$objActSheet-getStyle(A7)-getAlignment()-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT)-setIndent(2);//日期格式化 $spreadsheet-getActiveSheet()-setCellValue(D1, 2018-06-15); $spreadsheet-getActiveSheet()-getStyle(D1) -getNumberFormat() -setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); //设置格式为FORMAT_NUMBER避免某些大数字被使用科学记数方式显示配合下面的setAutoSize方法可以让每一行的内容 //都按原始内容全部显示出来。 $objStyleA5 $objActSheet-getStyle(A5); $objStyleA5-getNumberFormat()-setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00); //货币格式化 $objStyleA5-getNumberFormat()-setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objActSheet-getStyle(J11)-getNumberFormat()-setFormatCode(¥#,##0.00); //设置对齐方式 $objAlignA5$objStyleA5-getAlignment(); $objAlignA5-setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT); $objAlignA5-setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); //设置边框 $objBorderA5$objStyleA5-getBorders(); $objBorderA5-getTop()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getTop()-getColor()-setARGB(FFFF0000);//边框color $objBorderA5-getBottom()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getLeft()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); $objBorderA5-getRight()-setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN); //从指定的单元格复制样式信息. $objActSheet-duplicateStyle($objStyleA5,B1:C22);设置CELL背景色//设置字体 $objActSheet-getStyle(B1:I2)-getFont()-setName(Candara);//字体 $objActSheet-getStyle(B1)-getFont()-setSize(20);//大小 $objActSheet-getStyle(B1)-getFont()-setBold(true);//加粗 $objActSheet-getStyle(B1)-getFont()-setUnderline(Font::UNDERLINE_SINGLE);//下划线 $objActSheet-getStyle(B1)-getFont()-getColor()-setARGB(Color::COLOR_WHITE); $objActSheet-getStyle(B1)-getFont()-setSuperscript(true);//设置上标 $objActSheet-getStyle(B1)-getFont()-setSubscript(true);//设置下标 //设置CELL背景色 $objActSheet-getStyle(A1:I2)-getFill() -setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) -getStartColor()-setARGB(FFFFFF); //添加图片 $drawing new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing-setName(Logo); $drawing-setDescription(Logo); $drawing-setPath(./images/officelogo.jpg); $drawing-setHeight(36); $drawing-setCoordinates(A1); /*设置图片要插入的单元格*/ $drawing-setWorksheet($objActSheet); //显示网格线: $objPHPExcel-getActiveSheet()-setShowGridlines(true); //Add a hyperlink to the sheet 添加链接 $objActSheet-setCellValue(E26, www.phpexcel.net); $objActSheet-getCell(E26)-getHyperlink()-setUrl(http://www.phpexcel.net); $objActSheet-getCell(E26)-getHyperlink()-setTooltip(Navigate to website); $objActSheet-getStyle(E26)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //Add conditional formatting $objConditional1 new PHPExcel_Style_Conditional(); $objConditional1-setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS); $objConditional1-setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN); $objConditional1-setCondition(0); $objConditional1-getStyle()-getFont()-getColor()-setARGB(PHPExcel_Style_Color::COLOR_RED); $objConditional1-getStyle()-getFont()-setBold(true); //Set autofilter 自动过滤 $objActSheet-setAutoFilter(A1:C9); //设置文档安全 打开需要密码Excel2007生效 $objPHPExcel-getSecurity()-setLockWindows(true); $objPHPExcel-getSecurity()-setLockStructure(true); $objPHPExcel-getSecurity()-setWorkbookPassword(PHPExcel); //设置工作表安全 全部只读 $objActSheet-getProtection()-setPassword(password);//设置保护密码 $objActSheet-getProtection()-setSheet(true);// This should be enabled in order to enable any of the following! $objActSheet-protectCells(A1:C22); //Set outline levels $objActSheet-getColumnDimension(E)-setOutlineLevel(1); $objActSheet-getColumnDimension(E)-setVisible(false); $objActSheet-getColumnDimension(E)-setCollapsed(true);//设置工作表的页面方向和大小 $spreadsheet-getActiveSheet()-getPageSetup() -setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE); $spreadsheet-getActiveSheet()-getPageSetup() -setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4); //页面设置缩放选项 $spreadsheet-getActiveSheet()-getPageSetup()-setFitToWidth(1); $spreadsheet-getActiveSheet()-getPageSetup()-setFitToHeight(0); //页边距 $spreadsheet-getActiveSheet()-getPageMargins()-setTop(1); $spreadsheet-getActiveSheet()-getPageMargins()-setRight(0.75); $spreadsheet-getActiveSheet()-getPageMargins()-setLeft(0.75); $spreadsheet-getActiveSheet()-getPageMargins()-setBottom(1); //指定打印区域 $spreadsheet-getActiveSheet()-getPageSetup()-setPrintArea(A1:E5,G4:M20); //PhpSpreadsheet可以在页面顶部/左侧重复特定的行/单元格。以下代码是如何在特定工作表的每个打印页面上重复第1至5行的示例 $spreadsheet-getActiveSheet()-getPageSetup()-setRowsToRepeatAtTopByStartAndEnd(1, 5);在单元格中添加评论要将注释添加到单元格请使用以下代码。下面的示例向单元格E11添加注释$spreadsheet-getActiveSheet() -getComment(E11) -setAuthor(Mark Baker); $commentRichText $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(PhpSpreadsheet:); $commentRichText-getFont()-setBold(true); $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(\r\n); $spreadsheet-getActiveSheet() -getComment(E11) -getText()-createTextRun(Total amount on the current invoice, excluding VAT.);单元格部分文字加粗、放大、添加颜色//创建一个富文本对象 $richText new \PhpOffice\PhpSpreadsheet\RichText\RichText(); $richText-createText(铁扇公主); //需要改变大小或颜色的文字内容 $payable $richText-createTextRun(芭蕉妹妹); $payable-getFont()-setBold(true); $payable-getFont()-setItalic(true); $payable-getFont()-setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN ) ); $richText-createText(牛魔王); $spreadsheet-getActiveSheet()-getCell(A18)-setValue($richText);//显示隐藏列$objPHPExcel-getActiveSheet()-getColumnDimension(C)-setVisible(true);$objPHPExcel-getActiveSheet()-getColumnDimension(D)-setVisible(false);//显示隐藏行$objPHPExcel-getActiveSheet()-getRowDimension(10)-setVisible(false);做软件中难免会遇到数据导入的功能而数据导入要生成一个模板模板中有些字段是需要固定那几种的下拉菜单就是EXCEL中的数据有效性简单研究一下下面把源码贴出来$objValidation $objActSheet-getCell(A1)-getDataValidation(); //这一句为要设置数据有效性的单元格 $objValidation - setType(PHPExcel_Cell_DataValidation::TYPE_LIST) - setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION) - setAllowBlank(false) - setShowInputMessage(true) - setShowErrorMessage(true) - setShowDropDown(true) - setErrorTitle(输入的值有误) - setError(您输入的值不在下拉框列表内.) - setPromptTitle(设备类型) - setFormula1(列表项1,列表项2,列表项3);列数字与字母的相互转化超过第26个字母Z有bugprint_r(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString(D));//echo 4 echo \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4) // echo E//phpexcel默认是没有冻结的下面是冻结列。定二则定全部$sheet-freezePane(A1);$sheet-freezePane(B1);冻结行$sheet-freezePane(D1);$sheet-freezePane(D2);不能再次定义A,b,c否则列冻结被替代public function freezePaneByColumnAndRow($pColumn 0, $pRow 0){$this-freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);}public function unfreezePane() {$this-freezePane();}$worksheet-setInputEncoding(UTF-8);//$freeze $sheet-getFreezePane();批量设置单元格格式$styleArray [ font [ bold true, ], borders [ //上下左右画线 allBorders [borderStyle \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], ], alignment [ horizontal \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, //横向居中 vertical \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, //纵向居中 wrap true, //自动换行 indent 2, //缩进2个字符 ], fill [ fillType \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR, rotation 90, startColor [ argb FFA0A0A0, ], endColor [ argb FFFFFFFF, ], ], ]; $spreadsheet-getActiveSheet()-getStyle(A1:J35)-applyFromArray($styleArray);设置外边框$styleArray [ borders [ outline [ borderStyle \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, color [argb FFFF0000], ], ], ]; $objActSheet-getStyle(B2:G8)-applyFromArray($styleArray);获得公式计算值$workbook-getActiveSheet()-getCell(G1)-getCalculatedValue();获得日期的格式化数值$workbook-getActiveSheet()-getCell(G2)-getFormattedValue();导出phpexcel大数据导出,数据追加19万行分20个sheet, 1万行是一个sheet,第一次到1个sheet数据其他数据追加到excel,分19次覆盖对应的sheet先用 PhpExcel 建立reader再load文件这样打开已经存在的文档然后再建立writer将reader中的数据都复制过来再用 PhpExcel 进行数据修改再以load的文件名覆盖保存。$reader new PHPExcel_Reader_Excel2007; $workbook $reader-load(document.xlsx); $workbook-getActiveSheet()-getSecurity()-setWorkbookPassword(your password); $workbook-getActiveSheet()-getCell(D1)-getValue(); $workbook-getActiveSheet()-setCellValueByColumnAndRow(1, 5, PhpSpreadsheet);//A5的值 $cellValue $spreadsheet-getActiveSheet()-getCellByColumnAndRow(1, 5)-getValue();//第2列第5行B5方法二分批导出导出多个文件,通过程序将所有文件加入压缩包使用phpspreadsheet进行导出excel的时候遇到了内存溢出的问题官方提供了memory saving的解决方案官方文档中提供了APC、redis和memcache的缓存方案。//关闭公式计算预处理 $writer new Xlsx($spreadsheet); $writer-setPreCalculateFormulas(false);切割excel大文件导入导出大数据导出分片查询每次读取1000条数据追加到导入文件或者生成新文件// 示例 $inputFileType Xls; $inputFileName ./sampleData/example2.xls; /** Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter */ class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { private $startRow 0; private $endRow 0; /** Set the list of rows that we want to read */ public function setRows($startRow, $chunkSize) { $this-startRow $startRow; $this-endRow $startRow $chunkSize; } public function readCell($column, $row, $worksheetName ) { // Only read the heading row, and the configured rows if (($row 1) || ($row $this-startRow $row $this-endRow)) { return true; } return false; } } $reader \PhpOffice\PhpSpreadsheet\IOFactory::createReader(Xlsx); $perpage 10000; $page 0; $chunkFilter new ChunkReadFilter(); $reader-setReadFilter($chunkFilter); while (true) { $startRow 2 $perpage * $page; $chunkFilter-setRows($startRow, $perpage); $spreadsheet $reader-load($filePath); $sheet $spreadsheet-getActiveSheet(); $sheetData $sheet-toArray(null, true, true, true); $sheetData array_slice($sheetData, $startRow - 1, $perpage); if (empty($sheetData)) { break; } $this-importDatas($sheetData); //业务处理函数 $page; }节省内存PhpSpreadsheet在工作表中平均每个单元格使用约1k因此大型工作簿可以迅速用尽可用内存。默认情况下PhpSpreadsheet将所有单元格对象保留在内存中。单元缓存提供了一种机制使PhpSpreadsheet可以将单元对象维护在较小的内存或非内存中例如在磁盘上在APCu中内存缓存或Redis中。这使您可以减少大型工作簿的内存使用量尽管以访问单元数据的速度为代价。要求将软件包放入您的项目中composer require cache/simple-cache-bridge cache/redis-adapter$client new \Redis(); $client-connect(127.0.0.1, 6379); $pool new \Cache\Adapter\Redis\RedisCachePool($client); $simpleCache new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool); \PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);读取CSV内容$reader \PhpOffice\PhpSpreadsheet\IOFactory::createReader(Csv); $reader-setReadDataOnly(true); $spreadsheet $reader-load(/mnt/d/文档/test.csv); $data $spreadsheet-getActiveSheet()-toArray(); $headers array_shift($data); //剔除表头读取XLSX内容?php class ExcelReader { private static $rowStart 7; private static $colStart 3; /** * 获取起始行. */ public static function getStartRow(): int { return self::$rowStart; } /** * 获取起始列. */ public static function getStartCol(): int { return self::$colStart; } /** * param string $destFile 文件路径 * param string $extensionName 扩展名 * param array $headerIndexArr [3 name, 4 mobile]; * * throws Exception */ public static function readExcelData(string $destFile, string $extensionName, array $headerIndexArr): array { // 验证文件路径和扩展名 if (!file_exists($destFile)) { throw new Exception(文件不存在: {$destFile}); } if (!is_readable($destFile)) { throw new Exception(文件不可读: {$destFile}); } if (!in_array(strtolower($extensionName), [xls, xlsx])) { throw new Exception(不支持的文件扩展名: {$extensionName}); } // 创建 Reader 对象 $readerType xls strtolower($extensionName) ? Excel5 : Excel2007; $objReader PHPExcel_IOFactory::createReader($readerType); $objReader-setReadDataOnly(true); try { $objPHPExcel $objReader-load($destFile); $workSheet $objPHPExcel-getSheet(0); $highestRow $workSheet-getHighestRow(); $highestColumn $workSheet-getHighestColumn(); $highestColumnIndex PHPExcel_Cell::columnIndexFromString($highestColumn); // 检查文件是否为空 if ($highestRow self::$rowStart || $highestColumnIndex self::$colStart) { return []; } $excelData []; for ($row self::$rowStart; $row $highestRow; $row) { $rowData []; for ($col self::$colStart; $col $highestColumnIndex; $col) { if (array_key_exists($col, $headerIndexArr)) { $cellValue trim((string) $workSheet-getCellByColumnAndRow($col, $row)-getValue()); $rowData[$headerIndexArr[$col]] $cellValue; } } if (!empty($rowData)) { $excelData[] $rowData; } } return $excelData; } catch (Exception $e) { throw new Exception(读取 Excel 文件时出错: .$e-getMessage()); } } /** * 设置起始行和列. * * param int $startRow 起始行号 * param int $startCol 起始列号 * * throws InvalidArgumentException 如果参数无效 */ protected static function setStartRowAndCol(int $startRow, int $startCol): void { if ($startRow 0 || $startCol 0) { throw new InvalidArgumentException(起始行和列必须大于等于0); } // 设置属性 self::$rowStart $startRow; self::$colStart $startCol; } }将xls文件转换为CSV?php $reader new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadsheet $reader-load($tmpFile); //CSV保存 $writer new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); $writer-setUseBOM(true); $writer-setDelimiter(,); $writer-setEnclosure(); $writer-setLineEnding(\r\n); $writer-setSheetIndex(0); $writer-save($csvFile); ?获取csv文件最大行数/** * 获取csv文件最大行数 * param $filePath * return int */ public function getCsvFileMaxRows($filePath) { $row 0; if (($handle fopen($filePath, r)) ! FALSE) { while (fgetcsv($handle)) { $row; } fclose($handle); } return $row; }
