여러 군대 찾아도 정보가 미흡하여, 사용 가능하게 정리하였습니다.
- 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();
}
