您好,登錄后才能下訂單哦!
這篇文章主要講解了“C#操作Excel常用組件及類(lèi)之Excel控制類(lèi)代碼分享”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“C#操作Excel常用組件及類(lèi)之Excel控制類(lèi)代碼分享”吧!
C# 操作Excel常用組件及類(lèi)之添加引用
添加com組件(Microsoft Office 11.0 Object Library )命名空間為Microsoft.Office.Interop.Excel
添加Excel.exe引用默認(rèn)路徑為C:\Program Files\Microsoft Office\OFFICE11\Excel.exe
C# 操作Excel常用組件及類(lèi)之Excel控制類(lèi)
using System; //C# 操作Excel常用組件及類(lèi)using System.Collections.Generic; using System.Text; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace ExcelOperation { public class ExcelControl { private Excel.Application m_objExcel = null; private Excel.Workbooks m_objBooks = null; private Excel._Workbook m_objBook = null; private Excel.Sheets m_objSheets = null; private Excel._Worksheet m_objSheet = null; private Excel.Range m_objRange = null; private object m_objOpt = System.Reflection.Missing.Value; /// ﹤summary﹥ /// 打開(kāi)沒(méi)有模板的操作。 /// ﹤/summary﹥ public void Open() { this.Open(String.Empty); } //C# 操作Excel常用組件及類(lèi) /// ﹤summary﹥ /// 功能:實(shí)現(xiàn)Excel應(yīng)用程序的打開(kāi) /// ﹤/summary﹥ /// ﹤param name="TemplateFilePath"﹥模板文件物理路徑﹤/param﹥ public void Open(string TemplateFilePath) { //打開(kāi)對(duì)象 m_objExcel = new Excel.Application(); m_objExcel.Visible = false; m_objExcel.DisplayAlerts = false; if (m_objExcel.Version != "11.0") { MessageBox.Show("您的 Excel 版本不是 11.0 (Office 2003),操作可能會(huì)出現(xiàn)問(wèn)題。"); 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 { //C# 操作Excel常用組件及類(lèi)m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); } m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objExcel.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler( m_objExcel_WorkbookBeforeClose); } private void m_objExcel_WorkbookBeforeClose( Excel.Workbook m_objBooks, ref bool _Cancel) { MessageBox.Show("保存完畢!"); } /// ﹤summary﹥ //C# 操作Excel常用組件及類(lèi) /// 將圖片插入到指定的單元格位置。 /// 注意:圖片必須是絕對(duì)物理路徑 /// ﹤/summary﹥ /// ﹤param name="RangeName"﹥單元格名稱(chēng),例如:B4﹤/param﹥ /// ﹤param name="PicturePath"﹥要插入圖片的絕對(duì)路徑。﹤/param﹥ public void InsertPicture(string RangeName, string PicturePath) { m_objRange = m_objSheet.get_Range(RangeName, m_objOpt); m_objRange.Select(); Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt); pics.Insert(PicturePath, m_objOpt); } /// ﹤summary﹥ /// 將圖片插入到指定的單元格位置,并設(shè)置圖片的寬度和高度。 /// 注意:圖片必須是絕對(duì)物理路徑 /// ﹤/summary﹥ /// ﹤param name="RangeName"﹥單元格名稱(chēng),例如:B4﹤/param﹥ /// ﹤param name="PicturePath"﹥要插入圖片的絕對(duì)路徑。﹤/param﹥ /// ﹤param name="PictuteWidth"﹥插入后,圖片在Excel中顯示的寬度。﹤/param﹥ /// ﹤param name="PictureHeight"﹥插入后,圖片在Excel中顯示的高度。﹤/param﹥ public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight) { m_objRange = m_objSheet.get_Range(RangeName, m_objOpt); m_objRange.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(m_objRange.Left); PicTop = Convert.ToSingle(m_objRange.Top); //參數(shù)含義: C# 操作Excel常用組件及類(lèi)//圖片路徑 //是否鏈接到文件 //圖片插入時(shí)是否隨文檔一起保存 //圖片在文檔中的坐標(biāo)位置(單位:points) //圖片顯示的寬度和高度(單位:points) //參數(shù)詳細(xì)信息參見(jiàn):http://msdn2.microsoft.com/zh-cn/library/aa221765(office.11).aspx m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight); } /// ﹤summary﹥ /// 將圖片填充到Excel中的某個(gè)或某些單元格中 /// 注意:圖片必須是絕對(duì)物理路徑 /// ﹤/summary﹥ /// ﹤param name="RangeName"﹥單元格名稱(chēng),例如:B4﹤/param﹥ /// ﹤param name="PicturePath"﹥要插入圖片的絕對(duì)路徑。﹤/param﹥ /// ﹤param name="PictuteWidth"﹥插入后,圖片在Excel中顯示的寬度。﹤/param﹥ /// ﹤param name="PictureHeight"﹥插入后,圖片在Excel中顯示的高度。﹤/param﹥ /// ﹤summary﹥//C# 操作Excel常用組件及類(lèi) /// 將圖片填充到Excel中的某個(gè)或某些單元格中 /// ﹤/summary﹥ /// ﹤param name="BeginRangeName"﹥插入的開(kāi)始單元格﹤/param﹥ /// ﹤param name="EndRangeName"﹥插入的結(jié)束單元格﹤/param﹥ /// ﹤param name="PicturePath"﹥插入圖片的絕對(duì)物理路徑﹤/param﹥ /// ﹤param name="IsMergeCells"﹥是否合并上面的單元格﹤/param﹥ public void InsertPicture(string BeginRangeName,string EndRangeName, string PicturePath,bool IsMergeCells) { m_objRange = m_objSheet.get_Range(BeginRangeName, EndRangeName); //C# 操作Excel常用組件及類(lèi)//計(jì)算單元格的寬和高 float PictuteWidth, PictureHeight; PictuteWidth = Convert.ToSingle(m_objRange.Width); PictureHeight = Convert.ToSingle(m_objRange.Height); if (IsMergeCells) { //合并單元格 m_objRange.Merge(System.Reflection.Missing.Value); m_objRange = m_objSheet.get_Range(BeginRangeName, BeginRangeName); } m_objRange.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(m_objRange.Left); PicTop = Convert.ToSingle(m_objRange.Top); //參數(shù)含義: //圖片路徑 //是否鏈接到文件 //圖片插入時(shí)是否隨文檔一起保存 //圖片在文檔中的坐標(biāo)位置(單位:points) //圖片顯示的寬度和高度(單位:points) //參數(shù)詳細(xì)信息參見(jiàn):http://msdn2.microsoft.com/zh-cn/library/aa221765(office.11).aspx m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight); } /// ﹤summary﹥ /// /// ﹤/summary﹥ /// ﹤param name="BeginRangeName"﹥﹤/param﹥ /// ﹤param name="EndRangeName"﹥﹤/param﹥ /// ﹤param name="IsMergeCells"﹥﹤/param﹥ public void InsertLine(string BeginRangeName, string EndRangeName, bool IsMergeCells) { m_objRange = m_objSheet.get_Range(BeginRangeName, EndRangeName); //C# 操作Excel常用組件及類(lèi)//計(jì)算單元格的寬和高 float PictureWidth, PictureHeight; PictureWidth = Convert.ToSingle(m_objRange.Width); PictureHeight = Convert.ToSingle(m_objRange.Height); if (IsMergeCells) { //合并單元格 m_objRange.Merge(System.Reflection.Missing.Value); m_objRange = m_objSheet.get_Range(BeginRangeName, BeginRangeName); } m_objRange.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(m_objRange.Left); PicTop = Convert.ToSingle(m_objRange.Top); //參數(shù)含義: //線(xiàn)的起點(diǎn)(X,Y) //線(xiàn)的終點(diǎn)(X,Y) m_objSheet.Shapes.AddLine(PicLeft,PicTop,PicLeft + PictureWidth,PicTop +PictureHeight); } /// ﹤summary﹥ /// 將Excel文件保存到指定的目錄,目錄必須事先存在,文件名稱(chēng)不一定要存在。 /// ﹤/summary﹥ /// ﹤param name="OutputFilePath"﹥要保存成的文件的全路徑。﹤/param﹥ public void SaveFile(string OutputFilePath) { m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); this.Close(); } //C# 操作Excel常用組件及類(lèi)/// ﹤summary﹥ /// 關(guān)閉應(yīng)用程序 /// ﹤/summary﹥ private void Close() { m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } /// ﹤summary﹥ /// 釋放所引用的COM對(duì)象。注意:這個(gè)過(guò)程一定要執(zhí)行。 /// ﹤/summary﹥ public void Dispose() { ReleaseObj(m_objSheets); ReleaseObj(m_objBook); ReleaseObj(m_objBooks); ReleaseObj(m_objExcel); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); } /// ﹤summary﹥ /// 釋放對(duì)象,內(nèi)部調(diào)用 /// ﹤/summary﹥ /// ﹤param name="o"﹥﹤/param﹥ private void ReleaseObj(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } } /// ﹤summary﹥ /// 刪除指定sheet下的指定行 /// ﹤/summary﹥ /// ﹤param name="SheetIndex"﹥sheet索引﹤/param﹥ /// ﹤param name="DeleteRowIndex"﹥行索引﹤/param﹥ public void DeleteRow(int SheetIndex, int DeleteRowIndex) { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(SheetIndex)); m_objRange = (Excel.Range)m_objSheet.Rows[DeleteRowIndex, System.Reflection.Missing.Value]; m_objRange.EntireRow.Delete(Excel.XlDirection.xlToRight); } /// ﹤summary﹥ /// 刪除指定sheet下的指定列 /// ﹤/summary﹥ /// ﹤param name="SheetIndex"﹥sheet索引﹤/param﹥ /// ﹤param name="DeleteColumnIndex"﹥列索引﹤/param﹥ public void DeleteColumn(int SheetIndex, int DeleteColumnIndex) { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(SheetIndex)); m_objRange = (Excel.Range)m_objSheet.Columns[DeleteColumnIndex, System.Reflection.Missing.Value]; m_objRange.EntireColumn.Delete(Excel.XlDirection.xlDown); } } }
感謝各位的閱讀,以上就是“C#操作Excel常用組件及類(lèi)之Excel控制類(lèi)代碼分享”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)C#操作Excel常用組件及類(lèi)之Excel控制類(lèi)代碼分享這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。