HOW TO: 从 C++ .NET 和 MFC 实现 Excel 的自动化操作以便使用数组填充或获取某个范围的数据
http://support.microsoft.com/kb/308407
http://support.microsoft.com/kb/307473/EN-US
http://hi.baidu.com/chenlu_vc/blog/item/5d370319e1af57038718bf25.html
关于错误的提示处理
oExcel.put_DisplayAlerts(FALSE);
oExcel.put_Visible(TRUE);
oExcel.put_UserControl(TRUE);
几个方法记录
CApplication oExcel;
CWorkbook oBook;
CWorkbooks oBooks;
CWorksheets oSheets;
CWorksheet oSheet;
CRange oRange;
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
// If you have not created Excel, create a new instance.
if (oExcel.m_lpDispatch == NULL) {
oExcel.CreateDispatch(_T("Excel.Application"));
}
// Show Excel to the user.
oExcel.put_Visible(TRUE);
oExcel.put_UserControl(TRUE);
// Add a new workbook and get the first worksheet in that book.
oBooks = oExcel.get_Workbooks();
oBook = oBooks.Add(covOptional);
oSheets = oBook.get_Worksheets();
oSheet = oSheets.get_Item(COleVariant((short)1));
// Get a range of data.
oRange = oSheet.get_Range(COleVariant(_T("A1")),covOptional);
oRange = oRange.get_Resize(COleVariant((short)5),COleVariant((short)5));
COleSafeArray saRet;
DWORD numElements[2];
numElements[0] = 5;
numElements[1] = 5;
long index[2];
// Create a BSTR or double safe array.
if (m_bFillWithStrings.GetCheck())
saRet.Create(VT_BSTR,2,numElements);
else
saRet.Create(VT_R8,2,numElements);
// Fill the array with data.
for (int iRow = 1; iRow <= 5; iRow++) {
for (int iCol = 1; iCol <= 5; iCol++) {
index[0]=iRow-1;
index[1]=iCol-1;
if (m_bFillWithStrings.GetCheck()) {
CString szTemp;
szTemp.Format(_T("%d|%d"),iRow,iCol);
BSTR bstr = szTemp.AllocSysString();
saRet.PutElement(index,bstr);
SysFreeString(bstr);
} else {
double d = iRow * iCol;
saRet.PutElement(index,&d);
}
}
}
// Send the array to Excel.
oRange.put_Value(covOptional,COleVariant(saRet));
COleVariant temp_path ( _T("temp.xls"));
oExcel.put_DisplayAlerts(FALSE);
oBook.SaveAs(temp_path,covOptional,
covOptional,covOptional,
covOptional,covOptional,(long)0,covOptional,covOptional,covOptional,
covOptional,covOptional);
oBook.Close(COleVariant((short)1),covOptional,covOptional);
oBooks.Close();
oExcel.Quit();
oExcel.ReleaseDispatch();
打开已有处理类似
CWorkbook oBook;
CWorkbooks oBooks;
CWorksheets oSheets;
CWorksheet oSheet;
CRange oRange;
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
if (oExcel.m_lpDispatch == NULL) {
if(!oExcel.CreateDispatch(_T("Excel.Application")))
{
AfxMessageBox(_T("您没有安装excel"));
return;}
}
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
// if (oExcel.m_lpDispatch == NULL) {
// oExcel.CreateDispatch(_T("Excel.Application"));
// }
CString path_t;
TCHAR szFilters[]=_T("excel文件(*.xls)|*.xls|excel文件(*.xlsx)|*xlsx||");
CFileDialog fd( TRUE, _T(""), _T("*.xls"),OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT , szFilters, NULL);
if(fd.DoModal()!=IDOK)
return ;
path_t = fd.GetPathName();
oBooks = oExcel.get_Workbooks();
oBooks.Open(path_t,VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional,VOptional,VOptional,VOptional);
COleVariant index_t((short)1);
oBook = oBooks.get__Default(index_t);
if (oExcel.m_lpDispatch == NULL) {
AfxMessageBox(_T("Excel has not been started. Press button1 to start Excel."));
return;
}
// Get the first worksheet.
oSheets = oBook.get_Worksheets();
oSheet = oSheets.get_Item(COleVariant((short)1));
// Set the range of data to retrieve
oRange = oSheet.get_Range(COleVariant(_T("A3")),COleVariant(_T("F5")));
// Get the data.
COleSafeArray saRet(oRange.get_Value(covOptional));
long iRows;
long iCols;
saRet.GetUBound(1, &iRows);
saRet.GetUBound(2, &iCols);
CString valueString = _T("Array Data:\r\n");
long index[2];
// Loop through the data and report the contents.
for (int rowCounter = 1; rowCounter <= iRows; rowCounter++) {
for (int colCounter = 1; colCounter <= iCols; colCounter++) {
index[0]=rowCounter;
index[1]=colCounter;
COleVariant vData;
saRet.GetElement(index,vData);
CString szdata(vData);
valueString += szdata;
valueString +=_T("\t");
}
valueString += _T("\r\n");
}
AfxMessageBox(valueString,MB_SETFOREGROUND,NULL);
oBook.Close(COleVariant((short)0), VOptional, VOptional);
oBooks.Close();
oExcel.Quit();
oExcel.ReleaseDispatch();