MFC에서 Excel 컨트롤 하기(2)

여러 군대 찾아도 정보가 미흡하여,  사용 가능하게 정리하였습니다.

 - XLEzAutomation이나 ExcelLib의 기능에 원하는 기능 구현이 없고 수정 추가에도 어려움이 있어서...

 - 잘 활용하시기 바랍니다.

 

Excel 컨트롤 클래스 만들기.

(필요할 때마다 하나씩 추가 하자~~~~)

구현 기능

1 파일 열기,

2 파일 닫기,

3 다른이름 저장,

4 값 일기,

5 값 쓰기,

6 이미지 저장하기(클립보드, 절대 경로 Link, Link를 이용한 포함시키기)

7 Sheet Active 시키기.

 

미구현 기능

8 차트 만들기.

9 Sheet이름 바꾸기.

10 범용 알고리즘 콜하기.

 

추가된 Excel File들은 복사하여 다른 프로젝트에 Link를 걸고 그대로 사용 가능하다.

(MFC에서 Excel 컨트롤 하기(1)을 매 프로젝트에 추가할 필요는 없다)

#include "CApplication.h"//
#include "CWorkbook.h"//
#include "CWorkbooks.h"//

#include "CWorksheet.h"//
#include "CWorksheets.h"//

#include "CChart.h"//
#include "CCharts.h"//

#include "CBorder.h"//
#include "CBorders.h"//

#include "CPicture.h"//
#include "CPictures.h"//

#include "CRange.h"//
#include "CFont0.h"//

#include "CShapes.h"
#include "CShape.h"

 

 

구현 해더 파일.(ExcelCtrl.h)

#pragma once

#include "CApplication.h"//
#include "CWorkbook.h"//
#include "CWorkbooks.h"//

#include "CWorksheet.h"//
#include "CWorksheets.h"//


#include "CChart.h"//
#include "CCharts.h"//

#include "CBorder.h"//
#include "CBorders.h"//

#include "CPicture.h"//
#include "CPictures.h"//

#include "CRange.h"//
#include "CFont0.h"//

#include "CShapes.h"
#include "CShape.h"

static CString ColToColLetter(int colIndex)
{
	int div = colIndex;
	CString colLetter;// = String.Empty;
	int mod = 0;

	while (div > 0)
	{
		mod = (div - 1) % 26;
		colLetter = (char)(65 + mod) + colLetter;
		div = (int)((div - mod) / 26);
	}
	return colLetter;
}
static int ColLetterToColIndex(CString columnLetter)
{
	columnLetter = columnLetter.MakeUpper();
	int sum = 0;

	for (int i = 0; i < columnLetter.GetLength(); i++)
	{
		sum *= 26;
		sum += (columnLetter[i] - L'A' + 1);
	}
	return sum;
}

class CExcelCtrl
{
public:
	CExcelCtrl();
	~CExcelCtrl();

private:
	// spreadsheet variables
	CApplication	m_app;
	CWorkbook		m_book;
	CWorkbooks		m_books;


	CWorksheets		m_worksheets;


	COleVariant		m_covTrue;
	COleVariant		m_covFalse;
	COleVariant		m_covOptional;

	CString			m_strOpenFile;


private:
	void CloseWorkbook();
public:
	//1) File 열고 닫기.
	bool OpenFile(CString strFilePath);
	bool CloseFile();
	bool SaveFile();
	bool SaveAsFile(CString newFullPath);
	//2) Data 읽고 쓰기.
	CString GetValue(int nSheet, CString strCellPos);//CellPos :"A1", "O21"
	void SetValue(int nSheet, CString strCellPos, CString strNewValue);
	//3) Image 삽입.
	void SetPasteClipboard(int nSheet, CString strCellPos, double fViewSizeW = 0.0, double fViewSizeH = 0.0);//클립 보드의 이미지 삽입
	void SetPasteFileLink(int nSheet, CString strCellPos, CString strImgPath, double fViewSizeW = 0.0, double fViewSizeH = 0.0);//주어진 이미지 절대 경로를 삽입.(경로만)
	void SetFileLinkAndAdd(int nSheet, CString strCellPos, CString strImgPath, int orgW, int orgH, double fViewSizeW = 0.0, double fViewSizeH = 0.0);//경로의 파일을 삽입.(Excel File에 첨부)

	void SetActiveSheet(int nSheet);
};

 

구현 소스파일(ExcelCtrl.cpp)

#include "stdafx.h"
#include "ExcelCtrl.h"
#define rmm     23
CExcelCtrl::CExcelCtrl():
	m_app(nullptr),
	m_book(nullptr),
	m_books(nullptr),
	m_covTrue((short)TRUE),
	m_covFalse((short)TRUE),
	m_covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR)

{
	try
	{
		OleUninitialize(); //프로젝트 내 다른  COM이 초기화해 놓은 것을 죽여 놓으
		DWORD dwOleVer;

		dwOleVer = CoBuildVersion();

		// check the OLE library version
		if (rmm != HIWORD(dwOleVer))
		{
			MessageBox(NULL, _T("Incorrect version of OLE libraries."), L"Failed", MB_OK | MB_ICONSTOP);
			return;
		}

		// could also check for minor version, but this application is
		// not sensitive to the minor version of OLE

		// initialize OLE, fail application if we can't get OLE to init.
		if (FAILED(OleInitialize(NULL)))
		{
			MessageBox(NULL, _T("Cannot initialize OLE."), L"Failed", MB_OK | MB_ICONSTOP);
			return;
		}

		if (!m_app.CreateDispatch(L"Excel.Application"))
		{
			AfxMessageBox(L"Could not start Excel.");
			return;
		}
		m_app.put_DisplayAlerts(VARIANT_FALSE);
		m_app.put_UserControl(FALSE);
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
}
CExcelCtrl::~CExcelCtrl()
{
	CloseFile();
	OleUninitialize();
	OleInitialize(NULL);  //프로젝트 내 다른 COM을 위한 센스
}
bool CExcelCtrl::OpenFile(CString strFilePath)
{
	m_strOpenFile.Format(L"%s", strFilePath);
	// close already-opened workbook
	CloseWorkbook();
	bool retV = false;
	try
	{
		// Get Workbooks collection.
		LPDISPATCH lpDisp;
		lpDisp = m_app.get_Workbooks();  // Get an IDispatch pointer
		ASSERT(lpDisp);
		m_books.AttachDispatch(lpDisp);  // Attach the IDispatch pointer to the books object.

		// open the document

		lpDisp = m_books.Open(m_strOpenFile.GetBuffer(m_strOpenFile.GetLength()),
			m_covOptional, m_covOptional, m_covOptional, m_covOptional,
			m_covOptional, m_covOptional, m_covOptional, m_covOptional,
			m_covOptional, m_covOptional, m_covOptional, m_covOptional,
			m_covOptional, m_covOptional);

		ASSERT(lpDisp);

		//Set CWorkbook to use lpDisp, the IDispatch* of the actual workbook.
		m_book.AttachDispatch(lpDisp);
		//WorkSheets Link.
		m_worksheets = m_book.get_Sheets();

		retV = true;
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
	return retV;
}

bool CExcelCtrl::CloseFile()
{
	bool retV = false;
	CloseWorkbook();
	try
	{
		m_app.Quit();
		m_app.ReleaseDispatch();
		retV = true;
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
	return retV;
}
bool CExcelCtrl::SaveFile()
{
	bool retV = false;
	if (m_app == nullptr || m_books == nullptr || m_book == nullptr)
		return retV;
	try
	{
		m_book.Save();
		retV = true;
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}

	return retV;
}
bool CExcelCtrl::SaveAsFile(CString newFullPath)
{
	bool retV = false;
	if (m_app == nullptr || m_books == nullptr || m_book == nullptr)
		return false;
	try
	{
		//51 = xlOpenXMLWorkbook(without macro's in 2007-2016, xlsx)
		//52 = xlOpenXMLWorkbookMacroEnabled(with or without macro's in 2007-2016, xlsm)
		//50 = xlExcel12(Excel Binary Workbook in 2007 - 2016 with or without macro's,xlsb)
		//56 = xlExcel8(97 - 2003 format in Excel 2007 - 2016, xls)
		int nPos  = newFullPath.ReverseFind(L'.');
		CString strExeName;
		strExeName.Format(L"%s", newFullPath.Mid(nPos+1));
		//strExeName.MakeUpper();
		long nFileCode = 51;
		if (strExeName.CompareNoCase(L"xlsm") == 0)
			nFileCode = 52;

		m_book.SaveAs(
			COleVariant(newFullPath.GetBuffer(newFullPath.GetLength())),
			COleVariant(nFileCode),//file format
			m_covOptional,//COleVariant(_T(""), // password
			m_covOptional,//COleVariant(_T(""), VT_BSTR), //readonly
			m_covOptional,//covFalse, 
	  //createbackup
			m_covOptional,//covFalse, 
	  //accessmode
			1,
			m_covOptional,//COleVariant((long) 1),  
			m_covOptional,
			m_covOptional,
			m_covOptional, m_covOptional);

		retV = true;
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
	return retV;
}
void CExcelCtrl::CloseWorkbook()
{
	try
	{
		m_worksheets.ReleaseDispatch();
		// close already-opened workbook
		m_book.ReleaseDispatch();
		m_books.Close();
		m_books.ReleaseDispatch();
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
}

CString CExcelCtrl::GetValue(int nSheet, CString strCellPos)
{
	if (nSheet < 1 || m_app == nullptr)
		return CString(L"");
	
	CString szValue;
	try
	{
		// sheet 생성, 연결 (1번 시트)
		CWorksheet sheet;
		sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
		sheet.Activate();
		// range 생성, 연결
		CRange range;
		range.AttachDispatch(sheet.get_Cells(), true);

		range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
		range.Select();
		//------------------------------------------------------
		COleVariant vargValue = range.get_Value2();
		switch (vargValue.vt)
		{
		case VT_UI1:
		{
			unsigned char nChr = vargValue.bVal;
			//szValue = nChr;
			szValue.Format(L"%d", nChr);// = nChr;
		}
		break;
		case VT_I4:
		{
			long nVal = vargValue.lVal;
			szValue.Format(L"%i", nVal);
		}
		break;
		case VT_R4:
		{
			float fVal = vargValue.fltVal;
			szValue.Format(L"%f", fVal);
		}
		break;
		case VT_R8:
		{
			double dVal = vargValue.dblVal;
			szValue.Format(L"%f", dVal);
		}
		break;
		case VT_BSTR:
		{
			BSTR b = vargValue.bstrVal;
			szValue = b;
		}
		break;
		case VT_BYREF | VT_UI1:
		{
			//Not tested
			unsigned char* pChr = vargValue.pbVal;
			//					szValue = *pChr;
			szValue.Format(L"%f", *pChr);
		}
		break;
		case VT_BYREF | VT_BSTR:
		{
			//Not tested
			BSTR* pb = vargValue.pbstrVal;
			szValue = *pb;
		}
		case 0:
		{
			//Empty
			szValue = _T("");
		}

		break;
		}
		//------------------------------------------------------
		range.ReleaseDispatch();
		sheet.ReleaseDispatch();
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
	return szValue;
}
void CExcelCtrl::SetValue(int nSheet, CString strCellPos, CString strNewValue)
{
	if (nSheet < 1 || m_app == nullptr)
		return;

	try
	{
		// sheet 생성, 연결 (1번 시트)
		CWorksheet sheet;
		sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
		sheet.Activate();
		// range 생성, 연결
		CRange range;
		range.AttachDispatch(sheet.get_Cells(), true);

		range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
		range.Select();
		//------------------------------------------------------
		//Data 쓰기.
		range.put_Value2(COleVariant(strNewValue));
		//------------------------------------------------------
		range.ReleaseDispatch();
		sheet.ReleaseDispatch();
	}
	catch (CMemoryException* e)
	{
		CMemoryException* ep = e;
		AfxMessageBox(L"CMemoryException Could not clean up workbook.");
	}
	catch (CFileException* e)
	{
		CFileException* pe = e;
		AfxMessageBox(L"CFileException Could not clean up workbook.");
	}
	catch (CException* e)
	{
		CException* pe = e;
		AfxMessageBox(L"CException Could not clean up workbook.");
	}
}

void CExcelCtrl::SetActiveSheet(int nSheet)
{
	if (nSheet < 1 || m_app == nullptr)
		return;
	CWorksheet sheet;
	sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
	sheet.Activate();
}
void CExcelCtrl::SetPasteClipboard(int nSheet, CString strCellPos,  double fViewSizeW , double fViewSizeH)
{
	if (nSheet < 1 || m_app == nullptr)
		return;
	CWorksheet sheet;
	CRange range;
	CPicture pic;
	CPictures pics;
	//CBorder border;

	sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
	pics = sheet.Pictures(m_covOptional);
	sheet.Activate();
	// insert first picture and put a border on it
	range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
	range.Select();

	
	//링크 집어넣기
	//pic = pics.Insert(strLoadImgPath.GetBuffer(strLoadImgPath.GetLength()), covOptional);
	//붙여넣기
	sheet.Activate();
	pic = pics.Paste(m_covOptional);

	VARIANT varL = range.get_Left();
	VARIANT varT = range.get_Top();
	VARIANT varW = range.get_Width();
	VARIANT varH = range.get_Height();

	double l = varL.dblVal + 2.0;
	double t = varT.dblVal + 2.0;
	double w = varW.dblVal;
	double h = varH.dblVal;
	//Sheet 에서 위치 지정.
	pic.put_Left(l);
	pic.put_Top(t);
	pic.put_Locked(FALSE);
	//크기 지정.
	if (fViewSizeW > 0.0 )//&& fViewSizeH > 0.0)
	{
		pic.put_Width(fViewSizeW);
		//pic.put_Height(fViewSizeW);
	}
	pic.put_Visible(TRUE);

	
	//VARIANT Replace;
	//pic.Select(Replace);
	//m_book.Save();
	pic.ReleaseDispatch();
	pics.ReleaseDispatch();
	range.ReleaseDispatch();
	sheet.ReleaseDispatch();
}
void CExcelCtrl::SetPasteFileLink(int nSheet, CString strCellPos, CString strImgPath, double fViewSizeW, double fViewSizeH)
{
	if (nSheet < 1 || m_app == nullptr || strImgPath.GetLength()<3)
		return;
	CWorksheet sheet;
	CRange range;
	CPicture pic;
	CPictures pics;
	//CBorder border;

	sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
	sheet.Activate();

	pics = sheet.Pictures(m_covOptional);
	// insert first picture and put a border on it
	// 링크 집어넣기
	range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
	range.Select();
	VARIANT varL = range.get_Left();
	VARIANT varT = range.get_Top();
	VARIANT varW = range.get_Width();
	VARIANT varH = range.get_Height();

	double l = varL.dblVal + 2.0;
	double t = varT.dblVal + 2.0;
	double w = varW.dblVal;
	double h = varH.dblVal;

	sheet.Activate();
	pic = pics.Insert(strImgPath.GetBuffer(strImgPath.GetLength()), m_covOptional);
	//붙여넣기
	//range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
	//range.Select();
	//pic = pics.Paste(m_covOptional);


	//Sheet 에서 위치 지정.
	pic.put_Left(l);
	pic.put_Top(t);

	//크기 지정.
	if (fViewSizeW > 0.0)//&& fViewSizeH > 0.0)
	{
		pic.put_Width(fViewSizeW);
		//pic.put_Height(fViewSizeW);
	}

	pic.ReleaseDispatch();
	pics.ReleaseDispatch();


	//m_book.Save();
	range.ReleaseDispatch();
	sheet.ReleaseDispatch();
}

void CExcelCtrl::SetFileLinkAndAdd(int nSheet, CString strCellPos, CString strImgPath, int orgW, int orgH, double fViewSizeW, double fViewSizeH)
{
	if (nSheet < 1 || m_app == nullptr || strImgPath.GetLength() < 3)
		return;
	CWorksheet sheet;
	CRange range;
	CPicture pic;
	CPictures pics;
	//CBorder border;

	sheet = m_worksheets.get_Item(COleVariant((short)nSheet));
	sheet.Activate();
	range = sheet.get_Range(COleVariant(strCellPos.GetBuffer(strCellPos.GetAllocLength())), m_covOptional);
	range.Select();
	VARIANT varL = range.get_Left();
	VARIANT varT = range.get_Top();
	VARIANT varW = range.get_Width();
	VARIANT varH = range.get_Height();

	double l = varL.dblVal + 2.0;
	double t = varT.dblVal + 2.0;
	double w = varW.dblVal;
	double h = varH.dblVal;

	CShapes  objShapes;
	CShape   objShape;
	objShapes = sheet.get_Shapes();
	objShape = objShapes.AddPicture(strImgPath, // Filename
		(long)0,    // LinkToFile
		(long)-1,	// SaveWithDocument
		(float)l,	// Left
		(float)t,   // Top
		(float)orgW,  // Width
		(float)orgH); // Height

	if (fViewSizeW > 0.0 && fViewSizeH > 0.0)
	{
		objShape.put_Width((float)fViewSizeW);
		objShape.put_Height((float)fViewSizeH);
	}
	if (objShapes.m_lpDispatch)
		objShapes.ReleaseDispatch();
	if (objShape.m_lpDispatch)
		objShape.ReleaseDispatch();

	range.ReleaseDispatch();
	sheet.ReleaseDispatch();
}