使用gdocs中的zend gdata检索电子表格的特定列 [英] Retrieve only specific columns of spreadsheet with zend gdata from gdocs

问题描述

我正在使用来自gdocs的相对较大的电子表格，并通过zend库进行访问.我在任何给定时间都只需要某些列，因此为了节省内存，使用基于列表的供稿仅检索这些特定列而不是整个电子表格会很有帮助.我正在使用的基本查询是根据zend文档

I am working with relatively large spreadsheets from gdocs and access them via the zend library. I only need certain columns at any given time so in order to save memory it would be helpful to use the list-based feed to only retrieve these certain columns and not the whole spreadsheet. The basic query I am using is according to the zend documentation

$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId); 
$listFeed = $spreadsheetService->getListFeed($query);

我有机会通过$query->setSpreadsheetQuery('name=someName');发送结构化查询，但据我所知，这仅适用于限制返回的行数而不是列数.有没有办法以某种方式使用它来获取特定的列?替代地，仅获得电子表格的某些先前指定的行将是有帮助的，以便仅检索电子表格的块并且一次精简一个块.无论哪种方式，我都需要避免在任何给定时间将整个电子表格存储在内存中.

I have the opportunity to send a structured query via $query->setSpreadsheetQuery('name=someName'); but as far as I can see this only works for limiting the number of rows returned and not the number of columns. Is there a way to somehow use this to get specific columns? Alternatively it would be helpful to get only certain, previously specified rows of the spreadsheet so as to retrieve only blocks of the spreadsheet and thin out one block at the time. Either way, I need to avoid having the whole spreadsheet in the memory at any given time.

感谢您的帮助.

推荐答案

我设法使用基于单元的提要来解决此问题.基本上，我要做的就是首先获取特定列标题的列号，例如

I managed to work around this problem using a cell-based feed. Basically what I did was to first get the column number for a specific column header like so

$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($spreadsheetkey);
$query->setWorksheetId($wworksheetkey);
$query->setMinRow(1);
$query->setMaxRow(1);
$headerFeed = $spreadsheetService->getCellFeed($query);

foreach($headerFeed as $cellEntry){
    if(strcasecmp($cellEntry->cell->getText(), $column) ==  0){
        $colNr = $cellEntry->cell->getColumn();
        break;
    }
}

这将为我提供$column列的列号.然后，对于给定的列号，我将继续获取特定范围的行，这样我就可以逐块读取整个列，而无需将整个电子表格(或列)存储在内存中.可以这样做

which will give me the column number for the $column column. I would then proceed to get a specific range of rows for the given column number so that I can read in the whole column block by block without ever having the whole spreadsheet(or column) in memory. This can be done as

$query->setMinCol($colNr);
$query->setMaxCol($colNr);
$query->setMinRow($startingRow);
$query->setMaxRow($endingRow);

$columnFeed = $spreadsheetService->getCellFeed($query);
foreach($columnFeed as $cellEntry){
    $result[$cellEntry->cell->getRow()] = $cellEntry->cell->getText();
}

通过更改$startingRow和$endingRow，可以遍历该列.不过，您必须谨慎选择这些选项，因为如果它们超出范围"，则Google api将返回http 400代码.单元格不必在其中具有值，但必须存在(向下滚动电子表格，最后看到的行号应为两个值的最大值).我认为默认情况下会创建一个包含100(空)行的电子表格.

by changing $startingRow and $endingRow I can iterate through the column. One has to be careful with the choice of these though, since if they are 'out of bounds' the google api will return a http 400 code. The cells don't have to have value in them, but they have to exist(scroll down your spreadsheet and the last row number you see should be your maximum for both values). I think per default a spreadsheet with 100(empty) rows is created.

这可能不是最优雅或最快的解决方案，但它对我有用.

This is probably not the most elegant or fastest solution, but it works for me.

这篇关于使用gdocs中的zend gdata检索电子表格的特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！