VC 智能指针操作Excel

1.在stdafx.h定义

//操作EXCEL文件用智能指针需要
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE16\\MSO.DLL" rename("RGB", "MSRGB")
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\VBA6\\VBE6EXT.OLB" raw_interfaces_only, rename("Reference", "ignorethis"), rename("VBE", "testVBE")
#import "C:\\Program Files (x86)\\Microsoft Office\\Office16\\EXCEL.EXE" exclude("IFont", "IPicture"), rename("RGB", "ignorethis"), rename("DialogBox", "ignorethis"), rename("VBE", "testVBE"), rename("ReplaceText", "EReplaceText"), rename("CopyFile","ECopyFile"), rename("FindText", "EFindText"), rename("NoPrompt", "ENoPrompt"),rename("FontPtr","ExcelFontPtr")

注意:安装位置不同使用的路径不同 

2.创建类OperaExcel

3.在OperaExcel.h文件中创建全局变量

Excel::_ApplicationPtr m_app;
Excel::_WorkbookPtr    m_Workbook;
Excel::_WorksheetPtr   m_Worksheet;

4.在OperaExcel.cpp中创建函数

(1)打开Excel文件

BOOL COperaExcel::ExcelOpen(CString FilePath)
{
	try
	{
		if (m_app != NULL)
		{
			m_app.Release();
			m_app = NULL;
		}

		HRESULT hr = m_app.CreateInstance(L"Excel.Application");

		if(FAILED(hr))
		{
			AfxMessageBox(_T("无法连接Excel!"));
			m_app.Release();
			m_app = NULL;
			return FALSE;
		}

		m_app->PutVisible(0,VARIANT_TRUE);//使EXCEL可见
		m_app->PutDisplayAlerts(0,VARIANT_FALSE);//不弹出提示

		ATLASSERT(SUCCEEDED(hr));
		CComVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
		Excel::WorkbooksPtr mBook = m_app->Workbooks;
		m_Workbook = mBook->Open((_bstr_t)FilePath,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,VARIANT_TRUE,covOptional,covOptional,covOptional,covOptional,covOptional);
		return TRUE;
	}
	catch (_com_error& error)
	{
		AfxMessageBox(CString(error.ErrorMessage()));
		return FALSE;
	}
}

(2)关闭Excel

BOOL COperaExcel::ExcelClose(CString filePath)
{
	try
	{
		m_app->PutAlertBeforeOverwriting(0,VARIANT_FALSE);
		m_app->PutDisplayAlerts(0,VARIANT_FALSE);//屏蔽警告
		m_Workbook->Close(VARIANT_TRUE);//保存关闭
		m_app->Quit();//退出

		//CComVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
		//m_Workbook->SaveAs((_bstr_t)cstrPath,covOptional,covOptional,covOptional,covOptional,covOptional,Excel::xlNoChange,covOptional,covOptional,covOptional,covOptional,covOptional,VARIANT_FALSE);//另存为
		//m_Workbook->ExportAsFixedFormat(Excel::XlFixedFormatType::xlTypePDF, (_bstr_t)filePath);//Excel转PDF
		m_app = NULL;
		return TRUE;
	}
	catch (_com_error& e)
	{
		ATLASSERT(FALSE);
		AfxMessageBox(e.ErrorMessage());//输出错误
		return FALSE;
	}
}

(3)写Excel单元格值

BOOL COperaExcel::ExcelWrite(CString LookSheetName,int row,int col,int IntValue,double DoubValue,CString StrValue)
{
	try
	{
		int isheetnum = m_Workbook->Worksheets->Count;
		int k = 1;
		for (; k <= isheetnum; k++)
		{
			m_Worksheet = m_Workbook->Worksheets->Item[(long)k];
			CString SheNa = m_Worksheet->Name;
			if (LookSheetName == SheNa)
			{
				if (IntValue != -1 && DoubValue == -1 && StrValue == L"0")
				{
					Excel::RangePtr m_range = m_Worksheet->Cells;
					m_range->Item[row][col] = IntValue;
					break;
				}
				if (IntValue == -1 && DoubValue != -1 && StrValue == L"0")
				{
					Excel::RangePtr m_range = m_Worksheet->Cells;
					m_range->Item[row][col] = DoubValue;
					break;
				}
				if(IntValue == -1 && DoubValue== -1 && StrValue != L"0")
				{
					Excel::RangePtr m_range = m_Worksheet->Cells;
					m_range->Item[row][col] = _variant_t(StrValue);
					break;
				}
			}
		}
		if (k > isheetnum)
		{
			AfxMessageBox(L"配置文件和计算书没有相同的Sheet名!\n" + LookSheetName);
			return FALSE;
		}
		return TRUE;
	}
	catch (_com_error& e)
	{
		AfxMessageBox(e.ErrorMessage());
		return FALSE;
	}
}

(4)读Excel单元格数据

BOOL COperaExcel::ExcelRead(CString LookSheetName, int iSheetNum, int Erow, int Ecol, CString& cstrValue)
{
	try
	{
		if (LookSheetName != _T(""))
		{
			m_Worksheet = m_Workbook->Worksheets->GetItem(_variant_t(LookSheetName));
		}
		if (iSheetNum > 0)
		{
			m_Worksheet = m_Workbook->Worksheets->GetItem(iSheetNum);
		}
		if (m_Worksheet == NULL)
		{
			AfxMessageBox(_T("ExcelRead获取Sheet失败!"));
			return FALSE;
		}
		Excel::RangePtr m_Range = m_Worksheet->Cells;
		cstrValue = m_Range->Item[Erow][Ecol];

		return TRUE;
	}
	catch (_com_error& e)
	{
		AfxMessageBox(e.ErrorMessage());
		return FALSE;
	}
}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值