excel - 使用来自 VBA 的身份验证执行 API 调用

Question

我不擅长 VBA 编程（非常基础），所以我希望您在以下问题上提供一些意见，因为我找不到我所关心的正确答案。我想使用 VBA 执行带有身份验证的 API 调用。我有以下代码，但在 Java 中（用于测试目的），但我的目标是在 Excel 中接收响应。这个想法是登录并获取请求的记录，并以 XML 或任何最终以广泛的 Excel 用户形式可读的形式返回它。

如何解决这个问题？我不一定需要现成的代码，任何抬头的开始都会受到赞赏。

package tests;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;

import org.apache.commons.lang3.StringUtils;
import org.w3c.dom.Document;

import com.tgmtSystems.core.util.XML_Utils;

public class TestQueryService
{
    static String url = ".....";

    static String ACCOUNT = "......";
    static String USERNAME = ".....";
    static String PASSWORD = "***********";
    static String SERVICE = "queryservice.do";
    static String QUERY_SEARCH_INSTANCE_ID = "14289081";

    public static class ListResponse
    {
        String[ ] itemInstanceIds;
        String projectInstanceId;
        String templateInstanceId;
    }

    public static void main( String[ ] args )
    throws Exception
    {
        String credentials = TestQueryService.login( );
        ListResponse listResponse = listItems( credentials );
        getItem( credentials, listResponse, listResponse.itemInstanceIds[ 0 ] );
        
        //This will return the 10 first records identified by their Ids ( itemInstanceId )
        getItems( credentials, listResponse, "11324721,11324744,11324745,11324746,11324748,11324751,11324758,11324829,11324830,11324836" );
        
        //This will return All Ids, if there are more than 50, there will be an error, you can only fetch 50 at a time.
        //getItems( credentials, listResponse, StringUtils.join( listResponse.itemInstanceIds, "," ) );
    }

    public static String login( )
    throws Exception
    {
        String xmlRequest = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><REQUEST><action>login</action><accountName>" + ACCOUNT + "</accountName><username>" + USERNAME + "</username><password>" + PASSWORD + "</password></REQUEST>";

        Document loginResponseDoc = sendRequest( xmlRequest );

        String credentials = XML_Utils.getNodeValue( "credentials", loginResponseDoc );
        return credentials;
    }

    public static ListResponse listItems( String credentials )
    throws Exception
    {
        String xmlRequest = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><REQUEST><action>listItems</action><credentials>" + credentials + "</credentials><querySearchInstanceId>" + QUERY_SEARCH_INSTANCE_ID + "</querySearchInstanceId></REQUEST>";

        Document responseDoc = sendRequest( xmlRequest );

        ListResponse listResponse = new ListResponse( );

        listResponse.itemInstanceIds = StringUtils.split( XML_Utils.getNodeValue( "itemInstanceIds", responseDoc ), "," );
        listResponse.projectInstanceId = XML_Utils.getNodeValue( "projectInstanceId", responseDoc );
        listResponse.templateInstanceId = XML_Utils.getNodeValue( "templateInstanceId", responseDoc );

        return listResponse;
    }

    public static Document getItem( String credentials, ListResponse listResponse, String itemInstanceId )
    throws Exception
    {
        String xmlRequest = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><REQUEST><action>getItem</action><credentials>" + credentials + "</credentials><templateInstanceId>" + listResponse.templateInstanceId + "</templateInstanceId><projectInstanceId>" + listResponse.projectInstanceId + "</projectInstanceId><itemInstanceId>" + itemInstanceId + "</itemInstanceId></REQUEST>";
        return sendRequest( xmlRequest );
    }
    
    public static Document getItems( String credentials, ListResponse listResponse, String itemInstancesId )
    throws Exception
    {
        String xmlRequest = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><REQUEST><action>getItems</action><credentials>" + credentials + "</credentials><templateInstanceId>" + listResponse.templateInstanceId + "</templateInstanceId><projectInstanceId>" + listResponse.projectInstanceId + "</projectInstanceId><itemInstancesId>" + itemInstancesId + "</itemInstancesId></REQUEST>";
        return sendRequest( xmlRequest );
    }

    public static Document sendRequest( String xmlRequest )
    throws Exception
    {
        String type = "text/xml";

        System.out.println( "REQUEST\n" + xmlRequest );

        URL u = new URL( url + SERVICE );
        HttpURLConnection conn = (HttpURLConnection) u.openConnection( );
        conn.setDoOutput( true );
        conn.setRequestMethod( "POST" );
        conn.setRequestProperty( "Content-Type", type );
        conn.setRequestProperty( "Content-Length", String.valueOf( xmlRequest.length( ) ) );
        OutputStream os = conn.getOutputStream( );
        os.write( xmlRequest.getBytes( ) );

        StringBuilder stringBuilder = new StringBuilder( );
        try
        {
            InputStream inputStream = conn.getInputStream( );
            if ( inputStream != null )
            {
                BufferedReader bufferedReader = new BufferedReader( new InputStreamReader( inputStream ) );
                char[ ] charBuffer = new char[ 128 ];
                int bytesRead = - 1;
                while ( ( bytesRead = bufferedReader.read( charBuffer ) ) > 0 )
                {
                    stringBuilder.append( charBuffer, 0, bytesRead );
                }
                bufferedReader.close( );
            }
            else
            {
                stringBuilder.append( "" );
            }
        }
        catch ( Exception ex )
        {
            throw ex;
        }

        String responseXML = stringBuilder.toString( );
        responseXML = responseXML.trim( );

        System.out.println( "RESPONSE\n" + responseXML );

        conn.disconnect( );

        return XML_Utils.getDocument( responseXML );
    }
}

标签： excelvbaapiauthenticationresponse