• How to use VS to manipulate Excel使用MFC读写Excel


    1. Build project with MFC Dialogue;

    2. Open (ctrl + alt + x) class wizard:

    3. Add class - From a type library

    4. Choose From File:

    5. Find the Road of excel.exe

    5. Add some classes to the project:

    6. Press F7 to test whether the code works;

    7. Delete or comment the code in each of the added in files.

    1 #import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace

    Or, you will face the problem when compiling.

    8. If you still can not pass the process of compiling, in a class that I do not remember, change Dialog to _dialog, which will be very beneficial.

    9. Now you will be able to use it to control Excel.

    10. We can read Excel with code like this:

     1 ::CoInitialize(NULL);
     2     CApplication app;
     3     CWorkbooks books;
     4     CWorkbook book;
     5     CWorksheets sheets;
     6     CWorksheet sheet;
     7     CRange range;
     8     CRange iCell;
     9     LPDISPATCH lpDisp;
    10     COleVariant vResult;
    11     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    12 
    13     //if can not connect to Excel
    14     if(!app.CreateDispatch("Excel.Application"))
    15     {
    16         AfxMessageBox("Can not start Excel server!");
    17         return;
    18     }
    19 
    20  
    21 
    22     //*****
    23     //open an Excel file
    24     books.AttachDispatch(app.get_Workbooks());
    25 
    26 
    27     lpDisp = books.Open("C:\\Documents and Settings\\Administrator.PC-201202231058\\桌面\\excel\\excel\\in.xls",
    28         covOptional, covOptional, covOptional, covOptional, covOptional,
    29         covOptional, covOptional, covOptional, covOptional, covOptional,
    30         covOptional, covOptional, covOptional, covOptional );
    31 
    32 
    33     //*****
    34     //get Workbook
    35     book.AttachDispatch(lpDisp);
    36 
    37 
    38     //*****
    39     //get Worksheets
    40     sheets.AttachDispatch(book.get_Worksheets());
    41 
    42  
    43 
    44     //*****
    45     //get active sheet
    46     //if this cell is still in editing, the program will wait
    47     lpDisp=book.get_ActiveSheet();
    48     sheet.AttachDispatch(lpDisp);
    49 
    50  
    51 
    52     //*****
    53     //get the value of the first cell
    54     range.AttachDispatch(sheet.get_Cells());
    55     
    56     // the number of 1 here can be changed to get values of other cells
    57 range.AttachDispatch(range.get_Item(COleVariant((long)1),COleVariant((long)1)).pdispVal);
    58     vResult =range.get_Value2();
    59     CString str;
    60     if(vResult.vt == VT_BSTR) //String
    61     {
    62     str=vResult.bstrVal;
    63     }
    64     else if (vResult.vt==VT_R8) //number
    65     {
    66     str.Format("%f",vResult.dblVal);
    67     }
    68 
    69     else if(vResult.vt==VT_DATE) //time
    70     {
    71     SYSTEMTIME st;
    72     //VariantTimeToSystemTime(&vResult.date, &st);
    73     }
    74     else if(vResult.vt==VT_EMPTY) //blank
    75     {
    76     str="";
    77     }

    11. To write a file, we can do like this:

     1     //Init COM
     2     ::CoInitialize(NULL);
     3 
     4     //Export to Excel
     5     CString sss,s1,s2,e1,e2,strSQL;
     6     CStringArray sa;
     7     
     8     
     9     CString fname,fname1, sheetname,s;
    10 
    11     
    12     fname="C:\\Documents and Settings\\Administrator.PC-201202231058\\桌面\\excel\\excel\\out.xls";//得到要导出保存的路径及文件名
    13 
    14     //Define the objects
    15     CApplication objApp;
    16     CWorkbooks objBooks;
    17     CWorkbook objBook;
    18     CWorksheets objSheets;
    19     CWorksheet objSheet;
    20     CRange objRange,objRange1,objRange2;    
    21 
    22     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    23     COleVariant covTrue((short)TRUE), covFalse((short)FALSE),\
    24         varFormat((short)-4143),varCenter((short)-4108),varLeft((short)-4131),varText("TEXT",VT_BSTR),var,\
    25         varRange1("A1",VT_BSTR),varRange2("D1",VT_BSTR);
    26     
    27     //Creat Excel
    28     objApp.m_bAutoRelease=true;
    29     if(!objApp.CreateDispatch("Excel.Application"))
    30     {
    31         AfxMessageBox("Failed to connect to Excel!");
    32         return;
    33     }
    34     //get Workbooks
    35     objBooks=objApp.get_Workbooks();
    36 
    37     //open Excel file
    38     objBook.AttachDispatch(objBooks.Add(_variant_t("")));
    39     objSheets=objBook.get_Sheets();
    40 
    41     
    42     //Define as the first excel object
    43     objSheet=objSheets.get_Item((_variant_t)short(1));
    44     sheetname="sheetname";
    45     objSheet.put_Name(sheetname);
    46     objSheet.Activate();
    47     objRange.AttachDispatch(objSheet.get_Cells(),true); 
    48     
    49     //Write to the cell A1
    50     //If you wan to write to other cells, just change this flag 
    51     s1.Format("A1");
    52     e1=s1;
    53     objRange1=objSheet.get_Range(_variant_t(s1),_variant_t(s1));
    54 
    55     //s is the thing to write
    56     //if it is a string, s = "'111"
    57     //if it is a number, s = "111"
    58     s="111.11";
    59 
    60     //Write to cell
    61     objRange1.put_FormulaR1C1(_variant_t(s));
    62 
    63         
    64     //save
    65     objBook.SaveAs(_variant_t(fname),varFormat,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);
    66 
    67     objApp.Quit();
    68     objRange.ReleaseDispatch();
    69     objSheet.ReleaseDispatch();
    70     objSheets.ReleaseDispatch();
    71     objBook.ReleaseDispatch();
    72     objBooks.ReleaseDispatch();

    We need to use ::CoInitializeEx(NULL, COINIT_MULTITHREADED) instead of ::CoInitialize(NULL)

    12. Do CoUninitialize(); when finishing.

  • 相关阅读:
    经典面试题(二)附答案 算法+数据结构+代码 微软Microsoft、谷歌Google、百度、腾讯
    ctype.h库函数
    结构体大小与内存对齐问题
    整数v,从高位到低位,取c位数,得到最大数 (其中:v>=10^c)
    32位与64 位下各类型长度对比
    C++容器类
    笔试题 相对位置不变的正负数排序
    composer 下载安装
    nginx 多进程 + io多路复用 实现高并发
    go 成长路上的坑(1)
  • 原文地址:https://www.cnblogs.com/johnpher/p/2684809.html
Copyright © 2020-2023  润新知