c#数据集DataSet导出Excel

1、打开Microsoft Visual Studio 2005，新建一项目Test, 在项目下建一数据查询窗体，如下：

2、双击<查询>进入button1_Click事件，编写如下代码：public partial class Form1 : Form { SqlConnection con; SqlDataAdapter da; DataSet ds=new DataSet(); string str_connection = "SERVER=.;DATABASE=ERP;UID=sa;PWD=sa"; public Form1() { InitializeComponent(); } //订单数据表Orders,数据查询语句 string str_select = "select * from Orders"; private void button1_Click(object sender, EventArgs e) { try { //初始化清空数据集ds ds.Clear(); con = new SqlConnection(str_connection); //打开数据连接 con.Open(); da = new SqlDataAdapter(str_select, con); //填充数据集到ds da.Fill(ds); //绑定数据到DataGridView1 this.dataGridView1.DataSource = ds.Tables[0].DefaultView; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { con.Close(); } } }

3、在Test项目下新建一个类方法，命名为：ExportClass.cs，代码如下：class ExportClass { /// <summary> /// DataSet数据集导出到Excel /// </summary> /// <param name="dgv">DataSet控件</param> /// <param name="strTitle">导出的Excel标题</param> public void DataGridViewExportToExcel(DataSet ds, string strTitle) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = false; saveFileDialog.FileName = strTitle + ".xls"; if (saveFileDialog.ShowDialog() == DialogResult.Cancel) //导出时，点击【取消】按钮 { return; } Stream myStream = saveFileDialog.OpenFile(); StreamWriter sw = new StreamWriter(myStream, 　　　　　　System.Text.Encoding.GetEncoding(-0)); string strHeaderText = ""; try { //写标题 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { if (i > 0) { strHeaderText += "\t"; } strHeaderText += ds.Tables[0].Columns[i].ToString(); } sw.WriteLine(strHeaderText); //写内容 string strItemValue = ""; for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { strItemValue = ""; for (int k = 0; k < ds.Tables[0].Columns.Count; k++) { if (k > 0) { strItemValue += "\t"; } strItemValue += ds.Tables[0].Rows[j][k].ToString(); } sw.WriteLine(strItemValue); //把dgv的每一行的信息写为sw的每一行 } } catch (Exception ex) { MessageBox.Show(ex.Message, "软件提示"); throw ex; } finally { sw.Close(); myStream.Close(); } } }

4、双击<导出当前数据到Excel>按钮进入button2_Click事件，编写代码如下： //实例化导出Excle类 ExportClass MyExcel_Class = new ExportClass(); //调用导出方法 MyExcel_Class.DataGridViewExportToExcel(ds, "查询导出Excel");

5、到此代码编写完成，点Debug编译运行代码或按功能健F5运行，弹出如下窗体。