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

}

}

}

copy來的