Excel Automation

첨부 파일은 Excel Automaion Wrapper Class입니다.

[출처 : 데브피아]

아래 코드는 마소에서 샘플코드로 있는 소스 입니다.

고수분들은 보시면 알겠지만 텍스트 및 간단한 수치 계산 등의 과정을 보여주고 있습니다.

문제는 이 소스 코드를 실행시키면 엑셀 화면이 생기면서 데이타 값을 보여주게 되는데, 저는 보여주지 말고 특정한

파일이름(예를들어 날짜및 시간)으로 저장을 하고 싶습니다. 그래서 마지막 두줄에 중에 보여주는 부분을 FALSE로 놓고,

app.SaveWorkspace(COleVariant("파일.xls")); app.Quit(); 로 했는데 제대로 되지를 않습니다.

화면엔 나타나지 않으나 저장된 파일을 열려고 하면 이미 기존 파일이 있다면서 덮어쓰겠냐고 묻고, 덮어쓴다하면 파일 저장하는

창이 나타나네요.  문제는 이런 함수들...SaveWorkspace(...)같은 함수를 어디서 참조를 해야 할지.....msdn에는 안나오던데요..

고수님들 도와주세요...

------------------------- Source Code---------------------------------
COleVariant
                 covTrue((short)TRUE),
                 covFalse((short)FALSE),
                 covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


      _Application app;
      Workbooks books;
      _Workbook book;

      Worksheets sheets;
      _Worksheet sheet;
      Range range;
      Font font;
      Range cols;

      // Start Excel and get Application object.

      if(!app.CreateDispatch("Excel.Application"))
      {
       AfxMessageBox("Couldn't start Excel and get Application object.");
       return;
      }

      //Get a new workbook.
      books = app.GetWorkbooks();
      book = books.Add (covOptional);

      //Get the first sheet.
      sheets =book.GetSheets();
      sheet = sheets.GetItem(COleVariant((short)1));

      VARIANT retVal;
      VariantInit(&retVal);

      //Fill cells A1, B1, C1, and D1 one cell at a time with "headers".
      range = sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
      range.SetValue(retVal,COleVariant("First Name"));
      range = sheet.GetRange(COleVariant("B1"),COleVariant("B1"));
      range.SetValue(retVal,COleVariant("Last Name"));
      range = sheet.GetRange(COleVariant("C1"),COleVariant("C1"));
      range.SetValue(retVal,COleVariant("Full Name"));
      range = sheet.GetRange(COleVariant("D1"),COleVariant("D1"));
      range.SetValue(retVal,COleVariant("Salary"));


      //Format A1:D1 as bold, vertical alignment = center.
      range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
      font = range.GetFont();
      font.SetBold(covTrue);
      range.SetVerticalAlignment(
               COleVariant((short)-4108));   //xlVAlignCenter = -4108

      //Fill A2:B6 with an array of values (First & Last Names).
      {
         COleSafeArray saRet;
         DWORD numElements[]={5,2};   //5x2 element array
         saRet.Create(VT_BSTR, 2, numElements);


         //Fill the 5x2 SafeArray with the following data:
         //   John      Smith
         //   Tom       Brown
         //   Sue       Thomas
         //   Jane      Jones
         //   Adam      Johnson

         FillSafeArray(L"John", 0, 0, &saRet);
         FillSafeArray(L"Smith", 0, 1, &saRet);
         FillSafeArray(L"Tom", 1, 0, &saRet);
         FillSafeArray(L"Brown", 1, 1, &saRet);
         FillSafeArray(L"Sue", 2, 0, &saRet);
         FillSafeArray(L"Thomas", 2, 1, &saRet);
         FillSafeArray(L"Jane", 3, 0, &saRet);
         FillSafeArray(L"Jones", 3, 1, &saRet);
         FillSafeArray(L"Adam", 4, 0, &saRet);
         FillSafeArray(L"Johnson", 4, 1, &saRet);

         range = sheet.GetRange(COleVariant("A2"), COleVariant("B6"));
         range.SetValue(retVal,COleVariant(saRet));

         saRet.Detach();
      }

      //Fill C2:C6 with a relative formula (=A2 & " " & B2).
      range = sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
      range.SetFormula(COleVariant("=A2 & \" \" & B2"));

      //Fill D2:D6 with a formula(=RAND()*100000) and apply a number
      //format.
      range = sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
      range.SetFormula(COleVariant("=RAND()*100000"));
      range.SetNumberFormat(COleVariant("$0.00"));

      //AutoFit columns A:D.
      range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
      cols = range.GetEntireColumn();
      cols.AutoFit();

      //Manipulate a variable number of columns for Quarterly Sales Data.
      {
         short NumQtrs;
         CString msg;
         Range resizedrange;
         Interior interior;
         Borders borders;

         //Determine how many quarters to display data for.
         for(NumQtrs=1;NumQtrs<=3;NumQtrs++)
         {
            msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
            if(AfxMessageBox(msg,MB_YESNO)==IDYES)
            {
               break;
            }
         }
         msg.Format("Displaying data for %d quarters.", NumQtrs);
         AfxMessageBox(msg);

         //Starting at E1, fill headers for the number of columns selected.
         range = sheet.GetRange(COleVariant("E1"), COleVariant("E1"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(
                COleVariant("=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""));
         //Change the Orientation and WrapText properties for the headers.
         resizedrange.SetOrientation(COleVariant((short)38));
         resizedrange.SetWrapText(covTrue);
         //Fill the interior color of the headers.
         interior = resizedrange.GetInterior();
         interior.SetColorIndex(COleVariant((short)36));

         //Fill the columns with a formula and apply a number format.
         range = sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(COleVariant("=RAND()*100"));
         resizedrange.SetNumberFormat(COleVariant("$0.00"));

         //Apply borders to the Sales data and headers.
         range = sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
         resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
         borders = resizedrange.GetBorders();
         borders.SetWeight(COleVariant((short)2));   //xlThin = 2

         //Add a Totals formula for the Quarterly sales data and apply a
         //border.
         range = sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
         borders = resizedrange.GetBorders();
         {
            Border bottomborder;
            bottomborder = borders.GetItem((long)9);
            bottomborder.SetLineStyle(
                           COleVariant((short)-4119));   //xlDouble = -4119
            bottomborder.SetWeight(
                           COleVariant((short)4));       //xlThick = 4

         }
      }

      //Make the application visible and give the user control of
      //Microsoft Excel.
      app.SetVisible(TRUE);
      app.SetUserControl(TRUE);

















 이 글에 평점 주기:  0점 1점 2점 3점 4점 5점 6점 7점 8점 9점    





 [답변][참고] 2006-10-10 오후 2:53:37

이광진 (mirjini)    번호: 609614   / 평점:  (-)  
안녕하세요...

SaveWorkspace(..) 는 엑셀 내용을 저장하는 것이 아니라 엑셀의 현재 작업상태를 xlw 파일로 저장을 합니다.
 
엑셀 내용을 저장하시려면 _Workbook 클래스의 SaveAs(..) 를 이용하시면 됩니다.

그리고 MSDN 에도 나오긴 합니다. MSDN 에서 검색해 보시면 볼 수 있지만 VBA 로 설명을 하고 있답니다.

실제 오토메이션으로 사용하는 걸 찾으시려면 인터넷에서 검색하시면 많은 도움을 받으실 수 있습니다.


 이 글에 평점 주기:  0점 1점 2점 3점 4점 5점 6점 7점 8점 9점    





 [답변]... 2006-10-10 오후 2:57:16

원정숙 (hehe2020)    번호: 609617   / 평점:  (-)  
Excel2003 type libary를 기준으로 간단한 파일 열기/저장하는 방법을 설명할께요.

COleVariant vTrue((short)TRUE),  vFalse((short)FALSE), vOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

CApplication app;
CWorkbooks books;
CWorkbook   book

1. 엑셀과 연결
app.CreateDispatch("Excel.Application");
app.put_Visible(TRUE);    // FALSE로 주면 연결된 엑셀이 화면에 안보임

2. 파일 열기
books = app.get_Workbooks();
books.Open("파일명", vOptional, vOptional, vOptional, vOptional, vOptional, vOptional,
                vOptional, vOptional, vOptional, vOptional, vOptional, vOptional, vOptional, vOptional));

3. 파일 저장
book = books.get_Item(COleVariant((short)nItem)));    // nItem : 원하는 워크북의 인덱스값
or
book = app.get_ActiveWorkbook();                             // 현재 활성화된 워크북

book.SaveAs(COleVariant("파일명"), vOptional, vOptional, vOptional, vOptional, vOptional, 0,
                    vOptional, vOptional, vOptional, vOptional, vOptional);

//////////////////////////////////////////////////////////////////////////////////////////
SaveAs(FileName, FileFormat, LockComments, Password, AddToRecentFiles, WritePassword, ReadOnlyRecommended, EmbedTrueTypeFonts, SaveNativePictureFormat, SaveFormsData, SaveAsAOCELetter)
 
FileName   Optional Variant. The name for the document. The default is the current folder and file name. If the document has never been saved, the default name is used (for example, Doc1.doc). If a document with the specified FileName already exists, the document is overwritten without the user being prompted first.
FileFormat   Optional Variant. The format in which the document is saved. Can be one of the following WdSaveFormat constants: wdFormatDocument, wdFormatDOSText, wdFormatDOSTextLineBreaks, wdFormatEncodedText, wdFormatHTML, wdFormatRTF, wdFormatTemplate, wdFormatText, wdFormatTextLineBreaks, or wdFormatUnicodeText. To save a document in another format, specify the appropriate value for the SaveFormat property of the FileConverter object.
LockComments   Optional Variant. True to lock the document for comments.
Password   Optional Variant. A password string for opening the document.
AddToRecentFiles   Optional Variant. True to add the document to the list of recently used files on the File menu.
WritePassword   Optional Variant. A password string for saving changes to the document.
ReadOnlyRecommended   Optional Variant. True to have Word suggest read-only status whenever the document is opened.
EmbedTrueTypeFonts   Optional Variant. True to save TrueType fonts with the document.
SaveNativePictureFormat   Optional Variant. If graphics were imported from another platform (for example, Macintosh), True to save only the Windows version of the imported graphics.
SaveFormsData   Optional Variant. True to save the data entered by a user in a form as a data record.
SaveAsAOCELetter   Optional Variant. If the document has an attached mailer, True to save the document as an AOCE letter (the mailer is saved).
 
 
Open(FileName, ConfirmConversions, ReadOnly, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Format, Encoding, Visible)
 
FileName   Required Variant. The name of the document (paths are accepted).
ConfirmConversions   Optional Variant. True to display the Convert File dialog box if the file isn't in Microsoft Word format.
ReadOnly   Optional Variant. True to open the document as read-only.
AddToRecentFiles   Optional Variant. True to add the file name to the list of recently used files at the bottom of the File menu.
PasswordDocument   Optional Variant. The password for opening the document.
PasswordTemplate   Optional Variant. The password for opening the template.
Revert   Optional Variant. Controls what happens if Name is the file name of an open document. True to discard any unsaved changes to the open document and reopen the file. False to activate the open document.
WritePasswordDocument   Optional Variant. The password for saving changes to the document.
WritePasswordTemplate   Optional Variant. The password for saving changes to the template.
Format   Optional Variant. The file converter to be used to open the document. Can be one of the following WdOpenFormat constants: wdOpenFormatAllWord, wdOpenFormatAuto, wdOpenFormatDocument, wdOpenFormatEncodedText, wdOpenFormatRTF, wdOpenFormatTemplate, wdOpenFormatText, wdOpenFormatUnicodeText, or wdOpenFormatWebPages. The default value is wdOpenFormatAuto.
To specify an external file format, apply the OpenFormat property to a FileConverter object to determine the value to use with this argument.
Encoding   Optional Variant. The document encoding (code page or character set) to be used by Microsoft Word when you view the saved document. Can be any valid MsoEncoding constant. For the list of valid MsoEncoding constants, see the Object Browser in the Visual Basic Editor. The default is the system code page. Read/write Long.
Visible   Optional Variant. True if the document is opened in a visible window. The default is True


수고하세요.