[본문스크랩] [VC++] Excel Automation

// VC++을 이용하여 Excel Automation 이용 시 Tips

// API를 찾아보시면 정말 노가다라는 표현이 적절하다고 느끼실겁니다.

// 코딩을 하다가 함수 사용법을 찾아보느라 정말 시간이 오래걸렸네요.

// 물론 찾지 못한 것은 통밥으로 대입해보거나 엑셀 매크로를 참조했습니다.

// 코딩하시는 분들에게 도움이 될까해서 정리해봅니다.

// 초기화 작업은 msdn에서 찾아보시기 바랍니다.

// 퍼가실 땐 스크랩으로~~ 아시죠? ^^ 즐프하시기 바랍니다.

// 객체 및 변수 선언

_Application gObjApplication;
Workbooks gObjWorkbooks;
_Workbook gObjWorkbook;
Range gObjRange;
Worksheets gObjWorksheets;
_Worksheet gObjWorksheet;
Comment objComment;

 

Font font;
Borders borders;
Border bdLeft, bdTop, bdRight, bdBottom;
Interior interior;
Shapes objShapes;
Shape objShape;

 

COleVariant oleVariant((long) DISP_E_PARAMNOTFOUND, VT_ERROR);
COleVariant VFalse((short)FALSE);

CString strStartCell, strEndCell; // 셀의 좌표
CString strText;

 

// 엑셀 프로세스 시작 및 쉬트 객체를 얻음

gObjApplication.CreateDispatch("Excel.Application")

gObjWorkbooks = gObjApplication.GetWorkbooks();
gObjWorkbook = gObjWorkbooks.Add(oleVariant);
gObjWorksheets = gObjWorkbook.GetWorksheets();
gObjWorksheet = gObjWorksheets.GetItem(COleVariant((short) 1));

// 파일(strFileName)로 저장
gObjWorkbook.SaveAs(COleVariant(strFileName), COleVariant((long)1), COleVariant("", VT_BSTR), COleVariant("", VT_BSTR), VFalse, VFalse, 1, COleVariant((long) 1), oleVariant, oleVariant, oleVariant);

 

// 엑셀 프로그램 실행
gObjApplication.SetVisible(TRUE);
gObjApplication.SetUserControl(TRUE);

// Sheet Name 설정
String strSheetName;
strSheetName = "Sheet";
gObjWorksheet.SetName(LPCTSTR(strSheetName));

// Cell Merge
strStartCell = "A1";
strEndCell = "C1";
gObjRange = gObjWorksheet.GetRange(COleVariant(strStartCell), COleVariant(strEndCell));
gObjRange.SetColumnWidth(COleVariant(10.33)); // 100 pixel

 

// Text Insert
strText = "Test";
gObjRange.SetValue(COleVariant(strText));

// 좌기준 : -4131, 가운데기준 : -4108, 우기준 : -4152
gObjRange.SetVerticalAlignment(COleVariant((short)-4108)); // 수직 가운데기준
gObjRange.SetHorizontalAlignment(COleVariant((short)-4152)); // 수평 우기준

 

// Font : size 10, 굴림체, Bold, Italic
gObjRange = gObjWorksheet.GetRange(COleVariant(strStartCell), COleVariant(strEndCell));
font = gObjRange.GetFont();
font.SetSize(COleVariant((short)10));
font.SetName(COleVariant("굴림체"));
font.SetBold(COleVariant((short)1));
font.SetItalic(COleVariant((short)1));

 

// 셀 서식 : 텍스트
gObjRange.SetNumberFormatLocal(COleVariant("@"));

 

// 셀 테두리(border)

// 모든 테두리
gObjRange = gObjWorksheet.GetRange(COleVariant(strStartCell), COleVariant(strEndCell));
borders = gObjRange.GetBorders();
borders.SetLineStyle(COleVariant((short)1)); // single line

 

// 바깥쪽 테두리
gObjRange = gObjWorksheet.GetRange(COleVariant(strStartCell), COleVariant(strEndCell));
borders = gObjRange.GetBorders();

 

// left=7, top=8, right=10, bottom=9
bdLeft = borders.GetItem((long)7);
bdLeft.SetLineStyle(COleVariant((short)1));

bdTop = borders.GetItem((long)8);
bdTop.SetLineStyle(COleVariant((short)1));

bdRight = borders.GetItem((long)10);
bdRight.SetLineStyle(COleVariant((short)1));

bdBottom = borders.GetItem((long)9);
bdBottom.SetLineStyle(COleVariant((short)1));

COLORREF color = RGB(255, 255, 255);
interior = gObjRange.GetInterior();
interior.SetColor(COleVariant((double)color));

 

// 그림 삽입 및 크기 설정
objShapes = gObjWorksheet.GetShapes();
objShape = objShapes.AddPicture(strPictureName, // Filename
(long)0, // LinkToFile
(long)-1, // SaveWithDocument
(float)left, // Left
(float)top, // Top
(float)width, // Width
(float)height); // Height

objShape.SetWidth((float)width);
objShape.SetHeight((float)height);

 

// 메모 저장 및 로드(Comment)
gObjRange = gObjWorksheet.GetRange(COleVariant(strStartCell), COleVariant(strStartCell));
CString str = "Memo";
objComment = gObjRange.AddComment(COleVariant(str));

int index = 1;
objComment = objComments.Item(index); // 메모 객체
str = objComment.Text(COleVariant(""), COleVariant((long)1), COleVariant((short)FALSE));
gObjRange = objComment.GetParent(); // 메모의 parent는 셀의 범위

COleVariant value = gObjRange.GetValue2();
if (value.vt != VT_BSTR)
value.ChangeType(VT_BSTR);
CString strValue = value.bstrVal;

// 엑셀 프로세스 종료 작업
if (font.m_lpDispatch)
font.ReleaseDispatch();

if (borders.m_lpDispatch)
borders.ReleaseDispatch();

if (bdLeft.m_lpDispatch)
bdLeft.ReleaseDispatch();

if (bdTop.m_lpDispatch)
bdTop.ReleaseDispatch();

if (bdRight.m_lpDispatch)
bdRight.ReleaseDispatch();

if (bdBottom.m_lpDispatch)
bdBottom.ReleaseDispatch();

if (interior.m_lpDispatch)
interior.ReleaseDispatch();

if (objComment.m_lpDispatch)
objComment.ReleaseDispatch();

if (objShapes.m_lpDispatch)
objShapes.ReleaseDispatch();

if (objShape.m_lpDispatch)
objShape.ReleaseDispatch();

if (gObjRange.m_lpDispatch)
gObjRange.ReleaseDispatch();

if (gObjWorksheet.m_lpDispatch)
gObjWorksheet.ReleaseDispatch();

if (gObjWorksheets.m_lpDispatch)
gObjWorksheets.ReleaseDispatch();

if (gObjWorkbook.m_lpDispatch)
{
gObjWorkbook.Close(COleVariant((long) FALSE), oleVariant, oleVariant);
gObjWorkbook.ReleaseDispatch();
}

if (gObjWorkbooks.m_lpDispatch)
{
gObjWorkbooks.Close();
gObjWorkbooks.ReleaseDispatch();
}

gObjApplication.Quit();
gObjApplication.ReleaseDispatch();
gObjApplication.DetachDispatch();

 

Excel 다루기