1、Good is good, but better carries it.精益求精，善益求善。C操作EXCEL全解代碼-C#操作EXCEL全解（代碼）提示：運行之前必須先引用Interop.Excel.dll模塊usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.OleDb;usingSystem.Windows.Forms;usingSystem.Reflection;usingExcel;namespaceAnalysisSystem.DBpubliccl

2、assExcelOperationprivatestring_fileName;/保存路徑名publicExcelOperation(stringfileName)_fileName=fileName;privateOleDbConnectionGetConnection()stringconnectString=string.Format(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=0;ExtendedProperties=Excel8.0,_fileName);OleDbConnectionmyConn=newOleDbConnection(co

3、nnectString);/建立鏈接returnmyConn;publicSystem.Data.DataTableExecuteTableResult(stringstrSql)System.Data.DataTabledt=newSystem.Data.DataTable();tryOleDbConnectionconn=this.GetConnection();OleDbDataAdapterda=newOleDbDataAdapter(strSql,conn);/執行相關SQL語句da.Fill(dt);catch(System.Exceptionex)/donothingreturn

4、dt;publicDataSetExecuteSetResult(stringstrSql,stringtable_name)DataSetds=newDataSet();stringtemp_name=+table_name+$;tryOleDbConnectionconn=this.GetConnection();OleDbDataAdapterda=newOleDbDataAdapter(strSql,conn);da.Fill(ds,temp_name);catch(System.Exceptionex)/donothingreturnds;publicstringExecuteOne

5、Result(stringstrSql)stringresult=;System.Data.DataTabledt=newSystem.Data.DataTable();tryOleDbConnectionconn=this.GetConnection();OleDbDataAdapterda=newOleDbDataAdapter(strSql,conn);da.Fill(dt);catch(System.Exceptionex)/donothingif(dt!=null&dt.Rows.Count0)result=dt.Rows00.ToString();returnresult;publ

6、icvoidExecuteNonResult(stringstrSql)tryOleDbConnectionconn=this.GetConnection();OleDbCommandcmd=newOleDbCommand(strSql,conn);cmd.ExecuteNonQuery();catch(System.Exceptionex)/donothingprivate_WorkbookW_B(Excel.Applicationapp)Workbooksworkbooks=app.Workbooks;_Workbookworkbook=workbooks.Add(XlWBATemplat

7、e.xlWBATWorksheet);returnworkbook;private_WorksheetW_S(_Workbookwork_book)Sheetssheets=work_book.Worksheets;_Worksheetworksheet=(_Worksheet)sheets.get_Item(1);/獲取選擇第一個表，本身默認名稱為Sheet1returnworksheet;/從DataGridView中導出數據到Excel表,單表導出publicvoidExcel_out(DataGridViewdataGridView1)/建立Excel對象Excel.Applicati

8、onapp=newExcel.Application();try_Workbookworkbook=this.W_B(app);_Worksheetworksheet=this.W_S(workbook);stringsLen=;/取得最后一列列名charH=(char)(64+dataGridView1.ColumnCount/26);charL=(char)(64+dataGridView1.ColumnCount%26);if(dataGridView1.ColumnCount26)sLen=L.ToString();elsesLen=H.ToString()+L.ToString();

9、/標題stringsTmp=sLen+1;RangeranCaption=worksheet.get_Range(sTmp,A1);stringasCaption=newstringdataGridView1.ColumnCount;for(inti=0;idataGridView1.ColumnCount;i+)asCaptioni=dataGridView1.Columnsi.HeaderText;ranCaption.Value2=asCaption;/數據objectobj=newobjectdataGridView1.Columns.Count;for(intr=0;rdataGri

10、dView1.RowCount-1;r+)for(intl=0;ldataGridView1.Columns.Count;l+)if(dataGridView1l,r.ValueType=typeof(DateTime)objl=dataGridView1l,r.Value.ToString();elseobjl=dataGridView1l,r.Value;stringcell1=sLen+(int)(r+2).ToString();stringcell2=A+(int)(r+2).ToString();Rangeran=worksheet.get_Range(cell1,cell2);ra

11、n.Value2=obj;/保存workbook.SaveCopyAs(this._fileName);workbook.Saved=true;finally/關閉app.UserControl=false;app.Quit();/多表導出/DataGridView列表集合/表名稱集合publicvoidExcel_out_MulTable(ListdataGridView,stringTableList)/建立Excel對象Excel.Applicationapp=newExcel.Application();tryWorkbooksworkbooks=app.Workbooks;/定義一個

12、工作簿集合_Workbookworkbook=workbooks.Add(XlWBATemplate.xlWBATWorksheet);/向工作簿添加一個新工作簿Sheetssheets=workbook.Worksheets;/定義一個工作表集合_Worksheetworksheet;intwnumber=0;while(wnumber+(TableList.GetLength(0)-1)sheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value);/向一個工作表集合添加一個新工作表/*提醒：Missing類為命名空間

13、System.Reflection中的類，所以記得引入*/wnumber=0;foreach(DataGridViewdataGridView1indataGridView)worksheet=null;worksheet=(_Worksheet)sheets.get_Item(wnumber+1);/取出需要進行操作的工作表worksheet.Name=TableListwnumber;/設置改工作表名稱if(wnumber!=0)sheets.Select(wnumber);/選中操作表stringsLen=;/取得最后一列列名charH=(char)(64+dataGridView1.C

14、olumnCount/26);charL=(char)(64+dataGridView1.ColumnCount%26);if(dataGridView1.ColumnCount26)sLen=L.ToString();elsesLen=H.ToString()+L.ToString();/標題stringsTmp=sLen+1;RangeranCaption=worksheet.get_Range(sTmp,A1);stringasCaption=newstringdataGridView1.ColumnCount;for(inti=0;idataGridView1.ColumnCount;

15、i+)asCaptioni=dataGridView1.Columnsi.HeaderText;ranCaption.Value2=asCaption;/數據objectobj=newobjectdataGridView1.Columns.Count;for(intr=0;rdataGridView1.RowCount-1;r+)for(intl=0;ldataGridView1.Columns.Count;l+)if(dataGridView1l,r.ValueType=typeof(DateTime)objl=dataGridView1l,r.Value.ToString();elseobjl=dataGridView1l,r.Value;stringcell1=sLen+(int)(r+2).