vs2010操作 office2010自我笔记

来源：互联网发布：社工数据库手机号编辑：程序博客网时间：2024/05/22 09:50

【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 Office\Office14\EXCEL.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();}

0 0