MFC向Excel读写数据详细方法
MFC读写Excel详细步骤
准备工作：
打开开发环境VC或VS新建一个基于对话框的简单工程；通过类向导添加类-->类型库中的MFC类
打开创建类对话框
选择文件，位置编辑框填入Excel.exe的详细目录，可以通过浏览选择，在接口下方选择_Application（应用），_Workbook（视窗），Workbooks（整个视窗），Worksheets（单个表单），Worksheets（整个表单），Range（元素集合）六个基本的接口并生成类。选择接口后单击向右的单箭头即可，双箭头是生成所有接口的类；
生成类之后将添加的几个类的头文件中的
#import "E:\\Microsoft Office\\Office14\\EXCEL.EXE" no_namespace
注释掉，加上#include
然后编译，出现下面这俩个错误；点击DialodBoxA找到错误位置将DialodBox改为_DialodBox;再编译，没有错误；
warning C4003: “DialogBoxA”宏的实参不足
error C2059: 语法错误:“,”
在要操作Excel类的CPP文件中包含头文件：
准备工作完成。
向Excel写数据:
CFileDialog dlg(FALSE, ".xlsx",NULL,
OFN_FILEMUSTEXIST|OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,"Microsoft Excel
Files(*.xls)|*.xls|Microsoft Excel Files(*.xlsx)(推荐)|*.xlsx|All Files (*.*)|*.*||");
dlg.m_ofn.lpstrTitle ="保存到Excel";
if (dlg.DoModal() == IDOK)
{
//获取路径
CString strFileName=dlg.GetPathName();
CWorkbooks books;
CWorkbook book;
CApplication app;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CMyFont font;
CRange cols;
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if (!app.CreateDispatch("Excel.Application"))
{
this->MessageBox("无法创建Excel应用！");
return;
}
books=app.get_Workbooks();
book=books.Add(covOptional);
sheets=book.get_Worksheets();
sheet=sheets.get_Item(COleVariant((short)1));
//获得（A,1）(B,1)两个单元格
range=sheet.get_Range(COleVariant("A1"),COleVariant("B6"));
//设置公式“=RAND()*100000”
range.put_Formula(COleVariant("=RAND()*1000"));
//选择整列，并设置宽度为自适应
cols = range.get_EntireColumn();
cols.AutoFit();
//设置字体为粗体
font = range.get_Font();
font.put_Bold(COleVariant((short)TRUE));
//设置数字格式为货币型
//range.put_NumberFormat(COleVariant("$0.00"));
CString Range327[]={"C1","C2","C3","C4","C5","C6"};
CString fileinfo[]={"齿形","齿向","齿距","左齿面","右齿面","径跳"};
for(int i=0;i<6;i++)
{
range = sheet.get_Range(COleVariant(Range327[i]),
COleVariant(Range327[i]));
range.put_Value2(COleVariant(fileinfo[i]));
}
/*
//获得坐标为（C，2）单元格 //显示Excel表
range = sheet.get_Range(COleVariant("C1"), COleVariant("F10"));
//设置单元格内容位Hello Excel
range.put_Value2(COleVariant("哈尔滨精达测量仪器有限公司"));
*/
//选择整列，并设置宽度为自适应
cols = range.get_EntireColumn();
cols.AutoFit();
//显示列表控件
//app.put_Visible(TRUE);
//允许其他用户控制Excel，否则Excel、将一闪而过
//app.put_UserControl(TRUE);
book.SaveCopyAs(COleVariant(strFileName));
book.put_Saved(true);
range.ReleaseDispatch();
font.ReleaseDispatch();
cols.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
//以下关闭应用的两条代码结合使用且顺序不能反，否则无法关闭进程
app.Quit();
app.ReleaseDispatch();
}
读取Excel中的数据：
UpdateData(TRUE);
str327="";
UpdateData(FALSE);
CApplication app;
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CRange oCurCell;
CString strFileName1;
CFileDialog dlg(TRUE, ".xlsx",NULL,
OFN_FILEMUSTEXIST|OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,"Microsoft Excel
Files(*.xls)|*.xls|Microsoft Excel Files(*.xlsx)|*.xlsx|All Files (*.*)|*.*||");
dlg.m_ofn.lpstrTitle = "打开Excel文件" ;
int nRetVal = dlg.DoModal();
if ( nRetVal == IDOK )
{
if (!app.CreateDispatch( _T( "Excel.Application" ), NULL ) )
{
::MessageBox( NULL, _T( "创建Excel服务失败！" ), _T( "错误提示！" ), MB_OK | MB_ICONERROR);
return;
//exit(1);
}
//设置为显示
app.put_Visible(FALSE);
books.AttachDispatch(app.get_Workbooks(), TRUE );
LPDISPATCH lpDisp = NULL;
COleVariant covTrue((short)TRUE);
COleVariant covFalse((short)FALSE);
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
// 打开文件
CString strFilePath=dlg.GetPathName();
strFileName1=strFilePath;
//CStringstrFilePath="G:\\研发人员名单2016.xls";
lpDisp = books.Open( strFilePath,
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing),
_variant_t(vtMissing) );
// 获得活动的WorkBook( 工作簿 )
book.AttachDispatch(lpDisp, TRUE );
// 获得活动的WorkSheet( 工作表 )
sheet.AttachDispatch(book.get_ActiveSheet(), TRUE );
// 获得使用的区域Range( 区域 )
range.AttachDispatch(sheet.get_UsedRange(), TRUE );
// 获得使用的行数
long lgUsedRowNum = 0;
range.AttachDispatch(range.get_Rows(), TRUE );
lgUsedRowNum = range.get_Count();
// 获得使用的列数
long lgUsedColumnNum = 0;
range.AttachDispatch(range.get_Columns(), TRUE );
lgUsedColumnNum = range.get_Count();
// 读取Sheet的名称
CString strSheetName = sheet.get_Name();
//得到全部Cells，此时,CurrRange是cells的集合
range.AttachDispatch(sheet.get_Cells(), TRUE );
// 遍历整个Excel表格
CStringArray* arrayStr;
arrayStr = new CStringArray[lgUsedRowNum];
for ( int i = 0; i { for ( int j = 1; j <= lgUsedColumnNum; )//遍历列 { oCurCell.AttachDispatch( range.get_Item( COleVariant( (long)(i + 1)), COleVariant( (long)j ) ).pdispVal, TRUE ); VARIANT varItemName = oCurCell.get_Text(); CString strItemName; strItemName = varItemName.bstrVal; // AfxMessageBox(strItemName ); // 判断是否是合并的单元格 VARIANT varMerge = oCurCell.get_MergeCells(); CString PosInfo1[]={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S" ,"T"}; CString PosInfo2[]={"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17" ,"18","19","20"}; if ( varMerge.boolVal == -1 ) { AfxMessageBox( PosInfo1[j-1]+PosInfo2[i]+"是合并的单元格!" ); } // else if ( varMerge.boolVal == 0 ) // { // AfxMessageBox( _T( "不是合并的单元格!" ) ); // } arrayStr[i].Add( strItemName ); j++; } i++; } if(lgUsedColumnNum*lgUsedRowNum>=300) { MessageBox("文件过大，存储空间不足，无法读取！"); goto LLL; } CString strTempValue[20][20]; for ( int i = 0; i { for (int j = 0; j { strTempValue[i][j]=arrayStr[j].GetAt(i); } } // 释放二维数组 delete[] arrayStr; /********************************************************************************** ******************/ //这里设置了字符串类型，应该还能设置其它类型参数，比如说整型。解开上面代码中的注释， 可以尝试下其它功能。参考资源[1] //释放资源 // book.SaveCopyAs(COleVariant(strFilePath)); // book.put_Saved(true); book.ReleaseDispatch(); books.ReleaseDispatch(); app.Quit(); app.ReleaseDispatch(); long size327=lgUsedColumnNum*lgUsedRowNum; datafloat=atof(strTempValue[0][0]); UpdateData(TRUE); for ( int i = 0; i { if (i!=0) { str327=str327+"\r\n"; } for (int j = 0; j { if (!strTempValue[i][j].IsEmpty()) { str327=str327+"\r\n"+strTempValue[i][j]; } } } m_ddd=datafloat; UpdateData(FALSE); } LLL: /*******************资源释放***********************/ //book.SaveCopyAs(COleVariant(strFileName1)); //book.put_Saved(true); //不显示任何警告对话框 //在保存文件代码之前加上下面两句语句 app.put_AlertBeforeOverwriting(false); app.put_DisplayAlerts(false); //将对应Excel进程关闭 range.ReleaseDispatch(); oCurCell.ReleaseDispatch(); sheet.ReleaseDispatch(); sheets.ReleaseDispatch(); book.ReleaseDispatch(); books.ReleaseDispatch(); //以下关闭应用的两条代码结合使用且顺序不能反，否则无法关闭进程app.Quit(); app.ReleaseDispatch();