VBA 调用 dll 优化执行效率

问题描述

之前excel 用vba写过一个应用，请求的是aws lambda 后端,  但是受限于是云端服务，用起来响应特别慢，最近抽了点时间准备优化下，先加了点日志看看是哪里慢了

主方法代码如下，函数的主要目的是将 Excel 工作簿的数据（包括每个单元格的值和格式）转换为 JSON 格式并发送到指定的后端服务，以便进一步处理或存储。

该 VBA 函数 SendWorkbookSnapshotToBackend 的主要作用是将 Excel 工作簿的快照（即每个工作表的数据和格式）发送到一个指定的后端 API。以下是函数的详细解释：

功能概述

1. 参数：router：用于构建 API URL 的路径部分。函数首先定义了 API 的基本 URL，并将 router 参数附加到 URL 末尾。

2. 初始化变量：创建多个变量，包括用于存储 HTTP 请求对象、工作表对象、数据范围、快照字典等。

3. 遍历工作表：

   • 函数遍历当前工作簿中的每个工作表，获取每个工作表的最后一行和最后一列的索引。
   • 如果工作表中有数据，创建一个字典 sheetData 来存储每个单元格的值和格式。

4. 收集数据：

   • 使用双重循环遍历每个单元格，记录其地址、值和格式，并将它们添加到 sheetData 字典中。
   • 最后将每个工作表的快照添加到 snapshot 字典中，以工作表名称为键。

5. 创建请求：将快照字典嵌套在另一个字典 sts 中，并进一步嵌套在 payload 字典中，以形成最终的 JSON 请求参数。

6. 发送 HTTP 请求：

   • 使用 MSXML2.ServerXMLHTTP 对象发送 POST 请求，内容类型为 JSON。
   • 函数记录请求的执行时间，并处理可能的错误。

7. 解析响应：

   • 接收和解析响应内容，检查是否存在 workbook_snapshot 字段。
   • 如果存在，则调用 ProcessWorkbookSnapshot 函数处理响应中的工作簿快照。

8. 处理窗口操作：如果响应中包含 workbook_snapshot_operate 字段，则调用 ProcessWindowOperations 函数处理这些操作。

Function SendWorkbookSnapshotToBackend(router As String) As StringDim API_URL As StringAPI_URL = "http://xxx:xx/"Dim url As Stringurl = API_URL + routerDim http As ObjectDim ws As WorksheetDim lastRow As LongDim lastCol As LongDim cell As RangeDim snapshot As ObjectDim sheetData As ObjectDim jsonSnapshot As StringDim response As StringDim sheetName As VariantDim updatedSheetData As ObjectDim cellKey As VariantSet snapshot = CreateObject("Scripting.Dictionary")Dim startTime As DoubleDim endTime As DoublestartTime = TimerFor Each ws In ThisWorkbook.SheetsSet sheetData = CreateObject("Scripting.Dictionary")lastRow = ws.UsedRange.Rows.CountlastCol = ws.UsedRange.Columns.CountIf lastRow > 0 And lastCol > 0 ThenDim values As VariantDim formats As VariantDim dataRange As RangeSet dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))values = dataRange.ValueFor r = 1 To lastRowFor c = 1 To lastColDim addr As Stringaddr = ws.Cells(r, c).Address(False, False)sheetData.Add addr, values(r, c)sheetData.Add addr & "_Format", ws.Cells(r, c).NumberFormatNext cNext rsnapshot.Add ws.Name, sheetDataEnd IfNext wsendTime = TimerDebug.Print "snapshot Add executed in " & Format(endTime - startTime, "0.0000") & " seconds"Set sts = CreateObject("Scripting.Dictionary")sts.Add "Sheets", snapshotSet payload = CreateObject("Scripting.Dictionary")payload.Add "workbook_snapshot", stsDim jsonPayload As StringjsonPayload = JsonConverter.ConvertToJson(payload)Set http = CreateObject("MSXML2.ServerXMLHTTP")On Error Resume NextstartTime = Timerhttp.Open "POST", url, Falsehttp.setRequestHeader "Content-Type", "application/json"http.send jsonPayloadendTime = TimerDebug.Print "http.send executed in " & Format(endTime - startTime, "0.0000") & " seconds"If Err.Number <> 0 ThenMsgBox "HTTP: " & Err.Description & "  " & Err.Number & ")"SendWorkbookSnapshotToBackend = "Error: HTTP request failed - " & Err.NumberExit FunctionEnd IfOn Error GoTo 0startTime = Timerresponse = http.responseTextSet result = JsonConverter.ParseJson(response)endTime = TimerDebug.Print "ParseJson executed in " & Format(endTime - startTime, "0.0000") & " seconds"' update excel cell valueIf result.Exists("workbook_snapshot") ThenstartTime = TimerCall ProcessWorkbookSnapshot(result("workbook_snapshot"))endTime = TimerDebug.Print "ProcessWorkbookSnapshot executed in " & Format(endTime - startTime, "0.0000") & " seconds"End If' done the window operationIf result.Exists("workbook_snapshot_operate") ThenCall ProcessWindowOperations(result("workbook_snapshot_operate"))End IfSendWorkbookSnapshotToBackend = "Success: " & responseSet http = NothingSet snapshot = NothingSet result = Nothing
End Function

日志显示

可以看到整个处理耗时在非请求的部分挺多的， json解析的效率也不高，考虑把这些逻辑都挪到dll库里，让VBA调用dll来提升效率

安装MinGW

GitHub 上较新版的压缩包的命名又分为 msvcrt 和 ucrt。MSVCRT（Microsoft Visual C++ Runtime）和 UCRT（Universal C Runtime）是 Microsoft Windows 上的两种 C 运行时库。MSVCRT 在所有 Windows 版本上均可用，把./mingw64/bin 配置到PATH

有鉴于我的C语言都还给老师了， vcpkg 安装库依赖一直在报错，所以手写造了下cur 和cjson的轮子，放在一个文件里来打dll 文件

#include <windows.h>
#include <objbase.h>
#include <ole2.h>
#include <wininet.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>// Configuration: Backend URL
#define BACKEND_URL "http://127.0.0.1:5000"
#define LOG_FILE "D:\\xxxx\debug.log"void log_message(const char* message) {FILE* fp = fopen(LOG_FILE, "a");if (fp) {fprintf(fp, "%s\n", message);fclose(fp);}
}// BSTR conversion functions for MinGW
char* ConvertBSTRToString(BSTR bstr) {if (!bstr) return _strdup("");int len = WideCharToMultiByte(CP_UTF8, 0, bstr, -1, NULL, 0, NULL, NULL);char* str = (char*)malloc(len);WideCharToMultiByte(CP_UTF8, 0, bstr, -1, str, len, NULL, NULL);return str;
}BSTR ConvertStringToBSTR(const char* str) {if (!str) return SysAllocString(L"");int len = MultiByteToWideChar(CP_UTF8, 0, str, -1, NULL, 0);wchar_t* wstr = (wchar_t*)malloc(len * sizeof(wchar_t));MultiByteToWideChar(CP_UTF8, 0, str, -1, wstr, len);BSTR bstr = SysAllocString(wstr);free(wstr);return bstr;
}// Simplified JSON parser and generator
typedef enum { JSON_OBJECT, JSON_ARRAY, JSON_STRING, JSON_NUMBER } JsonType;typedef struct JsonValue {JsonType type;union {struct { char* key; struct JsonValue* value; } pair; // For object key-value pairsstruct { struct JsonValue* items; size_t count; } array; // For arrayschar* string; // For stringsdouble number; // For numbers} value;struct JsonValue* next; // For linked lists (object pairs or array items)
} JsonValue;JsonValue* json_create_object() {JsonValue* obj = (JsonValue*)calloc(1, sizeof(JsonValue));obj->type = JSON_OBJECT;return obj;
}JsonValue* json_create_array() {JsonValue* arr = (JsonValue*)calloc(1, sizeof(JsonValue));arr->type = JSON_ARRAY;return arr;
}JsonValue* json_create_string(const char* str) {JsonValue* val = (JsonValue*)calloc(1, sizeof(JsonValue));val->type = JSON_STRING;val->value.string = _strdup(str);return val;
}JsonValue* json_create_number(double num) {JsonValue* val = (JsonValue*)calloc(1, sizeof(JsonValue));val->type = JSON_NUMBER;val->value.number = num;return val;
}void json_add_to_object(JsonValue* obj, const char* key, JsonValue* value) {if (obj->type != JSON_OBJECT) return;JsonValue* pair = (JsonValue*)calloc(1, sizeof(JsonValue));pair->type = JSON_OBJECT;pair->value.pair.key = _strdup(key);pair->value.pair.value = value;pair->next = obj->value.pair.value;obj->value.pair.value = pair;
}void json_add_to_array(JsonValue* arr, JsonValue* value) {if (arr->type != JSON_ARRAY) return;value->next = arr->value.array.items;arr->value.array.items = value;arr->value.array.count++;
}void json_free(JsonValue* json) {if (!json) return;if (json->type == JSON_OBJECT) {JsonValue* pair = json->value.pair.value;while (pair) {JsonValue* next = pair->next;free(pair->value.pair.key);json_free(pair->value.pair.value);free(pair);pair = next;}} else if (json->type == JSON_ARRAY) {JsonValue* item = json->value.array.items;while (item) {JsonValue* next = item->next;json_free(item);item = next;}} else if (json->type == JSON_STRING) {free(json->value.string);}free(json);
}char* json_to_string(JsonValue* json) {char* result = (char*)calloc(1, 1024 * 1024); // Large buffersize_t pos = 0;char temp[256];if (json->type == JSON_OBJECT) {result[pos++] = '{';JsonValue* pair = json->value.pair.value;int first = 1;while (pair) {if (!first) result[pos++] = ',';first = 0;snprintf(temp, sizeof(temp), "\"%s\":", pair->value.pair.key);strcat(result + pos, temp);pos += strlen(temp);char* sub = json_to_string(pair->value.pair.value);strcat(result + pos, sub);pos += strlen(sub);free(sub);pair = pair->next;}result[pos++] = '}';} else if (json->type == JSON_ARRAY) {result[pos++] = '[';JsonValue* item = json->value.array.items;int first = 1;while (item) {if (!first) result[pos++] = ',';first = 0;char* sub = json_to_string(item);strcat(result + pos, sub);pos += strlen(sub);free(sub);item = item->next;}result[pos++] = ']';} else if (json->type == JSON_STRING) {snprintf(temp, sizeof(temp), "\"%s\"", json->value.string);strcat(result + pos, temp);pos += strlen(temp);} else if (json->type == JSON_NUMBER) {snprintf(temp, sizeof(temp), "%f", json->value.number);strcat(result + pos, temp);pos += strlen(temp);}result[pos] = '\0';return result;
}// Simplified JSON parser
JsonValue* json_parse(const char* str) {// Basic parser: expects {key:value} or [values], skips whitespaceJsonValue* result = NULL;const char* p = str;while (*p && isspace(*p)) p++;if (*p == '{') {result = json_create_object();p++;while (*p && *p != '}') {while (isspace(*p)) p++;if (*p != '"') break;p++;char key[256] = {0};int k = 0;while (*p && *p != '"' && k < sizeof(key) - 1) key[k++] = *p++;if (*p != '"') break;p++;while (isspace(*p)) p++;if (*p != ':') break;p++;while (isspace(*p)) p++;JsonValue* value = NULL;if (*p == '"') {p++;char val[256] = {0};int v = 0;while (*p && *p != '"' && v < sizeof(val) - 1) val[v++] = *p++;if (*p != '"') break;p++;value = json_create_string(val);} else if (isdigit(*p) || *p == '-') {char num[32] = {0};int n = 0;while ((isdigit(*p) || *p == '.' || *p == '-') && n < sizeof(num) - 1) num[n++] = *p++;value = json_create_number(atof(num));} else if (*p == '{') {value = json_parse(p);while (*p && *p != '}') p++;if (*p == '}') p++;} else {break;}json_add_to_object(result, key, value);while (isspace(*p)) p++;if (*p == ',') p++;}if (*p == '}') p++;} else if (*p == '[') {result = json_create_array();p++;while (*p && *p != ']') {JsonValue* value = json_parse(p);if (!value) break;json_add_to_array(result, value);while (*p && *p != ',' && *p != ']') p++;if (*p == ',') p++;}if (*p == ']') p++;}return result;
}JsonValue* json_get_object_item(JsonValue* obj, const char* key) {if (obj->type != JSON_OBJECT) return NULL;JsonValue* pair = obj->value.pair.value;while (pair) {if (strcmp(pair->value.pair.key, key) == 0) return pair->value.pair.value;pair = pair->next;}return NULL;
}// HTTP client using WinINet
typedef struct {char* memory;size_t size;
} HttpResponse;BOOL http_post(const char* url, const char* data, HttpResponse* response) {HINTERNET hInternet = InternetOpenA("ExcelSnapshot", INTERNET_OPEN_TYPE_PRECONFIG, NULL, NULL, 0);if (!hInternet) {log_message("Error: InternetOpenA failed");return FALSE;}HINTERNET hConnect = InternetOpenUrlA(hInternet, url, NULL, 0, INTERNET_FLAG_RELOAD, 0);if (!hConnect) {log_message("Error: InternetOpenUrlA failed");InternetCloseHandle(hInternet);return FALSE;}// Set headersconst char* headers = "Content-Type: application/json\r\n";DWORD dataLen = strlen(data);BOOL success = HttpAddRequestHeadersA(hConnect, headers, strlen(headers), HTTP_ADDREQ_FLAG_ADD);if (!success) {log_message("Error: HttpAddRequestHeadersA failed");InternetCloseHandle(hConnect);InternetCloseHandle(hInternet);return FALSE;}// Send requestDWORD bytesWritten;success = InternetWriteFile(hConnect, data, dataLen, &bytesWritten);if (!success || bytesWritten != dataLen) {log_message("Error: InternetWriteFile failed");InternetCloseHandle(hConnect);InternetCloseHandle(hInternet);return FALSE;}// Read responseresponse->memory = (char*)calloc(1, 1024 * 1024); // Large bufferresponse->size = 0;DWORD bytesRead;while (InternetReadFile(hConnect, response->memory + response->size, 1024, &bytesRead) && bytesRead > 0) {response->size += bytesRead;}response->memory[response->size] = '\0';InternetCloseHandle(hConnect);InternetCloseHandle(hInternet);return TRUE;
}// Hash table implementation (simplified for dictionary)
typedef struct {char* key;VARIANT value;
} HashEntry;typedef struct {HashEntry* entries;int size;int capacity;
} HashTable;HashTable* create_hash_table(int capacity) {HashTable* table = (HashTable*)malloc(sizeof(HashTable));table->entries = (HashEntry*)calloc(capacity, sizeof(HashEntry));table->size = 0;table->capacity = capacity;return table;
}void hash_table_add(HashTable* table, const char* key, VARIANT value) {if (table->size >= table->capacity) return; // Simplified, no resizingHashEntry* entry = &table->entries[table->size++];entry->key = _strdup(key);VariantInit(&entry->value);VariantCopy(&entry->value, &value);
}void free_hash_table(HashTable* table) {for (int i = 0; i < table->size; i++) {free(table->entries[i].key);VariantClear(&table->entries[i].value);}free(table->entries);free(table);
}// Main DLL exported function
__declspec(dllexport) BSTR __stdcall SendWorkbookSnapshotToBackend(BSTR router) {HRESULT hr;IDispatch* pExcel = NULL;IDispatch* pWorkbook = NULL;IDispatch* pWorksheets = NULL;JsonValue* snapshot = json_create_object();JsonValue* sheets = json_create_object();char* jsonPayload = NULL;HttpResponse response = {0};BSTR result = NULL;log_message("Starting SendWorkbookSnapshotToBackend");// Initialize COMhr = CoInitialize(NULL);if (FAILED(hr)) {log_message("Error: COM initialization failed");result = SysAllocString(L"Error: COM initialization failed");goto cleanup;}log_message("COM initialized");// Get Excel applicationCLSID clsid;hr = CLSIDFromProgID(L"Excel.Application", &clsid);if (FAILED(hr)) {log_message("Error: Excel not found");result = SysAllocString(L"Error: Excel not found");goto cleanup;}log_message("Excel ProgID found");hr = CoCreateInstance(&clsid, NULL, CLSCTX_LOCAL_SERVER, &IID_IDispatch, (void**)&pExcel);if (FAILED(hr)) {log_message("Error: Failed to create Excel instance");result = SysAllocString(L"Error: Failed to create Excel instance");goto cleanup;}log_message("Excel instance created");// Get active workbookVARIANT vResult;VariantInit(&vResult);DISPID dispid;OLECHAR* szMember = L"ActiveWorkbook";hr = pExcel->lpVtbl->GetIDsOfNames(pExcel, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get ActiveWorkbook");result = SysAllocString(L"Error: Failed to get ActiveWorkbook");goto cleanup;}log_message("ActiveWorkbook ID obtained");DISPPARAMS params = { NULL, NULL, 0, 0 };hr = pExcel->lpVtbl->Invoke(pExcel, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access ActiveWorkbook");result = SysAllocString(L"Error: Failed to access ActiveWorkbook");goto cleanup;}pWorkbook = vResult.pdispVal;log_message("ActiveWorkbook accessed");// Get worksheetsszMember = L"Worksheets";hr = pWorkbook->lpVtbl->GetIDsOfNames(pWorkbook, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get Worksheets");result = SysAllocString(L"Error: Failed to get Worksheets");goto cleanup;}log_message("Worksheets ID obtained");hr = pWorkbook->lpVtbl->Invoke(pWorkbook, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Worksheets");result = SysAllocString(L"Error: Failed to access Worksheets");goto cleanup;}pWorksheets = vResult.pdispVal;log_message("Worksheets accessed");// Iterate worksheetsszMember = L"Count";hr = pWorksheets->lpVtbl->GetIDsOfNames(pWorksheets, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get Worksheets count");result = SysAllocString(L"Error: Failed to get Worksheets count");goto cleanup;}log_message("Worksheets count ID obtained");hr = pWorksheets->lpVtbl->Invoke(pWorksheets, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr)) {log_message("Error: Failed to access Worksheets count");result = SysAllocString(L"Error: Failed to access Worksheets count");goto cleanup;}int sheetCount = vResult.lVal;log_message("Worksheets count obtained");for (int i = 1; i <= sheetCount; i++) {// Get worksheetVariantClear(&vResult);szMember = L"Item";hr = pWorksheets->lpVtbl->GetIDsOfNames(pWorksheets, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get Worksheet Item");continue;}VARIANT vIndex;VariantInit(&vIndex);vIndex.vt = VT_I4;vIndex.lVal = i;params.rgvarg = &vIndex;params.cArgs = 1;hr = pWorksheets->lpVtbl->Invoke(pWorksheets, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Worksheet");continue;}IDispatch* pWorksheet = vResult.pdispVal;log_message("Worksheet accessed");// Get worksheet nameVariantClear(&vResult);szMember = L"Name";hr = pWorksheet->lpVtbl->GetIDsOfNames(pWorksheet, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get Worksheet Name");pWorksheet->lpVtbl->Release(pWorksheet);continue;}hr = pWorksheet->lpVtbl->Invoke(pWorksheet, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_BSTR) {log_message("Error: Failed to access Worksheet Name");pWorksheet->lpVtbl->Release(pWorksheet);continue;}char* sheetName = ConvertBSTRToString(vResult.bstrVal);SysFreeString(vResult.bstrVal);log_message("Worksheet name obtained");// Get UsedRangeVariantClear(&vResult);szMember = L"UsedRange";hr = pWorksheet->lpVtbl->GetIDsOfNames(pWorksheet, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get UsedRange");free(sheetName);pWorksheet->lpVtbl->Release(pWorksheet);continue;}hr = pWorksheet->lpVtbl->Invoke(pWorksheet, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access UsedRange");free(sheetName);pWorksheet->lpVtbl->Release(pWorksheet);continue;}IDispatch* pRange = vResult.pdispVal;log_message("UsedRange accessed");// Get row and column countVariantClear(&vResult);szMember = L"Rows";hr = pRange->lpVtbl->GetIDsOfNames(pRange, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);if (FAILED(hr)) {log_message("Error: Failed to get Rows");pRange->lpVtbl->Release(pRange);free(sheetName);pWorksheet->lpVtbl->Release(pWorksheet);continue;}hr = pRange->lpVtbl->Invoke(pRange, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Rows");pRange->lpVtbl->Release(pRange);free(sheetName);pWorksheet->lpVtbl->Release(pWorksheet);continue;}IDispatch* pRows = vResult.pdispVal;szMember = L"Count";hr = pRows->lpVtbl->GetIDsOfNames(pRows, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pRows->lpVtbl->Invoke(pRows, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);long lastRow = vResult.lVal;pRows->lpVtbl->Release(pRows);log_message("Row count obtained");VariantClear(&vResult);szMember = L"Columns";hr = pRange->lpVtbl->GetIDsOfNames(pRange, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pRange->lpVtbl->Invoke(pRange, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);IDispatch* pCols = vResult.pdispVal;szMember = L"Count";hr = pCols->lpVtbl->GetIDsOfNames(pCols, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pCols->lpVtbl->Invoke(pCols, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);long lastCol = vResult.lVal;pCols->lpVtbl->Release(pCols);log_message("Column count obtained");// Create sheet dataJsonValue* sheetData = json_create_object();for (long r = 1; r <= lastRow; r++) {for (long c = 1; c <= lastCol; c++) {// Get cellVariantClear(&vResult);szMember = L"Cells";hr = pWorksheet->lpVtbl->GetIDsOfNames(pWorksheet, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);VARIANT vRow, vCol;VariantInit(&vRow);VariantInit(&vCol);vRow.vt = VT_I4;vRow.lVal = r;vCol.vt = VT_I4;vCol.lVal = c;VARIANT args[2] = { vCol, vRow };params.rgvarg = args;params.cArgs = 2;hr = pWorksheet->lpVtbl->Invoke(pWorksheet, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Cell");continue;}IDispatch* pCell = vResult.pdispVal;// Get cell addressVariantClear(&vResult);szMember = L"Address";hr = pCell->lpVtbl->GetIDsOfNames(pCell, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);VARIANT vFalse;VariantInit(&vFalse);vFalse.vt = VT_BOOL;vFalse.boolVal = VARIANT_FALSE;VARIANT argsAddr[2] = { vFalse, vFalse };params.rgvarg = argsAddr;params.cArgs = 2;hr = pCell->lpVtbl->Invoke(pCell, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);char* cellAddr = ConvertBSTRToString(vResult.bstrVal);SysFreeString(vResult.bstrVal);log_message("Cell address obtained");// Get cell valueVariantClear(&vResult);szMember = L"Value";hr = pCell->lpVtbl->GetIDsOfNames(pCell, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);params.rgvarg = NULL;params.cArgs = 0;hr = pCell->lpVtbl->Invoke(pCell, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);char valueStr[256];if (vResult.vt == VT_EMPTY) {strcpy(valueStr, "");} else if (vResult.vt == VT_R8) {sprintf(valueStr, "%f", vResult.dblVal);json_add_to_object(sheetData, cellAddr, json_create_number(vResult.dblVal));} else if (vResult.vt == VT_BSTR) {char* tmp = ConvertBSTRToString(vResult.bstrVal);strncpy(valueStr, tmp, sizeof(valueStr) - 1);valueStr[sizeof(valueStr) - 1] = '\0';json_add_to_object(sheetData, cellAddr, json_create_string(tmp));free(tmp);} else {strcpy(valueStr, "");}log_message("Cell value obtained");free(cellAddr);pCell->lpVtbl->Release(pCell);}}json_add_to_object(sheets, sheetName, sheetData);free(sheetName);pRange->lpVtbl->Release(pRange);pWorksheet->lpVtbl->Release(pWorksheet);log_message("Sheet processed");}// Create payloadJsonValue* sts = json_create_object();json_add_to_object(sts, "Sheets", sheets);JsonValue* payload = json_create_object();json_add_to_object(payload, "workbook_snapshot", sts);jsonPayload = json_to_string(payload);log_message("JSON payload created");// Send HTTP requestchar url[512];char* routerStr = ConvertBSTRToString(router);snprintf(url, sizeof(url), "%s/%s", BACKEND_URL, routerStr);free(routerStr);log_message("Sending HTTP request");if (!http_post(url, jsonPayload, &response)) {log_message("Error: HTTP request failed");result = SysAllocString(L"Error: HTTP request failed");goto cleanup;}log_message("HTTP response received");// Parse responseJsonValue* jsonResponse = json_parse(response.memory);if (!jsonResponse) {log_message("Error: Failed to parse JSON response");result = SysAllocString(L"Error: Failed to parse JSON response");goto cleanup;}log_message("JSON response parsed");// Process workbook snapshotJsonValue* workbookSnapshot = json_get_object_item(jsonResponse, "workbook_snapshot");if (workbookSnapshot) {JsonValue* sheets = json_get_object_item(workbookSnapshot, "Sheets");if (sheets && sheets->type == JSON_OBJECT) {JsonValue* sheet = sheets->value.pair.value;while (sheet) {char* sheetName = sheet->value.pair.key;JsonValue* sheetData = sheet->value.pair.value;IDispatch* pWorksheet = NULL;VariantClear(&vResult);szMember = L"Worksheets";hr = pWorkbook->lpVtbl->GetIDsOfNames(pWorkbook, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pWorkbook->lpVtbl->Invoke(pWorkbook, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);pWorksheets = vResult.pdispVal;log_message("Worksheets for update accessed");VARIANT vSheetName;VariantInit(&vSheetName);vSheetName.vt = VT_BSTR;vSheetName.bstrVal = ConvertStringToBSTR(sheetName);params.rgvarg = &vSheetName;params.cArgs = 1;szMember = L"Item";hr = pWorksheets->lpVtbl->GetIDsOfNames(pWorksheets, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pWorksheets->lpVtbl->Invoke(pWorksheets, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);SysFreeString(vSheetName.bstrVal);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Worksheet for update");pWorksheets->lpVtbl->Release(pWorksheets);sheet = sheet->next;continue;}pWorksheet = vResult.pdispVal;pWorksheets->lpVtbl->Release(pWorksheets);log_message("Worksheet for update accessed");// Update cellsJsonValue* cell = sheetData->value.pair.value;while (cell) {char* cellKey = cell->value.pair.key;JsonValue* cellValue = cell->value.pair.value;VariantClear(&vResult);szMember = L"Range";hr = pWorksheet->lpVtbl->GetIDsOfNames(pWorksheet, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);VARIANT vCellKey;VariantInit(&vCellKey);vCellKey.vt = VT_BSTR;vCellKey.bstrVal = ConvertStringToBSTR(cellKey);params.rgvarg = &vCellKey;params.cArgs = 1;hr = pWorksheet->lpVtbl->Invoke(pWorksheet, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);SysFreeString(vCellKey.bstrVal);if (FAILED(hr) || vResult.vt != VT_DISPATCH) {log_message("Error: Failed to access Cell for update");cell = cell->next;continue;}IDispatch* pCell = vResult.pdispVal;// Check for formulaVariantClear(&vResult);szMember = L"Formula";hr = pCell->lpVtbl->GetIDsOfNames(pCell, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);hr = pCell->lpVtbl->Invoke(pCell, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &params, &vResult, NULL, NULL);if (vResult.vt == VT_BSTR && vResult.bstrVal[0] == L'=') {log_message("Skipping cell with formula");pCell->lpVtbl->Release(pCell);cell = cell->next;continue;}// Set valueszMember = L"Value";hr = pCell->lpVtbl->GetIDsOfNames(pCell, &IID_NULL, &szMember, 1, LOCALE_USER_DEFAULT, &dispid);VARIANT vValue;VariantInit(&vValue);if (cellValue->type == JSON_NUMBER) {vValue.vt = VT_R8;vValue.dblVal = cellValue->value.number;} else if (cellValue->type == JSON_STRING) {vValue.vt = VT_BSTR;vValue.bstrVal = ConvertStringToBSTR(cellValue->value.string);} else {vValue.vt = VT_EMPTY;}params.rgvarg = &vValue;params.cArgs = 1;hr = pCell->lpVtbl->Invoke(pCell, dispid, &IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYPUT, &params, NULL, NULL, NULL);SysFreeString(vValue.bstrVal);pCell->lpVtbl->Release(pCell);log_message("Cell updated");cell = cell->next;}pWorksheet->lpVtbl->Release(pWorksheet);log_message("Sheet updated");sheet = sheet->next;}}}result = SysAllocString(L"Success");log_message("Success");cleanup:if (response.memory) free(response.memory);if (jsonPayload) free(jsonPayload);if (jsonResponse) json_free(jsonResponse);if (payload) json_free(payload);if (pWorksheets) pWorksheets->lpVtbl->Release(pWorksheets);if (pWorkbook) pWorkbook->lpVtbl->Release(pWorkbook);if (pExcel) pExcel->lpVtbl->Release(pExcel);VariantClear(&vResult);CoUninitialize();log_message("Cleanup completed");return result;
}// DLL entry point
BOOL APIENTRY DllMain(HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) {switch (ul_reason_for_call) {case DLL_PROCESS_ATTACH:case DLL_THREAD_ATTACH:case DLL_THREAD_DETACH:case DLL_PROCESS_DETACH:break;}return TRUE;
}

编译为dll文件

D:\mingw32\bin\gcc.exe -shared -o D:\userData\dll\excel_snapshot_32.dll .\excel_snapshot.c -lole32 -loleaut32 -lwininet -luuid "-Wl,--out-implib,D:\userData\dll\excel_snapshot_32.lib" > compile.log 2>&1

• -shared:

  • 这个选项告诉 gcc 创建一个共享库（DLL）。这是生成 DLL 文件所必需的。

• -o D:\userData\dll\excel_snapshot_32.dll:

  • -o 选项后面跟着的是输出文件的名称和路径。这里指定了生成的 DLL 文件名为 excel_snapshot_32.dll，并将其放在 D:\userData\dll\ 目录下。

• .\excel_snapshot.c:

  • 这是要编译的 C 源文件。相对路径表示当前目录下的 excel_snapshot.c 文件。

• -lole32 -loleaut32 -lwininet -luuid:

  • 这些是链接库的选项：
    • -lole32: 链接 OLE (Object Linking and Embedding) 库。
    • -loleaut32: 链接 OLE 自动化库。
    • -lwininet: 链接 Windows Internet API 库。
    • -luuid: 链接 UUID（通用唯一标识符）库，用于 COM 编程。

• "-Wl,--out-implib,D:\userData\deng\dll\excel_snapshot_32.lib":

  • -Wl, 表示将后面的选项传递给链接器。
  • --out-implib,D:\userData\deng\dll\excel_snapshot_32.lib 指定生成一个导入库（.lib 文件），该库可以用于其他项目中链接到此 DLL。

VBA 里添加对dll主函数的调用

Private Declare PtrSafe Function SendWorkbookSnapshotToBackend Lib "D:\userData\dll\excel_snapshot_32.dll" (ByVal router As LongPtr) As LongPtrSub CallDLL(router As String)On Error GoTo ErrorHandlerDim result As LongPtrIf Len(Trim(router)) = 0 ThenMsgBox "Error: Router parameter cannot be empty.", vbCriticalExit SubEnd IfCall SendWorkbookSnapshotToBackend(StrPtr(router))Exit SubErrorHandler:MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

尝试调用了下报错

Can't find DLL entry point SendWorkbookSnapshotToBackend in D:\userData\deng\dll\excel_snapshot_32.dll
 

说明 VBA 无法在 DLL 中找到名为 SendWorkbookSnapshotToBackend 的导出函数。这通常有几个常见原因，逐个排查下

在 objdump 输出中看到的是：

这是 标准的 Windows StdCall 函数修饰符格式，说明我的 DLL 使用了 __stdcall 调用约定，函数被导出为 SendWorkbookSnapshotToBackend@4 

而 VBA 默认寻找不带修饰符的函数名，比如 SendWorkbookSnapshotToBackend。

解决方案 ：用别名指定导出名

修改 Declare 语句，加上别名匹配导出名

Private Declare PtrSafe Function SendWorkbookSnapshotToBackend Lib "D:\userData\dll\excel_snapshot_32.dll" Alias "SendWorkbookSnapshotToBackend@4" (ByVal router As LongPtr) As LongPtr

执行成功，excel上的单元格逻辑执行了， 效率只能说稍微强了那么一丢丢