MFC向Excel读取、写入数据

通过MFC向Excel写入数据代码：

    CWorkbooks    books;
	CWorkbook     book;
	CApplication  app;
	CWorksheets   sheets;
	CWorksheet    sheet;
	CRange        range;

	COleVariant   vResult;
	COleVariant   covOptional((long) DISP_E_PARAMNOTFOUND,VT_ERROR);

	if (!app.CreateDispatch(_T("Excel.Application")))
	{
		AfxMessageBox(_T("无法启动服务器"));
		return ;
	}

	books.AttachDispatch(app.get_Workbooks()); 

	//得到Workbook  
	book.AttachDispatch(books.Add(covOptional),true);  

	//得到Worksheets  
	sheets.AttachDispatch(book.get_Worksheets(),true);  
	
	sheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1")),true); 
	range=sheet.get_Range(COleVariant(_T("A1")),COleVariant(_T("A1")));
	range.put_Value2(COleVariant(_T(" Write data to excel by MFC")));

// 	for (long i=0;i<11;i++)
// 		range.put_Item(_variant_t((long)2),_variant_t((long)i),_variant_t((long)i));//会报错，不知问题为何
	range=sheet.get_Range(COleVariant(_T("A1")),COleVariant(_T("B1")));
	range.Merge(_variant_t((long)0));	
	
	range.put_ColumnWidth(_variant_t((long)15));
	range=sheet.get_Range(COleVariant("A1"),COleVariant("A1"));
	//range.put_VerticalAlignment(_variant_t((long)-4018));//不能设置类range的put_VerticalAlignment属性
	range.put_HorizontalAlignment(_variant_t((long)-4131));
	range.put_RowHeight(COleVariant("25"));
	
	app.put_Visible(TRUE);
	book.SaveCopyAs(COleVariant("e:\\test_file.xlsx"));
	book.put_Saved(TRUE);

	books.Close();
	app.Quit();
	range.ReleaseDispatch();
	sheet.ReleaseDispatch();
	sheets.ReleaseDispatch();
	book.ReleaseDispatch();
	books.ReleaseDispatch();
	app.ReleaseDispatch();

 

通过MFC向Excel读取数据代码：

        CApplication app;  
        CWorkbooks books;  
        CWorkbook book;  
        CWorksheets sheets;  
        CWorksheet sheet;  
        CRange range;  
        
        LPDISPATCH lpDisp;  
        COleVariant vResult;  
        COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);  
        
		if(!app.CreateDispatch(_T("Excel.Application")))  
        {  
        
			AfxMessageBox(_T("无法启动Excel服务器!"));  
            return;  
        }  
       books.AttachDispatch(app.get_Workbooks());  
       lpDisp = books.Open("E:\\test_big_file.xlsx",covOptional, covOptional, covOptional, covOptional, 
       covOptional,covOptional, covOptional, covOptional, covOptional, covOptional,covOptional, covOptional, 
       covOptional,covOptional);  
          
        //得到Workbook  
        book.AttachDispatch(lpDisp);  

        //得到Worksheets  
        sheets.AttachDispatch(book.get_Worksheets());  
       
        //得到当前活跃sheet  
        //如果有单元格正处于编辑状态中，此操作不能返回，会一直等待  
       lpDisp=book.get_ActiveSheet();
	   sheet.AttachDispatch(lpDisp);
			
		//读取4行4列单元格的值  
       range.AttachDispatch(sheet.get_Cells());  
       range.AttachDispatch(range.get_Item (COleVariant((long)4),COleVariant((long)4)).pdispVal );  
        /*COleVariant*/ 
		vResult =range.get_Value2();  
        CString str;  
        if(vResult.vt == VT_BSTR) //字符串  
        {  
            str=vResult.bstrVal;  
        }  
        else if (vResult.vt==VT_R8) //8字节的数字  
        {  
           str.Format("%f",vResult.dblVal);  
        }  
       
        else if(vResult.vt==VT_DATE) //时间格式 
        { 
          SYSTEMTIME st; 
          VariantTimeToSystemTime(vResult.date, &st); 
        } 
        else if(vResult.vt==VT_EMPTY) //单元格空的 
        { 
           str=""; 
        } 

        books.Close();   
        app.Quit();             // 退出  
        //释放对象    
        range.ReleaseDispatch();  
        sheet.ReleaseDispatch();  
        sheets.ReleaseDispatch();  
        book.ReleaseDispatch();  
        books.ReleaseDispatch();  
        app.ReleaseDispatch();  
       // OnOK();  
        MessageBox(str);

 

books = app.get_Workbooks();

//books.AttachDispatch(app.get_Workbooks());可代替上面一行

book = books.Add(covOptional);

//book.AttachDispatch(books.Add(covOptional),true);可代替上面一行

sheets=book.get_Worksheets(); VT_ERROR);

//sheets.AttachDispatch(book.get_Worksheets(),true);可代替上面一行

sheet =sheets.get_Item(COleVariant((short)1));

//sheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1")),true);可代替上面一行



扫描二维码，关注“清远的梦呓”公众号，在手机端查看文章