資料表檔案匯出Execel檔案-本地測試成功

資料表檔案匯出Execel檔案-本地測試成功

測試了我一天.還是在睡覺之前搞定了

好開心的.希望能對大家也有協助

添加execel 11.0 liburary lib的引用

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Text;  
using System.IO;  
using System.Diagnostics;
using System.Reflection;

//按鈕事件

   string CurrentDir=System.Environment.CurrentDirectory;
   string strConnection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+CurrentDir+"//Database//user.mdb;";
   OleDbConnection myConn=new OleDbConnection(strConnection); 
  
   DataSet objDataset = new DataSet();
   OleDbConnection objConn = new OleDbConnection(strConnection);
   objConn.Open();

   OleDbDataAdapter objAdapter = new OleDbDataAdapter("SELECT top 10 * from Admin",objConn);
   objAdapter.Fill(objDataset);

   DataView oView = new DataView(objDataset.Tables[0]);   
   
   DataTable myTable=new DataTable();
   myTable=objDataset.Tables[0];

   //讀取資料庫中的資料表
   dataGrid2.DataSource = oView;
   
   Excel.Application excel = new Excel.ApplicationClass();
   excel.Workbooks.Add(true);
   Excel.Workbook workbook = excel.Workbooks[1];

   Excel.Range r = excel.get_Range(excel.Cells[1,1],excel.Cells[1,1]);
   excel.Cells[1,1] = dataGrid2.CaptionText.Trim();   
   r.Font.Name = "宋體";//字型
   r.Font.Size = 15;//字型大小
   r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平置中
   r.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//豎直置中
   for(int i = 0 ; i < myTable.Rows.Count; i++)
   {
    for(int j = 0 ; j < myTable.Columns.Count; j++)
    {
     excel.Cells[i+2,j+1] = myTable.Rows[i][j].ToString();
    }
   }
   Excel.Range r1 = excel.get_Range(excel.Cells[2,1],excel.Cells[myTable.Rows.Count+1,myTable.Columns.Count]);
   r1.Font.Name = "宋體";
   r1.Font.Size = 10;
   r1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
   r1.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
   r1.EntireColumn.AutoFit();//自動調整列寬
   r1.EntireRow.AutoFit();//自動調整行高
   //加表格線
   r1.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
   r1.Borders.Weight = Excel.XlBorderWeight.xlThin;
   //外框加粗
   r1.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
   r1.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
   r1.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
   r1.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
   
   string SaveFilePath=txtSaveExecelPath.Text;

   workbook.SaveAs(SaveFilePath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
  
   excel.Quit();
   MessageBox.Show("Execel檔案匯出成功！");
   //填充資料

   
   //填充資料庫   
   //dataGrid2..DataBindings();
   objConn.Close();
   objConn.Dispose();
   objConn = null;