Excel编程基础操作汇总

来源：互联网发布：淘宝网春秋女装编辑：程序博客网时间：2024/06/02 03:26

一、变量定义与初始化

LPDISPATCHlpDisp;CRangem_myRange;CWorkbookm_myBook;CWorkbooksm_myBooks;CWorksheetm_mySheet;CWorksheetsm_mySheets;CApplicationm_myApp;COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);if(!m_myApp.CreateDispatch(_T("Excel.Application"))){AfxMessageBox(_T("无法创建Excel应用"));return false;}m_myBooks.AttachDispatch(m_myApp.get_Workbooks());CString strFileName("test.xls");CString strSheetName("英语四级");

二、基础工作簿操作

/////////////////////////////////////////////////////////////////////////////////////////////////基础工作簿操作////////////////////////////////////////////////////////////////////////////////////////////long nItem;long nItemCount;//打开工作簿（Excel文件）try{lpDisp = m_myBooks.Open(strFileName, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional,  covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional);m_myBook.AttachDispatch(lpDisp);}catch (_com_error e){CString strMsg;strMsg.Format("%s", e.ErrorMessage());AfxMessageBox(strMsg);return false;}//新建工作簿（Excel文件）try{lpDisp = m_myBooks.Add(covOptional);m_myBook.AttachDispatch(lpDisp);}catch (_com_error e){CString strMsg;strMsg.Format("%s", e.ErrorMessage());AfxMessageBox(strMsg);return false;}//保存工作簿（保存Excel文件）--默认保存路径为Excel默认保存路径，不是程序当前路径，这个必须注意m_myBook.SaveAs(_variant_t(strFileName), covOptional, covOptional, covOptional, covOptional, covOptional, (long)0, covOptional, covOptional, covOptional, covOptional, covOptional);//保存工作簿一个副本m_myBook.SaveCopyAs(_variant_t(strFileName));//获取当前工作簿数量nItemCount = m_myBooks.get_Count();//获得指定工作簿nItem = 1;//在Excel中所有的序号都是从1开始，而不是从0开始if(nItem <= nItemCount){lpDisp = m_myBooks.get_Item(_variant_t(nItem));m_myBook.AttachDispatch(lpDisp);}//关闭工作簿m_myBook.Close(covOptional, covOptional, covOptional);

三、基础工作表操作

///////////////////////////////////////////////////基础工作表操作//////////////////////////////////////////////long nSheet;long nSheetCount;//获取当前工作表集lpDisp = m_myBook.get_Worksheets();m_mySheets.AttachDispatch(lpDisp);//获得当前工作表数量nSheetCount = m_mySheets.get_Count();//获得当前工作表lpDisp = m_myBook.get_ActiveSheet();m_mySheet.AttachDispatch(lpDisp);//获得指定工作表nSheet = 2;lpDisp = m_mySheets.get_Item(_variant_t(nSheet));//lpDisp = m_mySheets.get_Item(_variant_t(strSheetName));m_mySheet.AttachDispatch(lpDisp);//获取当前工作表名称strSheetName = m_mySheet.get_Name();//设置/更改当前工作表名称m_mySheet.put_Name(strSheetName);//添加工作表--在所有工作表之后添加一个工作表lpDisp = m_mySheets.Add(covOptional, _variant_t(nSheetCount), _variant_t((long)1), covOptional);m_mySheet.AttachDispatch(lpDisp);//删除指定工作表nItem = 5;lpDisp = m_mySheets.get_Item(_variant_t(nItem));//lpDisp = m_mySheets.get_Item(_variant_t(strSheetName));m_mySheet.AttachDispatch(lpDisp);m_mySheet.Delete();

四、基础单元格操作

///////////////////////////////////////////////////基本单元格操作//////////////////////////////////////////////long nRow, nCol;long nRowCount, nColCount;VARIANT vResult;CString strValue;//获取当前工作表获得区域lpDisp = m_mySheet.get_Cells();m_myRange.AttachDispatch(lpDisp);//获得当前工作表数据的行数lpDisp = m_myRange.get_Rows();m_myRange.AttachDispatch(lpDisp);nRowCount = m_myRange.get_Count();//获的当前工作表数据的列数lpDisp = m_myRange.get_Columns();m_myRange.AttachDispatch(lpDisp);nColCount = m_myRange.get_Count();//获取指定单元格的数据（字面数据，且以字符的形式读出）nRow = 2;nCol = 3;vResult = m_myRange.get_Item(_variant_t(nRow), _variant_t(nCol));if(vResult.vt == VT_DISPATCH){VARIANT values;m_myRange.AttachDispatch(vResult.pdispVal, TRUE);values = m_myRange.get_Text();strValue = values.bstrVal;}//设置/更改指定单元格数据nRow = 1;nCol = 5;//特别注意，Excel的所有序号都是从1开始m_myRange.put_Item(_variant_t(nRow), _variant_t(nCol), _variant_t(strValue));

0 0