XP下VS2005导入excel类

1, 通过添加类选择OLE Type Library, 然后选择文件,添加进相应的olb或exe, 例如C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE

2, 添加相应的类,例如:

CApplication
CWorkbook
CWorkbooks
CWorksheet
CRange
CWorksheets
CPicture
CPictures
CBorder
CBorders
CFont0
Cnterior

3, 实现一个简单的应用:

 

void CGM_ServerDlg::OnBnClickedOk()
{
 // TODO: 在此添加控件通知处理程序代码
 CApplication m_appExcel;       // Excel应用程序
 CWorkbooks m_books;
 CWorkbook m_book;
 CWorksheets sheets;
 CWorksheet sheet;
 CRange range;          //选择范围
 Cnterior interior;
 CFont0 font;               // 字体
 CBorders borders;         // 边框
 CBorder border;
 CRange column;
 CRange row;
 // 初始化Com
 if (::CoInitialize( NULL ) == E_INVALIDARG)
 {
  MessageBox("初始化Com失败!");
 }

 // 启动Excel
 if ( !m_appExcel.CreateDispatch(_T("Excel.Application"), NULL))
 {
  MessageBox(_T("创建Excel失败!"));
  ::CoUninitialize();
 }
 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
 m_appExcel.put_Visible(TRUE);
 m_books.AttachDispatch(m_appExcel.get_Workbooks());
 m_book.AttachDispatch(m_books.Add(covOptional));
 sheets.AttachDispatch(m_book.get_Worksheets()); //得到Worksheets
 sheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1"))); //得到sheet1
 sheet.put_Name("1234"); //sheet1改名

 //所有单元格颜色设为白色
 range.AttachDispatch(sheet.get_Cells());
 interior.AttachDispatch(range.get_Interior());
 interior.put_Color(VT(RGB(255, 255, 255)));
 interior.ReleaseDispatch();

 range.ClearContents();
 range.ReleaseDispatch();

 range.AttachDispatch(sheet.get_Range(VT("A1"), VT("C1001")));
 range.ClearFormats();
 //插入数据
 range.put_Item(VT(1), VT(1), VT("函数"));
 range.put_Item(VT(1), VT(2), VT("大项目"));
 range.put_Item(VT(1), VT(3), VT("小项目"));
 for (int i = 2; i < 1000; i++)
 {
  range.put_Item(VT(i), VT(2), VT(i - 1));
  range.put_Item(VT(i), VT(3), VT("37122368~37097735~"));
 }

 // 为四周和内部加上边框
 borders.AttachDispatch(range.get_Borders());
 for (long i = xlEdgeLeft; i <= xlInsideHorizontal; i++)
 {
  border = borders.get_Item(i);
  border.put_LineStyle(VT(xlContinuous));
  border.ReleaseDispatch();
 }
 borders.ReleaseDispatch();

 //调整列宽
 column = range.get_EntireColumn();
 column.put_ColumnWidth(VT(18.63));
 column.ReleaseDispatch();
 range.ReleaseDispatch();

 range.AttachDispatch(sheet.get_Range(VT("A10"), VT("A20"))); //选中
 range.Merge(VT(0)); //合并单元格
 range.ReleaseDispatch();

 range.AttachDispatch(sheet.get_Range(VT("A1"), VT("C1")));
 interior.AttachDispatch(range.get_Interior());
 interior.put_ColorIndex(VT(7));
 interior.put_Pattern(VT(xlPatternSolid));
 interior.ReleaseDispatch();

 font.AttachDispatch(range.get_Font());
 font.put_ColorIndex(VT(6));
 font.get_Bold();
 font.ReleaseDispatch();
 range.ReleaseDispatch();

 range.AttachDispatch(sheet.get_Range(VT("A2"), VT("C1001")));  //设置range对象的范围
 interior.AttachDispatch(range.get_Interior());  //选择表格内部
 interior.put_ColorIndex(VT(13));   //颜色
 interior.put_Pattern(VT(xlPatternSolid));  //加粗
 interior.ReleaseDispatch();

 font.AttachDispatch(range.get_Font());  //选择字
 font.put_ColorIndex(VT(3));  //设置字颜色
 font.ReleaseDispatch();

 row.AttachDispatch(range.get_EntireRow()); //选择range里的全部行
 row.put_RowHeight(VT(24));  //行高
 row.ReleaseDispatch();
 range.ReleaseDispatch();

 sheet.ReleaseDispatch();
 sheets.ReleaseDispatch();
 m_book.ReleaseDispatch();
 m_books.ReleaseDispatch();
 m_appExcel.ReleaseDispatch();

}

 

4, 编译. 报了一大堆错.

解决方法: 打开CApplication.h文件

把原先的import路径注解掉. 例如注解掉//#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE" no_namespace

换成:

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" raw_interfaces_only  rename_namespace("VBE6")
#import "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL" raw_interfaces_only  rename_namespace("Office")
#import "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" raw_interfaces_only,named_guids rename("RGB","RGBEx"),rename("DialogBox","DialogBoxEx"),rename_namespace("ExcelEx") exclude("IFont","IPicture")
#import "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB" raw_interfaces_only  rename("ExitWindows","ExitWindowsEx"),named_guids,rename_namespace("MSWord")
using namespace VBE6;
using namespace Office;
using namespace ExcelEx;
using namespace MSWord;

 

5,  再编译, 发现报_IMsoDispObj未定义基类的错.

怀疑可能是执行次序的问题, 修改如下:

// 从类型库向导中用“添加类”创建的计算机生成的 IDispatch 包装类
#pragma once

//#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE" no_namespace
#import "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL" raw_interfaces_only  rename_namespace("Office")
using namespace Office;

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" raw_interfaces_only  rename_namespace("VBE6")
using namespace VBE6;

#import "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" raw_interfaces_only,named_guids rename("RGB","RGBEx"),rename("DialogBox","DialogBoxEx"),rename_namespace("ExcelEx") exclude("IFont","IPicture")
using namespace ExcelEx;

#import "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB" raw_interfaces_only  rename("ExitWindows","ExitWindowsEx"),named_guids,rename_namespace("MSWord")
using namespace MSWord;

 

 

6, 再编译. 通过