利用COM编程兑现在VC中对Excel2003的导入和导出

利用COM编程实现在VC中对Excel2003的导入和导出

1、导入excell.exe相关组件：

a、在添加类中选择typelib中的MFC类；

b、选择office安装目录下的excell.exe；

c、导入如下的组件：

需要选_Application、_Workbook、Workbooks、_Worksheet、Worksheets、Range

2、在stdafx.h下添加如下头文件

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

3、导入函数

map<pair<long,long>,CString> strResult;//保存结果
pair<long,long> strResultNum;//保存结果所在的位置

::CoInitialize(NULL);
	CApplication app;
	CRange range;
	CWorkbooks books;
	CWorksheets sheets;
	CWorkbook book;
	CWorksheet sheet;
	//star excel
	if (!app.CreateDispatch(L"Excel.Application"))
	{
		AfxMessageBox(L"无法启动Excel启动器");
		return ;
	}
	//open *.xls
	books.AttachDispatch(app.get_Workbooks());
	CString strPath = L"c:\\1.xls";//从c:\\1.xls中读取
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	LPDISPATCH lpDisp = books.Open(strPath,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional);
	//get workbook
	book.AttachDispatch(lpDisp);

	//get worksheets
	sheets.AttachDispatch(book.get_Worksheets());
	//get the current active 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++)
		{
			range.AttachDispatch(sheet.get_Cells());
			range.AttachDispatch(range.get_Item(COleVariant(iStarRow),COleVariant(iStarCol)).pdispVal);
			COleVariant vResult = range.get_Value2();
			CString str;
			if (vResult.vt == VT_BSTR)//character string
			{
				str = vResult.bstrVal;
			}
			else if (vResult.vt == VT_R8)//8 byte of digital
			{
				str.Format(L"%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();
	sheets.ReleaseDispatch();
	book.Close(covOptional,COleVariant(strPath),covOptional);
	books.Close();
	app.Quit();

4、导出函数：

::CoInitialize(NULL);
	//export to excel
	//get the export file path
	
	CString fname = L"c:\\2.xls";//保存到c:\\2.xls
	//define the objects
	CApplication objApp;
	CWorkbooks objBooks;
	CWorkbook objBook;
	CWorksheets objSheets;
	CWorksheet objSheet;
	CRange objRange;
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
	COleVariant covTrue((short)TRUE),
		covFalse((short)FALSE),
		varFormat((short)-4143),
		varCenter((short)-4108),
		varLeft((short)-4131),
		varText(L"TEXT",VT_BSTR);
	//creat excel
	objApp.m_bAutoRelease = TRUE;
	if (!objApp.CreateDispatch(L"Excel.Application"))
	{
		AfxMessageBox(L"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 = L"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;
			strName.Format(_T("%c"),cCell);
			strRow.Format(_T("%d"),row);
			strName += strRow;
			s1.Format(strName);
			objRange1 = objSheet.get_Range(_variant_t(s1),_variant_t(s1));
			//get the unit value
			sNum.first = row;
			sNum.second = col;
			s = strResult[sNum];
			objRange1.put_FormulaR1C1(_variant_t(s));
		}
	}
	//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();