利用OleDb方式对DataSet 和 Excel 数据快速导入导出

一：DataSet 导入到Excel

       /// <summary>
        /// 导出数据到Excel
        /// </summary>
        /// <param name="Path">需要导入的Excel地址</param>
        /// <param name="oldds">需要导入的数据</param>
        /// <param name="TableName">表名</param>
        public static void DSToExcel2003(string Path, DataSet oldds, string TableName)
        {
            //Excel2003的连接字符串
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
            //执行导入
            ExcuteSQL(oldds, TableName, strCon);

        }

        /// <summary>
        /// 导出数据到Excel
        /// </summary>
        /// <param name="Path">需要导入的Excel地址</param>
        /// <param name="oldds">需要导入的数据</param>
        /// <param name="TableName">表名</param>
        public static void DSToExcel2007(string Path, DataSet oldds, string TableName)
        {
            //Excel2007的连接字符串
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
            //执行导入
            ExcuteSQL(oldds, TableName, strConn);

        }
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="oldds">需要导入的数据</param>
        /// <param name="TableName">表名</param>
        /// <param name="strCon">连接字符串</param>
        private static void ExcuteSQL(DataSet oldds, string TableName, string strCon)
        {
            //连接
            OleDbConnection myConn = new OleDbConnection(strCon);

            string strCom = "select * from [" + TableName + "$]";

            try
            {
                myConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

                System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);

                //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
                //获取insert语句中保留字符（起始位置）
                builder.QuotePrefix = "[";

                //获取insert语句中保留字符（结束位置）
                builder.QuoteSuffix = "]";

                DataSet newds = new DataSet();
                //获得表结构
                DataTable ndt = oldds.Tables[0].Clone();
                //清空数据
                //ndt.Rows.Clear();

                ndt.TableName = TableName;
                newds.Tables.Add(ndt);

                //myCommand.Fill(newds, TableName);

                for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
                {
                    //在这里不能使用ImportRow方法将一行导入到news中，
                    //因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
                    //在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added
                    DataRow nrow = newds.Tables[0].NewRow();
                    for (int j = 0; j < oldds.Tables[0].Columns.Count; j++)
                    {
                        nrow[j] = oldds.Tables[0].Rows[i][j];
                    }
                    newds.Tables[0].Rows.Add(nrow);
                }
                //插入数据
                myCommand.Update(newds, TableName);
            }
            finally
            {
                myConn.Close();
            }
        }

使用实例

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {

            //string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
            string excelModlePath = @"G:\Study\ExcelOper\Model\学籍表.xls";

            //文件保存对话框
            System.Windows.Forms.SaveFileDialog fd = new SaveFileDialog();

            fd.DefaultExt = ".xls";


            //string savefilepath = @"G:\Study\ExcelOper\SaveFolder\study.xls";

            if (fd.ShowDialog() == DialogResult.OK)
            {
                using (ExcelHelper excel = new ExcelHelper())
                {
                    excel.Open(excelModlePath);
                    //保存数据到用户指定的文件夹
                    excel.SaveFile(fd.FileName);
                }

                string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";

                DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");

                ds.Tables[0].TableName = "study";

                ExcelHelper.DSToExcel2003(fd.FileName, ds, "study");
            }
        }

二 Excel导出到DataSet

       /// <summary>
        /// 查询Excle2007的数据
        /// </summary>
        /// <param name="Path"></param>
        /// <returns></returns>
        public static DataSet Excel2007ToDS(string Path, string SheetName)
        {
            //Excel2007链接字符串
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
            return ExcuteQuery(strConn, SheetName);
        }

        /// <summary>
        /// 查询Excle2003的数据
        /// </summary>
        /// <param name="Path"></param>
        /// <returns></returns>
        public static DataSet Excel2003ToDS(string Path, string SheetName)
        {
            //Excel2003链接字符串
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

            return ExcuteQuery(strConn, SheetName);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="strConn"></param>
        /// <returns></returns>
        private static DataSet ExcuteQuery(string strConn, string SheetName)
        {
            //默认sheet名
            if (string.IsNullOrEmpty(SheetName))
            {
                SheetName = "Sheet1";
            }

            //链接
            OleDbConnection conn = new OleDbConnection(strConn);
            DataSet ds = null;
            try
            {
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;

                strExcel = "select * from [" + SheetName + "$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                //查询数据
                myCommand.Fill(ds, SheetName);
            }
            finally
            {
                conn.Close();
            }
            //

            return ds;
        }

使用实例

        private void btnImport_Click(object sender, EventArgs e)
        {
            //openFileDialog1.ShowDialog();

            //if (openFileDialog1.RestoreDirectory)
            //{

            //}

            string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                TransferData(fd.FileName, "study", connString);

                DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");

                dataGridView1.DataSource = ds.Tables[0];
            }

        }

注意事项：

一：DataSet导入到Excel时导入的Excel的文件的Sheet名要和要导入的DataTable名一致，

Sheet的数据列名也要和DataTable列名一致放在Sheet的第一排

如下图:

二：Excel导出数据到DataSet的SheetName 需要和实际的Sheet名一致

总结：和直接使用office内核访问Excel数据的方式相比相信这种方式的读写速度上会占有很大优势

并且不用担心非托管资源的释放

幽夜底衣角，那一片清风。