class1.cs

将MS SQL中的数据导入Excel文件 C#源代码

//定义方法GetData(),返回一个数据表
        private DataTable GetData()
        {
            SqlConnection conn = new SqlConnection(@"Server=PXGD2;Initial Catalog=pingxiang;Uid=sa;Pwd=;");
            SqlDataAdapter adapter = new SqlDataAdapter("select username 用户名,catalyst_port 占用端口,home_address 住宅地址,ip_address ip地址,phone 电话,addtime 开通日期 from userinfo where catalyst_port=1 or catalyst_port=' order by ip_address desc", conn);

            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds, "Customer");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return ds.Tables[0];
        }

        //按钮
        private void button1_Click(object sender, System.EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;

            excel.Application.Workbooks.Add(true);

            DataTable table = GetData();

            //将所得到的表的列名,赋值给单元格
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            //同样方法处理数据
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
            //不可见,即后台处理
            excel.Visible = true;
        }