在使用listWorksheetNames()时使用phpexcel进行读写 [英] read and write using phpexcel while using listWorksheetNames()

问题描述

我有一个.xlsx文件.在.xlsx文件中，有4个活动"表， 性能"，商店"，展示". 我想一次只在内存中加载一张纸，然后再向其中添加数据. 报告写出来.我的代码在下面

I am having a .xlsx file. In the .xlsx file there are 4 sheets "activity", "performance", "store", "display". I want to load only one sheet in the memory at a time and after adding data to report write it. my code is below

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$worksheet_names = $objReader->listWorksheetNames('/tmp/ac.xlsx');
$objReader->setLoadSheetsOnly('store');
$objPHPExcel = $objReader->load('/tmp/ac.xlsx');
$objPHPExcel->setActiveSheetIndexByName('store');
$sheet = $objPHPExcel->getActiveSheet();
$max_row = $sheet->getHighestRow();
$sheet->setCellValue("A$max_row", "Data");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('/tmp/ac.xlsx');
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);

问题是它正在商店工作表上写而删除了所有其他工作表. 更新商店工作表"时，如何保留所有工作表 有没有可以一次写一张纸的功能 保留其他床单.

the problem is it is writing on store sheet and deleting all the other sheets. How do i make remain all the sheets while updating 'store sheet' is there any function which will write one sheet at a time while retaining the other sheets.

推荐答案

仅将您已加载到PHPExcel对象中的内容放置到新文件中.

Only what you have loaded into the PHPExcel object will be placed into your new file.

createWriter不会编辑文件并保留工作表，而只是编写传递给您的文件名的新副本.在这种情况下，它将覆盖文件，因为它是您刚打开要读取的文件.因此，您必须格外小心，以首先获取整个工作簿，然后更改您想要的内容(从整个工作表中获取).之后，使用新的更改将所有内容写入文件.

The createWriter isn't editing the file and retaining the sheets, it's just writing a fresh copy of what you pass in to the file name you give it. In this case, it will overwrite the file because it is the same file that you have just opened to read from. So, you must take some caution to grab the entire workbook first, then alter what you want (from the entire worksheet). After that, write everything to the file with the new changes.

下面的代码应帮助您保留其他表.要仅编辑特定的工作表，只需将要编辑的工作表名称放在$ editable_worksheets数组中.我对这些评论的描述性很强，因此希望他们能逐步阐明这样做的方式.

The code below should help you out with retaining the other sheets. To edit only specific sheets just place the sheet names you want to edit in the $editable_worksheets array. I was very descriptive with the comments, so hopefully they will clarify step by step how this is done.

// Load your PHPExcel class
require_once 'classes/PHPExcel/Classes/PHPExcel.php';

// Set variables for file location and type to make code more portable and 
// less memory intensive
$file = '/tmp/ac.xlsx';
$file_type = 'Excel2007';

// Open file for reading
$objReader = PHPExcel_IOFactory::createReader($file_type);

// Take all exisiting worksheets in open file and place their names into an array
$worksheet_names = $objReader->listWorksheetNames($file);

// Array of worksheet names that should be editable
$editable_worksheets = array('activity', 'store');

// You will need to load ALL worksheets if you intend on saving to the same 
// file name, so we will pass setLoadSheetsOnly() the array of worksheet names 
// we just created.
$objReader->setLoadSheetsOnly($worksheet_names);

// Load the file
$objPHPExcel = $objReader->load($file);

// Loop through each worksheet in $worksheet_names array
foreach($worksheet_names as $worksheet_name) {

    // Only edit the worksheets with names we've allowed in  
    // the $editable_worksheets array
if(in_array($worksheet_name, $editable_worksheets)) {
        // Take each sheet, one at a time, and set it as the active sheet
        $objPHPExcel->setActiveSheetIndexByName($worksheet_name);

        // Grab the sheet you just made active
        $sheet = $objPHPExcel->getActiveSheet();

        // Grab the highest row from the current active sheet
        $max_row = $sheet->getHighestRow();

        // Set the value of column "A" in the last row to the text "Data"
        $sheet->setCellValue("A" . $max_row, "Data");

    }

    // Foreach loop will repeat until all sheets in the workbook have been looped
    // through
}

// Unset variables to free up memory
unset($worksheet_names, $worksheet_name, $sheet, $max_row);

// Prepare to write a new file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $file_type);

// Tell excel not to precalculate any formulas
$objWriter->setPreCalculateFormulas(false);

// Save the file
$objWriter->save($file);

// This must be called before unsetting to prevent memory leaks
$objPHPExcel->disconnectWorksheets();

// Again, unset variables to free up memory
unset($file, $file_type, $objReader, $objPHPExcel);

这篇关于在使用listWorksheetNames()时使用phpexcel进行读写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！