操作Excel文件初始化：
a. project->add class->MFC class from typelib （项目->添加类->Typelib中的MFC类）导入Excel.exe，一般都在C:/Program Files/Microsoft Office/Office12路径下（即选择你的office安装路径下的excel.exe加入）
b. 选中以下几项_Application,_WorkSheet,_WorkBook,WorkSheets,WorkBooks,Range，然后导入；
c. 导入后自动在工程中添加CApplication,CWorkSheet,CWorkBook,CWorkSheets,CWorkBooks,CRange这些类；
然后需要把这些类的头文件中的第一句话 #import ".......EXCEL.EXE" nonamespace 删除；
引入之后如果编译遇到错误，Not enough actual parameters for macro 'DialogBoxW'.
解决方法是在CRange类中，
VARIANT DialogBox()
{
VARIANT result;
InvokeHelper(0xf5, DISPATCH_METHOD, VT_VARIANT, (void*)&result, NULL);
return result;
}
DialogBox()前面添加下划线变成_DialogBox()，解决了！
3.操作excel文件
在“资源”里面选择MFC自动建立的对话框，双击“打开”按钮（若没有就自己做一个），然后程序会将你到对这个按钮的函数里，在函数中写如下内容： （文件是CxxxDlg.cpp xxx是你的项目名称）
在这个文件的前端记得加入：
#include "CApplication.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
void Cexcel打开3Dlg::OnBnClickedOk()
{
// TODO: 在此添加控件通知处理程序代码
CApplication app;
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CRange iCell;
LPDISPATCH lpDisp;
COleVariant vResult;
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if(!app.CreateDispatch(L"Excel.Application"))
{
AfxMessageBox(L"无法启动Excel服务器!");
return;
}
books.AttachDispatch(app.get_Workbooks());
lpDisp = books.Open(“d://test.xls”,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);
//读取第一个单元格的值
range.AttachDispatch(sheet.get_Cells());
range.AttachDispatch(range.get_Item (COleVariant((long)2),COleVariant((long)1)).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(L"%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);
}
开始有一个教程没有如下代码（上一个代码框已包含）
这样会导致程序在关闭以后仍然有excel的进程存在，文件不关闭，使用以上代码后解决。
运行结果是，程序会将d:/test.xls打开，并且在对话框中输入格(1,1)的数据。
实战代码：
====打开EXCEL文件写入数据列表==========================================================================================================================================
bool CExcelOper::CreateApplication(CString szPath)
{
if (!excelApp.CreateDispatch(_T("Excel.Application"),NULL))
{ // 创建Excel2007进程
AfxMessageBox(_T("创建Excel服务失败!"));
return false;
}
excelApp.put_Visible(false);//设置为不可见
excelApp.put_DisplayAlerts(false);//禁止弹出对话框
// 获取系统时间
SYSTEMTIME systm;
CString szTime;
GetLocalTime(&systm);
szTime.Format(_T("保存时间: %4d-%.2d-%.2d %.2d:%.2d:%.2d"),systm.wYear, systm.wMonth, systm.wDay, systm.wHour, systm.wMinute, systm.wSecond);
///////////////////下面得到应用程序所在的路径///////////////////
CString TempPath;
TempPath.Format(_T("%s"), szPath);
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
excelbooks.AttachDispatch(excelApp.get_Workbooks(),TRUE);
excelBook.AttachDispatch(excelbooks.Add((_variant_t)covOptional),TRUE); //加载EXCEL模板
excelSheets.AttachDispatch(excelBook.get_Sheets(),TRUE);//加载Sheet页面
int l_ttemp = excelSheets.get_Count();
for(int m = 2;m <= l_ttemp;m++) //excel页面加载成功后，保留"参数数据"界面，删除加载excel时自动生成的其余的sheet工作页面
{
CWorksheet Singlesheet;
Singlesheet = (CWorksheet)(excelSheets.get_Item((_variant_t)2));
Singlesheet.Delete();
}
int CountCol = g_VecSaveBuffer.nAcqCount;
int CountLine = g_Width;
////////////////////////////防止出现列数大于255Excel崩掉的问题//////////////////////////////////////////////
//int Count = g_VecSaveBuffer.nAcqCount;
//int l_IQuot = CountCol / 256; //根据采集次数添加新的sheet工作页面 2015-4-21 张宁
//int l_IRema = CountCol % 256;
//if(l_IQuot > 0 && l_IRema >= 0)
//{
// for(int n = l_IQuot+1;n > l_IQuot;n--)
// {
// excelSheets.Add(vtMissing,vtMissing,_variant_t((long)n),vtMissing );
// }
//}
/////////////////////////////////////////////////////////////////////////
///////合并第一行单元格A1至E1///////////////////////////////////////
//dataRange.AttachDispatch(dataSheet.get_Range(_variant_t("A1"),_variant_t("E1")),TRUE); // 合并单元格
//dataRange.Merge(_variant_t((long)0));
///////合并第一行单元格F1至I1补充PR类型//////
//dataRange.AttachDispatch(dataSheet.get_Range(_variant_t("F1"),_variant_t("I1")),TRUE); // 合并单元格
//dataRange.Merge(_variant_t((long)0));
////////设置表格内容////////
// dataRange.AttachDispatch(dataSheet.get_Cells(),TRUE); //加载所有单元格
// dataRange.put_HorizontalAlignment(_variant_t((long)-4108));
// dataRange.put_VerticalAlignment(_variant_t((long)-4108));
VARIANT arry ; //创建安全数组,存放数据
arry.vt = VT_ARRAY|VT_VARIANT;
SAFEARRAYBOUND ttab[2];
ttab[0].lLbound =1; ttab[0].cElements = CountLine;
ttab[1].lLbound =1; ttab[1].cElements = CountCol;
arry.parray = SafeArrayCreate(VT_VARIANT,2,ttab);
//定义两个单元格，为读取Excel中的范围
VARIANT l_start;
l_start.vt = VT_BSTR;
VARIANT l_stop;
l_stop.vt = VT_BSTR;
//VARIANT l_varInt;
//l_varInt.vt = VT_I4;
VARIANT l_varDbl;
l_varDbl.vt = VT_R8;
dataSheet.AttachDispatch(excelSheets.get_Item(_variant_t((long)/*code+*/1)),TRUE);
dataRange.AttachDispatch(dataSheet.get_Cells(),TRUE);
CRange rgResault;
int u = 0;
int code = 0;
long pos[2];
for(int c = 0;c<CountCol;c++)
{
//if (c%255 == 0)
{
//for (int i = 0;i<CountCol;i++)
{
for (int j = 0;j<g_Width;j++)
{
pos[0]=j+1;
pos[1]=c+1;
l_varDbl.dblVal =g_saveExData[c*g_Width+j];
SafeArrayPutElement(arry.parray,pos,&l_varDbl);
}
}
//code++;
// u=0;
}
// u++;
/*if (u == 255)
{
l_start.bstrVal = ::SysAllocString(L"A1");
CString sStop;
sStop.Format("IU%d",CountLine);
l_stop.bstrVal = sStop.AllocSysString();
rgResault = dataRange.get_Range(l_start,l_stop);
rgResault.put_Value2(arry);
u = 0;
}*/
}
l_start.bstrVal = ::SysAllocString(L"A1");
CString sStop;
if( CountCol< 26)
sStop.Format(_T("%C%d"),(CountCol%26-1)+'A',CountLine);
else
sStop.Format(_T("%C%C%d"),(int)((CountCol/26-1)+'A'),(CountCol%26-1)+'A',CountLine);
l_stop.bstrVal = sStop.AllocSysString();
rgResault = dataRange.get_Range(l_start,l_stop);
rgResault.put_Value2(arry);
SafeArrayDestroy(arry.parray); //释放安全数组
dataSheet.AttachDispatch(excelSheets.get_Item(_variant_t((long)1)),TRUE); //选中第一个Sheet页面
dataRange.AttachDispatch(dataSheet.get_Cells());
// dataRange.put_Item(_variant_t((long)1),_variant_t((long)1),_variant_t(szTime/*szTime*/));//安全数组释放后，在EXCEL的第一页中设置系统时间
dataRange.put_HorizontalAlignment(_variant_t((long)-4108));//设置齐方式为水平垂直居中
dataRange.put_VerticalAlignment(_variant_t((long)-4108));
//////////////为表格设置边框/////////////
//保存
dataSheet.SaveAs(TempPath,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
dataRange.ReleaseDispatch(); //释放对象（相当重要！）
for(int iSheet = 0; iSheet < excelSheets.get_Count(); ++iSheet)
{
dataSheet = excelSheets.get_Item(COleVariant((long)(iSheet+1)));
dataSheet.ReleaseDispatch();
}
dataSheet.ReleaseDispatch();
excelSheets.ReleaseDispatch();
COleVariant vOptional(long(DISP_E_PARAMNOTFOUND), VT_ERROR);
excelBook.Close(COleVariant(long(0)), vOptional, vOptional);
excelbooks.Close();
excelBook.ReleaseDispatch();
excelbooks.ReleaseDispatch();
excelApp.ReleaseDispatch(); //释放excelApp
excelApp.Quit(); //退出程序
return true;
}
=====打开EXCEL文件导入数据到程序内存========================================================================================================================
int CExcelOper::LoadExcelFile(CString szPath)
{
if (!excelApp.CreateDispatch(_T("Excel.Application"),NULL))
{
AfxMessageBox(_T("创建Excel服务失败!"));
return -1;
}
excelApp.put_Visible(FALSE);
excelApp.put_DisplayAlerts(FALSE);//禁止弹出对话框
excelbooks.AttachDispatch(excelApp.get_Workbooks(),TRUE);
LPDISPATCH lpDis = NULL;
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
//打开文件
lpDis = excelbooks.Open(szPath,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,
covOptional);
//获得WorkBook工作薄
excelBook.AttachDispatch(lpDis,TRUE);
//获得Sheets工作页数
long lgUsedsheetCount = 1;
excelSheets.AttachDispatch(excelBook.get_Sheets(),TRUE);//加载Sheet页面
lgUsedsheetCount = excelSheets.get_Count();
long lgcurUsedRowNum = 0;
long lgcurUsedColumnNum = 0;
VARIANT VsCell,VeCell;
VsCell.vt = VT_BSTR,VeCell.vt = VT_BSTR;
VARIANT l_varDbl;
l_varDbl.vt = VT_R8;
VARIANT arrd;
long larrRow,larrCol;
///*arrd = dataRange.get_Value(covOptional);*/
//COleSafeArray sabRead(arrd);//将EXCEL文件数据导入安全数组中
//---读取excel文件考虑读取多个Sheet页的情况---
//tagSaveData saveEData;
int iCount;
//for (int shCount = 0;shCount<lgUsedsheetCount;shCount++)
{
dataSheet.AttachDispatch(excelSheets.get_Item(_variant_t((long)/*shCount+*/1)),TRUE);//当前Sheet工作表
//获得WorkSheet工作表
//dataSheet.AttachDispatch(excelBook.get_ActiveSheet(),TRUE);
//获得Range区域
dataRange.AttachDispatch(dataSheet.get_UsedRange(),TRUE);
//获得有效行数
dataRange.AttachDispatch(dataRange.get_Rows(),TRUE);
lgcurUsedRowNum = dataRange.get_Count();
g_icurExcelRow = lgcurUsedRowNum;
//获得有效列数
dataRange.AttachDispatch(dataRange.get_Columns(),TRUE);
lgcurUsedColumnNum = dataRange.get_Count();
g_icurExcelCol = lgcurUsedColumnNum;
//g_icurExcelCol = lgUsedColumnNum;
//获取Sheet页的名称
CString strSheetName = dataSheet.get_Name();
//获取全部Cells,此时dataRange是Cells的集合
dataRange.AttachDispatch(dataSheet.get_Cells(),TRUE);
g_iTempBuffer = new float[g_icurExcelRow*lgcurUsedColumnNum];
/*memcpy(g_iTempBuffer,0,)*/
CString SzsCell,SzeCell;
SzsCell = _T("A1");
if( lgcurUsedColumnNum< 26)
SzeCell.Format(_T("%C%d"),(lgcurUsedColumnNum%26-1)+'A',lgcurUsedRowNum);
else
SzeCell.Format(_T("%C%C%d"),(int)((lgcurUsedColumnNum/26-1)+'A'),(lgcurUsedColumnNum%26-1)+'A',lgcurUsedRowNum);
VsCell.bstrVal = SzsCell.AllocSysString();
VeCell.bstrVal = SzeCell.AllocSysString();
dataRange = dataSheet.get_Range(VsCell,VeCell);
//VARIANT arrd;
//long larrRow,larrCol;
arrd = dataRange.get_Value(covOptional);
COleSafeArray sabRead(arrd);//将EXCEL文件数据导入安全数组中
sabRead.GetUBound(1,&larrRow);//获取安全数组的行列数
sabRead.GetUBound(2,&larrCol);
iCount = lgcurUsedRowNum*lgcurUsedColumnNum;
//memcpy(g_iTempBuffer,0,iCount);
int dex = 0;
for (int j=1;j<=larrCol;j++)
{
for (int i = 1;i<=larrRow;i++)
{
long pos[] = {i,j};
sabRead.GetElement(pos,&l_varDbl);
/*int curI = varInt.intVal;
BYTE cueB = (BYTE)varInt.intVal;*/
g_iTempBuffer[(j-1)*larrRow+(i-1)] = l_varDbl.dblVal;
/*CString str;
str.Format(_T("%d"),g_iTempBuffer[dex]);
AfxMessageBox(str);*/
dex++;
}
}
/*saveEData.pData = new BYTE[lgcurUsedRowNum*lgcurUsedColumnNum];
saveEData.uiDatasize = iCount;
saveEData.uiSaveStart = 0;
saveEData.cstrSavePath = _T("E:\\");
memcpy(saveEData.pData,g_iTempBuffer,iCount);
g_VecSaveBuffer.AppendData(saveEData);*/
}
//关闭EXCEL文件
excelBook.Close(covOptional,COleVariant(szPath),covOptional);
excelbooks.Close();
//释放对象
dataRange.ReleaseDispatch();
dataSheet.ReleaseDispatch();
excelSheets.ReleaseDispatch();
excelBook.ReleaseDispatch();
excelbooks.ReleaseDispatch();
excelApp.ReleaseDispatch();
excelApp.Quit();
return 1;
}
浙公网安备 33010602011771号