php 如何使用PHPExcel合并Excel中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15084517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to merge column in Excel using PHPExcel
提问by swapnesh
I already checked this Merging cells in Excel by rows and columns together using PHPExcelbut its showing only for row addition however I applied some tests for column as well but none is working.
我已经使用 PHPExcel 通过行和列检查了 Excel 中的合并单元格，但它仅显示行添加，但是我也对列应用了一些测试，但没有一个工作正常。
This code although creates the excel sheet successfully but the output in excel is throwing error.
此代码虽然成功创建了 excel 表，但 excel 中的输出抛出错误。
EDIT
编辑
however this is working if remove the llop and put this in simple terms -
但是，如果删除 llop 并用简单的术语来说这是有效的 -
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:B1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');
Main code for adding the data -
添加数据的主要代码 -
// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$i = "A";
$j ="B";
for($num =1; $num <= 5; $num++ )
{
$concat = "{$i}1:{$j}1";
$objPHPExcel->setActiveSheetIndex(0)->mergeCells($concat);
$i++;$j++;
}
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');
My whole code -
我的整个代码 -
<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Asia/Calcutta');
/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';
// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->
getProperties()->setCreator("Swapnesh Sinha")
->setLastModifiedBy("Swapnesh")
->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
echo date('H:i:s') , " Add some data" , EOL;
$i = "A";
$j ="B";
for($num =1; $num <= 5; $num++ )
{
$concat = "{$i}1:{$j}1";
$objPHPExcel->setActiveSheetIndex(0)->mergeCells($concat);
$i++;$j++;
}
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');
// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Student Data');
// Set document security
echo date('H:i:s') , " Set document security" , EOL;
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");
// Set sheet security
echo date('H:i:s') , " Set sheet security" , EOL;
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$filename = "Student-data-sheet".".xlsx";
$objWriter->save($filename);
echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;
回答by swapnesh
I figure out the solution on my own -
我自己想出解决方案-
Actually in case of column merge we actually dont need to iterate. let us say I want to merge from column A1to E1.
实际上，在列合并的情况下，我们实际上不需要迭代。假设我想从列合并A1到E1.
So instead of going loop by loop it can very easily be done via -
因此，它可以很容易地通过以下方式完成：
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:E1');
So in my case I first need to find the last element and the first and then simply put in mergeCells() method to make it working.
所以在我的情况下，我首先需要找到最后一个元素和第一个元素，然后简单地放入 mergeCells() 方法以使其工作。
回答by Vishal Bhandare
This issue happens when you are trying to merge two cell in loop. Reason is not the loop you are iterating but its column name you are passing. if you are passing wrong column names then your excel will not produce proper output.
当您尝试在循环中合并两个单元格时会发生此问题。原因不是您正在迭代的循环，而是您正在传递的列名。如果您传递了错误的列名，那么您的 excel 将不会产生正确的输出。
Following is simple program to get next excel column so that we dont face such kind of issue.
以下是获取下一个 excel 列的简单程序，这样我们就不会遇到此类问题。
/* manage excel index */
function getColumn($prevColumn){
if(strlen($prevColumn)==1&&$prevColumn!="Z")
{ $prevColumn++;return $prevColumn; }
$colum = '';
$list = str_split($prevColumn);
if(count($list)==1&&$list[0]=='Z')
{
$list[0] = "A";
$list[1] = "A";
}
else if(count($list)==2&&$list[1]!='Z')
{
$list[1]++;
}
else if(count($list)==2&&$list[1]=='Z')
{
$list[0] = "B";
$list[1] = "A";
}
return implode('',$list);
}
## us this like below
$column = "A";
$row = 1;
foreach($array_to_print as $details)
{
$column = getColumn($column); // product next column B
$Excelobj->getActiveSheet()->setCellValue($column.$row, $details);
$column = getColumn($column);// product next column C
$Excelobj->setActiveSheetIndex(0)->mergeCells($from_cell.':'.$to_cell);// now this will work till A-ZZ column in excel
}
