如果想要在指定位置使用Java向Google表格中插入一行并写入该行数据,应该如何操作?
如果想要在指定位置使用Java向Google表格中插入一行并写入该行数据,应该如何操作?
在大量的谷歌搜索、一些挣扎和改编其他答案后,我终于想出了这个问题的解决方法。
注意:您可以生成自己的credentials.json
。在大多数IDE中,它会放在您的“资源”文件夹中。
以下是我使用的完整代码 - 您将需要的相关方法为下面的insertRow
方法,该方法从writeTest
中调用。通常情况下,我只会发布相关的方法,但是表格API中的“快速入门”肯定不足,所以这里是完整的源代码:
类和导入
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
public class SheetsTest {
private static final String APPLICATION_NAME = "Metrics project";
private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
private static final String TOKENS_DIRECTORY_PATH = "tokens";
private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
private static final String CREDENTIALS_FILE_PATH = "/credentials.json";
获取凭据:
private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException{
InputStream in = SheetsTest.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
.setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
.setAccessType("offline")
.build();
LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
}
编写测试
//Creates a new row at row 2 then writes data to it...
static void writeTest(Report r, String sheetID, String range)throws GeneralSecurityException, IOException{
final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
ValueRange requestBody = requestBuilder(r, range);
Sheets sheetsService = new Sheets.Builder(HTTP_TRANSPORT,JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
.setApplicationName(APPLICATION_NAME)
.build();
insertRow(sheetsService, sheetID);
Sheets.Spreadsheets.Values.Append request =
sheetsService.spreadsheets().values().append(sheetID, range, requestBody);
request.setValueInputOption("RAW");
//request.setInsertDataOption("INSERT_ROWS");
AppendValuesResponse resp = request.execute();
System.out.println(resp);
}
insertRow
的意思是在HTML表格中插入一行。它是一个JavaScript方法,可以通过DOM(文档对象模型)来访问和操作表格元素。使用该方法,可以在表格的任何位置插入新行,并且可以在新行中添加单元格并设置单元格的属性和内容。//Inserts row at index, this is hardcoded to row 2 (0-indexed)
private static void insertRow(Sheets ss, String sheetID)throws IOException{
InsertDimensionRequest insertRow = new InsertDimensionRequest();
insertRow.setRange(new DimensionRange().setDimension("ROWS").setStartIndex(1).setEndIndex(2).setSheetId(0));
BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest().setRequests(Arrays.asList(
new Request().setInsertDimension(insertRow)
));
ss.spreadsheets().batchUpdate(sheetID, r).execute();
}
requestBuilder
//Populate ValueRange
static ValueRange requestBuilder( Report r, String range){
ValueRange v = new ValueRange()
.setValues(Arrays.asList(
Arrays.asList(r.value1),
Arrays.asList(r.value2),
Arrays.asList(r.value3),
Arrays.asList(r.value4),
Arrays.asList(r.value5)
))
.setMajorDimension("ROWS")
.setRange(range)
;
return v;
}
我不能对Elric的回答发表评论,但我想做一个更正。
如果要添加一行值,请在以下方法中:
requestBuilder \
应该使用.setMajorDimension("COLUMNS")
而不是"ROWS"
。
希望这有所帮助。