bool CopySheet(CHAR szItem[20][64],char *szModelFilePath)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
::CoInitialize(NULL);
AfxEnableControlContainer();
CApplication app;
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CRange cols;
int iNum = 0;
CString csSheetName = szItem[0];
CHAR szNewFilePath[MAX_PATH] = {0x00};
for (iNum = 0;iNum < 20;iNum++)
{
if(strlen(szItem[iNum]) == 0)
break;
}
::GetCurrentDirectory(MAX_PATH,szNewFilePath);
sprintf(szNewFilePath,"%s\\file.xlsx",szNewFilePath);
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if( !app.CreateDispatch("Excel.Application") )
{
::MessageBox(NULL,"Can not create EXCEL!","error",MB_OK);
return false;
}
books=app.get_Workbooks();
LPDISPATCH lpDisp;
lpDisp = books.Open(szModelFilePath,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional
);
book.AttachDispatch(lpDisp);
sheets=book.get_Sheets();
sheet=sheets.get_Item(COleVariant((short)3));
COleVariant temp;
temp.pdispVal=sheet.m_lpDispatch;
temp.vt=VT_DISPATCH;
sheet.put_Name(csSheetName.Left(20));
for(int i = 0;i < iNum-1;i++)
sheet.Copy(vtMissing, temp);
for(int i = 0;i < iNum;i++)//复制N个sheet
{
csSheetName = szItem[i];
sheet=sheets.get_Item(COleVariant((short)(i+3)));
sheet.put_Name(csSheetName.Left(31));//31是Excel允许的最大sheet名长度
}
book.SaveAs(COleVariant(szNewFilePath),covOptional,
covOptional,covOptional,
covOptional,covOptional,(long)0,
covOptional,covOptional,covOptional,
covOptional,covOptional);
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
app.Quit();
app.ReleaseDispatch();
temp.Detach();
temp.Clear();
sprintf(szModelFilePath,"%s",szNewFilePath);
::CoUninitialize();
return true;
}
常用代码备份--OLE操作Excel之sheet复制
最新推荐文章于 2023-09-24 17:43:41 发布