电子商务网站推广的主要方法,黄冈seo推广优势,美图秀秀在线使用,专业网站开发平台单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列#xff0c;2007以上版本是16384列。
xlswriter
xlswriter - PHP 高性能 Excel 扩展#xff0c;功能类似phpspreadsheet。它能够处理非常大的文件#xff0…单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列2007以上版本是16384列。
xlswriter
xlswriter - 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();IDE Helper
composer require viest/php-ext-xlswriter-ide-helper:dev-masterphpspreadsheet
php 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);$objActSheet-getActiveSheet()-getStyle(3)-getNumberFormat()-setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//使用\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(Arial);
$spreadsheet-getDefaultStyle()-getFont()-setSize(8);
$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有bug
print_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);phpspreadsheet切割excel大文件导入
// 示例
$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 rowsif (($row 1) || ($row $this-startRow $row $this-endRow)) {return true;}return false;}
}/** Create a new Reader of the type defined in $inputFileType **/
$reader \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);/** Define how many rows we want to read for each chunk **/
$chunkSize 2048;
/** Create a new Instance of our Read Filter **/
$chunkFilter new ChunkReadFilter();/** Tell the Reader that we want to use the Read Filter **/
$reader-setReadFilter($chunkFilter);/** Loop to read our worksheet in chunk size blocks **/
for ($startRow 2; $startRow 65536; $startRow $chunkSize) {/** Tell the Read Filter which rows we want this iteration **/$chunkFilter-setRows($startRow,$chunkSize);/** Load only the rows that match our filter **/$spreadsheet $reader-load($inputFileName);// Do some processing here
}读取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内容
$objReader IOFactory::createReader(Xlsx);
$objReader-setReadDataOnly(TRUE);
$filename $_FILES[inputExcelclass][tmp_name];
$objPHPExcel $objReader-load($filename); //$filename可以是上传的表格或者是指定的表格
$sheet $objPHPExcel-getSheet(0); //excel中的第一张sheet
$highestRow $sheet-getHighestRow(); // 取得总行数
$highestColumn $sheet-getHighestColumn(); // 取得总列数
for ($j 2; $j $highestRow; $j) {$data[$j - 2] [title trim($objPHPExcel-getActiveSheet()-getCell(A . $j)-getValue()),content trim($objPHPExcel-getActiveSheet()-getCell(B . $j)-getValue()),prices trim($objPHPExcel-getActiveSheet()-getCell(C . $j)-getValue()),create_time ($objPHPExcel-getActiveSheet()-getCell(D . $j)-getValue()-25569)*24*60*60,end_time ($objPHPExcel-getActiveSheet()-getCell(E . $j)-getValue()-25569)*24*60*60,];
}
