OLEDB操作EXCEL

OLEDB对EXCEL进行增删改查

       Microsoft.Office.Interop.Excel.ApplicationClass 将DataTable中的数据插入EXCEL

        /// <summary>
        /// 将DataSet里所有数据导入Excel.
        /// 需要添加COM: Microsoft Excel Object Library.
        /// using Excel;
        /// </summary>
        /// <param name="filePath">Excel文件的路径</param>
        /// <param name="ds">到导入Excel的数据源</param>
        private void ExportToExcel(string filePath, DataSet ds)
        {
            object oMissing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            try
            {
                //打开EXCEL文件
                Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                // Excel.Workbook xlWorkbook=xlApp.Workbooks.只有Open属性，没有Write属性
                Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
                //循环所有DataTable
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    //添加入一个新的Sheel页
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing);
                    //以TableName作为新加的sheel页名
                    xlWorksheet.Name = ds.Tables[i].TableName;
                    //取出这个DataTable中的所有值，暂时存于stringBuffer中
                    StringBuilder stringBuffer =new StringBuilder();
                   
                    for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                    {
                        for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
                        {
                            stringBuffer.Append( ds.Tables[i].Rows[j][k].ToString());
                            if (k < ds.Tables[i].Columns.Count - 1)
                                stringBuffer.Append("\t");
                        }
                        stringBuffer.Append("\n");
                    }
                    //利用系统剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject("");
                    //将stringBuffer放入剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
                    //选中这个sheel页中的第一个单元格
                    ((Excel.Range)xlWorksheet.Cells[1, 1]).Select();
                    //粘贴
                    xlWorksheet.Paste(oMissing, oMissing);
                    //清空系统剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject("");
                }
                //保存并关闭这个工作薄
                xlWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //释放...
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                GC.Collect();
            }
        }

       

        OleDbConnection connection;

        //打开数据库连接
        public void OpenConnection(string xlsFils) {
            if (!File.Exists(xlsFils))
            {
                MessageBox.Show("文件\"" + xlsFils + "\"不存在", "提示");

                return;
            }
            string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + xlsFils + ";Extended Properties='Excel 8.0;HDR=no;IMEX=0'";
            connection = new OleDbConnection(conn);
            connection.Open();
        }

        //查询数据
        public DataTable Select()
        {
            DataTable dt = new DataTable();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, connection);
            mycommand.Fill(dt);
            return dt;
        }
         

        private void Form1_Load(object sender, EventArgs e)
        {
            string xlsFile = System.Windows.Forms.Application.StartupPath + "/" + "ExcelFiles/test.xls";
            OpenConnection(xlsFile);
        }

        //插入数据
        public void Insert()
        {
            string sql = string.Format("insert into [Sheet1$] values('{0}','{1}','{2}')", "陈太汉", "陈晓玲", "520");
            OleDbCommand myCommand = new OleDbCommand(sql, connection);
            myCommand.ExecuteNonQuery();
            Select();
        }

        private void btAdd_Click(object sender, EventArgs e)
        {
            Insert();
        }

        //Excel不支持SQl语句的方式进行删除，可以用把每个字段的值设为空的方式进行删除
        public void Delete()
        {
            string sql = string.Format("Update [Sheet1$] set col1=NULL,col2=NULL,col3=NULL where col1='{0}'", "陈太汉");
            OleDbCommand myCommand = new OleDbCommand(sql, connection);
            myCommand.ExecuteNonQuery();
            Select();
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            Delete();
        }

        //更新数据
        private new void Update() {
            string sql = string.Format("update  [Sheet1$] set col1='{0}' where col1='{1}'", "陈晓玲","陈太汉");
            OleDbCommand myCommand = new OleDbCommand(sql, connection);
            myCommand.ExecuteNonQuery();
            Select();
        }
        private void btUpdate_Click(object sender, EventArgs e)
        {
            Update();
        }

        private void btSelect_Click(object sender, EventArgs e)
        {
            Select();

        }

注：1）使用 Excel 工作簿时，默认情况下，区域中的第一行是标题行（或字段名称）。如果第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 HDR=NO。如果您在连接字符串中指定 HDR=NO，Jet OLE DB 提供程序将自动为您命名字段（不管excel中的列叫什么名字，F1 表示第一个字段，F2 表示第二个字段，依此类推，select F1,F2 from [sheet1$]）；

2）IMEX=1将所有读入数据看作字符，其他值（0、2）请查阅相关帮助文档；

3）如果出现“找不到可安装的isam”错误，一般是连接字符串错误

DoOleSql(sql,"test.xls");

6、删除excel文件中的数据：不提倡使用这种方法

7、对于非标准结构的excel表格，可以指定excel中sheet的范围

1）读取数据：string sql = "select * from [sheet1$A3:F20]";

2）更新数据：string sql = "update [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";

3）插入数据：string sql = "insert into [sheet1$A9:F15](FieldName1,FieldName2,…) values('a',’b’,…)";

2003和2007的链接区别

//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件

string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件

//获取excel中都有哪些sheet

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);