using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using Microsoft.Office.Interop.Excel;using System.Data.SqlClient;using System.Data.OleDb;using System.Reflection;namespace ExcelPrj{    /// <summary>    /// Excel 系统中的主文件Excel.exe 本身就是 COM 组件,通过在.NET 项目中引用Exel.exe 文件可以实现对Excel 的功能控制    /// 与COM 组件相互操作是通过使用"包装类"(Wrapper Class) 和"代理"(Proxy) 的机制实现的.包装类使.NET 程序可以识别COM 组件提供的接口,而代理类则是提供对 COM 接口的访问    /// </summary>    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        private void button3_Click(object sender, EventArgs e)        {            ExportTasks(Bind(), dataGridView1);        }        //如果 Excel 安装在计算机上,侧导出表格内容到 Excel        public void ExportTasks(DataSet TasksData, DataGridView TasksGridView)        {            // 定义要使用的Excel 组件接口            // 定义Application 对象,此对象表示整个Excel 程序            Microsoft.Office.Interop.Excel.Application excelApp = null ;            // 定义Workbook对象,此对象代表工作薄            Microsoft.Office.Interop.Excel.Workbook workBook;            // 定义Worksheet 对象,此对象表示Execel 中的一张工作表            Microsoft.Office.Interop.Excel.Worksheet ws=null;            //定义Range对象,此对象代表单元格区域            Microsoft.Office.Interop.Excel.Range r;            int row = 1; int cell = 1;            try            {              //初始化 Application 对象 excelApp                excelApp = new Microsoft.Office.Interop.Excel.Application();                //在工作薄的第一个工作表上创建任务列表                workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);                ws =(Worksheet)workBook.Worksheets[1];                // 命名工作表的名称为 "Task Management"                ws.Name = "Task Management";                #region 创建表格的列头                // 遍历数据表中的所有列                foreach (DataGridViewColumn cs in TasksGridView.Columns)                {                    // 假如并不想把主键也显示出来                    if (cs.HeaderText != "编号")                    {                        ws.Cells[row, cell] = cs.HeaderText;                        r = (Range)ws.Cells[row, cell];                        ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;                                      //此处用来设置列的样式                        cell++;                    }                }                            // 创建行,把数据视图记录输出到对应的Excel 单元格                for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++)                {                    for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++)                    {                                                ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString();                      // r = (Range)ws.Cells[i,j];                        Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);                        rg.EntireColumn.ColumnWidth = 20;                  //    rg.Columns.AutoFit();                        rg.NumberFormatLocal = "@";                    }                }                #endregion            }            catch (Exception ex)            {                MessageBox.Show(ex.ToString());            }                        //显示 Excel            excelApp.Visible = true;                              }        private void button5_Click(object sender, EventArgs e)        {            DataSet ds = Bind();            dataGridView1.DataSource = ds.Tables[0];        }        private DataSet Bind()        {            SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");            SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);            DataSet ds = new DataSet();            da.Fill(ds);            return ds;        }        private void button2_Click(object sender, EventArgs e)        {            SaveFileDialog sfd = new SaveFileDialog();            sfd.Title = "请选择将导出的EXCEL文件存放路径";            sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";            sfd.Filter = "Excel文档(*.xls)|*.xls";            sfd.ShowDialog();                      if (sfd.FileName != "")            {                                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();                if (excelApp == null)                {                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");                }                else                {                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);                    Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1];                    DataSet ds=Bind();                    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)                    {                        for (int j = 1; j < ds.Tables[0].Columns.Count;j++ )                        {                            if (i == 1)                            {                                worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;                                                            }                            worksheet.Cells[i+1, j] = ds.Tables[0].Rows[i][j].ToString();                        }                    }                    //保存方式一：保存WorkBook                      //workbook.SaveAs(@"F:\CData.xls",                      // Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,                      // Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,                      // Missing.Value,Missing.Value);                      //保存方式二：保存WorkSheet                      // worksheet.SaveAs(@"F:\CData2.xls",                      // Missing.Value, Missing.Value, Missing.Value, Missing.Value,                      // Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                    ////保存方式三                    //workbook.Saved = true;                    //workbook.SaveCopyAs(sfd.FileName);                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);                    worksheet = null;                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                    workbook = null;                    workbooks.Close();                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);                    workbooks = null;                    excelApp.Quit();                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);                    excelApp = null;                    MessageBox.Show("导出Excel完成!");                }            }                  }        private void button4_Click(object sender, EventArgs e)        {            string strExcelFileName = @"F:\\2007-07-16-学生信息.xls";            string strSheetName = "sheet1";            #region Aspnet 操作Excel  正确            ////源的定义            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties =‘Excel 8.0;HDR=NO;IMEX=1‘";            ////Sql语句            //string strExcel = "select * from  [" + strSheetName + "$]";            ////定义存放的数据表            //DataSet ds = new DataSet();            ////连接数据源            //OleDbConnection conn = new OleDbConnection(strConn);            //conn.Open();            ////适配到数据源            //OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);            //adapter.Fill(ds,"res");            //conn.Close();            //// 一般的情况下. Excel  表格的第一行是列名            //dataGridView2.DataSource = ds.Tables["res"];            #endregion            #region COM 组件读取复杂Excel            Microsoft.Office.Interop.Excel.Application excelApp = null;            Microsoft.Office.Interop.Excel.Workbook workBook;            Microsoft.Office.Interop.Excel.Worksheet ws = null;            try            {                excelApp = new Microsoft.Office.Interop.Excel.Application();                workBook = excelApp.Workbooks.Open(@"F:\\Book1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                ws = (Worksheet)workBook.Worksheets[1];                                        //Excel 默认为 256 列..                MessageBox.Show(ws.Cells.Columns.Count.ToString());                excelApp.Quit();            }            catch (Exception ex)            {                throw ex;            }            #endregion        }    }}