dataset 导出到 excel

最新推荐文章于 2025-09-18 10:03:23 发布

转载最新推荐文章于 2025-09-18 10:03:23 发布 · 634 阅读

Asp.net 专栏收录该内容

115 篇文章

订阅专栏

本文介绍了一个使用C#实现从数据库读取数据并将其导出为Excel文件的方法。该程序通过SaveFileDialog让用户选择保存路径及文件名，并定义了连接字符串及SQL查询语句来获取数据。利用SqlDataAdapter填充DataSet后，再将数据逐条写入到指定路径的Excel文件中。

         private void button1_Click(object sender, EventArgs e)
        {            
            saveFileDialog1.Title = "保存的excel文件";
            saveFileDialog1.InitialDirectory = "c:\\";
            saveFileDialog1.Filter = "Excel97-2003 (*.xls)|*.xls|All Files (*.*)|*.*";
            saveFileDialog1.ShowDialog();
            if (saveFileDialog1.FileName == "" || saveFileDialog1.FileName == null)
            {
                MessageBox.Show("文件名不能为空!");
                return;
            }
            string path = saveFileDialog1.FileName;
            string constr = "Data Source=.;Initial Catalog=Exhibition;User ID=sa;Password=";
            string sql = GetStrSql();
            DataSet ds=new DataSet();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
            }
            if (ds == null)
            {
                MessageBox.Show("数据获取有误!");
                return;
            }
            WriteExcel(ds, path);      
        }

 public void WriteExcel(DataSet ds, string path)
        {
            try
            {
                long totalCount = ds.Tables[0].Rows.Count;
                lblTip.Text = "共有" + totalCount + "条数据。";
                Thread.Sleep(1000);
                long rowRead = 0;
                float percent = 0;

                StreamWriter sw = new StreamWriter(path, false,Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                {
                    sb.Append(ds.Tables[0].Columns[k].ColumnName.ToString() + "\t");
                }
                sb.Append(Environment.NewLine);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                    Pbar.Maximum = (int)totalCount;
                    Pbar.Value = (int)rowRead;
                    lblTip.Text = "正在写入[" + percent.ToString("0.00") + "%]...的数据";
                    System.Windows.Forms.Application.DoEvents();

                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        sb.Append(ds.Tables[0].Rows[i][j].ToString() + "\t");
                    }
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                MessageBox.Show("已经生成指定Excel文件!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

public string GetStrSql()
        {
            string strSql = "";
            return strSql;
        }