将sql数据导入到Excel

刚好看到一个百度空间的代码，果断转过来了，直接看代码：

初学C#，多多指教

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data.SqlClient;

namespace SQL导出到Excel
{
    public partial class SQLtoExcel : Form
    {
        public SQLtoExcel()
        {
            InitializeComponent();
        }

        private void BtnToExcel_Click(object sender, EventArgs e)
        {
            try
            {
                //选择文件路径和文件名的对话框
                SaveFileDialog SaveExcelDlg = new SaveFileDialog();
                //设置文件过滤器（文件扩展名）
                SaveExcelDlg.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";

                if (SaveExcelDlg.ShowDialog() == DialogResult.OK)
                {
                    //以下是SQL相关的定义
                    SqlConnection MySqlConnection = new SqlConnection();
                    SqlCommand MySqlCommand = new SqlCommand();
                    DataSet MyDataSet = new DataSet();
                    SqlDataAdapter MySqlDataAdpter;

                    string SqlConnStr = "server=" + TxtServer.Text.Trim()
                                        + ";Database=" + TxtDB.Text.Trim()
                                        + ";Uid=" + TxtUid.Text.Trim()
                                        + ";Pwd=" + TxtPwd.Text.Trim();

                    MySqlConnection.ConnectionString = SqlConnStr;
                    MySqlConnection.Open();//连接数据库
                    MySqlCommand.CommandType = CommandType.Text;
                    MySqlCommand.CommandText = "SELECT * FROM " + TxtTable.Text.Trim();
                    MySqlCommand.Connection = MySqlConnection;

                    MySqlDataAdpter = new SqlDataAdapter(MySqlCommand);
                    MySqlDataAdpter.Fill(MyDataSet);

                    MySqlConnection.Close();//关闭数据库连接

                    //以下是Excel相关的定义
                    Microsoft.Office.Interop.Excel.Application ExcelApplication = new Microsoft.Office.Interop.Excel.Application(); //Excel应用程序
                    //决定创建excel时，表格是否可见
                    ExcelApplication.Visible = true;
                    //设置禁止弹出保存的询问提示框
                    ExcelApplication.DisplayAlerts = false;
                    //设置禁止弹出覆盖的询问提示框
                    ExcelApplication.AlertBeforeOverwriting = false;

                    //创建工作簿并加入一张工作表
                    Workbook ExcelWorkbook = ExcelApplication.Application.Workbooks.Add(1);
                    Worksheet ExcelWorksheet = new Worksheet();
                    ExcelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkbook.Sheets[1];
                    ExcelWorksheet.Name = TxtTable.Text.Trim();

                    //以下代码在Excel表第一行插入表头，即MyDataSet.Tables[0]这个表的列名
                    for (int i = 0; i < MyDataSet.Tables[0].Columns.Count; i++)
                    {
                        ExcelWorksheet.Cells[1, i + 1] = MyDataSet.Tables[0].Columns[i].ColumnName.Trim();
                    }

                    //Excel表格中将要插入的行和列的数量
                    int ExcelRows = MyDataSet.Tables[0].Rows.Count;
                    int ExcelColumns = MyDataSet.Tables[0].Columns.Count;

                    //把MyDataSet中的数据导入到Excel中
                    for (int i = 0; i < ExcelRows; i++)
                    {
                        for (int j = 0; j < ExcelColumns; j++)
                        {
                            //下面这一句只是为了增强可视化效果，每次在向Excel单元格写数据前先进行选中
                            ExcelWorksheet.get_Range(ExcelWorksheet.Cells[i + 2, j + 1], ExcelWorksheet.Cells[i + 2, j + 1]).Select();
                            //这一句的作用就是把MyDataSet中的数据写到Excel单元格
                            ExcelWorksheet.Cells[i + 2, j + 1] = MyDataSet.Tables[0].Rows[i][j];
                        }
                    }

                    //保存Excel
                    ExcelWorkbook.SaveAs(SaveExcelDlg.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate,
                                         Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                         Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    ExcelWorkbook.Close(true, Missing.Value, Missing.Value);//关闭工作簿
                    ExcelApplication.Quit();//结束Excel进程
                    pictureBox1.Visible = true;

                    MessageBox.Show("数据已经导出到以下位置：\n" + SaveExcelDlg.FileName);
                   
                }
                else
                {
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.Trim());
            }
        }

       

    }

      
     
}