MFC向Excel读取、写入数据(OLE/COM)

原文链接：http://blog.csdn.net/u014023993/article/details/25799877

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

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();

来自CODE的代码片

.cpp

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

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);

来自CODE的代码片

.cpp

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);可代替上面一行