VC6.0处理Excel2003的实例程序

 原代码如下// TODO: Add your control notification handler code hereAfxMessageBox("请选择输入文件");CFileDialog dlg(true,"*.xls","",OFN_HIDEREADONLY,"Excel文件(*.xls)|*.xls");CString strPath;if(dlg.DoModal()==IDOK){strPath=dlg.GetPathName();//int nPos=strPath.ReverseFind("//");int nPos=strPath.ReverseFind('//');//strPath=strPath.Left(nPos);}//AfxMessageBox(strPath);_Application app;Workbooks books;_Workbook book;Worksheets sheets;_Worksheet sheet;Range range;Range iCell;LPDISPATCH lpDisp;COleVariant vResult;COleVariant covTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);/////////////////////////初始化OLE对象if(!AfxOleInit()){AfxMessageBox("COM对象初始化失败!");return;}//启动EXCEL服务器if(!app.CreateDispatch("Excel.Application")){AfxMessageBox("启动Excel服务器失败!");return;}//AfxMessageBox("启动Excel服务器成功");//app.SetVisible(TRUE);//使EXCEL可见app.SetUserControl(TRUE);//允许其它用户使用EXCEL//////////////////////////////////////////////打开文件books.AttachDispatch(app.GetWorkbooks());lpDisp = books.Open(strPath/*"E:////测试用文件.xls"*/,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);//得到Workbookbook.AttachDispatch(lpDisp);//得到Worksheetssheets.AttachDispatch(book.GetWorksheets());//得到当前活跃的sheetlpDisp = book.GetActiveSheet();sheet.AttachDispatch(lpDisp);//读取已经使用的区域信息，行数，列数，起始行，起始列Range usedRange;usedRange.AttachDispatch(sheet.GetUsedRange());//Range是范围的意思range.AttachDispatch(usedRange.GetRows());//得到所有的行long iRowNum = range.GetCount();range.AttachDispatch(usedRange.GetColumns());//得到所有的列long iColNum = range.GetCount();//得到所有的列数long iStartRow = usedRange.GetRow();long iStartCol = usedRange.GetColumn();///////////////////////////////////////////////////////读取第一个单元格的对齐方式，数据类型:VT_I4//读取水平对齐方式range.AttachDispatch(sheet.GetCells());iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetHorizontalAlignment();if(vResult.lVal!=0){switch (vResult.lVal){case 1: //默认break;case -4108: //居中break;case -4131 : //靠左break;case -4152 : //靠右break;}}//垂直对齐方式iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetVerticalAlignment();if(vResult.lVal!=0){switch (vResult.lVal){case -4160 : //靠上break;case -4108 : //居中break;case -4107 : //靠下break;}}CString cStr1 = "任选";volatile double xxzf = 0;//选修总分long lsum = 0;while(1){range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant((long)(lsum+2)),COleVariant((long)1)).pdispVal);vResult = range.GetValue2();if(vResult.vt ==VT_EMPTY)break;range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant((long)(lsum+2)),COleVariant((long)6)).pdispVal);vResult = range.GetValue2();if(vResult.bstrVal == cStr1){range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant((long)2),COleVariant((long)5)).pdispVal);vResult = range.GetValue2();xxzf += vResult.dblVal;}lsum ++;}AfxMessageBox("读取文件");//进行第二遍的扫描 进行数据的读取。//定义数组 智育成绩 绩点学分 个人总学分 选修课学分CString xuehao[1000],cName[1000],cClass[1000];double dZychj[1000],dJdxf[1000],dGrzxf[1000],dXxkxf[1000],dpjjd[1000],dxxs[1000];long lsump = -1;CString c3Str,c32Str,c33Str,c31Str = "";double f3d ,f3d1;c3Str = "000";c33Str = "";long i=2;//初始化部分for(i=0;i<1000;i++){dZychj[i]=0;dJdxf[i]=0;dGrzxf[i]=0;dXxkxf[i]=0;dpjjd[i]=0;dxxs[i]=0;}i=2;while(1){range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)1)).pdispVal);vResult = range.GetValue2();if(vResult.bstrVal == c33Str)break;if(vResult.bstrVal == c3Str)//结果为同一个人;{//取成绩range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)7)).pdispVal);vResult = range.GetValue2();f3d = vResult.dblVal;f3d = GetValue(f3d);//得到相应的绩点range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)5)).pdispVal);vResult = range.GetValue2();f3d1 = vResult.dblVal;//处理完成个人绩点学分。dGrzxf[lsump] += f3d1;dJdxf[lsump] += f3d1*f3d;//进行个人的选修学分的累加。range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)6)).pdispVal);vResult = range.GetValue2();c31Str = vResult.bstrVal;if(c31Str == cStr1)//如是选修{//取选修学分range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)5)).pdispVal);vResult = range.GetValue2();f3d1 = vResult.dblVal;dXxkxf[lsump] +=f3d1;dxxs[lsump]++;}}else{lsump++;//使学生的总数加1//进行学号，姓名，班级的录入。range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)1)).pdispVal);vResult = range.GetValue2();c3Str = vResult.bstrVal;xuehao[lsump] = c3Str;//姓名range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)2)).pdispVal);vResult = range.GetValue2();c32Str = vResult.bstrVal;cName[lsump] = c32Str;//班级range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)3)).pdispVal);vResult = range.GetValue2();c32Str = vResult.bstrVal;cClass[lsump] = c32Str;////取成绩range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)7)).pdispVal);vResult = range.GetValue2();f3d = vResult.dblVal;f3d = GetValue(f3d);//得到相应的绩点range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)5)).pdispVal);vResult = range.GetValue2();f3d1 = vResult.dblVal;//内容为个人学分//处理完成个人绩点学分。dGrzxf[lsump] = f3d1;dJdxf[lsump] = f3d1*f3d;//进行个人的选修学分的累加。range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)6)).pdispVal);vResult = range.GetValue2();c31Str = vResult.bstrVal;if(c31Str == cStr1)//如是选修{//取选修学分;感觉一定少一个选修的门数值。用来计算某人的平均选修课学分。range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant(i),COleVariant((long)5)).pdispVal);vResult = range.GetValue2();f3d1 = vResult.dblVal;dXxkxf[lsump] =f3d1;dxxs[lsump]++;}}i++;}books.AttachDispatch(app.GetWorkbooks());//14个covOptionalAfxMessageBox("请选择输出文件");if(dlg.DoModal()==IDOK){strPath=dlg.GetPathName();//int nPos=strPath.ReverseFind("//");int nPos=strPath.ReverseFind('//');//strPath=strPath.Left(nPos);}lpDisp = books.Open(strPath/*"E:////结果.xls"*/,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);//得到Workbookbook.AttachDispatch(lpDisp);//得到Worksheetssheets.AttachDispatch(book.GetWorksheets());//得到当前活跃的sheetlpDisp = book.GetActiveSheet();sheet.AttachDispatch(lpDisp);//读取已经使用的区域信息，行数，列数，起始行，起始列usedRange.AttachDispatch(sheet.GetUsedRange());//Range是范围的意思range.AttachDispatch(usedRange.GetRows());//得到所有的行iRowNum = range.GetCount();range.AttachDispatch(usedRange.GetColumns());//得到所有的列iColNum = range.GetCount();//得到所有的列数iStartRow = usedRange.GetRow();iStartCol = usedRange.GetColumn();//加上以前的行列信息试试。AfxMessageBox("处理数据");//选修平均分double avgxuanxiu = xxzf/lsump*1.0;for(int j = 0;j<=lsump;j++){dpjjd[j] = 1.0*dJdxf[j]/dGrzxf[j];dZychj[j] = 1.0*dJdxf[j]/dGrzxf[j]*17.5;//dXxkxf[j] = 1.0*dXxkxf[j]/dxxs[j];if(dXxkxf[j]>=avgxuanxiu)dZychj[j]+=1.0*(dXxkxf[j] - avgxuanxiu)*0.4;}AfxMessageBox("数据的写回");//进行数据的写入文件。/////////////////////////////////////////////////////////////////////////////range.AttachDispatch(sheet.GetCells());iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetHorizontalAlignment();if(vResult.lVal!=0){switch (vResult.lVal){case 1: //默认break;case -4108: //居中break;case -4131 : //靠左break;case -4152 : //靠右break;}}//垂直对齐方式iCell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetVerticalAlignment();if(vResult.lVal!=0){switch (vResult.lVal){case -4160 : //靠上break;case -4108 : //居中break;case -4107 : //靠下break;}}//////////////////////////////////////////////////////////////////////////////range.SetItem(COleVariant((long)1),COleVariant((long)1),COleVariant("学号"));range.SetItem(COleVariant((long)1),COleVariant((long)2),COleVariant("姓名"));range.SetItem(COleVariant((long)1),COleVariant((long)3),COleVariant("班级"));range.SetItem(COleVariant((long)1),COleVariant((long)4),COleVariant("智育成绩"));range.SetItem(COleVariant((long)1),COleVariant((long)5),COleVariant("平均学分绩点"));range.SetItem(COleVariant((long)1),COleVariant((long)6),COleVariant("个人总学分"));range.SetItem(COleVariant((long)1),COleVariant((long)7),COleVariant("选修课学分"));range.SetItem(COleVariant((long)1),COleVariant((long)8),COleVariant("个人绩点学分"));range.SetItem(COleVariant((long)1),COleVariant((long)9),COleVariant("选修课数目"));for(long k = 2;k<=lsump+2;k++){range.SetItem(COleVariant(k),COleVariant((long)1),COleVariant(xuehao[k-2]));range.SetItem(COleVariant(k),COleVariant((long)2),COleVariant(cName[k-2]));range.SetItem(COleVariant(k),COleVariant((long)3),COleVariant(cClass[k-2]));range.SetItem(COleVariant(k),COleVariant((long)4),COleVariant(dZychj[k-2]));range.SetItem(COleVariant(k),COleVariant((long)5),COleVariant(dpjjd[k-2]));range.SetItem(COleVariant(k),COleVariant((long)6),COleVariant(dGrzxf[k-2]));range.SetItem(COleVariant(k),COleVariant((long)7),COleVariant(dXxkxf[k-2]));range.SetItem(COleVariant(k),COleVariant((long)8),COleVariant(dJdxf[k-2]));range.SetItem(COleVariant(k),COleVariant((long)9),COleVariant(dxxs[k-2]));}//关闭文件AfxMessageBox("文件的关闭");book.Close(covOptional,COleVariant(strPath/*"E:////结果.xls"*/),covOptional);books.Close();app.Quit();AfxMessageBox("完成");

在空间里有完整的工程文件。