• vs2010操作 office2010自我笔记


    【FAG按顺序添加这些东西】
    1.创建一个新的工程,选择mfc application,选择dialog,在advanced features选择automation(这一项我不确定有没有必要,查资料说要选上,我就选上了)
    2.创建完工程后,在dialog对话框上右击,选择class wizard(或是ctrl+shift+x)
    3.在add class下拉框内选择add class from typelib
    4.在add class from下面选择file,然后你的office安装目录下的EXCEL.EXE(我的目录是C:Program Files (x86)Microsoft OfficeOffice14EXCEL.EXE)
    5.在下面添加6个类到我们的工程,(_Application, Worksheets, _Worksheet, Workbooks, _Workbook, Range)注意有的有下划线,别添加错了

    【FAG注释掉下面】
    把导入的6个类对应的头文件最开始的一句话[注释]

    #import "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" no_namespace  

    【FAG添加下划线】
    把Range这个类自动生成的头文件(我这是CRage.h)里面的
    VARIANT DialogBox()
    VARIANT _DialogBox()

    【FAG添加头文件】

    //====6个类的头文件================================
    #include "CApplication.h"  
    #include "CRange.h"  
    #include "CWorkbook.h"  
    #include "CWorkbooks.h"  
    #include "CWorksheet.h"  
    #include "CWorksheets.h" 
    
    //====里面定义变量时用到==============================
    #include <map>
    #include <utility>
    using namespace std;
    

    【FAG使用函数】

    void Cfag_EXCEL00Dlg::OnBnClickedButton1()
    {
        // TODO: Add your control notification handler code here
        ::CoInitialize(NULL);
        //用到六个excel类
        //要操作表,必须先逐步获取Workbooks->Workbook->Worksheets->Worksheet->Range
        CApplication app;   //先创建一个_Application类,用_Application来创建一个Excel应用程序接口
        CWorkbooks books;   //工作薄集合
        CWorkbook book;     //工作薄
        CWorksheets sheets; //工作表集合
        CWorksheet sheet;   //工作表
        CRange range;       //单元格区域对象
    
        //star excel
        if (!app.CreateDispatch("Excel.Application"))
        {
            AfxMessageBox("无法启动Excel启动器");
            return ;
        }
    
        //=======================
    
        //========================
        //open *.xls
        books.AttachDispatch(app.get_Workbooks());
            //get file path
        LPCTSTR szFilter = _T("txt(*.txt)|*.txt|excel(*.xls)|*.xls|All Filter(*.*)|*.*||");
        CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY,szFilter);
        if (IDOK != dlg.DoModal())
        {
            return ;
        }
        //得到excel的路径
        CString strPath = dlg.GetPathName();
    
         /*    COleVariant类为VARIANT数据类型的包装,
           在自动化程序中,通常都使用ARIANT数据类型进行参数传递。
               故下列程序中,函数参数都是通过COleVariant类来转换了的。
        */  
    
        //covOptional 可选参数的VARIANT类型
        COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    
        //打开books
        LPDISPATCH lpDisp = books.Open(strPath,covOptional,covOptional,covOptional,
                                        covOptional,covOptional,covOptional,covOptional,
                                        covOptional,covOptional,covOptional,covOptional,
                                        covOptional,covOptional,covOptional);
        //get workbook【得到book】
        book.AttachDispatch(lpDisp);
    
        //get worksheets【得到sheets】
        sheets.AttachDispatch(book.get_Worksheets());
    
        //get the current active sheet【得到sheet】
        lpDisp = book.get_ActiveSheet();
        sheet.AttachDispatch(lpDisp);
    
        //get used regional information【得到已经使用区域的原始信息】
        CRange UsedRange;
        UsedRange.AttachDispatch(sheet.get_UsedRange());
    
        //get used line numbers【得到已经使用行的数目】
        range.AttachDispatch(UsedRange.get_Rows());
        long iRowNum = range.get_Count();
    
        //get used column numbers【得到已经使用列的数目】
        range.AttachDispatch(UsedRange.get_Columns());
        long iColNum = range.get_Count();
    
        //read the starting line and column【读取行和列的开始信息】
        long iStarRow = UsedRange.get_Row();
        long iStarCol = UsedRange.get_Column();
    
        //read the table values【读取每个单元格的数值】
    
    
        for (;iStarRow <= iRowNum;iStarRow++)//控制行循环
        {
            for (iStarCol = UsedRange.get_Column();iStarCol <= iColNum;iStarCol++)//控制列循环
            {   
                //得到sheet中所有单元格,并放入range中
                range.AttachDispatch(sheet.get_Cells());
    
                //从已经被使用的单元中,选出一个,并放入range中
                range.AttachDispatch(range.get_Item(COleVariant(iStarRow),COleVariant(iStarCol)).pdispVal);
    
                //取出单元格数据放入vResult结构体中
                COleVariant vResult = range.get_Value2();
    
               //将vResult结构体变量解析成为CString类型,放入str中。
                CString str;
                if (vResult.vt == VT_BSTR)//character string【如果是中文】
                {
                    str = vResult.bstrVal;
                }
                else if (vResult.vt == VT_R8)//8 byte of digital【或是数字】
                {
                    str.Format("%f",vResult.dblVal);
                }
                else if (vResult.vt == VT_DATE)//date time【时间】
                {
                    SYSTEMTIME st;
                    VariantTimeToSystemTime(vResult.date,&st);
                }
                else if (vResult.vt == VT_EMPTY)//blank space【空白区】
                {
                    str = "";
                }
    
    
                strResultNum.first = iStarRow; //单元格行标
                strResultNum.second = iStarCol;//单元格列标
    
                strResult[strResultNum] = str;//此位置单元格的字符串
            }
        }
    
        //release
        sheet.ReleaseDispatch();//释放sheet
        sheets.ReleaseDispatch();//释放sheets
        book.Close(covOptional,COleVariant(strPath),covOptional);//关闭book
        books.Close();//关闭books
        app.Quit();//关闭app
        //CoUninitialize();
    
    
    }
    
    
    void Cfag_EXCEL00Dlg::OnBnClickedButton2()
    {
        // TODO: Add your control notification handler code here
        ::CoInitialize(NULL);
        //export to excel
    
        //get the export file path
        CFileDialog dlg(FALSE,NULL,NULL,OFN_HIDEREADONLY,"excel(*.xls)|*.xls|txt(*.txt)|*.txt|All Filter(*.*)|*.*||");
        if (IDOK != dlg.DoModal())
        {
            return ;
        }
        CString fname = dlg.GetPathName();
    
        //define the objects
        CApplication objApp;
        CWorkbooks objBooks;
        CWorkbook objBook;
        CWorksheets objSheets;
        CWorksheet objSheet;
        CRange objRange;
    
        COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR),
                    covTrue(    (short)TRUE     ),
                    covFalse(   (short)FALSE    ),
                    varFormat(  (short)-4143    ),
                    varCenter(  (short)-4108    ),
                    varLeft(    (short)-4131    ),
                    varText(    "TEXT",VT_BSTR  );
    
        //creat excel
        objApp.m_bAutoRelease = TRUE;
        if (!objApp.CreateDispatch("Excel.Application"))
        {
            AfxMessageBox("Failed to connect to excel!");
            return ;
        }
    
        //get Workbooks
        objBooks = objApp.get_Workbooks();
    
        //open excel file
        objBook.AttachDispatch(objBooks.Add(_variant_t("")));
    
        //get worksheets
        objSheets = objBook.get_Worksheets();
    
        //get worksheet【获取第一个工作表】
        objSheet = objSheets.get_Item((_variant_t)short(1));
    
        //set worksheet name
        CString sheetname = "答案设置";
        objSheet.put_Name(sheetname);
    
        //write to cells
        CString s1;
        CString s;
        pair<long,long> sNum;
        CRange objRange1;
        int row,col;
        CString strRow;
        CString strName = fname;
        char cCell;
        for (row = 1;row <= strResultNum.first;row++)
        {
            for (col = 1;col <= strResultNum.second;col++)
            {
                //get the unit head
                cCell = 'A' + col - 1;//A
                strName.Format(_T("%c"),cCell);//A
                strRow.Format(_T("%d"),row);//1
                strName += strRow;//strName=A1
    
                s1.Format(strName);
                objRange1 = objSheet.get_Range(_variant_t(s1),_variant_t(s1));//移到A1这个单元格
    
                //get the unit value【往此格里装值】
                sNum.first = row;
                sNum.second = col;
                s = strResult[sNum];
                objRange1.put_FormulaR1C1(_variant_t(s));
                //【put_FormulaR1C1】返回或设置指定对象的公式
            }
        }
    
        //save【保存一些后续的工作就行】
        objBook.SaveAs(     _variant_t(fname),
                            varFormat,
                            covOptional,
                            covOptional,
                            covOptional,
                            covOptional,
                            0,
                            covOptional,
                            covOptional,
                            covOptional,
                            covOptional,
                            covOptional
                        );
    
        //release
        objApp.Quit();
        objRange.ReleaseDispatch();
        objSheet.ReleaseDispatch();
        objSheets.ReleaseDispatch();
        objBook.ReleaseDispatch();
        objBooks.ReleaseDispatch();
    }
  • 相关阅读:
    SpringBoot优雅的全局异常处理
    react格式化展示json
    Pycharm调试按钮
    HttpURLConnection和okhttp的使用
    objection自动生成hook代码
    hookString
    python取中位数 位运算
    scrapy mongo pipeline
    xpath tips
    IT日语
  • 原文地址:https://www.cnblogs.com/fag888/p/5789086.html
Copyright © 2020-2023  润新知