form1.cs

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using ExportToExcel;

namespace Sql2Excel
{
    public partial class Form1 : Form
    {
        private string _filepath = string.Empty;
        List<string[]> celltitle = null;
        string[] tablename = null;
        string[] tabletitle = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            #region 表头

            //1.时段信息表,2.库容信息表,3.日志信息表,4.用户信息表,5.大坝信息表,6.大坝代码表,7.水位信息表,8.水位数据表
            string[] celltitle1 ={ "测站", "类型", "编号", "开始时间", "结束时间" };
            string tabletitle1 = "STCD,TYPE, BH, KS ,JS";
            string[] celltitle2 ={ "测站", "编号", "水位", "库容" };
            string tabletitle2 = "STCD,BH,SW,KR ";

            string[] celltitle3 ={ "时间", " 来源 ", "内容 ", "类型 " };
            string tabletitle3 = "TM, LY, NR, TYPE";

            string[] celltitle4 ={ "用户名", " 密码 ", "权限" };
            string tabletitle4 = "Name, Password, Power";

            string[] celltitle5 ={ "大坝名称", " 编号", "参数名", "参数值" };
            string tabletitle5 = "DAM, BH ,NAME, DATA";

            string[] celltitle6 ={ "大坝名称", "代码" };
            string tabletitle6 = "STCD, CODE";

            string[] celltitle7 ={ "编号", "名称", "类型", "大坝", "序号" };
            string tabletitle7 = "STCD,NAME,TYPE,DAM,XH";

            string[] celltitle8 ={ "编号", "时间", " 数据" };
            string tabletitle8 = "STCD,TM,DATA";
            #endregion
            celltitle = new List<string[]>();
            celltitle.Add(celltitle1);
            celltitle.Add(celltitle2);
            celltitle.Add(celltitle3);
            celltitle.Add(celltitle4);
            celltitle.Add(celltitle5);
            celltitle.Add(celltitle6);
            celltitle.Add(celltitle7);
            celltitle.Add(celltitle8);

            ProgressBar.Visible = false;
            string[] tabletext ={ "时段信息表", "库容信息表", "日志信息表", "用户信息表", "大坝信息表", "大坝代码表", "水位信息表", "水位数据表" };
            for (int i = 0; i < tabletext.Length; i++)
                ckltbox.Items.Add(tabletext[i]);
            tablename = new string[] { "T_P_QX_SD", "T_P_QX_KR", "T_P_LOG", "T_P_USER", "T_P_SW_DAM", "T_P_SDZ", "T_P_SW", "T_R_SW" };
            tabletitle = new string[] { tabletitle1, tabletitle2, tabletitle3, tabletitle4, tabletitle5, tabletitle6, tabletitle7, tabletitle8 };
        }

        private void toolbar_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            if (ckltbox.CheckedItems.Count != 0)
            {
                FolderBrowserDialog fb = new FolderBrowserDialog();
                if (DialogResult.OK == fb.ShowDialog())
                {
                    _filepath = fb.SelectedPath;
                    ProgressBar.Visible = true;
                    ProgressBar.Value = 0;
                    ProgressBar.Maximum = ckltbox.CheckedItems.Count * 2;

                    #region ExcelManager封装类导出Excel
                    String strConnet = "Data Source=200.2.11.10;Password = ;User ID=sa;Initial Catalog=SQ";
                    System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(strConnet);
                    for (int i = 0; i < celltitle.Count; i++)
                    {
                        SaveExcel(sqlConn, tablename[i], celltitle[i], tabletitle[i],
                            ckltbox.Items[i].ToString(), _filepath + "\\" + ckltbox.Items[i].ToString(),
                           ckltbox.GetItemChecked(i));
                        System.Windows.Forms.Application.DoEvents();
                    }

                    #endregion
                    ProgressBar.Visible = false;
                }
            }
        }
        private void SaveExcel(SqlConnection sqlConn, string tablename, string[] celltitle, string tabletitle, string exceltitle, string savepath, bool isoutput)
        {
            if (isoutput == true)
            {
                ExcelManager exc = new ExcelManager(65530, tablename, sqlConn);
                ProgressBar.Value += 1;
                try
                {
                    exc.DeclareExcelApp(celltitle, tabletitle, tablename, exceltitle);
                    ProgressBar.Value += 1;
                    exc.SaveExcelApp(savepath);
                }
                catch (Exception E)
                {
                    MessageBox.Show(E.ToString());
                }
                finally
                {
                    exc.Dispose();
                    exc.KillExcel();
                }
            }
        
        }
        private void button1_Click(object sender, EventArgs e)
        {
            

        }

        private void CheckAll(bool blIsCheck)
        {
            for (int i = 0; i < ckltbox.Items.Count; i++)
                ckltbox.SetItemChecked(i,blIsCheck);
        }
        private void button1_Click_1(object sender, EventArgs e)
        {
            CheckAll(true);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            CheckAll(false);
        }
    }
}