mfc中将excel导入数据库

#include <afxdb.h>
#include <comdef.h>
#include <atlbase.h>
#include <xlsxwriter.h>

void ImportExcelToDatabase(const CString& excelFilePath, const CString& connectionString)
{
    // 初始化COM库
    CoInitialize(NULL);

    // 创建Excel应用程序对象
    CComPtr<_Application> pExcelApp;
    HRESULT hr = pExcelApp.CoCreateInstance(L"Excel.Application");
    if (FAILED(hr))
    {
        AfxMessageBox(_T("无法创建Excel应用程序对象"));
        return;
    }

    // 打开Excel文件
    CComPtr<Workbooks> pWorkbooks;
    hr = pExcelApp->get_Workbooks()->Open(CComBSTR(excelFilePath), 0, TRUE, 5, CComBSTR(""), CComBSTR(""), TRUE, CComBSTR(""), CComBSTR(""), TRUE, CComBSTR(""), CComB串(""), NULL);
    if (FAILED(hr))
    {
        AfxMessageBox(_T("无法打开Excel文件"));
        return;
    }

    // 获取第一个工作表
    CComPtr<Worksheet> pWorksheet;
    CComPtr<Worksheets> pWorksheets;
    pExcelApp->get_Worksheets(&pWorksheets);
    pWorksheets->Item(COleVariant((short)1), &pWorksheet);

    // 获取数据范围
    CComPtr<Range> pRange;
    pWorksheet->get_Range(CComBSTR("A1"), CComBSTR("Z100"), &pRange);

    // 读取数据
    CComVariant varData;
    CComSafeArray saData;
    pRange->get_Value2(&varData);
    saData.Attach(varData.parray);

    // 连接数据库
    CDatabase db;
    db.OpenEx(connectionString, CDatabase::noOdbcDialog);

    // 插入数据
    CString sql;
    for (long i = 1; i <= saData.GetUpperBound(1); ++i)
    {
        sql.Format(_T("INSERT INTO YourTable (Column1, Column2, Column3) VALUES ('%s', %f, '%s')"),
            (LPCTSTR)saData.GetElement(1, i), saData.GetElement(2, i), (LPCTSTR)saData.GetElement(3, i));
        db.ExecuteSQL(sql);
    }

    // 关闭数据库连接
    db.Close();

    // 释放COM对象
    pRange.Release();
    pWorksheet.Release();
    pWorksheets.Release();
    pWorkbooks.Release();
    pExcelApp.Release();

    CoUninitialize();
}