这几天做C#向Excel 插入数据,其中有插入图片的需求,经试验,下面 2 种方法都可以实现C#向Excel 插入数据,但各有不同的用处.现将这 2 种方法共享出来, 希望需要的朋友进行参考,代码中已经有详细注释了. 注意:使用之前需要引用COM:Microsoft Office 11.0 Object Library 如果引用列表中没有,需要自行添加C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE 调用方法: 1. MengXianhui.Utility.ExcelReport.InsertPictureToExcelipt=
newMengXianhui.Utility.ExcelReport.InsertPictureToExcel(); 2. 3. 4. 5. 6. ipt.Open(); ipt.InsertPicture("B2",@"C:\Excellogo.gif"); ipt.InsertPicture("B8",@"C:\Excellogo.gif",120,80); ipt.SaveFile(@"C:\ExcelTest.xls"); ipt.Dispose(); 简单包装的类: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
12. 13. 14. 15. 16. 17. 18. 19. 20. ///<summary> ///功能:实现Excel 应用程序的打开///</summary> ///<paramnameparamname="TemplateFilePath">模板文件物理路径</param> namespaceMengXianhui.Utility.ExcelReport { classInsertPictureToExcel { ///<summary> ///打开没有模板的操作. ///</summary> publicvoidOpen() { this.Open(String.Empty); } usingSystem; usingSystem.Windows.Forms; usingExcel=Microsoft.Office.Interop.Excel; 21. 22. 23. 24. 25. 26.
27. 28. 29. 30. publicvoidOpen(stringTemplateFilePath) { //打开对象m_objExcel=newExcel.Application(); m_objExcel.Visible=false; m_objExcel.DisplayAlerts=false; if(m_objExcel.Version!="11.0") { MessageBox.Show("您的Excel 版本不是11.0(Office2003),操作可能会出现问题. "); 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. m_objExcel.Quit(); return; } m_objBooks=(Excel.Workbooks)m_objExcel.Workbooks; if(TemplateFilePath.Equals(String.Empty))
{ m_objBook=(Excel._Workbook)(m_objBooks.Add(m_objOpt)); } else { m_objBook=m_objBooks.Open (TemplateFilePath,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOp t, m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOp t); 43. 44. 45.
46. } m_objSheets=(Excel.Sheets)m_objBook.Worksheets; m_objSheet=(Excel._Worksheet)(m_objSheets.get_Item(1));
m_objExcel.WorkbookBeforeClose+=newExcel.
AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClo se); 47. 48.
49. } privatevoidm_objExcel_WorkbookBeforeClose(Excel.Workbookm_objBooks,r efbool_Cancel) 50. { 51. 52. 53. 54. 55. 56. 57. 58. 59. 60. 61. 62. 63. 64. 65. 66. 67. 68. 69. 70.
71. 72. 73. 74. MessageBox.Show("保存完毕!"); } ///<summary> ///将图片插入到指定的单元格位置. ///注意:图片必须是绝对物理路径///</summary> ///<paramnameparamname="RangeName">单元格名称,例如:B4</param> ///<paramnameparamname="PicturePath">要插入图片的绝对路径.</param> publicvoidInsertPicture(stringRangeName,stringPicturePath)
{ m_objRange=m_objSheet.get_Range(RangeName,m_objOpt); m_objRange.Select(); Excel.Picturespics=(Excel.Pictures)m_objSheet.Pictures(m_objOpt);
pics.Insert(PicturePath,m_objOpt); } ///<summary> ///将图片插入到指定的单元格位置,并设置图片的宽度和高度. ///注意:图片必须是绝对物理路径///</summary> ///<paramnameparamname="RangeName">单元格名称,例如:B4</param> ///<paramnameparamname="PicturePath">要插入图片的绝对路径.</param> ///<paramnameparamname="PictuteWidth">插入后,图片在Excel 中显示的宽度. </param> 75. ///<paramnameparamname="PictureHeight">插入后,图片在Excel 中显示的高度. </param> 76. publicvoidInsertPicture (stringRangeName,stringPicturePath,floatPictuteWidth,floatPictureHeigh t)
77. 78. 79. 80. 81. 82. 83. 84. { m_objRange=m_objSheet.get_Range(RangeName,m_objOpt); m_objRange.Select(); floatPicLeft,PicTop; PicLeft=Convert.ToSingle(m_objRange.Left); PicTop=Convert.ToSingle(m_objRange.Top); //参数含义: //图片路径85. 86. 87. 88. 89. //是否链接到文件//图片插入时是否随文档一起保存//图片在文档中的坐标位置(单位:points) //图片显示的宽度和高度(单位:points) //参数详细信息参见: /zh-cn/library/aa221765(office.11).aspx 90. m_objSheet.Shapes.AddPicture(PicturePath,Microsoft.Office.Core.MsoTr iState. msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue,PicLeft,PicTop,
PictuteWidth,PictureHeight); 91. 92. 93. 94. 95. 96. 97. 98. 99. } ///<summary> ///将Excel 文件保存到指定的目录,目录必须事先存在,文件名称不一定要存在. ///</summary> ///<paramnameparamname="OutputFilePath">要保存成的文件的全路径. </param> publicvoidSaveFile(stringOutputFilePath)
{ m_objBook.SaveAs(OutputFilePath,m_objOpt,m_objOpt, 100. m_objOpt,m_objOpt,m_objOpt,Excel.XlSaveAsAccessMode.xlNoChange, 101. m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt); 102. 103. this.Close(); 104. } 105. ///<summary> 106. ///关闭应用程序107. ///</summary> 108. privatevoidClose() 109. { 110. m_objBook.Close(false,m_objOpt,m_objOpt); 111. m_objExcel.Quit(); 112. } 113. 114. ///<summary> 115. ///释放所引用的COM 对象.注意:这个过程一定要执行. 116. ///</summary> 117. publicvoidDispose() 118. { 119. ReleaseObj(m_objSheets); 120. ReleaseObj(m_objBook); 121. ReleaseObj(m_objBooks); 122. ReleaseObj(m_objExcel); 123. System.GC.Collect(); 124. System.GC.WaitForPendingFinalizers(); 125. } 126. ///<summary> 127. ///释放对象,内部调用128. ///</summary> 129. ///<paramnameparamname="o"></param> 130. privatevoidReleaseObj(objecto) 131. { 132. try 133. { 134. System.Runtime.InteropServices.Marshal.ReleaseComObject(o); 135. } 136. catch{} 137. finally{o=null;} 138. } 139. 140. privateExcel.Applicationm_objExcel=null; 141. privateExcel.Workbooksm_objBooks=null; 142. privateExcel._Workbookm_objBook=null; 143. privateExcel.Sheetsm_objSheets=null; 144. privateExcel._Worksheetm_objSheet=null; 145. privateExcel.Rangem_objRange=null; 146. privateobjectm_objOpt=System.Reflection.Missing.Value; 147. } 148. }。
