# Visual Studio Tools for Office (VSTO)

Visual Studio Tools for Office (VSTO) 是一個 Microsoft Visual Studio 的擴充套件，專門用於開發 Microsoft Office 應用程式的擴充功能和自訂功能。開發者可以使用 VSTO 在 Office 應用程式中建立自己的自訂功能，例如新增自訂功能表、工具列、按鈕或面板，並進行各種操作和處理。

VSTO 提供了一個基於 .NET Framework 的開發環境，支援使用 C# 或 VB.NET 等語言來開發自訂功能。透過 VSTO，開發者可以輕鬆地與 Office 應用程式進行整合和互動，進而提升應用程式的功能性和生產力。

總的來說，Visual Studio Tools for Office 提供了開發者一個方便且強大的工具，讓他們能夠進行 Office 應用程式的自訂和擴充，以滿足不同使用者的需求，並提高應用程式的效率和便利性。

## 查詢

程式執行流程：

graph LR;
是否有編輯中的儲存格-->初始化工作表;
初始化工作表-->設定儲存格格式;
設定儲存格格式-->資料繫結至工作表;
資料繫結至工作表-->設定工作表樣式;
設定工作表樣式-->GcExcel;

### 是否有編輯中的儲存格

/// <summary>
/// 是否有編輯中的儲存格
/// </summary>
public static void CheckActiveCell(this Excel.Worksheet ws)
{
    try
    {
        ws.Select(Type.Missing);
    }
    catch
    {
        Excel.Range rng = ws.Application.ActiveCell;
        string activeCell = ToExcelColumn(rng.Column) + rng.Row;
        throw new Exception("儲存格(" + activeCell + ") 編輯中，請確認後再執行。");
    }
}

### 初始化工作表

/// <summary>
/// 將工作表還原至初始默認狀態
/// </summary>
public static void InitWorksheet(this Excel.Worksheet ws)
{
    Excel.Range range;

    Cursor.Current = Cursors.WaitCursor; // 搭配 GcExcel()

    //ws.Application.ScreenUpdating = false;
    ws.Application.EnableEvents = false;

    // 解除保護
    if (ws.ProtectContents) ws.Unprotect();
    // 取消自動篩選
    if (ws.AutoFilterMode) ws.AutoFilterMode = false;

    // 清除工作表的所有内容
    ws.Cells.Clear();

    ws.Rows.Delete(); // *刪除所有插入的行
    ws.Columns.Delete(); // *刪除所有插入的列
    ws.Rows.Hidden = false; // 顯示所有隱藏的行
    ws.Columns.Hidden = false; // 顯示所有隱藏的列

    ws.Cells.ClearFormats();  // 清除儲存格格式
    ws.Cells.ClearComments(); // 清除儲存格的註解
    ws.Cells.Validation.Delete();  // 清除資料驗證(Data Validation)
    ws.UsedRange.MergeCells = false;  // 取消合併儲存格

    // 清除所有儲存格的公式和值
    ws.UsedRange.Formula = "";
    ws.UsedRange.Value = "";

    // 檢查列(直排)是否有群組
    range = ws.UsedRange.Columns;
    foreach (Excel.Range column in range)
    {
        if (column.OutlineLevel > 1)
            ws.Columns.Ungroup();
    }

    // 解除凍結窗格
    ws.Application.ActiveWindow.FreezePanes = false;

    //// 重設所有的圖表和圖形
    //foreach (Excel.ChartObject chartObject in ws.ChartObjects())
    //    chartObject.Delete();

    //ws.Application.ActiveWindow.ScrollRow = 1; // 滾動至第 1 行
    ws.Columns.AutoFit();

    //ws.Application.ScreenUpdating = true;
    ws.Application.EnableEvents = true;
}

### 設定儲存格格式

值"OCT1"會在儲存格尚未指定成文字格式前，因通用格式而自動轉換成日期格式。
解決方法是<span style="color:red">先設定儲存格格式，再讓資料繫結上去</span>。

ws.Range[row, column].NumberFormat = "@"; // 文字格式

### 資料繫結至工作表

/// <summary>
/// 將 DataTable 載入 Worksheet。並且設定基本樣式。
/// </summary>
public static void CreateSheet(this Excel.Worksheet ws, DataTable dtExcel, string[] showColumns)
{
    int dtRowCnt = dtExcel.Rows.Count;
    int showColumnCnt = showColumns.Length;

    // 先關閉自動更新畫面，以提高效能
    ws.Application.ScreenUpdating = false;
    // 關閉事件處理
    ws.Application.EnableEvents = false;
    //// 關閉特定的提醒及訊息
    //ws.Application.DisplayAlerts = false;

    // 使用者的習慣是公式計算選項選「手動」，所以 VSTO 程式要自己觸發公式計算 ws.Calculate();
    //ws.Application.Calculation = Excel.XlCalculation.xlCalculationManual;
    try
    {
        //【Header】
        for (int i = 0; i < showColumnCnt; i++)
        {
            var Cell = ws.Cells[1, i + 1];
            // 先設定格式再賦予值 (@ 就是原始的內容)
            Cell.NumberFormat = "@";
            //if (dtExcel.Columns.Contains(showColumns[i]))
            //    Cell.Value2 = dtExcel.Columns[showColumns[i]].ColumnName;
            //else
            Cell.Value2 = showColumns[i];
        }

        //【Row Data】
        object[,] DataList = new object[dtRowCnt, showColumnCnt];
        for (int i = 0; i < dtRowCnt; i++)
        {
            for (int j = 0; j < showColumnCnt; j++)
            {
                if (dtExcel.Columns.Contains(showColumns[j]))
                    DataList[i, j] = dtExcel.Rows[i][showColumns[j]].ToString().Replace(";", "\n");
            }
        }

        ws.Range[ws.Cells[2, 1], ws.Cells[1 + dtRowCnt, showColumnCnt]].Value2 = DataList;

        BasicStyleSettings(ws);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        // 啟用更新畫面
        ws.Application.ScreenUpdating = true;
        // 啟用事件處理
        ws.Application.EnableEvents = true;
        //// 啟用特定的提醒及訊息
        //ws.Application.DisplayAlerts = true;
    }
}

/// <summary>
/// 將 List<T> 載入 Worksheet。並且設定基本樣式。
/// </summary>
public static void CreateSheet<T>(this Excel.Worksheet ws, List<T> list, string[] showColumns)
{
    int rowCnt = list.Count;
    int showColumnCnt = showColumns.Length;

    ws.Application.ScreenUpdating = false;
    ws.Application.EnableEvents = false;            
    try
    {
        //【Header】
        for (int i = 0; i < showColumnCnt; i++)
        {
            var Cell = ws.Cells[1, i + 1];
            Cell.NumberFormat = "@";
            Cell.Value2 = showColumns[i];
        }

        //【Row Data】
        object[,] DataList = new object[rowCnt, showColumnCnt];
        for (int i = 0; i < rowCnt; i++)
        {
            for (int j = 0; j < showColumnCnt; j++)
            {
                PropertyInfo prop = typeof(T).GetProperty(showColumns[j]);
                DataList[i, j] = prop.GetValue(list[i], null)?.ToString().Replace(";", "\n");
            }
        }

        ws.Range[ws.Cells[2, 1], ws.Cells[1 + rowCnt, showColumnCnt]].Value2 = DataList;

        BasicStyleSettings(ws);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        ws.Application.ScreenUpdating = true;
        ws.Application.EnableEvents = true;
    }
}

/// <summary>
/// 查無資料時，只產生 Header 欄位。並且設定基本樣式。
/// </summary>
public static void GenerateHeaderColumns(this Excel.Worksheet ws, string[] showColumns)
{
    int showColumnCount = showColumns.Length;

    ws.Application.ScreenUpdating = false;
    try
    {
        for (int i = 0; i < showColumnCount; i++)
        {
            var Cell = ws.Cells[1, i + 1];
            Cell.NumberFormat = "@";
            Cell.Value2 = showColumns[i];
        }

        BasicStyleSettings(ws);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        ws.Application.ScreenUpdating = true;
    }
}

### 設定工作表樣式

/// <summary>
/// 設定基本樣式
/// </summary>
public static void BasicStyleSettings(this Excel.Worksheet ws)
{
    int usedRowCnt = ws.UsedRange.Rows.Count;
    int usedColCnt = ws.UsedRange.Columns.Count;

    if (usedRowCnt <= 1) usedRowCnt++; // 如果只有 Header (查無資料)，給一行空行。

    // 格線隱藏
    //ws.Application.ActiveWindow.DisplayGridlines = false;

    //【Worksheet 樣式】
    ws.Cells.Font.Name = "Microsoft JhengHei";
    ws.Cells.Font.Name = "Gill Sans MT";
    ws.Cells.Font.Size = "11";
    ws.Cells.Font.Color = Color.FromArgb(64, 64, 64);
    ws.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
    ws.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
    ws.Cells.Locked = false;

    //【Header 樣式】
    Excel.Range rngHeader = ws.Range[ws.Cells[1, 1], ws.Cells[1, usedColCnt]];
    rngHeader.Font.Bold = true;
    rngHeader.Font.Size = "12";
    rngHeader.Font.Color = Color.FromArgb(255, 255, 255); // White
    rngHeader.Interior.Color = Color.FromArgb(75, 172, 198); // Interior 儲存格背景顏色
    // 鎖定狀態在首次設定時並不會影響單元格的行為。只有當工作表被保護（Worksheet 的 Protect 方法被呼叫）後，設置為 "鎖定" 的單元格或範圍才將無法被用戶編輯。
    //rngHeader.Locked = true;

    //【資料範圍樣式】
    Excel.Range rngData = ws.Range[ws.Cells[2, 1], ws.Cells[usedRowCnt, usedColCnt]];
    rngData.Interior.Color = Color.FromArgb(255, 255, 255); // Interior 儲存格背景顏色
    rngData.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
    rngData.Borders.Weight = Excel.XlBorderWeight.xlThin;
    rngData.Borders.Color = Color.FromArgb(128, 128, 128); // Gray

    ws.Columns.AutoFit();
}

### 回復 Excel 基本設定、釋放資源

/// <summary>
/// 回復 Excel 基本設定、釋放資源
/// </summary>
public static void GcExcel(this Excel.Worksheet ws)
{
    //if (ws != null)
    //{
    //    Marshal.ReleaseComObject(ws);
    //    ws = null;
    //}
    //GC.Collect();
    //GC.WaitForPendingFinalizers();

    ws.Application.ScreenUpdating = true;
    ws.Application.EnableEvents = true;
    ws.Application.DisplayAlerts = true;
    ws.Application.StatusBar = "";

    Cursor.Current = Cursors.Default;
}

## 異動資料庫(Insert、Update、Delete)

程式執行流程：

graph LR;
BeforeSaveWorksheet-->異動資料庫;
異動資料庫-->AfterSaveWorksheet;

### BeforeSaveWorksheet

/// <summary>
/// 儲存資料至資料庫之前，工作表該做的設定
/// </summary>
public static void BeforeSaveWorksheet(this Excel.Worksheet ws)
{
    Cursor.Current = Cursors.WaitCursor;

    if (ws.AutoFilterMode)
        ws.AutoFilterMode = false; // 清除已應用的篩選器。
}

### AfterSaveWorksheet

/// <summary>
/// 恢復 BeforeSaveWorksheet() 做的設定。一般用在 finally{}
/// </summary>
public static void AfterSaveWorksheet(this Excel.Worksheet ws)
{
    int usedColCnt = ws.UsedRange.Columns.Count;
    if (!ws.AutoFilterMode)
        ws.Range[ws.Cells[1, 1], ws.Cells[1, usedColCnt]].AutoFilter(1); // 加篩選器

    Cursor.Current = Cursors.Default;
}

## [Excel 物件模型](https://learn.microsoft.com/zh-tw/visualstudio/vsto/excel-object-model-overview?view=vs-2019&tabs=csharp)

### Application 應用程式物件

#### ActiveWindow.FreezePanes

凍結窗格

ws.Range["A2"].Select(); // 欄位
ws.Application.ActiveWindow.FreezePanes = true;

#### Calculate

計算所有開啟的活頁簿、活頁簿中的特定工作表

ws.Calculate();

ws.Application.Calculate();

### Range 物件

宣告 Range 變數

Excel.Range rng = ws.Range[row, column];

#### AddComment

新增註解。

rng.AddComment("表頭黃字為必填欄位");
rng.Comment.Shape.TextFrame.AutoSize = true;

#### AutoFilter

加篩選器。

Excel.Range row = ws.Range[row, column];
row.AutoFilter(1);

#### AutoFit

將範圍內的欄寬度或範圍內的列高度變更為最合適的大小。

rng.Columns.AutoFit();

## 其他常用方法

### 欄位編碼

|Column number|1|2|3|4|5|6|7|...|27|28|...|
|-------------|-|-|-|-|-|-|-|---|--|--|---|
|Column name |A|B|C|D|E|F|G|...|AA|AB|...|

/// <summary>
/// 將數字轉換成 Excel 的列字母
/// </summary>
public static string ToExcelColumn(this int columnNumber)
{
    int dividend = columnNumber;
    string columnName = string.Empty;
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }
    return columnName;
}