使用VB或VC操作Excel 2003

前端之家收集整理的这篇文章主要介绍了使用VB或VC操作Excel 2003前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1.VB操作EXCEL2003

  1. ‘************************************************************************
  2. ‘演示用VB.NET设置Excel单元格值和风格的小程序(例程)
  3. ‘程序功能是:打开文件,设置单元格的风格和值,冻结窗口等
  4. ‘网上类似的程序很多,但Excel2003的SaveAs函数由新版本的函数代替,原函数
  5. ‘为_saveas,这个程序同时也演示了如何使用新函数,另外也演示了如何冻结窗格
  6. ‘关键字:Excel2003SaveAs冻结窗格单元格风格
  7. ‘开发环境:VisualStudio.NET2003
  8. ‘Excel环境:Excel2003
  9. ‘作者:高宏伟(DukeJoe)
  10. ‘时间:2006-03-1211:27
  11. ‘地点:黑龙江省哈尔滨市平房区
  12. ‘注释:无
  13. ‘***********************************************************************
  14. ModuleModule1
  15. SubMain()
  16. DimThisApplicationAsMicrosoft.Office.Interop.Excel.Application=NewMicrosoft.Office.Interop.Excel.Application
  17. DimThisWorkbookAsMicrosoft.Office.Interop.Excel.Workbook
  18. DimThisSheetsAsMicrosoft.Office.Interop.Excel.Sheets
  19. DimThisWorkSheetAsMicrosoft.Office.Interop.Excel.Worksheet
  20. DimThisRangeAsMicrosoft.Office.Interop.Excel.Range
  21. ThisApplication.Visible=True
  22. ThisWorkbook=ThisApplication.Workbooks.Add()
  23. Console.WriteLine(ThisWorkbook.Name)
  24. ThisSheets=ThisWorkbook.Worksheets
  25. ThisWorkSheet=ThisSheets(1)
  26. Console.WriteLine(ThisWorkSheet.Name)
  27. ’设置整个sheet的填充色为白色
  28. ThisWorkSheet.Cells.Interior.Color=RGB(255,255)
  29. ThisWorkSheet.Cells.ClearContents()
  30. ThisRange=ThisWorkSheet.Range("A1:C5")
  31. Console.WriteLine(ThisRange.Cells(1,1).Value)
  32. ’ThisRange.Interior.Color=0
  33. ThisRange.ClearFormats()
  34. ThisRange.Cells(1,1).Value="哈尔滨市平房区"
  35. ThisRange.Cells(1,2).Value="高宏伟"
  36. "QQ:21807822"
  37. ThisRange.Cells(2,1).Value="1"
  38. ThisRange.Cells(3,1).Value="2"
  39. ThisRange.Cells(4,1).Value="3"
  40. ThisRange.Cells(5,1).Value="4"
  41. ’为Range的四周和内部加上边框
  42. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  43. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  44. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  45. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  46. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  47. ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
  48. ’设置第一行的格式(背景色、粗体、颜色、列宽)
  49. ThisRange.Range("A1:C1").Interior.ColorIndex=47
  50. ThisRange.Range("A1:C1").Interior.Pattern=Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
  51. ThisRange.Range("A1:C1").Font.ColorIndex=6
  52. ThisRange.Range("A1:C1").Font.Bold= ThisRange.EntireColumn.ColumnWidth=18.63
  53. ThisRange.Range("A2:C5").Interior.ColorIndex=16
  54. ThisRange.Range("A2:C5").Interior.Pattern=Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
  55. ThisRange.Range("A2:C5").Font.ColorIndex=2
  56. ’冻结窗格
  57. ThisApplication.ActiveWindow.FreezePanes=False
  58. ThisApplication.Range("A2").Select()
  59. ThisWorkbook.SaveAs("D:/VisualStudioProjects/VBAReportDemo/bin/VBAReportDemo.xls",Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7,_
  60. "","",False,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,_
  61. Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,False)
  62. ThisWorkbook.Close()
  63. ThisApplication.Quit()
  64. Console.WriteLine("pressanykeytocontinue")
  65. ’Console.ReadLine()
  66. EndSub
  67. Module

2.VC操作EXCEL2003

[cpp] copy
    /******************************************************************************
  1. *
  2. *这是上面VBAReportDemo的对应程序
  3. *演示使用VC操作Excel2003
  4. *功能:设置单元格,风格,冻结窗格。以及如何将对应的VB代码翻译成VC
  5. *时间:2007-04-1609:31
  6. *作者:高宏伟(DukeJoe)
  7. *QQ:21807822
  8. *Blog:http://dukejoe.yeah.net
  9. *注释:本代码可单独研究,但最好和上面的VBAReportDemo对应看比较好
  10. *开发环境:VisualStudio.NET2003
  11. *操作系统:WindowsXPHomeEditionServicePack2
  12. *
  13. *****************************************************************************/
  14. #include<iostream>
  15. usingnamespacestd;
  16. #import"C:/ProgramFiles/CommonFiles/MicrosoftShared/OFFICE11/mso.dll"rename("RGB","MSRGB")
  17. #import"C:/ProgramFiles/CommonFiles/MicrosoftShared/VBA/VBA6/VBE6EXT.OLB"raw_interfaces_only,/
  18. rename("Reference","ignorethis"),rename("VBE","JOEVBE")
  19. #import"C:/ProgramFiles/MicrosoftOffice/OFFICE11/excel.exe"exclude("IFont","IPicture")/
  20. rename("RGB",rename("DialogBox","JOEVBE"),/
  21. rename("ReplaceText","JOEReplaceText"),rename("CopyFile","JOECopyFile"),/
  22. rename("FindText","JOEFindText"),rename("NoPrompt","JOENoPrompt")
  23. namespaceOffice;
  24. namespaceVBIDE;
  25. namespaceExcel;
  26. intExportExcelFile();
  27. intmain(intargc,char*argv[])
  28. {
  29. if(Failed(::CoInitialize(NULL)))
  30. return1;
  31. ExportExcelFile();
  32. ::CoUninitialize();
  33. return0;
  34. }
  35. intExportExcelFile()
  36. _ApplicationPtrpApplication=NULL;
  37. _WorkbookPtrpThisWorkbook=NULL;
  38. _WorksheetPtrpThisWorksheet=NULL;
  39. SheetsPtrpThisSheets=NULL;
  40. RangePtrpThisRange=NULL;
  41. _variant_tvt;
  42. Excel::XlFileFormatvFileFormat;
  43. Excel::XlSaveAsAccessModevSaveAsAccessMode;
  44. Excel::XlSaveConflictResolutionvSaveConflictResolution;
  45. pApplication.CreateInstance("Excel.Application");
  46. pApplication->PutVisible(0,VARIANT_TRUE);
  47. pThisWorkbook=pApplication->GetWorkbooks()->Add();
  48. pThisSheets=pThisWorkbook->GetWorksheets();
  49. pThisWorksheet=pThisSheets->GetItem((short)1);
  50. //设置整个sheet的填充色为白色
  51. pThisWorksheet->GetCells()->GetInterior()->PutColor(RGB(255,255));
  52. pThisWorksheet->GetCells()->ClearContents();
  53. pThisRange=pThisWorksheet->GetRange("A1:C5");
  54. pThisRange->ClearFormats();
  55. //如果有不会的,可以在debug文件夹的excel.tlh里找找
  56. //pThisRange->GetItem(1,1);
  57. pThisRange->PutItem(1,1,_variant_t("哈尔滨市平房区"));
  58. pThisRange->PutItem(1,2,_variant_t("高宏伟"));
  59. "QQ:21807822"));
  60. pThisRange->PutItem(2,_variant_t("1"));
  61. pThisRange->PutItem(3,_variant_t("2"));
  62. pThisRange->PutItem(4,_variant_t("3"));
  63. pThisRange->PutItem(5,_variant_t("4"));
  64. //为Range的四周和内部加上边框
  65. pThisRange->GetBorders()->GetItem(xlEdgeLeft)->PutLineStyle(xlContinuous);
  66. pThisRange->GetBorders()->GetItem(xlEdgeTop)->PutLineStyle(xlContinuous);
  67. pThisRange->GetBorders()->GetItem(xlEdgeRight)->PutLineStyle(xlContinuous);
  68. pThisRange->GetBorders()->GetItem(xlEdgeBottom)->PutLineStyle(xlContinuous);
  69. pThisRange->GetBorders()->GetItem(xlInsideHorizontal)->PutLineStyle(xlContinuous);
  70. pThisRange->GetBorders()->GetItem(xlInsideVertical)->PutLineStyle(xlContinuous);
  71. //设置第一行的格式(背景色、粗体、颜色、列宽)
  72. pThisRange->GetRange("A1:C1")->GetInterior()->ColorIndex=47;
  73. pThisRange->GetRange("A1:C1")->GetInterior()->Pattern=xlPatternSolid;
  74. pThisRange->GetRange("A1:C1")->GetFont()->ColorIndex=6;
  75. pThisRange->GetRange("A1:C1")->GetFont()->Bold=TRUE;
  76. pThisRange->GetEntireColumn()->ColumnWidth=18.63;
  77. pThisRange->GetRange("A2:C5")->GetInterior()->ColorIndex=16;
  78. pThisRange->GetRange("A2:C5")->GetInterior()->Pattern=xlPatternSolid;
  79. pThisRange->GetRange("A2:C5")->GetFont()->ColorIndex=2;
  80. //冻结窗格
  81. pApplication->ActiveWindow->FreezePanes=FALSE;
  82. pApplication->Range["A2"]->Select();
  83. pApplication->ActiveWindow->FreezePanes=TRUE;
  84. //存盘退出
  85. vSaveAsAccessMode=xlNoChange;
  86. vFileFormat=xlWorkbookNormal;
  87. vSaveConflictResolution=xlLocalSessionChanges;
  88. pThisWorkbook->SaveAs(_variant_t("D://VisualStudioProjects//VCReportDemo//joe.xls"),vFileFormat,_variant_t(""),_variant_t(false),
  89. _variant_t(false));
  90. pThisWorkbook->Close();
  91. pApplication->Quit();
  92. }

原文网址:http://blog.donews.com/dukejoe/archive/2007/04/16/1156319.aspx

猜你在找的VB相关文章