转--------C#访问Excell

最新推荐文章于 2023-10-03 00:00:27 发布

jjjjyyyyy

最新推荐文章于 2023-10-03 00:00:27 发布

阅读量827

点赞数

文章标签： dataset microsoft insert properties object null

本文链接：https://blog.csdn.net/jjjjyyyyy/article/details/1584148

版权

C#访问Excell可以有以下两种方法:

一、OleDb访问：

using System.Data.OleDb;

try
               {//选择Excell中数据
                   OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=imagedata.xlsx;Extended Properties=Excel 12.0");//此处为office2007，其他版本的连接方法可在网上搜到，不再赘述
                   conn.Open();
                   string sql = "select Test1,Test2,Test3 from [Sheet1$]";//Test1、Test2、Test3分别指Excell中第一行，就像数据库列名一样
                   OleDbCommand com = new OleDbCommand(sql, conn);
                   OleDbDataAdapter ad = new OleDbDataAdapter(com);
                   DataSet ds = new DataSet();
                   ad.Fill(ds);
                   System.Data.DataTable dt = ds.Tables[0];
                   this.dataGridView1.DataSource = dt;
                   this.dataGridView1.Update();

//插入新数据

                  OleDbDataAdapter myCommand = new OleDbDataAdapter(sql,conn);
                   OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
                   //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
                   builder.QuotePrefix = "[";        //获取insert语句中保留字符（起始位置）
                   builder.QuoteSuffix = "]"; //获取insert语句中保留字符（结束位置）
                   DataSet newds = new DataSet();
                   myCommand.Fill(newds, "Table1");
                   for (int i = 0; i < 4; i++)
                   {
                       DataRow nrow = newds.Tables[0].NewRow();
                       for (int j = 0; j < newds.Tables[0].Columns.Count; j++)
                       {
                           nrow[j] = "新插入值        ："+i.ToString()+j.ToString();
                       }
                       newds.Tables[0].Rows.Add(nrow);
                   }
                   myCommand.Update(newds, "Table1");
                   conn.Close();

               }
               catch (OleDbException ex)
               {
               }

二：用COM组件

首先添加引用，在COM下选择Microsoft Excell 12.0 Object Library（12.0为Excell2007，也可为其他版本，如9.0）

using Microsoft.Office.Interop.Excel;

//以下为具体访问代码

             object m_objOpt = System.Reflection.Missing.Value;
               Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
               Workbooks wbs = (Workbooks)app.Workbooks;
               Workbook wb = (Workbook)wbs.Open(System.Windows.Forms.Application.StartupPath + "//imagedata.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               _Worksheet sheet = (_Worksheet)wb.Worksheets[1];//第一张工作单，没有0索引
               sheet.Cells[1, 1] = "Test";//同样指第一个单元格，无0索引
               wb.Save();
               wbs.Close();
               System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
               System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
               System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
               wbs = null;
               wb = null;
               sheet = null;
               GC.Collect();

//一定要注意减少COM对象的引用记数