PHPexcel保存到CSV链接的单元格到另一个工作表是空的

Could someone help me? I have xlsx file, with 2 sheets.

有人能帮帮我吗?我有xlsx文件,有2张。

Second sheet contain cells linked to another(first) sheet. When I save sheet to CSV file:

第二张纸包含链接到另一张(第一张)纸张的单元格。当我将工作表保存到CSV文件时:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

This function doesn't save (linked) cells value...

此功能不保存(链接)单元格值...

My code looking like this:

我的代码看起来像这样:

$objPHPExcel = new PHPExcel();

//  Read your Excel workbook
try 
{
    $inputFileType = PHPExcel_IOFactory::identify($excelFile);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setLoadSheetsOnly('list'); 
    $objReader->setLoadSheetsOnly('main'); 
    /* I also tried like this: 
    $worksheetList = $objReader->listWorksheetNames($excelFile);
    $sheetname = $worksheetList[0]; 
    $sheetname2 = $worksheetList[1]; 
    $objReader->setLoadSheetsOnly($worksheetList[0]); 
    $objReader->setLoadSheetsOnly($sheetname); 
    */
$objPHPExcel = $objReader->load($excelFile);
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($filename);

I also tried to save to EXCEL files (xls and xlsx) -> the same problem, the cells (which was linked) they are empty...

我也尝试保存到EXCEL文件(xls和xlsx) - >同样的问题,单元格(已链接)它们是空的...

My linked cells looked like this: "=list!C46"

我链接的单元格看起来像这样:“= list!C46”

Many hours of looking for answer, I have found not good solution: I've removed any of these lines: $objReader->setLoadSheetsOnly('list'); and add:

很多小时寻找答案,我发现不是很好的解决方案:我删除了以下任何一行:$ objReader-> setLoadSheetsOnly('list');并添加:

$activeSheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$objPHPExcel->getActiveSheet()->fromArray($activeSheetData, false);

just after:

$objPHPExcel = $objReader->load($excelFile);

$ objPHPExcel = $ objReader-> load($ excelFile);

and also add: $objWriter->setSheetIndex(1); Now it will works, but with problems...

并且还添加:$ objWriter-> setSheetIndex(1);现在它会起作用,但有问题......

One column in original format have linked cells like this: "=list!$AV$46" I mean with $ symbol.

原始格式的一列链接了这样的单元格:“= list!$ AV $ 46”我的意思是$符号。

More detailed: If I have: $objReader->setReadDataOnly(true); and have those cells: "=list!$AV$46" then they are empty in output.

更详细:如果我有:$ objReader-> setReadDataOnly(true);并拥有那些单元格:“= list!$ AV $ 46”然后它们在输出中是空的。

But if I remove: $objReader->setReadDataOnly(true); then those cells: "=list!$AV$46" works good and have a value, but with format like: 11/17/2017.
As I removed $objReader->setReadDataOnly(true);, then I can't apply this my code: $objPHPExcel->setActiveSheetIndex(1)->getStyle('AV1:AV'.$highestRow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD);

但是如果我删除:$ objReader-> setReadDataOnly(true);然后那些单元格:“= list!$ AV $ 46”运作良好且有价值,但格式如下:2017年11月17日。当我删除$ objReader-> setReadDataOnly(true);时,我无法应用此代码:$ objPHPExcel-> setActiveSheetIndex(1) - > getStyle('AV1:AV'。$ highestRow) - > getNumberFormat() - > setFormatCode(PHPExcel_Style_NumberFormat :: FORMAT_DATE_YYYYMMDD);

Then second question, how to write new date format? I also wanted to say, that initial date format was: 17.11.2017. And I wanted 17-11-17 (FORMAT_DATE_YYYYMMDD).

那么第二个问题,如何编写新的日期格式?我还想说,初始日期格式是:17.11.2017。我想要17-11-17(FORMAT_DATE_YYYYMMDD)。

And again, with cells which looks like "=list!AV46" all works good.

再一次,看起来像“= list!AV46”的细胞一切都很好。

UPDATE: Solvation: $objReader->setLoadSheetsOnly(['list', 'main']); + $objPHPExcel->setActiveSheetIndex(1); before any of: $objPHPExcel->getActiveSheet()->...

更新:Solvation:$ objReader-> setLoadSheetsOnly(['list','main']); + $ objPHPExcel-> setActiveSheetIndex(1);之前的任何一个:$ objPHPExcel-> getActiveSheet() - > ...

1 个解决方案

#1

Here's your first problem

这是你的第一个问题

$objReader->setLoadSheetsOnly('list'); 
$objReader->setLoadSheetsOnly('main');

You're only ever loading one sheet, the last one that you tell PHPExcel to load, which is main. Multiple calls to setLoadSheetsOnly() overwrite the setting of the previous call.

你只需要加载一张表,最后一张表示你要告诉PHPExcel加载,这是主要的。多次调用setLoadSheetsOnly()会覆盖上一次调用的设置。

If you want to load both sheets, then you need to pass an array listing all the sheetnames that you want to load

如果要加载两个工作表,则需要传递一个列出要加载的所有工作表名称的数组

$objReader->setLoadSheetsOnly(['list', 'main']);

This is explained in the PHPExcel Documentation

这在PHPExcel文档中有解释

The documentation also says not to use $objReader->setReadDataOnly(true); unless you understand what it is doing; it tells PHPExcel to load only the rw data, not the formatting of data; and it is the formatting that differentiates a number from a date in Excel.

文档还说不要使用$ objReader-> setReadDataOnly(true);除非你明白它在做什么;它告诉PHPExcel只加载rw数据,而不是加载数据的格式;它是格式,区分数字与Excel中的日期。

#1