您现在的位置：程式碼之家 >> 編程語言 > >> C語言 >> VC >> 關於VC++

透過 ODBC 直接讀寫 Excel 檔案完整範例

編輯：關於VC++

前置條件

ODBC 驅動程式已安裝 Microsoft Excel Driver (*.xls)。

標頭檔

#include 
#include

建立並寫入 Excel

void WriteToExcel() {
    CDatabase db;
    CString drv = "MICROSOFT EXCEL DRIVER (*.XLS)";
    CString file = "c:\\demo.xls";
    CString sql;

    TRY {
        sql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
                   drv, file, file);
        if (db.OpenEx(sql, CDatabase::noOdbcDialog)) {
            db.ExecuteSQL("CREATE TABLE demo (Name TEXT, Age NUMBER)");
            db.ExecuteSQL("INSERT INTO demo (Name,Age) VALUES ('徐景周',26)");
            db.ExecuteSQL("INSERT INTO demo (Name,Age) VALUES ('徐志慧',22)");
            db.ExecuteSQL("INSERT INTO demo (Name,Age) VALUES ('郭徽',27)");
        }
        db.Close();
    } CATCH_ALL(e) {
        TRACE1("Excel驅動未安裝: %s", drv);
    } END_CATCH_ALL;
}

讀取 Excel

void ReadFromExcel() {
    CDatabase db; CString sql, name, age, drv, dsn, file="demo.xls";
    drv = GetExcelDriver(); if (drv.IsEmpty()) { AfxMessageBox("無Excel驅動"); return; }
    dsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", drv, file);

    TRY {
        db.Open(NULL, FALSE, FALSE, dsn);
        CRecordset rs(&db);
        sql = "SELECT Name, Age FROM demo ORDER BY Name";
        rs.Open(CRecordset::forwardOnly, sql, CRecordset::readOnly);
        while (!rs.IsEOF()) {
            rs.GetFieldValue("Name", name);
            rs.GetFieldValue("Age", age);
            rs.MoveNext();
        }
        db.Close();
    } CATCH(CDBException, e) {
        AfxMessageBox("資料庫錯誤: " + e->m_strError);
    } END_CATCH;
}

取得已安裝 Excel 驅動名稱

CString GetExcelDriver() {
    char buf[2001]; WORD out;
    if (!SQLGetInstalledDrivers(buf, 2000, &out)) return "";
    char* p = buf;
    do {
        if (strstr(p, "Excel")) return CString(p);
        p = strchr(p, '\0') + 1;
    } while (p[1] != '\0');
    return "";
}