理查德筆記

程式架構：
在Main將一列一列建立抓取之資料入主表得到PK，接著把PK傳入各個Submain function以便給其他表的索引鍵FK使用

備註︰
使用前請加入參考,「專案」->「加入參考」->「COM」->「類型程式庫」->「Microsoft Excel 12.0 Object Library」

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

class Program
{
    public static string connString = @"Data Source = [IP]; Initial Catalog = [DB_Name]; User ID = XXX; Password = ***";
    public static string ExcelFilePath = @"D:\XXX\abc.xlsx";
    public static int 起始列 = 1;
    public static int 結束列 = 1024
    public static int 起始行 = 1;
    public static int 結束列 = 20;

    public static void Main(string[] args)
    {
        //====開啟Excel====
        //開啟應用程式
        Excel.Application xlApp = new Excel.Application();
        //關閉警告顯示
        xlApp.DisplatAlerts = false;
        //開啟路徑之Excel File
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelFilePath)
        //Excel的Sheet可以用矩陣方式也可以直接抓Sheet名稱讀資料,ex:xlWorksheet.Name="TEST"
        Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        //選取Sheet內表格的範圍,也可讓程式自動選擇使用範圍xlWorksheet.UsedRange;
        Excel.Range xlRange = xlWorksheet.Range[xlWorksheet.Cells[起始列, 起始行], xlWorksheet.Cells[結束列, 結束列]];
        //選定範圍欄與列的數量
        int RowCount = xlRange.Rows.Count;
        int ColCount = xlRange.Columns.Count;



        //====連線至DataBase====
        //建立一個新的連線目標
        SqlConnection conn = new SqlConnection(connString);
        //直接下sql指令或預存程序名稱
        string commString = "sp_name"
        //建立sql指令連結
        SqlCommand cmd = new SqlCommand(commString, conn);
        //*若指令為預存程序要多設定指令類別
        cmd.CommandType = CommandType.StoredProcedure;
        //開啟連線
        conn.Open();



        //====資料的初步篩選及校正====
        //開始將Excel一列一列匯入資料庫
        //第一列為項目,從i=2開始讀資料
        for(int i=2;i<=RowCount, i++)
        {
            try
            {
                if(xlRange.Cells[i,100] != null && xlRange.Cells[i,100].Value2 != null)
                {
                //do what u wanna do
                //xlRange.Cells資料分為Text, Value 和 Value2 三種
                //Text直接把顯示於excel上的資料直接變文字
                //Value,Value2則會轉換日期為數值格式或金錢類別的資料增加dollar sign等等的格式變動
                string 匯入的資料=xlRange.Cells[i,100].Text;
                }

                //====執行sql command====
                //將資料加入至執行預存程序所對應的參數
                cmd.Parameters.Add("@par1", SqlDbType.VarChar).Value = 匯入的資料;
                //新增,修改用ExecuteNonQuery方法(不回傳Table)
                cmd.ExecuteNonQuery();

                //====若須匯入至關聯子表,抓剛DataBase資料之PK(自動編號)====
                commString = "Select [PK編號] From [Table] Where [Item]=條件";
                //重新建立sql指令連結
                cmd = new SqlCommand(commString, conn);
                //讀取器(只能順項讀取)
                SqlDataReader dr = cmd.ExecuteReader();

                while(dr.Read())
                {
                    //讀到PK後將傳入submain function,以利新增資料至其他關聯子表
                    PK = Convert.ToInt32(dr[0]);
                    //傳入(Excel選取範圍, PK Value, 第幾列)
                    submainfn(xlRange, PK, i);
                }
                //關閉讀取器
                dr.Close();
            }
            catch(Exception)
            {
                //匯入出錯就暫停
                Console.ReadLine();
            }
            finally
            {
                //清除加入至cmd的參數,讓下個迴圈的資料可重新加入
                cmd.Clear();
            }
        }

        //====釋放所有資源====
        //關閉資料庫連接
        cmd.Cancel();
        conn.Close();
        Conn.Dispose();
        //關閉Excel應用程式
        xlWorlbook.Close();
        xlApp.Quit;
        //執行Garbage Collect回收記憶體
        //因為GC回收時是在另一個thread
        //所以使用WaitForPendingFinalizers等到確實回收後再繼續執行
        //GC.Collect();
        //GC.WaitForPendingFinalizers(); 
    }

    public static void submainfn(Excel.Range xlRange, int PK, int Row)
    {
        //重複上面流程[連線至DataBase]->[資料的初步篩選]->[執行sql command]->[釋放所有資源]
    }

}

如果要在IIS上讓user使用

會遇到IIS不支援Excel，出現權限錯誤需要另外設定

key word: C sharp excel 0x80070005

請參考：https://dotblogs.com.tw/v6610688/2015/02/19/iis_office_access_word_excel_com_interop_api_configuration

另外Microsoft Excel Application要在32位元MMC才會出現

開始工具列-->執行 如下命令：mmc comexp.msc /32

 

Reference：

1.How to export databse to excel file

http://csharp.net-informations.com/excel/csharp-excel-export.htm

2.C# 輸出 Excel

http://xyz.cinc.biz/2013/10/csharp-create-excel.html

3.How to read data from excel file using c# [duplicate]

https://stackoverflow.com/questions/15793442/how-to-read-data-from-excel-file-using-c-sharp

4.Read Excel File in C#

https://coderwall.com/p/app3ya/read-excel-file-in-c