[C++杂谈]：MFC中使用excel2007读写excel表格

环境：vs2015，excel2007，win7 64位

第一步：建立MFC工程，导入Application Range Workbook Worksheet Workbooks Worksheets

//最终要包含的excel服务的头文件#include "CApplication.h"  #include "CRange.h"  #include "CWorkbook.h"  #include "CWorksheet.h"  #include "CWorkbooks.h"  #include "CWorksheets.h"  

第二步：建立excel服务初始化、资源关闭、excel保存的基类

//ExcelFileOp.h#pragma once#include "CApplication.h"  #include "CRange.h"  #include "CWorkbook.h"  #include "CWorksheet.h"  #include "CWorkbooks.h"  #include "CWorksheets.h"  #include <string>  #include <utility>#include <vector>#include <map>using namespace std;// 进价、售价struct _StrSaleInfo{    CString strStockPrice;    CString strSalePrice;};// 货物信息typedef map<CString, _StrSaleInfo>  _st_cargo_info_;class CMyExcel{private:    //标记Excel对象的变量      static CApplication *m_papp;    // excel app 指针，多个使用，但只初始化一次    static int m_siInference;       // app 的引用计数，初始化一次+1，只有为0时才真正初始化，其他时候只做+1操作。析构自动-1，如果为0则释放m_papp    CWorkbooks m_books;             // books    long m_rowCount;    //标记Excel当前写入的列数      long m_sheetCount;  //标记Excel使用了多少标签页的变量      long m_totalRow;    //标记Excel总列数的变量      long m_totalCol;    //标记Excel总行数的变量      char *m_colPst;     //一个含有A-Z的数组      pair<long, long> m_cellPosition;    //cell的行和列protected:    COleVariant m_covTrue, m_covFalse, m_coverOptional, m_filePath;    enum _EN_VISIBLE_    {        eUN_VISIBLE = 0,    // 可见        eVISIBLE,   // 不可见    };    COleVariant covOptional;    // 初始化books、打开book的时候需要用到    CApplication * fnGetApp()    {        return m_papp;    }public:    CMyExcel();    virtual ~CMyExcel();};

// ExcelFileOp.cpp#include "stdafx.h"#include "ExcelFileOp.h"#include"stdafx.h"  // 初始化静态成员CApplication * CMyExcel::m_papp = NULL;int CMyExcel::m_siInference = 0;CMyExcel::CMyExcel() :m_rowCount(1), m_sheetCount(1), m_totalCol(256), m_totalRow(65536),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR){    // 获取系统的excel句柄      if (NULL == m_papp)    {        m_papp = new CApplication;        if (!m_papp->CreateDispatch(TEXT("Excel.Application")))        {            AfxMessageBox(_T("Could not start Excel and get Application object !"));            return;        }    }    m_siInference++;    m_covTrue = COleVariant((short)TRUE);    m_covFalse = COleVariant((short)FALSE);    m_coverOptional = COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR);    m_books = m_papp->get_Workbooks();    m_papp->put_Visible(FALSE);     // 初始化excel为不可见    m_papp->put_UserControl(FALSE); // 初始化控件为不可见}// 释放资源CMyExcel::~CMyExcel(){    if (0 == --m_siInference)    {        m_books.Close();        m_books.ReleaseDispatch();        m_papp->Quit();        m_papp->ReleaseDispatch();        delete m_papp;        m_papp = NULL;    }}

第三步：创建实际的读写excel的类，继承于CMyExcel

// ExcelBookReadWrite.h#pragma once#include "ExcelFileOp.h"class CExcelBookReadWrite :    public CMyExcel{public:    CExcelBookReadWrite(const CString &csFilePath,const bool bVisible = false);    ~CExcelBookReadWrite();private:    _EN_VISIBLE_ m_eVisible;    CWorkbooks m_books;    CWorkbook m_book;    CWorksheets m_sheets;    //标记excel中当前写入的标签页      CWorksheet m_sheet;    CRange m_range;     //标记写入的范围      CApplication *m_pAppInstance;public:    // 获取excel的行数    virtual long fnGetLineCount();    // 获取excel的列数    virtual long fnGetCloCount();    // 设置指定cell的值    virtual void fnSetCell(const pair<long, long> &pos, const CString &csNews);    // 获取指定cell的值    virtual CString fnGetCell(const pair<long, long> &pos);    // 获取当前cell的坐标（行、列）    virtual pair<long, long> fnGetCellPosition();    // 根据cell文字的长度，设置列的长度。如果pos为-1，-1则根据当前cell的内容来设置。设置长度时，先获取当前cell的长度，如果需要设置的长度较长则更新    virtual void fnSetLineLength(const pair<long, long> &pos = make_pair(-1, -1));    // 保存excel    virtual void fnSaveBook();};

// ExcelBookReadWrite.cpp#include "stdafx.h"#include "ExcelBookReadWrite.h"// 打开指定路径的excelCExcelBookReadWrite::CExcelBookReadWrite(const CString &csFilePath, const bool bVisible){    m_pAppInstance = fnGetApp();    if (NULL == m_pAppInstance)    {        return;    }    m_books = m_pAppInstance->get_Workbooks();    m_book = m_books.Open(csFilePath, covOptional, covOptional,        covOptional, covOptional, covOptional, covOptional,        covOptional, covOptional, covOptional, covOptional,        covOptional, covOptional, covOptional, covOptional);    m_sheets = m_book.get_Sheets();    m_sheet = m_sheets.get_Item(COleVariant((short)1));    m_range = m_sheet.get_UsedRange();    CRange cols = m_range.get_EntireColumn();    cols.AutoFit();    m_eVisible = eVISIBLE;    if (bVisible)    {        m_eVisible = eUN_VISIBLE;    }    m_pAppInstance->put_Visible(bVisible);    m_pAppInstance->put_Visible(TRUE);}// 关闭打开的bookCExcelBookReadWrite::~CExcelBookReadWrite(){    // 如果此处不save，则会造成退出时，有残留的 excel.exe 进程    m_book.put_Saved(TRUE);    //m_book.Close();    m_book.ReleaseDispatch();}// 获取excel当前sheet的行数（横向）long CExcelBookReadWrite::fnGetLineCount(){    //_Worksheet ws;    //Range range;    //range ＝ ws.GetUsedRange();//获得Worksheet已使用的范围    //range = range.GetRows();   //获得总行数（LPDISPATCH类型）    //long UsedRows = range.GetCount(); //即可获得已使用的行数了。    //获得列数也是一样的方法，把GetRows()改为GetColumns即可。    //CRange range =   m_sheet.get_UsedRange();    CRange rows = m_range.get_Rows();    return rows.get_Count();}// // 获取excel当前sheet的列数（纵向）long CExcelBookReadWrite::fnGetCloCount(){    //CRange range = m_sheet.get_UsedRange();    CRange cols = m_range.get_Columns();    return cols.get_Count();}void CExcelBookReadWrite::fnSetCell(const pair<long, long> &pos,CONST CString &csNews){    m_range.AttachDispatch(m_sheet.get_Cells(),TRUE);    m_range.put_Item(_variant_t(pos.first), _variant_t(pos.second), _variant_t(csNews));}CString  CExcelBookReadWrite::fnGetCell(const pair<long, long> &pos){    m_range.AttachDispatch(m_sheet.get_Cells(), TRUE);    return m_range.get_Item(_variant_t(pos.first), _variant_t(pos.second));}pair<long, long> CExcelBookReadWrite::fnGetCellPosition() {     m_range.AttachDispatch(m_sheet.get_Cells(), TRUE);    return make_pair(m_range.get_Column(), m_range.get_Row());}void CExcelBookReadWrite::fnSetLineLength(const pair<long, long> &pos){    CRange cell = m_range.get_Item(COleVariant(long(pos.first)), COleVariant(long(pos.second))).pdispVal;    CString cs = fnGetCell(pos);    double dwidth = cs.GetLength()*0.7;    // 将 _variant_t 转换为 long    _variant_t val = cell.get_ColumnWidth();    val.ChangeType(VT_R8);    double dCurrentWidth = val.dblVal;    // 如果目标长度比当前长度长，则改变长度    if (dwidth > dCurrentWidth)    {        cell.put_ColumnWidth(_variant_t(dwidth));    }}void CExcelBookReadWrite::fnSaveBook() {     //m_book.put_Saved(TRUE);   //退出excel.exe必须    // 设置自适应    CRange cols = m_range.get_EntireColumn();    cols.AutoFit();    m_book.Save();  // 保存book}

第四步：读写excel，略

完整代码可在链接中下载，一个简单的excel读写MFC应用。

代码下载地址