从文本到DATATABLE再到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.IO;
//using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics;
namespace Win070116
{
    public partial class Form1 : Form
    {
        private Stopwatch stopWatch;
        System.Data.DataTable w2 = null;
        List<int> A3 = new List<int>();
        public Form1()
        {
            InitializeComponent();
            stopWatch = new Stopwatch();
        }
        //相符
        private void button1_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.DefaultExt = "txt";        
            string s1="";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                s1 = this.openFileDialog1.FileName;               
                            }
            if (s1 != "")
            {
                if (s1.IndexOf("_CGL_比对相符") >= 0)
                {
                    NewMethod1(s1);
                }
                else
                    MessageBox.Show("ERROR1");
                            }

        }
        private void button2_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.DefaultExt = "txt";
          
            string s1 = "";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                s1 = this.openFileDialog1.FileName;
            }
            if (s1 != "")
            {
                if (s1.IndexOf("_CGL_比对不符") >= 0)
                {
                    NewMethod1(s1);
                }
                else
                    MessageBox.Show("ERROR1");
            }
        }
        private void NewMethod1(string fil)
        {
            StreamReader m_streamReader = new StreamReader(fil);
            m_streamReader = new StreamReader(fil, Encoding.Default);
            m_streamReader.BaseStream.Seek(0, SeekOrigin.Begin);
            string myString = m_streamReader.ReadLine();
            char[] separator = { ';' };
            string[] sss;
            sss = myString.Split(separator);          
            this.dataGridView1.DataSource = null;
            List<string> A0 = new List<string>();
            A0.Clear();         
            A3.Clear();
            string ss;
            foreach (string s1 in sss)
            {
                if (s1.IndexOf("#") >= 0)
                    ss = s1.Remove(0, 1);
                else
                    ss = s1;
                //  MessageBox.Show(ss);
                A0.Add(ss);
            }
            w2 = new System.Data.DataTable("T2");
            w2.Columns.Add("ID", System.Type.GetType("System.Int32"));
            int k = 0;
            foreach (string s2 in A0)
            {
               k++;
                try
                {
                if (s2.IndexOf("额") >= 0)
                {
                    w2.Columns.Add(s2, System.Type.GetType("System.Decimal"));
                    A3.Add(k);
                }
                else
                    w2.Columns.Add(s2, System.Type.GetType("System.String"));
             
                }
                catch(Exception y)
                {
                    w2.Columns.Add(s2+"A", System.Type.GetType("System.String"));
                                    }
            }
            List<string> A1 = new List<string>();
            A1.Clear();
            myString = m_streamReader.ReadLine();
            while (myString != null)
            {
                A1.Add(myString);
                myString = m_streamReader.ReadLine();
            }
            m_streamReader.Close();
            int kk = 0;
            foreach (string s2 in A1)
            {
                kk++;
                char[] separator1 = { '~' };
                string[] sss1;
                sss1 = s2.Split(separator1);
                DataRow rr = w2.NewRow();
                rr["ID"] = kk;
                int ll = 0;
                foreach (string s3 in sss1)
                {
                    //  MessageBox.Show(s3);
                    ll++;
                    if (A3.Contains(ll))
                    {
                        //  MessageBox.Show(ll.ToString());
                        rr[ll] = Convert.ToDecimal(s3);
                    }
                    else
                        rr[ll] = s3;
                }
                w2.Rows.Add(rr);
                this.dataGridView1.DataSource = w2;
            }
        }
 
        private void button5_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.AppStarting;
            stopWatch.Start();
            object MyApp;
            object MyBook;
            object MyBooks;
            object MySheet;
            object MySheets;
            object MyRange;
            object[] MyParameters;         
            Type MyClassType;
            try
            {
                // 获取Excel类型
                MyClassType = Type.GetTypeFromProgID("Excel.Application");
                MyApp = Activator.CreateInstance(MyClassType);
                //获取workbooks集合
                MyBooks = MyApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, MyApp, null);
                //新增workbook.
                MyBook = MyBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, MyBooks, null);
                //获取worksheets集合
                MySheets = MyBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, MyBook, null);
                //获取第一个 worksheet.
                MyParameters = new object[1];
                MyParameters[0] = 1;
                MySheet = MySheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, MySheets, MyParameters);
                //获取A1单元格所在区域
                int kk = w2.Columns.Count;
                object[] objHeaders = new Object[w2.Columns.Count];
               MyParameters = new object[1];
                MyParameters[0] = "A1:" + Convert.ToString(Convert.ToChar(64 + w2.Columns.Count)) + "1";
              //  MyParameters[1] = Missing.Value;
                MyRange = MySheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, MySheet, MyParameters);
                //在A1单元格中填充数据.
                MyParameters = new object[1];
               // objHeaders[0] = w2.Columns[1].ToString();
                for (int i = 0; i < w2.Columns.Count; i++)
                {
                    objHeaders[i] = w2.Columns[i].ToString();

                }
                MyParameters[0] = objHeaders;
                MyRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, MyRange, MyParameters);
                Object[,] MyData = new Object[w2.Rows.Count, w2.Columns.Count];
                for (int ll = 0; ll < w2.Rows.Count; ll++)
                {
                    for (int ii = 0; ii < w2.Columns.Count; ii++)
                    {
                        if (A3.Contains(ii))
                            MyData[ll, ii] = w2.Rows[ll][ii];
                        else
                            MyData[ll, ii] = "'" + w2.Rows[ll][ii].ToString();                    }
                }
                MyParameters = new object[2];
                MyParameters[0] = "A2:" + Convert.ToString(Convert.ToChar(64 + w2.Columns.Count)) + (w2.Rows.Count + 1).ToString().Trim();
                MyParameters[1] = Missing.Value;
                MyRange = MySheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, MySheet, MyParameters);
                MyParameters = new object[1];
                MyParameters[0] = MyData;
                MyRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, MyRange, MyParameters);         
       
                MyParameters = new object[1];
                //启动 Excel
                MyParameters[0] = true;
                MyApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, MyApp, MyParameters);
                MyApp.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, MyApp, MyParameters);
            }
            catch (Exception MyEx)
            {               
                MessageBox.Show(MyEx.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            stopWatch.Stop();
            MessageBox.Show(stopWatch.Elapsed.ToString());
            stopWatch.Reset();
            this.Cursor = Cursors.Default;
        }
        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }
   
       
    }
}

转载于:https://www.cnblogs.com/lin6234/archive/2007/01/19/624369.html