用VS2005操作 Excel

Excel對象
(1) Application对象。Application对象处于Excel对象层次结构的顶层，表示Excel自身的运行环境。 
(2) Workbook对象。Workbook对象直接地处于Application对象的下层，表示一个Excel工作薄文件。
(3) Worksheet对象。Worksheet对象包含于Workbook对象，表示一个Excel工作表。
(4) Range对象。Range对象包含于Worksheet对象，表示Excel工作表中的一个或多个单元格。

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Reflection;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        InsertDataToSaveExcel();
    }
    /// <summary>
    /// 用VisualC#打開Excel表格
    /// </summary>
    private void OpenExcel()
    {
        //打開Eecel表格
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Workbooks.Add(true);//引用excel工作部
        excel.Cells[1, 1] = "第一行第一列";//往EXCEL中插入數據
        excel.Cells[2, 1] = "第二行第二列";
        excel.Visible = true;//使excel可視

    }
    public void InsertDataToNewExcel()
    {
        try
        {
            Microsoft.Office.Interop.Excel.Application excel = new Application();
            excel.Workbooks.Add(true);

            SqlConnection con = new SqlConnection("server=hsmaximouat;database=maximo02;User ID=maximo;Password=maximo");
            con.Open();

            string strSQL = "select top 10 location , description ,siteid, type, disabled, orgid ,isdefault from locations order by location ";
            SqlCommand cmd = new SqlCommand(strSQL, con);
            SqlDataReader dr = cmd.ExecuteReader();

            int row = 2, cols;
            cols = dr.FieldCount;

            //get DB Field Name
            for (int i = 0; i < cols; i++)
            {
                excel.Cells[1, i + 1] = dr.GetName(i);
            }
            //get DB Data
            while (dr.Read())
            {
                for (int i = 0; i < cols; i++)
                    excel.Cells[row, i + 1] = dr[i];
                row++;
            }
            //使excel可視
            excel.Visible = true;
            excel = null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    /// <summary>
    /// 將數據庫中的數據保存到Excel中
    /// </summary>
    public void InsertDataToSaveExcel()
    {
        Microsoft.Office.Interop.Excel.Application excel = new Application();

        Microsoft.Office.Interop.Excel.Workbook xBook = excel.Workbooks._Open(@"C:\Sample.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);

        //指定要操作的sheet
        Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
        //或者 Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;

        //GetDB Data
        #region GetDB Data
        SqlConnection con = new SqlConnection("server=hsmaximouat;database=maximo02;User ID=maximo;Password=maximo");
        con.Open();

        string strSQL = "select top 10 location , description ,siteid, type, disabled, orgid ,isdefault from locations order by location ";
        SqlCommand cmd = new SqlCommand(strSQL, con);
        SqlDataReader dr = cmd.ExecuteReader();

        int row = 2, cols;
        cols = dr.FieldCount;
        //get DB Field Name
        for (int i = 0; i < cols; i++)
        {
            excel.Cells[1, i + 1] = dr.GetName(i);
        }

        //get DB Data
        while (dr.Read())
        {
            for (int i = 0; i < cols; i++)
                excel.Cells[row, i + 1] = dr[i];
            row++;
        }
        #endregion

        //在C12寫入數據
        Microsoft.Office.Interop.Excel.Range rng3 = xSheet.get_Range("C12", Missing.Value);
        rng3.Value2 = "Helloaa";
        rng3.Interior.ColorIndex = 6; //6代表黃色

        //保存方式一：保存WorkBook　（新建xBookData.xls文件）
        xBook.SaveAs(@"C:\xBookData.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 （新建xSheetData.xls文件）
        xSheet.SaveAs(@"C:\xSheetData.xls",
        Missing.Value, Missing.Value, Missing.Value, Missing.Value,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        ////保存方式三
        //xBook.Save();

        xSheet = null;
        xBook = null;
        excel.Quit();//Excel從內在中退出
        excel = null;
    }
}