C++ Excel操作(ADO方式)

头文件中主要定义：

    long            lFirstLBound;
    long            lFirstUBound;
    long            lSecondLBound;
    long            lSecondUBound;
    CWorkbook        book;
    CWorkbooks        books;
    CWorksheet        sheet;
    CWorksheets        sheets;
    CRange            range;
    CApplication    ExcelApp;
    CListCtrl        m_ListCtrl;

从Excel中读取的数据显示到CListCtrl上。

MFC布局如下图：

点击ListCtrl对应行列，显示其中的数据。

 

主要实现代码：

 1、"浏览..."Button响应函数，通过CFileDialog打开资源管理器，选择Excel文件:

void COpenExcelDlg::OnBnClickedButtonOpen()
{
    // TODO: 在此添加控件通知处理程序代码

    m_ButtonScanFile.EnableWindow(FALSE);
    m_ListCtrl.DeleteAllItems();        //删除所有行
    while(m_ListCtrl.DeleteColumn(0));

    m_StringRowCol = "行列号";
    m_StringData = "";


    //选择打开的Excel文件
    CFileDialog dlg(TRUE, NULL, NULL,
            OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
            _T("Worksheet Files (*.xls)|*.xlsx||"), this);

    if(dlg.DoModal() == IDOK)
    {
        m_StringPathName = dlg.GetPathName();
    }

    UpdateData(FALSE);
    MSG   msg; 
    while(PeekMessage(&msg, 0, 0, 0, PM_REMOVE)) 
    { 
        TranslateMessage(&msg); 
        DispatchMessage(&msg);
    }

    //创建Excel服务器(启动Excel)
    if(!ExcelApp.CreateDispatch(_T("Excel.Application"), NULL))
    {
        AfxMessageBox(_T("启动Excel服务器失败"));
        return;
    }
    ExcelApp.put_Visible(FALSE);
    ExcelApp.put_UserControl(FALSE);    

    //得到工作薄容器
    books.AttachDispatch(ExcelApp.get_Workbooks());

    try
    {
        //打开一个工作薄
        lpDisp = books.Open(m_StringPathName,
                vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
                vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
                vtMissing, vtMissing, vtMissing, vtMissing);
        book.AttachDispatch(lpDisp);
    }
    catch(...)
    {
        //增加一个新的工作薄
        lpDisp = books.Add(vtMissing);
        book.AttachDispatch(lpDisp);
    }


    //得到工作薄中的Sheet的容器
    sheets.AttachDispatch(book.get_Sheets());
    CString strSheetName = _T("NewSheet");
    //打开一个sheet，不存在则创建
    try
    {
        lpDisp = sheets.get_Item(_variant_t(strSheetName));
        sheet.AttachDispatch(lpDisp);
    }
    catch(...)
    {
        //创建新sheet
        lpDisp = sheets.Add(vtMissing, vtMissing, _variant_t((long)1), vtMissing);
        sheet.AttachDispatch(lpDisp);
        sheet.put_Name(strSheetName);
    }

    //向Excel中写入数据
    //WriteToExcel();
    m_ButtonScanFile.EnableWindow(TRUE);
    //从Excel中读出数据到ListCtrl
    ReadToCtrlList();
}

 

2、从Excel中读取数据到ListCtrl：

void COpenExcelDlg::ReadToCtrlList()
{
    CRange usedRange;
    long nRows, nCols;
    long iStartRow, iStartCol;

    usedRange.AttachDispatch(sheet.get_UsedRange());
    range.AttachDispatch(usedRange.get_Rows());
    nRows = range.get_Count();    //已经使用的行数

    range.AttachDispatch(usedRange.get_Columns());
    nCols = range.get_Count();    //已经使用的列数

    iStartRow = usedRange.get_Row();    //已经使用的区域起始行，从1开始
    iStartCol = usedRange.get_Column();    //已经使用的区域起始列，从1开始

    CString startName("");
    CString endName("");
    startName.Format(_T("%c%d"),iStartCol + 64, iStartRow);
    endName.Format(_T("%c%d"), nCols + 64, nRows);
    
    
    //读取Excel表中的多个单元格的值，在listCtrl中显示
    lpDisp = sheet.get_Range(_variant_t(startName), _variant_t(endName));
    range.AttachDispatch(lpDisp);
    


    VARIANT varRead = range.get_Value2();
    COleSafeArray olesaRead(varRead);

    VARIANT varItem;
    CString strItem;
    long index[2] = {0, 0};

    lFirstLBound = 0;
    lFirstUBound = 0;
    lSecondLBound = 0;
    lSecondUBound = 0;

    olesaRead.GetLBound(1, &lFirstLBound);
    olesaRead.GetUBound(1, &lFirstUBound);
    olesaRead.GetLBound(2, &lSecondLBound);
    olesaRead.GetUBound(2, &lSecondUBound);
    
    //插入列
    m_ListCtrl.InsertColumn(0, _T(""), 0, 100);

    for(long j = lSecondLBound; j <= lSecondUBound; j++)
    {
        CString strColName = _T("");
        strColName.Format(_T("%c"), j + 64);
        m_ListCtrl.InsertColumn(j, strColName, 0, 100);
    }

    for(long i = lFirstLBound; i <= lFirstUBound; i++)
    {
        CString strRowName = _T("");
        strRowName.Format(_T("%d"), i);
        m_ListCtrl.InsertItem(i - 1, strRowName);

        index[0] = i;
        for(long j = lSecondLBound; j <= lSecondUBound; j++)
        {
            index[1] = j;
            olesaRead.GetElement(index, &varItem);
            
            switch(varItem.vt)
            {
            case VT_R8:        //8字节的数字
                strItem.Format(_T("%d"),(int)varItem.dblVal);
                break;
            case VT_BSTR:        //字符串
                strItem = varItem.bstrVal;
                break;
            case VT_I4:
                strItem.Format(_T("%d"), (int)varItem.lVal);
                break;
            case VT_EMPTY:        //单元格为空
            case VT_NULL:
                strItem = "";
                break;
            default:
                break;
            }
            m_ListCtrl.SetItemText(i - 1, j, strItem);

        }
    }

    ReleaseSource();
}

 

3、释放资源：

void COpenExcelDlg::ReleaseSource()
{
    range.ReleaseDispatch();
    sheet.ReleaseDispatch();
    sheets.ReleaseDispatch();
    book.ReleaseDispatch();
    books.ReleaseDispatch();
    ExcelApp.Quit();
    ExcelApp.ReleaseDispatch();
}

 

4、ListCtrl点击时消息响应函数:

  (1: 可以在编辑界面时，选中ListCtrl，右键选中'添加事件处理程序'，找到NM_CLICK事件，添加到类中

  (2: 或手动在头文件中添加：

　　afx_msg void OnNMClickList1(NMHDR *pNMHDR, LRESULT *pResult);

　　cpp文件中：

BEGIN_MESSAGE_MAP(COpenExcelDlg, CDialogEx)
    ON_WM_SYSCOMMAND()
    ON_WM_PAINT()
    ON_WM_QUERYDRAGICON()
　　...
　　...
    ON_NOTIFY(NM_CLICK, IDC_LIST1, &COpenExcelDlg::OnNMClickList1)
END_MESSAGE_MAP()

   cpp实现函数：

void COpenExcelDlg::OnNMClickList1(NMHDR *pNMHDR, LRESULT *pResult)
{
    LPNMITEMACTIVATE pNMItemActivate = reinterpret_cast<LPNMITEMACTIVATE>(pNMHDR);
    // TODO: 在此添加控件通知处理程序代码

    NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR; 
    m_StringRowCol.Format(_T("%c%d"), pNMListView->iSubItem + 64, pNMListView->iItem + 1);

    LVITEM lvi;
    TCHAR buf[1024];
    lvi.iItem = pNMListView->iItem;
    lvi.iSubItem = pNMListView->iSubItem;
    lvi.mask = LVIF_TEXT; 
    lvi.pszText = buf; 
    lvi.cchTextMax = 1024; 
    m_ListCtrl.GetItem(&lvi); 

    m_StringData = buf;
    UpdateData(FALSE);

    MSG msg;
    while(PeekMessage(&msg, 0, 0, 0, PM_REMOVE))
    {
        TranslateMessage(&msg);
        DispatchMessage(&msg);
    }
    *pResult = 0;
}

 

整理代码即可实现读取Execl到CListCtrl中，并且点击ListCtrl行列，显示选中数据

写操作待续.....