第二十九讲 : ADO.NET（玩SQL语句） 实例补充(OLEDB操作)

上面那一篇文字，提到如何要清险指定行数据呢？我是找遍千山万水啊，终于。。。。还是没有搞定，纠结啊，于乎我VS的主线路上，分支学一下关于C#操作Excel这个COM控件对象，先引用到项目中来，using Excel = Microsoft.Office.Interop.Excel; 其实，不要给这个空间加别名了还好，反正这种预定义的空间很少有重复的，这样就不用每调用一个其中的类都要加前缀了，牛人说的我看着是迷迷糊糊的，手工的去敲代码吧，还是有不能当时就理解的，囫囵吞枣的先写着吧，磨磨会透的。

我是根据网上前辈们的例字自己一个个敲的，但都要标注参教连接，虽说是选择原创，但目的是为了想归到一个类别中去，学这个也是出于兴趣和爱好，没有用于商业的目的，分享开源万岁，希望前辈们莫怪罪。呵呵

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

/*
 * 此实例来自：http://www.cnblogs.com/gfsoft/articles/278942.html
 *大家需要了解Tlbimp这个工具的使用啊：）这个东东很有用，可以将普通Win32程序移植到.Net下面来：）
 *首先的一步就是使用Tlbimp这个工具将Excel9.0的对象库文件Excel8.olb转换成为dll，这样才能做为.Net平台Assembly来使用：）
 *操作如下：（这是office2000的时候作者写的，我用提2007就不用考虑这些的，我想2003也不用考虑这个。）
 *TlbImp Excel9.olb Excel.dll 
 */


namespace ConExcelTest
{
    class Program
    {
        /// <summary>
        /// 详细的了解一些关C#与Excel的关系
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            #region 操作Excel实例1，测试时，去除注释即可，我这个是控制台程序
            1. 创建一个新Excel的Application: 
            //Excel.Application excel = new Excel.Application();//实例化一个Excel程序对象
            //if (excel == null)
            //{
            //    //如果没有安装OFFICE程序的话会提示这个错误
            //    Console.WriteLine("错误：Excel程序无法被打开！");
            //}

            2. 让这个工程可见: 
            //excel.Visible = true;


            3. 获取WorkBooks集合:
            //Excel.Workbooks workbooks = excel.Workbooks;

            4. 加入新的WorkBook:
            //Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            5. 获取WorkSheets集合:
            //Excel.Sheets worksheets = excel.Worksheets;
            //Excel.Worksheet worksheet = worksheets.get_Item(1);

            try
            {
                worksheet = worksheets.get_Item(2);
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }//报错：无效索引。 (异常来自 HRESULT:0x8002000B (DISP_E_BADINDEX))

            //if (worksheet == null)
            //{
            //    Console.WriteLine("错误：找不到工作表！");
            //}

            6. 给单元格设置变量：
            //#region 例1
            Excel.Range range1 = worksheet.get_Range("C1", Missing.Value);
            if (range1 == null)
            {
                Console.WriteLine("错误：找不到指定的！");
            }
            const int nCells = 5;//一个常量
            object[] args1 = new object[1];//一个对象数组
            args1[0] = nCells;
            range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range1, args1); 
            //#endregion

            //const int nCells = 5;//一个整型常量
            //Excel.Range range2 = worksheet.get_Range("A1", "E1");//获取一个单元格范围
            //int[] array2 = new int[nCells];//一个整型数组
            //for (int i = 0; i < array2.GetLength(0); i++)//遍历数组并赋值
            //{
            //    array2[i] = i + 1;
            //}
            //object[] args2 = new Object[1];//参数数组
            //args2[0] = array2;
            //range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range2, args2);

            #endregion


            /*
             * 以下来自：http://www.cnblogs.com/GavinCome/archive/2007/10/26/937993.html
             *C#+EXCEL制作高级报表
             */

            string[,] myData = new string[11, 7]{

              {"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},

              {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},

              {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},

              {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},

              {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},

              {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},

              {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},

              {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},

              {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},

              {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},

              {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}

      };//一个二维数组

            Console.WriteLine("请输入一个单词，normal(普通报表)或advance(高级报表): ");
            //string getInfo = Console.ReadLine();
            //if (getInfo == "normal")
            //{ 
            //1. 创建一个Excel程序实例对象
            Excel.Application myExcel = new Excel.Application();
            myExcel.Application.Workbooks.Add(true);//这个地方不明白

            //2. 让Excel文件可见
            myExcel.Visible = true;

            //3. 第一行为报表名称
            myExcel.Cells[1, 4] = "'普通报表";

            //4. 逐行写入数据
            for (int i = 0; i < 11; i++)
            {
                for (int j = 0; j < 7; j++)
                {
                    //以单引号开头，表示该单元格为纯文本
                    myExcel.Cells[2 + i, 1 + j] = "\'" + myData[i, j];
                }
            }
            //}


            
            

            Console.ReadKey();
        }
    }
}