exportddform.cs

用Excel建表

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 System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace DBApplicateSystem
{
    public partial class ExportDDForm : Form
    {
        string connStringName = string.Empty;
        SaveFileDialog saveFileDialog1 = new SaveFileDialog();
        DataSet ds = new DataSet();
        bool tf = true;
        public ExportDDForm()
        {
            InitializeComponent();
        }

        private void btnConfigSQLServer2005_Click(object sender, EventArgs e)
        {
            // 需.Net库adodb.dll和COM库(Microsoft Ole DB Service Component 1.0 Type Library)支持
            try
            {
                ADODB._Connection ADOcon;
                ADOcon = new ADODB.ConnectionClass();
                MSDASC.DataLinks msLink = new MSDASC.DataLinks();
                ADOcon = (ADODB._Connection)msLink.PromptNew();
                if (!object.Equals(null, ADOcon))
                {
                    lbDBName.Text = "数据库：" + ADOcon.ConnectionString.Split(';')[4].Substring(ADOcon.ConnectionString.Split(';')[4].IndexOf('=') + 1);
                    connStringName = ADOcon.ConnectionString.Substring(ADOcon.ConnectionString.IndexOf(';') + 1);
                }
                string strSql = "select   [Name]   from   sysobjects   where   type='U'";
                SqlConnection connection = new SqlConnection(connStringName);
                SqlCommand command = new SqlCommand(strSql, connection);
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DBTable.Items.Clear();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    this.DBTable.Items.Add(ds.Tables[0].Rows[i][0].ToString());
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }
        }

        private void btAdd_Click(object sender, EventArgs e)
        {
            ArrayList al = new ArrayList();
            foreach (int i in this.DBTable.SelectedIndices)
            {
                al.Add(DBTable.GetItemText(DBTable.Items[i]));
                ChoosedTable.Items.Add(DBTable.GetItemText(DBTable.Items[i]));
            }

            for (int index = 0; index < al.Count; index++)
            {
                DBTable.Items.Remove(DBTable.Items[DBTable.FindStringExact(al[index].ToString())]);
            }

        }

        private void btDel_Click(object sender, EventArgs e)
        {
            ArrayList al = new ArrayList();
            foreach (int i in this.ChoosedTable.SelectedIndices)
            {
                al.Add(ChoosedTable.GetItemText(ChoosedTable.Items[i]));
                DBTable.Items.Add(ChoosedTable.GetItemText(ChoosedTable.Items[i]));
            }

            for (int index = 0; index < al.Count; index++)
            {
                int ii = ChoosedTable.FindStringExact(al[index].ToString());
                ChoosedTable.Items.Remove(ChoosedTable.Items[ii]);
            }
        }

        private void btExport_Click(object sender, EventArgs e)
        {
            string strsql = "SELECT "+
                          "(case when a.colorder=1 then d.name else '' end)表名,"+
                          "a.name 字段名, " +
                          "(case when (SELECT count(*) "+
                          "FROM sysobjects "+
                          "WHERE (name in "+
                          "(SELECT name " +
                          "FROM sysindexes "+
                          "WHERE (id = a.id) AND (indid in "+
                          "(SELECT indid "+
                          "FROM sysindexkeys "+
                          "WHERE (id = a.id) AND (colid in "+
                          "(SELECT colid "+
                          "FROM syscolumns "+
                          "WHERE (id = a.id) AND (name = a.name))))))) AND "+
                          "(xtype = 'PK'))>0 then '是' else '否' end) 主键, "+
                          "b.name 类型, "+
                          "(case when b.name='int' or b.name='datetime' or b.name='float' or b.name='money'  then '' "+
                          "when COLUMNPROPERTY(a.id,a.name,'PRECISION')=-1 then 'Max' else convert(char,COLUMNPROPERTY(a.id,a.name,'PRECISION')) end) 长度," +
                          "(case when a.isnullable=1 then '是'else '否' end) 允许空 "+
                          "FROM syscolumns a left join systypes b "+
                          "on a.xtype=b.xusertype "+
                          "inner join sysobjects d "+
                          "on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' "+
                          "where "+
                          "#strwhere  "+
                          "order by a.id,a.colorder ";
            string strwhere = string.Empty;
            for (int i = 0; i < ChoosedTable.Items.Count; i++)
            {
                strwhere = strwhere + " d.name='" + ChoosedTable.Items[i].ToString() + "' or";
            }
            strwhere = strwhere.Substring(0, strwhere.Length-2);
            strsql = strsql.Replace("#strwhere", strwhere);
            SqlConnection connection = new SqlConnection(connStringName);
            SqlCommand command = new SqlCommand(strsql, connection);
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds);

            saveFileDialog1.Filter = "Excel files (*.xsl)|*.xsl";
            saveFileDialog1.FilterIndex = 2;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.OverwritePrompt = true;
            saveFileDialog1.FileName ="数据字典.xls";
            if (saveFileDialog1.FileName == "")
            {
                return;
            }
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                bgwExportDD.RunWorkerAsync();
                pBExport.Style = ProgressBarStyle.Marquee;
                pBExport.Visible = true;
            }

        }

        private void bgwExportDD_DoWork(object sender, DoWorkEventArgs e)
        {
            SaveExcel(saveFileDialog1.FileName);
        }
        private void SaveExcel(string filename)
        {

            Microsoft.Office.Interop.Excel.Application xls;
            Excel.Workbook workbook;
            Excel.Worksheet wooksheet;

            xls = new Excel.Application();
            workbook = xls.Workbooks.Add(true);
            wooksheet = (Excel.Worksheet)workbook.ActiveSheet;
            wooksheet.Name = "数据字典";

            DataTable dtable = ds.Tables[0];
            for (int i = 0; i < dtable.Columns.Count; i++)
            {
                xls.Cells[1, i + 1] = dtable.Columns[i].ColumnName;
            }
            wooksheet.get_Range(xls.Cells[1, 1], xls.Cells[1, dtable.Columns.Count]).Interior.ColorIndex = 37;//设置背景为天兰色
            wooksheet.get_Range(xls.Cells[1, 1], xls.Cells[1, dtable.Columns.Count]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            wooksheet.get_Range(xls.Cells[1, 1], xls.Cells[1, dtable.Columns.Count]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            wooksheet.get_Range(xls.Cells[1, 1], xls.Cells[1, dtable.Columns.Count]).Font.Bold = true;
            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                for (int j = 0; j < dtable.Columns.Count; j++)
                {
                    xls.Cells[i + 2, j + 1] = dtable.Rows[i][j].ToString();
                }
            }
            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                if (dtable.Rows[i][0].ToString() != "")
                {
                    for (int j = 1; i + j < dtable.Rows.Count; j++)
                    {
                        if (dtable.Rows[i + j][0].ToString() != "")
                        {
                            wooksheet.get_Range(xls.Cells[i + 2, 1], xls.Cells[i + j + 1, 1]).MergeCells = true;
                            break;
                        }
                        if (i + j == dtable.Rows.Count - 1)
                        {
                            wooksheet.get_Range(xls.Cells[i + 2, 1], xls.Cells[i + 2 + j, 1]).MergeCells = true;
                        }
                    }
                }

            }
            wooksheet.get_Range(xls.Cells[1, 1], xls.Cells[dtable.Rows.Count + 1, dtable.Columns.Count]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            workbook.SaveCopyAs(filename);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wooksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);
            KillExcel();
            GC.Collect();

        }

        private void KillExcel()
        {
            try
            {

                Process[] processes = Process.GetProcesses();
                foreach (Process process in processes)
                {
                    if (process.ProcessName.ToLower().Equals("excel"))
                    {
                        process.Kill();
                    }
                }
            }
            catch (Exception exception)
            {
                exception.Message.ToString();
            }
        }

        private void bgwExportDD_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            MessageBox.Show("保存成功！");
            pBExport.Visible = false;
        }
    }
}