[★] VS2005[C#] 操作 Excel 全攻略 ZT

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
}
}
}