c# 数据库的写下到excel

c# 数据库的写入到excel

access数据库结构

表名ZZZ 结构内容:

ID FF DD

1 2 3

4 5 6

7 8 9

新建一个c#窗体应用程序

添加一个ComboBox控件和三个button按键还有三个textbox文本框

button1 --->access数据库位置

button2 --->EXCEL位置

button3 --->导入

textbox1--->access数据库的路径

textbox2--->EXCEL的路径

textbox3--->EXCEL文件名

ComboBox1--->显示数据库表名

自定义函数1:

public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)        {            try            {                string tem_sql = "";//定义字符串                string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句                System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库                System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类                tem_conn.Open();//打开连接的Access数据库                tem_sql = "select Count(*) From " + AccTable;//设置SQL语句，获取记录个数                tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类                int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句，并返回结果                //每个Sheet只能最多保存65536条记录。                tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from ZZZ";//记录连接Excel的语句                tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类                tem_comm.ExecuteNonQuery();//执行SQL语句，将数据表的内容导入到Excel中                tem_conn.Close();//关闭连接                tem_conn.Dispose();//释放资源                tem_conn = null;                MessageBox.Show("导入完成");            }            catch (Exception ex)            {                MessageBox.Show(ex.Message, "提示！");            }        }

自定义函数2:

 public void GetTable(string Apath, ComboBox ComBox)        {            string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Apath + ";Persist Security Info=True";            System.Data.OleDb.OleDbConnection tem_OleConn = new System.Data.OleDb.OleDbConnection(connstr);            tem_OleConn.Open();            DataTable tem_DataTable = tem_OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });            tem_OleConn.Close();            ComBox.Items.Clear();            for (int i = 0; i &lt; tem_DataTable.Rows.Count; i++)            {                ComBox.Items.Add(tem_DataTable.Rows[i][2]);            }            if (ComBox.Items.Count &gt; 0)                ComBox.SelectedIndex = 0;        }

        private void button1_Click(object sender, EventArgs e)        {            openFileDialog1.FileName = "";            if (openFileDialog1.ShowDialog() == DialogResult.OK)            {                textBox1.Text = openFileDialog1.FileName;                GetTable(textBox1.Text, comboBox1);            }        }

        private void button2_Click(object sender, EventArgs e)        {            folderBrowserDialog1.SelectedPath = "";            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)                textBox2.Text = folderBrowserDialog1.SelectedPath;        }

        private void button3_Click(object sender, EventArgs e)        {            AccessGuideJoinExcel(textBox1.Text, comboBox1.Text, textBox2.Text + "\\" + textBox3.Text);         }

c# 数据库的写下到excel

热点推荐