使用带有java的API在google电子表格中插入一行

I have a google spreadsheet with 20 rows in. How do I insert a new row below row 10.

我有一个包含20行的Google电子表格。如何在第10行下方插入新行。

I can delete a row with:

我可以删除一行:

            URL listFeedUrl = worksheet.getListFeedUrl();
            ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

            ListEntry row = listFeed.getEntries().get(10);
            row.delete();

I can add a row to the end by re-sizing the worksheet. See here: https://developers.google.com/google-apps/spreadsheets/#modifying_a_worksheets_title_and_size

我可以通过重新调整工作表的大小来添加一行。请参阅此处:https://developers.google.com/google-apps/spreadsheets/#modifying_a_worksheets_title_and_size

I want an example of how to insert a row in the middle of a sheet.

我想要一个如何在工作表中间插入行的示例。

1 个解决方案

#1

There is no "Insert" in the API. The solution was to manually move rows down to create a gap. This does not move "Styles" such as bold. And some "Valid" cell references cause errors when moving down and need to be changed to absolute references.

API中没有“插入”。解决方案是手动向下移动行以创建间隙。这不会移动“样式”,如粗体。向下移动时,一些“有效”单元格引用会导致错误,需要更改为绝对引用。

Code

Updates references after an insert row, or delete row. This was the most complex bit. There is more, but is not easy to split out.

在插入行或删除行之后更新引用。这是最复杂的一点。有更多,但不容易分裂。

    /**
     * @param locationRow
     */
    private void updateSheetReferences(int locationRow, boolean insertingNewRow) {
        System.out.printf("\n%s ms elapsed updateSheetReferences \n", System.currentTimeMillis() - startTime);
        Pattern cellRefPattern = Pattern.compile("R(\\[?)([-0-9]+)\\]?C(\\[?)([-0-9]*)\\]?");
          int incDirection = 1;
          if (insertingNewRow == false)  incDirection = -1;
          for(AppCell nextCell : activeWorksheetCells.getAllCells()) {
              int row = nextCell.row;
              int col = nextCell.col;
              String cellInputValue = nextCell.inputValue;
              if (cellInputValue == null) continue;


              // create a copy of the cell to replace
              String updateReference = cellInputValue; 

              if(updateReference.startsWith("=")) {
                  String removeReferenceBug = updateReference.replace( (CharSequence) "C:R", (CharSequence) "C[0]:R");


                  Matcher referenceMatcher = cellRefPattern.matcher(removeReferenceBug);
                  StringBuffer restultBuffer = new StringBuffer();
                  while (referenceMatcher.find()) {
                      try {
                          if(referenceMatcher.group(1).equals("[")) {
                              int rowOffset = Integer.parseInt(referenceMatcher.group(2));
                              int topRowOfSpan;
                              int bottomRowOfSpan;                                
                              int incSize = 1*incDirection;
                              // the location of the deleted row is relative, and so one row lower if row was deleted
                              int locationDeletedOffset = 0;
                              if(row >= locationRow && insertingNewRow == false) locationDeletedOffset = -1; 

                              // get the top and bottom rows of the 
                              if(rowOffset > 0) {
                                  topRowOfSpan = row;
                                  bottomRowOfSpan = row + rowOffset;
                              } else {
                                  topRowOfSpan = row + rowOffset;
                                  bottomRowOfSpan = row ;       
                                  incSize = -1*incDirection;
                              }                               
                              //System.out.println("move down: reference:"+cellAddr.reference+" topRowOfSpan:"+topRowOfSpan+
                              //      " insertLocationRow:"+insertLocationRow+" bottomRowOfSpan:"+bottomRowOfSpan);
                              // IF reference is the deleted row 
                              if(insertingNewRow == false && row + rowOffset == locationRow+locationDeletedOffset) {
                                  referenceMatcher.appendReplacement(restultBuffer, "{}");                                                                                                                    
                              } else {
                                  if(topRowOfSpan <= locationRow+locationDeletedOffset && bottomRowOfSpan >= locationRow+locationDeletedOffset) rowOffset += incSize;
                                  if(referenceMatcher.group(3).equals("[")) {
                                      referenceMatcher.appendReplacement(restultBuffer, "R["+rowOffset+"]C["+referenceMatcher.group(4)+"]");                                      

                                  } else {
                                      int colOffset = 0;                                          
                                      String colText = referenceMatcher.group(4);                                         
                                      if(colText != null && "".equals(colText) == false) {
                                          colOffset = Integer.parseInt(colText) - col;
                                      }                                       
                                      referenceMatcher.appendReplacement(restultBuffer, "R["+rowOffset+"]C["+colOffset+"]");                                                                              
                                  }                                   
                              }
                          } else {
                              int absoluteRow = Integer.parseInt(referenceMatcher.group(2));
                              // IF reference is the deleted row
                              if(insertingNewRow == false && absoluteRow == locationRow) {
                                  referenceMatcher.appendReplacement(restultBuffer, "{}");                                                                                                                    
                              } else {
                                  if(absoluteRow >= locationRow ) absoluteRow += 1 * incDirection;
                                  if(referenceMatcher.group(3).equals("[")) {
                                      referenceMatcher.appendReplacement(restultBuffer, "R"+absoluteRow+"C["+referenceMatcher.group(4)+"]");                                                                                                              
                                  } else {
                                      referenceMatcher.appendReplacement(restultBuffer, "R"+absoluteRow+"C"+referenceMatcher.group(4));                                                                                                                                                   
                                  }
                              }

                          }
                      } catch(NumberFormatException nfe) {}
                  } // END while
                  referenceMatcher.appendTail(restultBuffer);
                  updateReference = restultBuffer.toString();                                                                         

              } // END IF

              nextCell.inputValue =  updateReference;
          }
    }