C++ Excel表格解析（一、MFC工程中的应用）

最新推荐文章于 2024-05-15 04:59:53 发布

LyRics1996

最新推荐文章于 2024-05-15 04:59:53 发布

阅读量1.9k

点赞数 1

文章标签： excel c++

本文链接：https://blog.csdn.net/LyRics1996/article/details/113617605

版权

C++ 同时被 3 个专栏收录

85 篇文章 43 订阅

订阅专栏

MFC

37 篇文章 3 订阅

订阅专栏

杂七杂八

32 篇文章 0 订阅

订阅专栏

一、前言

C++怎么解析Excel呢，这两天研究了一下，这一篇就来谈一谈MFC中如何解析Excel。

二、准备

2.1 代码结构

在这里插入图片描述

2.2 类说明

2.2.1 类型库

	图中圈出来的这些类，都是一些自带的类，两种方式，
	方式一：你直接复制其他工程的即可
	方式二：添加新项->添加类->选择这几个类即可。（VS2019不支持）（2.3.2有介绍）

在这里插入图片描述

2.2.2 主界面类

在这里插入图片描述

2.2.3 加载图片界面类

在这里插入图片描述

2.2.4 MFC生成的启动类

在这里插入图片描述

2.3 新建工程

2.3.1 新建项目（两个关键步骤）

选择MFC应用（建议选择VS2019以下的版本）
在这里插入图片描述
选择基于对话框

2.3.2 添加新项

主界面右键->类向导
在这里插入图片描述
选择图右的类型库中的MFC类

找到你安装excel的位置，添加之前说的那几个类（2.2.1中有提到）

添加完成后，找到相关头文件，注释/删除

  #import "D:\\software\\office2010\\Office14\\EXCEL.EXE"no_namespace

再次编译还是有错误

 定位错误信息，将DialogBox()改为_DialogBox()

三、代码

3.1 ExcelBase类

.h文件

#pragma once

#define ciColorRed 0X0000FF
#define ciColorGreen 0X00AA00
#define ciColorGreen2 0X00FF80
#define ciColorYellow 0X00FFFF
#define ciColorYellow2 0X5AFFFF
#define ciColorBlue 0XFFC080
#define ciColorGray 0X00BBFF

#include "CApplication.h"
#include "CFont0.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CInterior.h"
#include "CShapes.h"
#include <vector>
using namespace std;

static	COleVariant	       COV_DEFAULT_OPT = COleVariant((LONG)DISP_E_PARAMNOTFOUND, VT_ERROR);		// 默认指针	// 不能加const
class CExcelBase
{
public:
    CExcelBase(void);
    ~CExcelBase(void);
    bool Initialize();
    BOOL OpenExcel(LPCTSTR strFilePath);
    void CloseExcel(BOOL bSaveFile = TRUE);
    void Finalize();

	// 获取sheet数
	BOOL GetSheets(int& num, vector<CString>& name);
	// 获取sheet名称
	CString getSheetName(long tableID);
	// 获取已用行数
	LONG GetRows();
	// 获取已用列数
	LONG GetCols();

    // 设置当前的excel
    void SetCurSheet(int num);

    BOOL BuildDirectory(CString strPath);
    void SetFilePath(CString strPathName_In);
    void SetFilePath(CString strPath_In, CString strFileName_In);

    bool SaveExcel();

    void ShowInExcel(BOOL bShow);
    // 获取单元格中的值
    CString GetCellValue(LONG lRow, LONG lCol);
    // 设置单元格中的值
    BOOL    SetCellValue(LONG lRow, LONG lCol, LPCTSTR pstrText);



    //  新建图表
    BOOL NewChart(int nLeft, int nTop, int nWidth, int nHeight);

    // 新建一个空系列
    BOOL  NewSeries();


    BOOL SetChartType(LONG nChartType);

    BOOL SetActiveChart(LONG lChartItem);

    // 设置图表标题
    BOOL  SetChartTitle(LPCTSTR pszChartTitle);	
    
    // 设置图表数据
    BOOL  SetXYAxis(LPCTSTR pszXBegin, LPCTSTR pszXEnd,  
        LPCTSTR pszYBegin,     LPCTSTR pszYEnd,   
        LPCTSTR pszXName,      LPCTSTR pszYName, 
        LPCTSTR pszSeriesName, LONG lSeriesData);
  

    CString GetCellName(int nCol);  // 获取列头名
    BOOL GetCellName(int nRow, int nCol, CString &strName); // 获取行号列号，如C4
    bool SetCellColor(int nRow, int nCol, UINT nColor);
    void GetSysTimeByMs(DWORD dwTime_In, SYSTEMTIME *pSysTime_Out);
    void SetRowColor(int nRow, int nLastCol, UINT nColor);
protected:
    CString m_strFilePathName_Me;
    CApplication m_appExcel;
    CWorkbook m_xlsBook;
    CWorkbooks m_xlsBooks;
    CWorksheet m_xlsSheet;
    CWorksheets m_xlsSheets;

public:
	CString         GetStrFilePathName_Me();
	CApplication    GetAppExcel();
	CWorkbook       GetXlsBook();
	CWorkbooks      GetXlsBooks();
	CWorksheet      GetXlsSheet();
	CWorksheets     GetXlsSheets();

};

.cpp文件

#include "../pch.h"
#include "ExcelBase.h"

CExcelBase::CExcelBase(void)
{
    m_appExcel        = NULL;
    m_xlsSheet      = NULL;
    m_xlsSheets     = NULL;
    m_xlsBook       = NULL;
    m_xlsBooks      = NULL;
}


CExcelBase::~CExcelBase(void)
{
    Finalize();
	//CoUninitialize();

}

bool CExcelBase::Initialize()
{
    if(SUCCEEDED(CoInitialize(NULL))) 
    {
        COleVariant
            covTrue((short)TRUE),
            covFalse((short)FALSE),
            covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
        if (m_appExcel.CreateDispatch(_T("Excel.Application")))
        {
            m_appExcel.put_DisplayAlerts(FALSE);           // 隐藏警告框  
            m_appExcel.put_AlertBeforeOverwriting(FALSE);  

            m_xlsBooks = m_appExcel.get_Workbooks();
            m_xlsBook = m_xlsBooks.Add(covOptional);
            m_xlsSheets = m_xlsBook.get_Worksheets();
            m_xlsSheet  = m_xlsSheets.get_Item(COleVariant((short)1));

            return true;
        }
    }
    return false;
}


BOOL CExcelBase::OpenExcel(LPCTSTR pszFilename)
{
    if (NULL == m_appExcel)
    {
        return FALSE;
    }
    CFileStatus status;

    if ( CFile::GetStatus(pszFilename, status) ) // 如果文件存在
    {
        // Workbooks的Open函数帮助的最下方有AutomationSecurity，可禁用所有警告
        m_xlsBooks.AttachDispatch(m_appExcel.get_Workbooks());
        m_xlsBook = m_xlsBooks.Open(pszFilename,  
            COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT,  
            COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT,  
            COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT,  
            COV_DEFAULT_OPT, COV_DEFAULT_OPT);
        m_xlsSheets = m_xlsBook.get_Worksheets();
        m_xlsSheet  = m_xlsSheets.get_Item(COleVariant((short)1));
        return TRUE;
    }

    return FALSE;
}

void CExcelBase::CloseExcel(BOOL bSaveFile)
{   
    if (bSaveFile)
    {
        m_xlsBook.Save();
        m_xlsBook.put_Saved(TRUE);
    }

    m_strFilePathName_Me.Empty();   

    m_xlsBook.Close(COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT); // 关闭当前处于活动状态的工作簿
    m_xlsBooks.Close(); // 关闭所有打开的工作簿    

    m_xlsSheet.ReleaseDispatch();
    m_xlsSheets.ReleaseDispatch();
    m_xlsBook.ReleaseDispatch();  
    m_xlsBooks.ReleaseDispatch(); 

    m_xlsSheet      = NULL;
    m_xlsSheets     = NULL;
    m_xlsBook       = NULL;
    m_xlsBooks      = NULL;
    
}

void CExcelBase::Finalize()
{
    if(NULL ==  m_appExcel)
    {
        return ;
    }

    // 要按顺序释放才能使进程结束。
    m_xlsBook.Save();
    m_xlsBook.put_Saved(TRUE);

    m_xlsBook.Close(COV_DEFAULT_OPT, COV_DEFAULT_OPT, COV_DEFAULT_OPT); // 关闭当前处于活动状态的工作簿
    m_xlsBooks.Close(); // 关闭所有打开的工作簿
    m_appExcel.Quit();    // 关闭excel应用

    m_xlsSheet.ReleaseDispatch();
    m_xlsSheets.ReleaseDispatch();
    m_xlsBook.ReleaseDispatch();
    m_xlsBooks.ReleaseDispatch();
    m_appExcel.ReleaseDispatch();
    m_xlsSheet         = NULL;
    m_xlsSheets        = NULL;
    m_xlsBook          = NULL;
    m_xlsBooks         = NULL;
    m_appExcel      = NULL;

    CoUninitialize();

    CloseExcel(TRUE);

    m_appExcel.Quit();
    m_appExcel.ReleaseDispatch();
    m_appExcel = NULL;
}

void CExcelBase::SetCurSheet(int num)
{
    if (num < 1) {
        return;
    }
    m_xlsSheet = m_xlsSheets.get_Item(COleVariant((short)num));
}

BOOL CExcelBase::BuildDirectory(CString strPath)
{
    // TODO: 在此处添加实现代码.
    CString strSubPath;
    CString strInfo;
    int nCount = 0;
    int nIndex = 0;

    //查找字符"\\"的个数
    do
    {
        nIndex = strPath.Find(_T("\\"), nIndex) + 1;
        nCount++;
    } while ((nIndex - 1) != -1);
    nIndex = 0;
    //检查，并创建目录
    while ((nCount - 1) >= 0)
    {
        nIndex = strPath.Find(_T("\\"), nIndex) + 1;
        if ((nIndex - 1) == -1)
        {
            strSubPath = strPath;
        }
        else
            strSubPath = strPath.Left(nIndex);
        if (!PathFileExists(strSubPath))
        {
            if (!::CreateDirectory(strSubPath, NULL))
            {
                return FALSE;
            }
        }
        nCount--;
    };
    return TRUE;
}

void CExcelBase::SetFilePath(CString strPathName_In)
{
    CFile file;
    CFileException e;

    m_strFilePathName_Me = strPathName_In;

    if(!file.Open(strPathName_In, CFile::modeRead, &e))
    {
        if (e.m_lOsError == CFileException::badPath)
        {
            CString strPath;
            int     nFind = strPathName_In.ReverseFind('\\');
            
            if (-1 != nFind)
            {
                strPath = m_strFilePathName_Me.Left(nFind + 1);
                if(!CreateDirectory(strPath, NULL))
                {
                    int nErr = GetLastError();

                }
            }

            BuildDirectory(strPath);
            
        }
        if(!file.Open(strPathName_In, CFile::modeCreate|CFile::shareDenyNone, &e))
        {
#ifdef _DEBUG
            e.ReportError();
#endif // _DEBUG
        }
        else
        {
            file.Close();
        }

    }

}

void CExcelBase::SetFilePath(CString strPath_In, CString strFileName_In)
{
    CString strTemp;
    if (strPath_In.Right(1) != _T("\\"))
    {
        strTemp = strPath_In + _T("\\");
    }

    SetFilePath(strTemp + strFileName_In);
}

CString CExcelBase::GetCellName(int nCol)
{
    CString strName;
    CString strTemp;
    if (nCol > 26)
    {
        if (nCol%26 == 0)
        {
            strName = GetCellName(nCol/26 - 1);
        }
        else
        {
            strName = GetCellName(nCol/26);
        }
        strTemp = strName;
        strName = GetCellName(nCol%26);
        strName = strTemp + strName;
    }
    else
    {
        char cCell;
        if (nCol == 0)
        {
            cCell = 'Z';
        }
        else
        {
            cCell = 'A' + nCol - 1;
        }     
        strName.Format(_T("%c"), cCell);
    }

    return strName;
}

BOOL CExcelBase::SetCellValue(LONG lRow, LONG lCol, LPCTSTR pstrText)
{
    CString strCellName;
    CRange range;

    if(GetCellName(lRow, lCol, strCellName))
    {
        range = m_xlsSheet.get_Range(COleVariant(strCellName), COleVariant(strCellName));
        range.put_Value2(COleVariant(pstrText));
        return TRUE;
    }
    return FALSE;
}

BOOL CExcelBase::GetSheets(int& num, vector<CString>& name)
{
    num = m_xlsSheets.get_Count();
    CString nameTemp = _T("");
    for (int i = 1; i < num+1; i++) {
        nameTemp = getSheetName(i);
        if (!nameTemp.IsEmpty()) {
            name.push_back(nameTemp);
        }
    }
    return true;
}


CString CExcelBase::getSheetName(long tableID)
{
	CWorksheet sheet;
	sheet.AttachDispatch(m_xlsSheets.get_Item(COleVariant((long)tableID)));
	CString name = sheet.get_Name();
	sheet.ReleaseDispatch();
	return name;
}


CString CExcelBase::GetCellValue(LONG lRow, LONG lCol)
{
    CString strReturn;

    if(lRow <= 0 || lCol <= 0)
    {
        return strReturn;
    }
    
    COleVariant vResult;
    CRange range = m_xlsSheet.get_Cells();     // 获取所有表格
    
    range.AttachDispatch(range.get_Item( COleVariant(LONG(lRow)),COleVariant(LONG(lCol)) ).pdispVal, TRUE);
    vResult =range.get_Value2();
    //VARIANT var = range.get_Item(COleVariant(LONG(lRow)),COleVariant(LONG(lCol))).pdispVal;
    //(LPCSTR)_bstr_t(var);
    range.ReleaseDispatch();

    if(vResult.vt == VT_BSTR)
    {
        strReturn=vResult.bstrVal;
    }
    //整数
    else if (vResult.vt==VT_INT)
    {
        strReturn.Format(_T("%d"),vResult.pintVal);
    }
    //8字节的数字 
    else if (vResult.vt==VT_R8)     
    {
        strReturn.Format(_T("%0.4lf"),vResult.dblVal);
    }
    //时间格式
    else if(vResult.vt==VT_DATE)    
    {
        SYSTEMTIME st;
        VariantTimeToSystemTime(vResult.date, &st);
        CTime tm(st); 
        strReturn=tm.Format("%Y-%m-%d");
    }
    //单元格为空
    else if (vResult.vt == VT_EMPTY)
    {
    }
    return  strReturn;
}

LONG CExcelBase::GetRows()
{
    CRange range = m_xlsSheet.get_UsedRange(); // 获取已用表格范围

    range = range.get_Rows();  // 获取已用行数范围
    return range.get_Count(); // 获取已用行数
}

LONG CExcelBase::GetCols()
{
    CRange range = m_xlsSheet.get_UsedRange(); // 获取已用表格范围

    range  = range.get_Columns();           // 获取已用行数范围
    
    return range.get_Count();             // 获取已用行数
}
//获取行号列号的单元格名称，例如第一行第一列为"A1",即strName赋值为“A1”
BOOL CExcelBase::GetCellName(int nRow, int nCol, CString &strName)
{
    if (nRow < 1 || nCol < 1)
    {
        strName = _T("");
        return FALSE;
    }

    CString strRow;
    strName = GetCellName(nCol);
    strRow.Format(_T( "%d "), nRow);
    strName += strRow;

    return TRUE;
}

bool CExcelBase::SetCellColor(int nRow, int nCol, UINT nColor)
{
    //设置单元格颜色
    const int BIG_CHAR_LENGHT = 256;
    CString strCell;
    GetCellName(nRow, nCol, strCell);
	int nStrLen = strCell.GetLength();
    char szContent[BIG_CHAR_LENGHT] = {0};
#ifdef _UNICODE
    USES_CONVERSION;
    memcpy(szContent, W2A(strCell.GetBuffer(strCell.GetLength())), min(nStrLen,BIG_CHAR_LENGHT));
#else
    memcpy(szContent, (strCell.GetBuffer(strCell.GetLength())), min(nStrLen,BIG_CHAR_LENGHT));
#endif
    strCell.ReleaseBuffer();

    CRange range = m_xlsSheet.get_Range(_variant_t(szContent), _variant_t((szContent)));
    CInterior interior = range.get_Interior();
    interior.put_Color(COleVariant(long(nColor)));
 
    return true;
}

void CExcelBase::SetRowColor(int nRow, int nLastCol, UINT nColor)
{
    const int BIG_CHAR_LENGHT = 256;
    CString strFristCol;
    GetCellName(nRow, 1, strFristCol);
    CString strLastCol;
    GetCellName(nRow, nLastCol, strLastCol);
    char szContent1[BIG_CHAR_LENGHT] = {0};
    char szContent2[BIG_CHAR_LENGHT] = {0};
	int nFristStrLen = strFristCol.GetLength();
	int nLastStrLen = strLastCol.GetLength();
#ifdef _UNICODE
    USES_CONVERSION;
    memcpy(szContent1, W2A(strFristCol.GetBuffer(strFristCol.GetLength())), min(nFristStrLen,BIG_CHAR_LENGHT));
    memcpy(szContent2, W2A(strLastCol.GetBuffer(strLastCol.GetLength())), min(nLastStrLen,BIG_CHAR_LENGHT));
#else
    memcpy(szContent1, (strFristCol.GetBuffer(strFristCol.GetLength())), min(nFristStrLen,BIG_CHAR_LENGHT));
    memcpy(szContent2, (strLastCol.GetBuffer(strLastCol.GetLength())), min(nLastStrLen,BIG_CHAR_LENGHT));
#endif
    strFristCol.ReleaseBuffer();
    strLastCol.ReleaseBuffer();

    CRange range = m_xlsSheet.get_Range(_variant_t(szContent1), _variant_t((szContent2)));
    CInterior interior = range.get_Interior();
    interior.put_Color(COleVariant(long(nColor)));
}

CString CExcelBase::GetStrFilePathName_Me()
{
    return m_strFilePathName_Me;
}

CApplication CExcelBase::GetAppExcel()
{
    return m_appExcel;
}

CWorkbook CExcelBase::GetXlsBook()
{
    return m_xlsBook;
}

CWorkbooks CExcelBase::GetXlsBooks()
{
    return m_xlsBooks;
}

CWorksheet CExcelBase::GetXlsSheet()
{
    return m_xlsSheet;
}

CWorksheets CExcelBase::GetXlsSheets()
{
    return m_xlsSheets;
}

bool CExcelBase::SaveExcel()
{

    m_xlsBook.SaveCopyAs(COleVariant(m_strFilePathName_Me));
    m_xlsBook.put_Saved(TRUE);
    return true;
}

void CExcelBase::GetSysTimeByMs(DWORD dwTime_In, SYSTEMTIME *pSysTime_Out)
{
    if (!pSysTime_Out)
    {
        return;
    }
    DWORD dwTemp = 0;
    SYSTEMTIME sysTime;
    sysTime.wMilliseconds = dwTime_In%1000;
    dwTemp = (dwTime_In - sysTime.wMilliseconds)/1000;

    sysTime.wSecond = dwTemp%60;
    dwTemp = (dwTemp - sysTime.wSecond)/60;

    sysTime.wMinute = dwTemp%60;
    dwTemp = (dwTemp - sysTime.wMinute)/60;

    sysTime.wHour = (WORD)dwTemp;

    *pSysTime_Out = sysTime;
}


void CExcelBase::ShowInExcel(BOOL bShow)
{
    m_appExcel.put_Visible(bShow);
    m_appExcel.put_UserControl(bShow);
}

BOOL CExcelBase::NewChart(int nLeft, int nTop, int nWidth, int nHeight)
{
    if (NULL == m_appExcel)
    {
        return FALSE;
    }
    return TRUE;
}

BOOL  CExcelBase::NewSeries()
{
    if (NULL == m_appExcel)
    {
        return FALSE;
    }

    return TRUE;
} 

BOOL  CExcelBase::SetChartTitle(LPCTSTR pszChartTitle)
{
    if(NULL == m_appExcel && pszChartTitle)
    {
        return FALSE;
    }
    return TRUE;
}

BOOL  CExcelBase::SetChartType(LONG nChartType)
{
    return TRUE;
}

BOOL  CExcelBase::SetXYAxis(LPCTSTR pszXBegin, LPCTSTR pszXEnd,  
    LPCTSTR pszYBegin,     LPCTSTR pszYEnd,   
    LPCTSTR pszXName,      LPCTSTR pszYName, 
    LPCTSTR pszSeriesName, LONG lSeriesData)
{
    if(NULL == m_appExcel)
    {
        return FALSE;
    }

    return TRUE;
}

BOOL  CExcelBase::SetActiveChart(LONG lChartItem)
{
    if(lChartItem <= 0 || NULL == m_appExcel)
    {
        return FALSE;
    }

    return TRUE;
}

3.2 CExcelDemoDlg

.cpp文件


// ExcelDemoDlg.cpp: 实现文件
//

#include "pch.h"
#include "framework.h"
#include "ExcelDemo.h"
#include "ExcelDemoDlg.h"
#include "afxdialogex.h"
#include "CDlgInsertImage.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// 用于应用程序“关于”菜单项的 CAboutDlg 对话框

class CAboutDlg : public CDialogEx
{
public:
	CAboutDlg();

// 对话框数据
#ifdef AFX_DESIGN_TIME
	enum { IDD = IDD_ABOUTBOX };
#endif

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV 支持

// 实现
protected:
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialogEx(IDD_ABOUTBOX)
{
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialogEx::DoDataExchange(pDX);
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialogEx)
END_MESSAGE_MAP()


// CExcelDemoDlg 对话框



CExcelDemoDlg::CExcelDemoDlg(CWnd* pParent /*=nullptr*/)
	: CDialogEx(IDD_EXCELDEMO_DIALOG, pParent)
{
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CExcelDemoDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialogEx::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_COMBO_SHEET, m_CombxSheet);
}

BEGIN_MESSAGE_MAP(CExcelDemoDlg, CDialogEx)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BTN_BROWSE, &CExcelDemoDlg::OnBnClickedBtnBrowse)
	ON_BN_CLICKED(IDC_BTN_LOAD, &CExcelDemoDlg::OnBnClickedBtnLoad)
	ON_CBN_SELCHANGE(IDC_COMBO_SHEET, &CExcelDemoDlg::OnCbnSelchangeComboSheet)
	ON_BN_CLICKED(IDC_BTN_READ, &CExcelDemoDlg::OnBnClickedBtnRead)
	ON_BN_CLICKED(IDC_BTN_WRITE, &CExcelDemoDlg::OnBnClickedBtnWrite)
	ON_BN_CLICKED(IDC_BTN_SAVE, &CExcelDemoDlg::OnBnClickedBtnSave)
	ON_BN_CLICKED(IDC_BTN_SAVE_OTHER, &CExcelDemoDlg::OnBnClickedBtnSaveOther)
	ON_BN_CLICKED(IDC_BTN_INSERT_IMAGE, &CExcelDemoDlg::OnBnClickedBtnInsertImage)
END_MESSAGE_MAP()


// CExcelDemoDlg 消息处理程序

BOOL CExcelDemoDlg::OnInitDialog()
{
	CDialogEx::OnInitDialog();

	// 将“关于...”菜单项添加到系统菜单中。

	// IDM_ABOUTBOX 必须在系统命令范围内。
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != nullptr)
	{
		BOOL bNameValid;
		CString strAboutMenu;
		bNameValid = strAboutMenu.LoadString(IDS_ABOUTBOX);
		ASSERT(bNameValid);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// 设置此对话框的图标。  当应用程序主窗口不是对话框时，框架将自动
	//  执行此操作
	SetIcon(m_hIcon, TRUE);			// 设置大图标
	SetIcon(m_hIcon, FALSE);		// 设置小图标

	// TODO: 在此添加额外的初始化代码
	m_FilePath = "";
	if (!m_Excel.Initialize()) {
		AfxMessageBox(_T("初始化excel失败！"));
	}
	return TRUE;  // 除非将焦点设置到控件，否则返回 TRUE
}

void CExcelDemoDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialogEx::OnSysCommand(nID, lParam);
	}
}

// 如果向对话框添加最小化按钮，则需要下面的代码
//  来绘制该图标。  对于使用文档/视图模型的 MFC 应用程序，
//  这将由框架自动完成。

void CExcelDemoDlg::OnPaint()
{
	if (IsIconic())
	{
		CPaintDC dc(this); // 用于绘制的设备上下文

		SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0);

		// 使图标在工作区矩形中居中
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// 绘制图标
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialogEx::OnPaint();
	}
}

//当用户拖动最小化窗口时系统调用此函数取得光标
//显示。
HCURSOR CExcelDemoDlg::OnQueryDragIcon()
{
	return static_cast<HCURSOR>(m_hIcon);
}

bool CExcelDemoDlg::BrowseFile(CString &path) {
	CString strPath;
	CString strFilter = _T("Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*||");
	CString strFilePath;
	CFileDialog hFileDlg(FALSE, NULL, _T(""), OFN_FILEMUSTEXIST | OFN_PATHMUSTEXIST,
		strFilter, NULL);
	hFileDlg.m_ofn.nFilterIndex = 1;
	hFileDlg.m_ofn.hwndOwner = m_hWnd;
	hFileDlg.m_ofn.lStructSize = sizeof(OPENFILENAME);
	hFileDlg.m_ofn.lpstrTitle = _T("AOI配置");//TEXT("打开图像文件...\0");
	hFileDlg.m_ofn.nMaxFile = MAX_PATH;
	if (hFileDlg.DoModal() == IDOK)
	{
		strFilePath = hFileDlg.GetPathName();
		strFilePath.Left(strFilePath.GetLength() - 4) + _T(".xls");

		if (strFilePath.Right(4) != _T(".xls"))
		{
			strFilePath += _T(".xls");
		}

		CString strMsg;
		CFile oFile;

		if (!oFile.Open(strFilePath, CFile::modeCreate | CFile::modeNoTruncate))
		{
			//strMsg = _T("文件创建失败!");
			//MessageBox(strMsg);
			return false;
		}
		else
		{
			oFile.Close();
			//strMsg = _T("文件创建成功!");
			//MessageBox(strMsg);
			path = strFilePath;
			return true;
		}
	}
	return false;
}

void CExcelDemoDlg::OnBnClickedBtnBrowse()
{
	// TODO: 在此添加控件通知处理程序代码
	//CString m_FilePath
	if (!BrowseFile(m_FilePath)) {
		AfxMessageBox(_T("浏览文件失败"));
		return;
	}
	GetDlgItem(IDC_EDIT_FILE_PATH)->SetWindowTextW(m_FilePath);
}


void CExcelDemoDlg::OnBnClickedBtnLoad()
{
	// TODO: 在此添加控件通知处理程序代码
	if (m_FilePath.IsEmpty()) {
		AfxMessageBox(_T("请先浏览excel文件"));
		return;
	}

	if (!m_Excel.OpenExcel(m_FilePath)) {
		AfxMessageBox(_T("加载excel文件失败"));
		return;
	}
	vector<CString> name;
	int num;
	if (!m_Excel.GetSheets(num, name)) {
		AfxMessageBox(_T("获取excel内容失败"));
		return;
	}
	m_CombxSheet.Clear();
	for (auto s : name) {
		m_CombxSheet.AddString(s);
	}
	m_CombxSheet.SetCurSel(0);

	SetDlgItemInt(IDC_EDIT_ROW_COUNT, m_Excel.GetRows());
	SetDlgItemInt(IDC_EDIT_COL_COUNT, m_Excel.GetCols());
}


void CExcelDemoDlg::OnCbnSelchangeComboSheet()
{
	int item = m_CombxSheet.GetCurSel();
	m_Excel.SetCurSheet(item+1);
	SetDlgItemInt(IDC_EDIT_ROW_COUNT, m_Excel.GetRows());
	SetDlgItemInt(IDC_EDIT_COL_COUNT, m_Excel.GetCols());
}


void CExcelDemoDlg::OnBnClickedBtnRead()
{
	// TODO: 在此添加控件通知处理程序代码
	int cul = GetDlgItemInt(IDC_EDIT_CUL_SEL);
	int row = GetDlgItemInt(IDC_EDIT_ROW_SEL);

	if (cul > m_Excel.GetCols() || cul < 0) {
		AfxMessageBox(_T("数字超出范围"));
		return;
	}
	if (row > m_Excel.GetRows() || row < 0) {
		AfxMessageBox(_T("数字超出范围"));
		return;
	}
	CString s = m_Excel.GetCellValue(row, cul);
	SetDlgItemText(IDC_EDIT_READ_WRITE, s);
}


void CExcelDemoDlg::OnBnClickedBtnWrite()
{
	// TODO: 在此添加控件通知处理程序代码
	int cul = GetDlgItemInt(IDC_EDIT_CUL_SEL);
	int row = GetDlgItemInt(IDC_EDIT_ROW_SEL);

	if (row < 0 || cul < 0) {
		AfxMessageBox(_T("数字超出范围"));
		return;
	}
	CString text;
	GetDlgItemTextW(IDC_EDIT_READ_WRITE,text);
	if (text.IsEmpty()) {
		int ret = AfxMessageBox(_T("内容为空，是否确定写入"), MB_OKCANCEL);
		if (ret == IDCANCEL) {
			return;
		}
	}
	int ret = AfxMessageBox(_T("是否确定写入 ")+text, MB_OKCANCEL);
	if (ret == IDCANCEL) {
		return;
	}
	ret = m_Excel.SetCellValue(row, cul, text);
	if(ret&&m_Excel.GetCellValue(row, cul).CompareNoCase(text) == 0){
		AfxMessageBox(_T("写入成功"));
	}
}


void CExcelDemoDlg::OnBnClickedBtnSave()
{
	int ret = AfxMessageBox(_T("是否确定保存更改"), MB_OKCANCEL);
	if (ret == IDCANCEL) {
		return;
	}
	if (m_Excel.SaveExcel()) {
		AfxMessageBox(_T("保存成功"));
	}
}


void CExcelDemoDlg::OnBnClickedBtnSaveOther()
{
	CString path;
	BrowseFile(path);
	if (path.IsEmpty()) {
		return;
	}
	int ret = AfxMessageBox(_T("是否确定令存为更改"), MB_OKCANCEL);
	if (ret == IDCANCEL) {
		return;
	}

	m_Excel.SetFilePath(path);
	if (m_Excel.SaveExcel()) {
		AfxMessageBox(_T("保存成功"));
	}
}


void CExcelDemoDlg::OnBnClickedBtnInsertImage()
{
	// TODO: 在此添加控件通知处理程序代码
	CDlgInsertImage image;
	image.SetExcel(&m_Excel);
	image.DoModal();
}

.h文件


// ExcelDemoDlg.h: 头文件
//

#pragma once
#include "include/ExcelBase.h"

// CExcelDemoDlg 对话框
class CExcelDemoDlg : public CDialogEx
{
// 构造
public:
	CExcelDemoDlg(CWnd* pParent = nullptr);	// 标准构造函数

// 对话框数据
#ifdef AFX_DESIGN_TIME
	enum { IDD = IDD_EXCELDEMO_DIALOG };
#endif

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);	// DDX/DDV 支持

private:
	CString m_FilePath;
	CExcelBase	m_Excel;
	bool BrowseFile(CString& path);
// 实现
protected:
	HICON m_hIcon;

	// 生成的消息映射函数
	virtual BOOL OnInitDialog();
	afx_msg void OnSysCommand(UINT nID, LPARAM lParam);
	afx_msg void OnPaint();
	afx_msg HCURSOR OnQueryDragIcon();
	DECLARE_MESSAGE_MAP()
public:
	CComboBox m_CombxSheet;

	afx_msg void OnBnClickedBtnBrowse();
	afx_msg void OnBnClickedBtnLoad();
	afx_msg void OnCbnSelchangeComboSheet();
	afx_msg void OnBnClickedBtnRead();
	afx_msg void OnBnClickedBtnWrite();
	afx_msg void OnBnClickedBtnSave();
	afx_msg void OnBnClickedBtnSaveOther();
	afx_msg void OnBnClickedBtnInsertImage();
};

3.3 CDlgInsertImage

cpp文件

// CDlgInsertImage.cpp: 实现文件
//

#include "pch.h"
#include "ExcelDemo.h"
#include "CDlgInsertImage.h"
#include "afxdialogex.h"


// CDlgInsertImage 对话框

IMPLEMENT_DYNAMIC(CDlgInsertImage, CDialogEx)

CDlgInsertImage::CDlgInsertImage(CWnd* pParent /*=nullptr*/)
	: CDialogEx(IDD_IMAGE_INSERT, pParent)
{
	m_Excel = NULL;
}



CDlgInsertImage::~CDlgInsertImage()
{
}

void CDlgInsertImage::SetExcel(CExcelBase *excel)
{
	m_Excel = excel;
	
}

bool CDlgInsertImage::BrowseFile(CString& path)
{
	CString strPath;
	CString strFilter = _T("图片文件(*.jpg)|*.jpg|所有文件(*.*)|*.*||");
	CString strFilePath;
	CFileDialog hFileDlg(FALSE, NULL, _T(""), OFN_FILEMUSTEXIST | OFN_PATHMUSTEXIST,
		strFilter, NULL);
	hFileDlg.m_ofn.nFilterIndex = 1;
	hFileDlg.m_ofn.hwndOwner = m_hWnd;
	hFileDlg.m_ofn.lStructSize = sizeof(OPENFILENAME);
	hFileDlg.m_ofn.lpstrTitle = _T("图片配置");//TEXT("打开图像文件...\0");
	hFileDlg.m_ofn.nMaxFile = MAX_PATH;
	if (hFileDlg.DoModal() == IDOK)
	{
		strFilePath = hFileDlg.GetPathName();
		strFilePath.Left(strFilePath.GetLength() - 4) + _T(".jpg");

		if (strFilePath.Right(4) != _T(".jpg"))
		{
			strFilePath += _T(".jpg");
		}

		CString strMsg;
		CFile oFile;

		if (!oFile.Open(strFilePath, CFile::modeCreate | CFile::modeNoTruncate))
		{
			//strMsg = _T("文件创建失败!");
			//MessageBox(strMsg);
			return false;
		}
		else
		{
			oFile.Close();
			//strMsg = _T("文件创建成功!");
			//MessageBox(strMsg);
			path = strFilePath;
			return true;
		}
	}
	return false;
}

void CDlgInsertImage::DoDataExchange(CDataExchange* pDX)
{
	CDialogEx::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_COMBO_SHEET_IMAGE, m_ComboSheet);
	DDX_Control(pDX, IDC_EDIT_ROW_SEL_IMAGE, m_EditRow);
}


BEGIN_MESSAGE_MAP(CDlgInsertImage, CDialogEx)
	ON_BN_CLICKED(IDC_BTN_BROWSE_IMAGE, &CDlgInsertImage::OnBnClickedBtnBrowseImage)
	//ON_CBN_SELCHANGE(IDC_COMBO_SHEET_IMAGE, &CDlgInsertImage::OnCbnSelchangeComboSheetImage)
	ON_BN_CLICKED(IDC_BTN_INSERT_IMAGE, &CDlgInsertImage::OnBnClickedBtnInsertImage)
END_MESSAGE_MAP()


// CDlgInsertImage 消息处理程序


void CDlgInsertImage::OnBnClickedBtnBrowseImage()
{
	if (!BrowseFile(m_ImageFilePath)) {
		AfxMessageBox(_T("用户已取消！"));
		return;
	}
}


void CDlgInsertImage::OnCbnSelchangeComboSheetImage()
{
	if (!m_Excel) {
		return;
	}
	int item = m_ComboSheet.GetCurSel();
	m_Excel->SetCurSheet(item + 1);
	SetDlgItemInt(IDC_EDIT_ROW_COUNT_IMAGE, m_Excel->GetRows());
	SetDlgItemInt(IDC_EDIT_COL_COUNT_IMAGE, m_Excel->GetCols());
}


void CDlgInsertImage::OnBnClickedBtnInsertImage()
{
	if (!m_Excel) {
		AfxMessageBox(_T("excel 句柄未实例化！"));
		return;
	}
	if (m_ImageFilePath.IsEmpty()) {
		AfxMessageBox(_T("请先选择图片！"));
		return;
	}
	CString cell1, cell2;
	GetDlgItemText(IDC_EDIT_ROW_SEL_IMAGE, cell1);
	GetDlgItemText(IDC_EDIT_CUL_SEL_IMAGE, cell2);
	if (cell1.IsEmpty()||cell2.IsEmpty()) {
		AfxMessageBox(_T("请正确输入图片插入范围，例如单元格1：A1，单元格2：A1"));
		return;
	}

	int sheetNo = m_ComboSheet.GetCurSel() + 1;

	CWorksheet sheet = m_Excel->GetXlsSheets().get_Item(COleVariant((short)sheetNo));
	CShapes shapes;
	shapes = sheet.get_Shapes();
	//获得Range对象，用来插入图片
	CRange range = sheet.get_Range(COleVariant(cell1), COleVariant(cell2));
	shapes.AddPicture(m_ImageFilePath, false, true, (float)range.get_Left().dblVal, (float)range.get_Top().dblVal, (float)range.get_Width().dblVal, (float)range.get_Height().dblVal);

}


BOOL CDlgInsertImage::OnInitDialog()
{
	CDialogEx::OnInitDialog();

	// TODO:  在此添加额外的初始化
	m_ComboSheet.AddString(_T("axib"));
	vector<CString> name;
	int num;
	if (!m_Excel->GetSheets(num, name)) {
		AfxMessageBox(_T("获取excel内容失败"));
	}

	m_ComboSheet.Clear();
	for (auto s : name) {
		m_ComboSheet.AddString(s);
	}
	m_ComboSheet.SetCurSel(0);

	SetDlgItemInt(IDC_EDIT_ROW_COUNT_IMAGE, m_Excel->GetRows());
	SetDlgItemInt(IDC_EDIT_COL_COUNT_IMAGE, m_Excel->GetCols());
	return TRUE;  // return TRUE unless you set the focus to a control
				  // 异常: OCX 属性页应返回 FALSE
}

.h文件

#pragma once
#include "include/ExcelBase.h"

// CDlgInsertImage 对话框

class CDlgInsertImage : public CDialogEx
{
	DECLARE_DYNAMIC(CDlgInsertImage)

public:
	CDlgInsertImage(CWnd* pParent = nullptr);   // 标准构造函数
	virtual ~CDlgInsertImage();

public:
	void SetExcel(CExcelBase *excel);
	bool BrowseFile(CString& path); 

private:
	CExcelBase *m_Excel;
	CString m_ImageFilePath;

// 对话框数据
#ifdef AFX_DESIGN_TIME
	enum { IDD = IDD_IMAGE_INSERT };
#endif

protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV 支持

	DECLARE_MESSAGE_MAP()
public:
	afx_msg void OnBnClickedBtnBrowseImage();
	CComboBox m_ComboSheet;
	CEdit m_EditRow;
	afx_msg void OnCbnSelchangeComboSheetImage();
	afx_msg void OnBnClickedBtnInsertImage();
	virtual BOOL OnInitDialog();
};

四、结果

在这里插入图片描述

LyRics1996

关注

1
点赞
踩
16

收藏

觉得还不错? 一键收藏
1
评论
C++ Excel表格解析（一、MFC工程中的应用）

一、前言C++怎么解析Excel呢，这两天研究了一下，这一篇就来谈一谈MFC中如何解析Excel。二、准备2.1 代码结构2.2 类说明2.2.1 类型库图中圈出来的这些类，都是一些自带的类，两种方式，方式一：你直接复制其他工程的即可方式二：添加新项->添加类->选择这几个类即可。（VS2019不支持）（2.3.2有介绍）2.2.2 主界面类2.2.3 加载图片界面类2.2.4 MFC生成的启动类2.3 新建工程2.3.1 新建项目（两个关键步骤）
复制链接

扫一扫

专栏目录