C操作EXCEL全解代码

1、C#操作EXCEL全解（代码）提示：运行之前必须先引用Interop.Excel.dll模块using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.OleDb;using System.Windows.Forms;using System.Reflection;using Excel;namespace AnalysisSystem.DB public class ExcelOperation private string _fileName;/保存路

2、径名 public ExcelOperation(string fileName) _fileName = fileName; private OleDbConnection GetConnection() string connectString = string.Format(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=0;Extended Properties=Excel 8.0,_fileName); OleDbConnection myConn = new OleDbConnection(connectString);/建立链接 retu

3、rn myConn; public System.Data.DataTable ExecuteTableResult(string strSql) System.Data.DataTable dt = new System.Data.DataTable(); try OleDbConnection conn = this.GetConnection(); OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);/执行相关SQL语句 da.Fill(dt); catch (System.Exception ex) /do nothing

4、return dt; public DataSet ExecuteSetResult(string strSql,string table_name) DataSet ds = new DataSet(); string temp_name = + table_name + $; try OleDbConnection conn = this.GetConnection(); OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); da.Fill(ds,temp_name); catch (System.Exception ex) /

5、do nothing return ds; public string ExecuteOneResult(string strSql) string result = ; System.Data.DataTable dt = new System.Data.DataTable(); try OleDbConnection conn = this.GetConnection(); OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); da.Fill(dt); catch (System.Exception ex) /do nothin

6、g if (dt != null & dt.Rows.Count 0) result = dt.Rows00.ToString(); return result; public void ExecuteNonResult(string strSql) try OleDbConnection conn = this.GetConnection(); OleDbCommand cmd = new OleDbCommand(strSql, conn); cmd.ExecuteNonQuery(); catch (System.Exception ex) /do nothing private _Wo

7、rkbook W_B(Excel.Application app) Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); return workbook; private _Worksheet W_S(_Workbook work_book) Sheets sheets = work_book.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);/获取选择第一个表

8、，本身默认名称为Sheet1 return worksheet; /从DataGridView中导出数据到Excel表,单表导出 public void Excel_out(DataGridView dataGridView1) /建立Excel对象 Excel.Application app = new Excel.Application(); try _Workbook workbook = this.W_B(app); _Worksheet worksheet = this.W_S(workbook); string sLen = ; /取得最后一列列名 char H = (char)(

9、64 + dataGridView1.ColumnCount / 26); char L = (char)(64 + dataGridView1.ColumnCount % 26); if (dataGridView1.ColumnCount 26) sLen = L.ToString(); else sLen = H.ToString() + L.ToString(); /标题 string sTmp = sLen + 1; Range ranCaption = worksheet.get_Range(sTmp, A1); string asCaption = new stringdataG

10、ridView1.ColumnCount; for (int i = 0; i dataGridView1.ColumnCount; i+) asCaptioni = dataGridView1.Columnsi.HeaderText; ranCaption.Value2 = asCaption; /数据 object obj = new objectdataGridView1.Columns.Count; for (int r = 0; r dataGridView1.RowCount - 1; r+) for (int l = 0; l dataGridView1.Columns.Coun

11、t; l+) if (dataGridView1l, r.ValueType = typeof(DateTime) objl = dataGridView1l, r.Value.ToString(); else objl = dataGridView1l, r.Value; string cell1 = sLen + (int)(r + 2).ToString(); string cell2 = A + (int)(r + 2).ToString(); Range ran = worksheet.get_Range(cell1, cell2); ran.Value2 = obj; /保存 wo

12、rkbook.SaveCopyAs(this._fileName); workbook.Saved = true; finally /关闭 app.UserControl = false; app.Quit(); / / 多表导出 / / DataGridView列表集合 / 表名称集合 public void Excel_out_MulTable(List dataGridView, string TableList) /建立Excel对象 Excel.Application app = new Excel.Application(); try Workbooks workbooks = a

13、pp.Workbooks;/定义一个工作簿集合 _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);/向工作簿添加一个新工作簿 Sheets sheets = workbook.Worksheets;/定义一个工作表集合 _Worksheet worksheet ; int wnumber = 0; while (wnumber+ (TableList.GetLength(0) - 1) sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.

14、Value);/向一个工作表集合添加一个新工作表 /*提醒：Missing类为命名空间System.Reflection中的类，所以记得引入*/ wnumber = 0; foreach (DataGridView dataGridView1 in dataGridView) worksheet = null; worksheet = (_Worksheet)sheets.get_Item(wnumber + 1);/取出需要进行操作的工作表 worksheet.Name = TableListwnumber;/设置改工作表名称 if (wnumber != 0) sheets.Select(

15、wnumber);/选中操作表 string sLen = ; /取得最后一列列名 char H = (char)(64 + dataGridView1.ColumnCount / 26); char L = (char)(64 + dataGridView1.ColumnCount % 26); if (dataGridView1.ColumnCount 26) sLen = L.ToString(); else sLen = H.ToString() + L.ToString(); /标题 string sTmp = sLen + 1; Range ranCaption = workshe

16、et.get_Range(sTmp, A1); string asCaption = new stringdataGridView1.ColumnCount; for (int i = 0; i dataGridView1.ColumnCount; i+) asCaptioni = dataGridView1.Columnsi.HeaderText; ranCaption.Value2 = asCaption; /数据 object obj = new objectdataGridView1.Columns.Count; for (int r = 0; r dataGridView1.RowCount - 1; r+) for (int l = 0; l dataGridView1.Columns.Count; l+) if (dataGridView1l, r.ValueType = typeof(DateTime) objl = dataGridView1l, r.Value.ToString(); else objl = dataGridView1l, r.Value; string cell1 = sLen +