使用带有Java的API将行插入到Google电子表格中 [英] insert a row in to a google spreadsheet using the API with java
问题描述
我有一个包含20行的google电子表格.如何在第10行下方插入新行.
I have a google spreadsheet with 20 rows in. How do I insert a new row below row 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();
我可以通过调整工作表的大小在行末添加一行.看这里: https://developers.google.com/google-apps/spreadsheets/#modifying_a_worksheets_title_and_size
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
我想要一个如何在表格中间插入一行的示例.
I want an example of how to insert a row in the middle of a sheet.
推荐答案
API中没有插入".解决方案是手动向下移动行以创建间隙.这不会移动样式"(如粗体).而且某些有效"单元格引用在向下移动时会导致错误，需要将其更改为绝对引用.
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.
在插入行或删除行之后更新引用.这是最复杂的部分. 还有更多，但不容易拆分.
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;
}
}
这篇关于使用带有Java的API将行插入到Google电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
