C#從零開始_自學C#、Visual Studio實境秀 37/ 利用Adodb.recordset 整合 Excel 二份活頁簿工作表

{
 using System;
 using System.Globalization;
 using static System.Console;
 using System.Text;
 using System.Linq;
 using ado=ADODB;
 using excel=Microsoft.Office.Interop.Excel;
 using System.Runtime.InteropServices;

 public class Example
 {

  public static void Main()
  {
   exl();
   //WriteLine(exl());
   //ReadKey();
  }

  static ado.Connection cnt = new ado.Connection();
  static ado.Recordset rst = new ado.Recordset();
  //static ado.Connection cntScaned = new ado.Connection();
  //static ado.Recordset rstScaned = new ado.Recordset();
  const string f= @"C:\Users\oscar\Documents\309與清詞別集、全清詞、清代詩文集彙編總目錄對照表_作為合併列印之來源.xlsx";
  //const string fScaned = @"D:\Downloads\309未掃書目.xlsm";
  /*"Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=;" & _
     "Extended Properties=""Excel 8.0;HDR=Yes;"";"
   */
  static void exl()
  {
   cnt.Open(@"Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=" + f + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";");
   //cntScaned.Open(@"Provider=Microsoft.ACE.OLEDB.12.0;
   //    Data Source=" + fScaned + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";");
   //rst.Open(@"select 冊ID,[309書名_合併列印用],[309作者_合併列印用],[309藏地],合併列印版本_309版本,合併列印篩選,卷數,清詞別集編號 from " +
   //    "[309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$] where 合併列印篩選=1",cnt,ado.CursorTypeEnum .adOpenKeyset ,ado.LockTypeEnum.adLockReadOnly,1);
   //rstScaned.Open(@"select 冊ID,[309書名_合併列印用],掃完,備註 from [309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$] where " +
   //    "掃完=1 or 掃完=0" , cntScaned, ado.CursorTypeEnum.adOpenForwardOnly, ado.LockTypeEnum.adLockReadOnly, 1);
   /*
    *  INNER JOIN 用韻 ON 詞牌_調式.詞牌_調式ID = 用韻.詞牌_調式ID
    */
   rst.Open(@"select [309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$].冊ID,[309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$].[309書名_合併列印用],[309作者_合併列印用],[309藏地],合併列印版本_309版本,合併列印篩選,卷數,清詞別集編號,掃完,[工作表2$].備註,掃描者 " +
    "from [309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$] "+
    "inner join  [工作表2$] on [309與清詞別集、全清詞、清代詩文集彙編總目錄對照表$].冊ID=[工作表2$].冊ID " +
    "where 合併列印篩選=1", cnt, ado.CursorTypeEnum.adOpenKeyset, ado.LockTypeEnum.adLockReadOnly, 1);
   //將 rst 寫入 ScanedList 活頁簿工作表1
   excel.Application app = new excel.Application();
   excel.Workbook exclNew = app.Workbooks.Add();
   //excel.Workbook exclNew=new excel.Workbook();
   for (int i = 1; i ＜ rst.Fields.Count + 1; i++)
    exclNew.ActiveSheet.Cells[1,i].Value= rst.Fields[i-1].Name;//列出欄名！
   exclNew.Worksheets[1].Cells[2, 1].CopyFromRecordset(rst);      
   exclNew.SaveAs("ScanedList");
   exclNew.Application.Visible = true;      
   //exclNew.Close();
   rst.Close();cnt.Close();rst = null;cnt = null;
   exclNew = null;

  }
 }

}