开通博客赚积分
发布资源赚积分
分类

源码开发语言/平台
当前位置：首页 > 源码/资料 > 行业应用 > 百货/超市行业 > 查看源码
MyExcel.cpp
资源名称：chaoshiguanli.rar　[点击查看]
上传用户：jzscgs158
上传日期：2022-05-25
资源大小：8709k
文件大小：14k
源码类别：

百货/超市行业

开发平台：

Visual C++

MyExcel.cpp：源码内容
  1. #include "stdafx.h"
  2. #include "MyExcel.h"
  3. CMyExcel::CMyExcel()
  4. {
  5. strFilePath=_T("");
  6. }
  7. CMyExcel::~CMyExcel()
  8. {
  9. COleVariant covFalse((short)FALSE);
  10. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
  11. MyRange.ReleaseDispatch();
  12. MySheet.ReleaseDispatch();
  13. MySheets.ReleaseDispatch();
  14. MyBook.Close(covFalse,_variant_t(strFilePath),covOptional);
  15. MyBook.ReleaseDispatch();
  16. MyBooks.Close();
  17. MyBooks.ReleaseDispatch();
  18. MyApp.Quit();
  19. MyApp.ReleaseDispatch();
  20. CoUninitialize();
  21. }
  22. BOOL CMyExcel::Open()
  23. {
  24. LPDISPATCH lpDisp=NULL;
  25. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);   
  26. CoInitialize(NULL);
  27. if (!MyApp.CreateDispatch("Excel.Application",NULL))
  28. {
  29. AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
  30. return FALSE;
  31. }
  32. lpDisp=MyApp.GetWorkbooks();
  33. MyBooks.AttachDispatch(lpDisp,TRUE);
  34. lpDisp = MyBooks.Add(covOptional); 
  35. MyBook.AttachDispatch(lpDisp,TRUE);
  36. lpDisp=MyBook.GetWorksheets();
  37. MySheets.AttachDispatch(lpDisp,TRUE);
  38. return TRUE;
  39. }
  40. BOOL CMyExcel::Open(CString strFile)
  41. {
  42. LPDISPATCH   lpDisp=NULL;
  43. CoInitialize(NULL);
  44. if (!MyApp.CreateDispatch("Excel.Application",NULL))
  45. {
  46. AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
  47. return FALSE;
  48. }
  49. lpDisp=MyApp.GetWorkbooks();
  50. MyBooks.AttachDispatch(lpDisp,TRUE);
  51. lpDisp = MyBooks.Open(strFile, vtMissing, vtMissing, vtMissing, 
  52. vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,vtMissing);
  53. MyBook.AttachDispatch(lpDisp,TRUE);
  54. lpDisp=MyBook.GetWorksheets(); 
  55. MySheets.AttachDispatch(lpDisp,TRUE);
  56. strFilePath=strFile;
  57. return TRUE;
  58. }
  59. BOOL CMyExcel::OpenSheet(CString strSheet)
  60. {
  61. LPDISPATCH  lpDisp=NULL;
  62. long len;
  63. len=MySheets.GetCount();
  64. for(long i=1;i<=len;i++)
  65. {
  66. lpDisp=MySheets.GetItem((_variant_t)(long)i);
  67. MySheet.AttachDispatch(lpDisp,TRUE);
  68. CString str=MySheet.GetName();
  69. if(MySheet.GetName()==strSheet)
  70. {
  71. lpDisp=MySheet.GetCells();
  72. MyRange.AttachDispatch(lpDisp,TRUE);
  73. return TRUE;
  74. }
  75. }
  76. return FALSE;
  77. }
  78. BOOL CMyExcel::SetItemText(long Row,long Col,CString strText)
  79. {
  80. long lRow=0,lCol=0;
  81. lRow=GetRowS();
  82. lCol=GetColS();
  83. if(Row>lRow ||Col>lCol)
  84. {
  85. CString strText;
  86. strText.Format("由于(%d,%d)已经超过了(%d,%d)的范围,所以在(%d,%d)设置失败!"
  87. ,Row,Col,lRow,lCol,Row,Col);
  88. AfxMessageBox(_T(strText),MB_OK|MB_ICONERROR);
  89. return FALSE;
  90. }
  91. MyRange.SetItem(_variant_t(Row), _variant_t(Col), _variant_t(strText));
  92. return TRUE;
  93. }
  94. CString CMyExcel::GetItemText(long Row,long Col)
  95. {
  96. CString strValue=_T("");
  97. long lRow=0,lCol=0;
  98. lRow=GetRowS();
  99. lCol=GetColS();
  100. if(Row>lRow ||Col>lCol)
  101. {
  102. CString strText;
  103. strText.Format("由于(%d,%d)已经超过了(%d,%d)的范围,所以返回空值"
  104. ,Row,Col,lRow,lCol,Row,Col);
  105. AfxMessageBox(_T(strText),MB_OK|MB_ICONERROR);
  106. return strValue;
  107. }
  108. VARIANT lpDisp=MyRange.GetItem(_variant_t(Row), _variant_t(Col));
  109. Range rgRgeValue;
  110. rgRgeValue.AttachDispatch(lpDisp.pdispVal, TRUE);
  111. _variant_t vtVal = rgRgeValue.GetValue();
  112. if (vtVal.vt == VT_EMPTY)
  113. {
  114. rgRgeValue.ReleaseDispatch();
  115. strValue = _T("");
  116. rgRgeValue.ReleaseDispatch();
  117. return strValue;
  118. }
  119. vtVal.ChangeType(VT_BSTR);
  120. strValue= vtVal.bstrVal;
  121. rgRgeValue.ReleaseDispatch();
  122. return strValue;
  123. }
  124. void CMyExcel::SaveAs(CString strPath)
  125. {
  126. if(IsFileExist(strPath,FALSE)==TRUE)
  127. DeleteFile(strPath);
  128. MyBook.SaveAs(_variant_t(strPath),vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
  129. ,0,vtMissing,vtMissing,vtMissing,vtMissing);
  130. strFilePath=strPath;
  131. // AfxMessageBox(_T("Excel保存成功"),MB_OK|MB_ICONINFORMATION);
  132. }
  133. void CMyExcel::Save()
  134. {
  135. MyBook.Save();
  136. // AfxMessageBox(_T("Excel保存成功"),MB_OK|MB_ICONINFORMATION);
  137. }
  138. void CMyExcel::AddSheet(CString strSheet)
  139. {
  140. LPDISPATCH  lpDisp=NULL;
  141. lpDisp=MySheets.Add(vtMissing,vtMissing,vtMissing,vtMissing);
  142. MySheet.AttachDispatch(lpDisp,TRUE);
  143. MySheet.SetName(strSheet);
  144. lpDisp=MySheet.GetCells();
  145. MyRange.AttachDispatch(lpDisp,TRUE);
  146. }
  147. void CMyExcel::GetRange(CString strBegin,CString strEnd)
  148. {
  149. MyRange=MySheet.GetRange(_variant_t(strBegin),_variant_t(strEnd));
  150. }
  151. void CMyExcel::AutoColFit()
  152. {
  153. Range rg=MyRange.GetEntireColumn();
  154. rg.AutoFit();
  155. rg.ReleaseDispatch();
  156. }
  157. void CMyExcel::AutoRowFit()
  158. {
  159. Range rg=MyRange.GetEntireRow();
  160. rg.AutoFit();
  161. rg.ReleaseDispatch();
  162. }
  163. void CMyExcel::SetWrapText(BOOL blnTrue)
  164. {
  165. MyRange.SetWrapText((_variant_t)(short)blnTrue);
  166. }
  167. void CMyExcel::SetVisible(BOOL blnVisible)
  168. {
  169. if(blnVisible==TRUE)
  170. if(strFilePath!="")
  171. {
  172. if(IsFileExist(strFilePath,FALSE))
  173. {
  174. Exit();
  175. ShellExecute(NULL,"open",strFilePath,NULL,NULL,SW_SHOW);
  176. }
  177. else
  178. {
  179. CString strName;
  180. strName="路径"+strFilePath+"错误,不能打开显示!";
  181. AfxMessageBox(strFilePath,MB_OK|MB_ICONINFORMATION);
  182. }
  183. }
  184. else
  185. {
  186. AfxMessageBox("请先存文件后,方可打开!",MB_OK|MB_ICONINFORMATION);
  187. }
  188. }
  189. void CMyExcel::SetFont(MyFont font)
  190. {
  191. Font f=MyRange.GetFont();
  192. f.SetName(_variant_t(font.Name));
  193. f.SetShadow((_variant_t)(short)font.Shadow);
  194. f.SetSize((_variant_t)(short)font.size);
  195. f.SetUnderline((_variant_t)(short)font.Underline);
  196. f.SetBold((_variant_t)(short)font.Bold);
  197. f.SetColor((_variant_t)(long)font.ForeColor);
  198. f.SetItalic((_variant_t)(short)font.Italic);
  199. f.SetStrikethrough((_variant_t)(short)font.Strikethrough);
  200. f.SetSubscript((_variant_t)(short)font.Subscript);
  201. f.SetSuperscript((_variant_t)(short)font.Subscript);
  202. f.ReleaseDispatch();
  203. }
  204. void CMyExcel::SetAlignment(MyAlignment XMyAlignment)
  205. {
  206. MyRange.SetHorizontalAlignment((_variant_t)(short)XMyAlignment.HorizontalAlignment);
  207. MyRange.SetVerticalAlignment((_variant_t)(short)XMyAlignment.VerticalAlignment);
  208. }
  209. void CMyExcel::AutoRange()
  210. {
  211. LPDISPATCH  lpDisp=NULL;
  212. lpDisp=MySheet.GetCells();
  213. MyRange.AttachDispatch(lpDisp,TRUE);
  214. }
  215. void CMyExcel::SetMergeCells(BOOL blnTrue)
  216. {
  217. if(blnTrue==TRUE)
  218. {
  219. int i,j;
  220. long Row=GetRowS();
  221. long Col=GetColS();
  222. for(j=2;j<=Col;j++) SetItemText(1,j,"");
  223. for(i=2;i<=Row;i++)
  224. for(j=1;j<=Col;j++)
  225. SetItemText(i,j,"");
  226. }
  227. MyRange.SetMergeCells((_variant_t)(short)blnTrue);
  228. }
  229. void CMyExcel::SetBackStyle(MyBackStyle BackStyle)
  230. {
  231. LPDISPATCH  lpDisp=NULL;
  232. Interior Itor;
  233. lpDisp=MyRange.GetInterior();
  234. Itor.AttachDispatch(lpDisp,TRUE);
  235. if(BackStyle.transparent==TRUE)
  236. Itor.SetColorIndex((_variant_t)(short)xlNone);
  237. else
  238. {
  239. Itor.SetColor((_variant_t)(long)BackStyle.Color);
  240. Itor.SetPattern((_variant_t)(short)BackStyle.Pattern);
  241. Itor.SetPatternColor((_variant_t)(long)BackStyle.PatternColor);
  242. }
  243. Itor.ReleaseDispatch();
  245. }
  246. void CMyExcel::SetBorderLine(short Xposition,MyBorder XBorder)
  247. {
  248. long Row,Col;
  249. Row=GetRowS();
  250. Col=GetColS();
  251. if(Row==1)
  252. if(Xposition==xlInsideHorizontal) return;
  253. if(Col==1)
  254. if(Xposition==xlInsideVertical) return;
  255. LPDISPATCH  lpDisp=NULL;
  256. lpDisp=MyRange.GetBorders();   
  257. Borders   bds;   
  258. bds.AttachDispatch(lpDisp);   
  259. Border   bd;   
  260. lpDisp=bds.GetItem((long)Xposition);   
  261. bd.AttachDispatch(lpDisp);   
  262. bd.SetLineStyle((_variant_t)(short)XBorder.LineStyle);
  263. bd.SetColor((_variant_t)(long)XBorder.Color);
  264. bd.SetWeight((_variant_t)(short)XBorder.Weight);
  265. bd.ReleaseDispatch();
  266. bds.ReleaseDispatch();
  267. }
  268. long CMyExcel::GetRowS()
  269. {
  270. long len=0;
  271. Range rg=MyRange.GetEntireRow();
  272. len=rg.GetCount();
  273. rg.ReleaseDispatch();
  274. return len;
  275. }
  276. long CMyExcel::GetColS()
  277. {
  278. long len=0;
  279. Range rg=MyRange.GetEntireColumn();
  280. len=rg.GetCount();
  281. rg.ReleaseDispatch();
  282. return len;
  283. }
  284. void CMyExcel::SetNumberFormat(MyNumberFormat XNumberFormat)
  285. {
  286. CString strText=XNumberFormat.strValue;
  287. MyRange.SetNumberFormat(_variant_t(strText));
  288. }
  289. void CMyExcel::SetColumnWidth(int intWidth)
  290. {
  291. double f=intWidth/8.08;
  292. MyRange.SetColumnWidth((_variant_t)(double)f);
  293. }
  294. void CMyExcel::SetRowHeight(int intHeight)
  295. {
  296. double f=intHeight/8.08;
  297. MyRange.SetRowHeight((_variant_t)(double)f);
  298. }
  299. void CMyExcel::InsertPicture(CString strFilePath)
  300. {
  301. LPDISPATCH  lpDisp=NULL;
  302. if(IsFileExist(strFilePath,FALSE))
  303. {
  304. // lpDisp=MySheet.get
  305. }
  306. }
  307. void CMyExcel::SetBackPicture(CString strFilePath)
  308. {
  309. if(IsFileExist(strFilePath,FALSE)==TRUE)
  310. MySheet.SetBackgroundPicture(strFilePath);
  311. }
  312. void CMyExcel::PrintOut(short CopySum)
  313. {
  314. if(CopySum<=0) CopySum=1;
  315. COleVariant covTrue((short)TRUE); 
  316. COleVariant covFalse((short)FALSE); 
  317. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
  318. MySheet.PrintOut(vtMissing,vtMissing,(_variant_t)(short)CopySum,vtMissing
  319. ,vtMissing,vtMissing,covTrue,vtMissing);
  320. }
  321. void CMyExcel::PrePrintOut(BOOL blnEnable)
  322. {
  323. COleVariant covOptional((short)blnEnable);
  324. MySheet.PrintPreview(covOptional);
  325. }
  326. BOOL CMyExcel::IsFileExist(CString strFn, BOOL bDir)
  327. {
  328.     HANDLE h;
  329. LPWIN32_FIND_DATA pFD=new WIN32_FIND_DATA;
  330. BOOL bFound=FALSE;
  331. if(pFD)
  332. {
  333. h=FindFirstFile(strFn,pFD);
  334. bFound=(h!=INVALID_HANDLE_VALUE);
  335. if(bFound)
  336. {
  337. if(bDir)
  338. bFound= (pFD->dwFileAttributes&FILE_ATTRIBUTE_DIRECTORY)!=NULL;
  339. FindClose(h);
  340. }
  341. delete pFD;
  342. }
  343. return bFound;
  344. }
  345. void CMyExcel::Exit()
  346. {
  347. COleVariant covFalse((short)FALSE);
  348. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
  349. MyRange.ReleaseDispatch();
  350. MySheet.ReleaseDispatch();
  351. MySheets.ReleaseDispatch();
  352. MyBook.Close(covFalse,_variant_t(strFilePath),covOptional);
  353. MyBook.ReleaseDispatch();
  354. MyBooks.Close();
  355. MyBooks.ReleaseDispatch();
  356. MyApp.Quit();
  357. MyApp.ReleaseDispatch();
  358. CoUninitialize();
  359. }
  360. MyFont::MyFont()
  361. {
  362. //名字
  363.     Name="Microsoft Sans Serif";
  364. //大小
  365. size=12;
  366. //前景
  367. ForeColor=RGB(0,0,0);
  368. //粗体
  369. Bold=FALSE;
  370. //斜体
  371. Italic=FALSE;
  372. //中间线
  373. Strikethrough=FALSE;
  374. //阴影
  375. Shadow=FALSE;
  376. //下标
  377. Subscript=FALSE;
  378. //上标
  379. Superscricp=FALSE;
  380. //下划线
  381. Underline=xlUnderlineStyleNone;
  382. }
  383. MyBorder::MyBorder()
  384. {
  385. //线条形状
  386. LineStyle=xlContinuous;
  387. //粗线
  388.     Weight=xlThin;
  389. //颜色
  390.     Color=RGB(0,0,0);
  391. }
  392. MyBackStyle::MyBackStyle()
  393. {
  394. //背景颜色
  395. Color=RGB(255,255,255);
  396. //背景图案
  397.     Pattern=xlSolid;
  398. //背景图案颜色
  399.     PatternColor=RGB(255,0,0);
  400. //默认为不透明
  401. transparent=FALSE;
  402. }
  403. MyAlignment::MyAlignment()
  404. {
  405. //普通
  406. HorizontalAlignment=xlGeneral;
  407. //居中对齐
  408. VerticalAlignment=xlCenter;
  409. }
  410. MyNumberFormat::MyNumberFormat()
  411. {
  412. strValue="G/通用格式";
  413. }
  414. CString MyNumberFormat::GetText()
  415. {
  416. strValue="@";
  417. return strValue;
  418. }
  419. CString MyNumberFormat::GetGeneral()
  420. {
  421. strValue="G/通用格式";
  422. return strValue;
  423. }
  424. CString MyNumberFormat::GetNumber(BOOL blnBox,int RightSum)
  425. {
  426. CString str="0";
  427. int i;
  428. if(RightSum<0) RightSum=0;
  429. if(blnBox==TRUE)
  430. {
  431. if(RightSum==0)
  432. {
  433. str="#,##0_ ";
  434. strValue=str;
  435. return strValue;
  436. }
  437. else
  438. {
  439. str="#,##0.";
  440. for(i=0;i<RightSum;i++) str=str+"0";
  441. str=str+"_ ";
  442. strValue=str;
  443. return strValue;
  444. }
  445. }
  446. else
  447. {
  448. if(RightSum==0)
  449. {
  450. str="0_ ";
  451. strValue=str;
  452. return strValue;
  453. }
  454. else
  455. {
  456. str="0.";
  457. for(i=0;i<RightSum;i++) str=str+"0";
  458. str=str+"_ ";
  459. strValue=str;
  460. return strValue;
  461. }
  462. }
  463. }
  464. CString MyNumberFormat::GetDate(BOOL blnChinese)
  465. {
  466. if(blnChinese==TRUE)
  467. strValue="yyyy"年"m"月"d"日";@";
  468. else
  469. strValue="yyyy-m-d;@";
  470. return strValue;
  471. }
  472. CString MyNumberFormat::GetDateTime(BOOL blnChinese)
  473. {
  474. if(blnChinese==TRUE)
  475. strValue="yyyy"年"m"月"d"日" h"时"mm"分"ss"秒";@";
  476. else
  477. strValue="yyyy-m-d h:mm:ss;@";
  478. return strValue;
  479. }
  480. CString MyNumberFormat::GetDBNumber(BOOL blnChinese)
  481. {
  482. if(blnChinese==TRUE)
  483. strValue="[DBNum1][$-804]G/通用格式";
  484. else
  485. strValue="[DBNum2][$-804]G/通用格式";
  486. return strValue;
  487. }
  488. CString MyNumberFormat::GetFractionNumBer(int DownSum,int DownNum)
  489. {
  490. CString str;
  491. int i;
  492. if(DownNum>0 && DownSum>0)
  493. {
  494. AfxMessageBox("分母位数和固定数只能设置一个,默认返回分母位数的设置!"
  495. ,MB_ICONINFORMATION|MB_OK);
  496. }
  497. if(DownSum<=0)
  498. {
  499. if(DownNum<=0)
  500. {
  501. str="# ?/1";
  502. strValue=str;
  503. return strValue;
  504. }
  505. else
  506. {
  507. str.Format("# ?/%d",DownNum);
  508. strValue=str;
  509. return strValue;
  510. }
  511. }
  512. else
  513. {
  514. str="# ?/";
  515. for(i=0;i<DownSum;i++) str=str+"?";
  516. strValue=str;
  517. return strValue;
  518. }
  519. }
  520. CString MyNumberFormat::GetMoney(BOOL blnChinese,int RightSum)
  521. {
  522. CString str;
  523. int i;
  524. if(RightSum<=0) RightSum=0;
  525. if(blnChinese==TRUE)
  526. {
  527. if(RightSum==0)
  528. {
  529. str="#,##0";
  530. strValue="￥"+str+";"+"￥-"+str;
  531. return strValue;
  532. }
  533. else
  534. {
  535. str="#,##0.";
  536. for(i=0;i<RightSum;i++) str=str+"0";
  537. strValue="￥"+str+";"+"￥-"+str;
  538. return strValue;
  539. }
  541. }
  542. else
  543. {
  544. if(RightSum==0)
  545. {
  546. str="#,##0";
  547. strValue="$"+str+";"+"$-"+str;
  548. return strValue;
  549. }
  550. else
  551. {
  552. str="#,##0.";
  553. for(i=0;i<RightSum;i++) str=str+"0";
  554. strValue="$"+str+";"+"$-"+str;
  555. return strValue;
  556. }
  557. }
  558. }
  559. CString MyNumberFormat::GetPercentNumBer(int RightSum)
  560. {
  561. CString str;
  562. int i;
  563. if(RightSum<=0) RightSum=0;
  564. if(RightSum==0)
  565. {
  566. str="0%";
  567. strValue=str;
  568. return strValue;
  569. }
  570. else
  571. {
  572. str="0.";
  573. for(i=0;i<RightSum;i++) str=str+"0";
  574. strValue=str+"%";
  575. return strValue;
  576. }
  577. }
  578. CString MyNumberFormat::GetTechNumBer(int RightSum)
  579. {
  580. CString str;
  581. int i;
  582. if(RightSum<=0) RightSum=0;
  583. str="0.";
  584. for(i=0;i<RightSum;i++) str=str+"0";
  585. strValue=str+"E+00";
  586. return strValue;
  587. }
  588. CString MyNumberFormat::GetTime(BOOL blnChinese)
  589. {
  590. if(blnChinese==TRUE)
  591. {
  592. strValue="h"时"mm"分"ss"秒";@";
  593. return strValue;
  594. }
  595. else
  596. {
  597. strValue="h:mm:ss;@";
  598. return strValue;
  599. }
  600. }
  601. CString MyNumberFormat::GetPost(int Sum)
  602. {
  603. int i;
  604. CString str="";
  605. if(Sum<=0) Sum=1;
  606. for(i=0;i<Sum;i++)
  607. str=str+"0";
  608. strValue=str;
  609. return strValue;
  610. }