GoogleSheetsJavaAPI写入工作表

Question

我一直在寻找一个答案或一个例子。我开始使用GoogleAPI sheets v4，并找到了将数据附加到电子表格的答案，其中的问题在这个堆栈问题中

/**
 * Application name.
 */
private static final String APPLICATION_NAME =
        "Google Sheets API Java Quickstart";

/**
 * Directory to store user credentials for this application.
 */
private static final java.io.File DATA_STORE_DIR = new java.io.File(
        System.getProperty("user.home"), ".credentials/sheets.googleapis.com-java-quickstart");

/**
 * Global instance of the {@link FileDataStoreFactory}.
 */
private static FileDataStoreFactory DATA_STORE_FACTORY;

/**
 * Global instance of the JSON factory.
 */
private static final JsonFactory JSON_FACTORY =
        JacksonFactory.getDefaultInstance();

/**
 * Global instance of the HTTP transport.
 */
private static HttpTransport HTTP_TRANSPORT;

/**
 * Global instance of the scopes, read/write commands depend on that
 * <p/>
 * If modifying these scopes, delete your previously saved credentials
 * at ~/.credentials/drive-java-quickstart
 */
private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS);


static {
    try {
        HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
    } catch (Throwable t) {
        t.printStackTrace();
        System.exit(1);
    }
}

/**
 * Creates an authorized Credential object.
 *
 * @return an authorized Credential object.
 * @throws IOException
 */
public static Credential authorize() throws IOException {
    // Load client secrets.
    InputStream in =
            SheetsWithAppendTest.class.getResourceAsStream("/client_secret.json");
    GoogleClientSecrets clientSecrets =
            GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

    // Build flow and trigger user authorization request.
    GoogleAuthorizationCodeFlow flow =
            new GoogleAuthorizationCodeFlow.Builder(
                    HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                    .setDataStoreFactory(DATA_STORE_FACTORY)
                    .setAccessType("offline")
                    .build();
    Credential credential = new AuthorizationCodeInstalledApp(
            flow, new LocalServerReceiver()).authorize("user");
    System.out.println(
            "Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
    return credential;
}

/**
 * Build and return an authorized Sheets API client service.
 *
 * @return an authorized Sheets API client service
 * @throws IOException
 */
public static Sheets getSheetsService() throws IOException {
    Credential credential = authorize();
    return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
            .setApplicationName("SHEETS_QUICK_START")
            .build();
}

public static String dateForReport() {
    DateFormat dateformat = new SimpleDateFormat("MM/dd/yy");
    Date dateObj = new Date();
    return dateformat.format(dateObj);
}

public static void main(String[] args) throws Exception {

    Sheets service = getSheetsService();

    String spreadSheetID = "xxx";
    Integer sheetID = 0;

    String defectReferenceValue = "Defect 9999";
    Double defectIDValue = 9999.0;
    String areaValue = "RandomArea";
    String screenValue = "";
    String fieldValue = "";
    String reqValue = "";
    String ruleValue = "";
    String fieldIDValue = "";
    String defectDescriptionValue = "";
    String screenShotValue = "ScreenShotLink";
    String dateValue = dateForReport();


    List<RowData> rowData = new ArrayList<RowData>();
    List<CellData> cellData = new ArrayList<CellData>();

    CellData defectReferenceField = new CellData();
    defectReferenceField.setUserEnteredValue(new ExtendedValue().setStringValue(defectReferenceValue));
    defectReferenceField.setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setType("DATE")));
    cellData.add(defectReferenceField);

    CellData defectIDField = new CellData();
    defectIDField.setUserEnteredValue(new ExtendedValue().setNumberValue(defectIDValue));
    cellData.add(defectIDField);

    CellData areaField = new CellData();
    areaField.setUserEnteredValue(new ExtendedValue().setStringValue(areaValue));
    cellData.add(areaField);

    CellData screenField = new CellData();
    screenField.setUserEnteredValue(new ExtendedValue().setStringValue(screenValue));
    cellData.add(screenField);

    CellData fieldField = new CellData();
    fieldField.setUserEnteredValue(new ExtendedValue().setStringValue(fieldValue));
    cellData.add(fieldField);

    CellData reqField = new CellData();
    reqField.setUserEnteredValue(new ExtendedValue().setStringValue(reqValue));
    cellData.add(reqField);

    CellData ruleField = new CellData();
    ruleField.setUserEnteredValue(new ExtendedValue().setStringValue(ruleValue));
    cellData.add(ruleField);

    CellData fieldIDField = new CellData();
    fieldIDField.setUserEnteredValue(new ExtendedValue().setStringValue(fieldIDValue));
    cellData.add(fieldIDField);

    CellData defectDescriptionField = new CellData();
    defectDescriptionField.setUserEnteredValue(new ExtendedValue().setStringValue(defectDescriptionValue));
    cellData.add(defectDescriptionField);

    CellData screenShotField = new CellData();
    screenShotField.setUserEnteredValue(new ExtendedValue().setStringValue(screenShotValue));
    cellData.add(screenShotField);

    CellData dateField = new CellData();
    dateField.setUserEnteredValue(new ExtendedValue().setStringValue(dateValue));
    cellData.add(dateField);

    rowData.add(new RowData().setValues(cellData));

    BatchUpdateSpreadsheetRequest batchRequests = new BatchUpdateSpreadsheetRequest();
    BatchUpdateSpreadsheetResponse response;
    List<Request> requests = new ArrayList<Request>();

    AppendCellsRequest appendCellReq = new AppendCellsRequest();
    appendCellReq.setSheetId(sheetID);
    appendCellReq.setRows(rowData);
    appendCellReq.setFields("userEnteredValue,userEnteredFormat.numberFormat");


    requests = new ArrayList<Request>();
    requests.add(new Request().setAppendCells(appendCellReq));
    batchRequests = new BatchUpdateSpreadsheetRequest();
    batchRequests.setRequests(requests);


    response = service.spreadsheets().batchUpdate(spreadSheetID, batchRequests).execute();
    System.out.println("Request \n\n");
    System.out.println(batchRequests.toPrettyString());
    System.out.println("\n\nResponse \n\n");
    System.out.println(response.toPrettyString());
}

尽管该解决方案是功能性的，但它不可伸缩，它以增量方式向工作表中添加值，而不检查标题。我真正想做的是为标题行“Defect”=“x”标题行“Date”=y等添加hashmap之类的值。

有没有人有一个解决这个问题的示例，或者你能告诉我一个可以用来回答这个问题的资源？任何帮助都将不胜感激。

提前谢谢大家,，

尼科斯。

Answer 1

存储列的列表

对于列表中的每个项目。。检查hashmap中的值如果该值不存在添加一个空白单元格如果该值存在将该值放入单元格

Map<String,String> values=new HashMap<>();
.
.
.
String[] columns ={"greek legend","minataur"};
for(int i=0;i<columns.length;i++){
    String val=values.get(columns[i]);
    if(val!=null)
    {
        //put value in cell
    }
    else{
        //add blank cell
    }
}