# 報表檔( R ) Excel

## Excel

#### Controller需要繼承 IApiController.==R(Report)==

HashSet<NavBtn> navBtns = UserPermissions.SetButtons
(insert: true, delete: true, save: true, search: true,
copy: true, expXls: true, print:true);

按鈕需要設定**expXls:true**才可以匯出EXCEL

==接著寫入 ExpXls(是匯出excel一定需要的Function)==
### 基本款Excel

[System.Web.Http.HttpPost]
public IHttpActionResult ExpXls(SearchClass a_Search)
{
      Searching += l_Master_Searching;
      //加入欄位定義
      List<XlsColumn> l_cols = new List<XlsColumn>
      {
            new XlsColumn("ID", "代碼",XlsColumnType.Text),
            new XlsColumn("NAME", "姓名",XlsColumnType.Text),
            new XlsColumn("SEX", "性別",XlsColumnType.Text),
            new XlsColumn("DATE1", "起始",XlsColumnType.Date),
            new XlsColumn("DATE2", "結束",XlsColumnType.DateTime)
       };
       return this.doExpXlsResult(a_Search, l_cols);
}

* ExpXls是在建立Excel中的欄位名稱
* 欄位名稱需和要匯出的Table一模一樣
* XlsColumn("Table欄位名稱","匯出想要的中文名稱","Type類型")

### 詳細版Excel

//匯出Excel
[System.Web.Http.HttpPost]
public IHttpActionResult ExpXls(SearchClass a_Search)
{
    XlsTool l_xlsTool = new XlsTool();

    DBController l_dbc = new DBController();
    Hashtable l_ht = new Hashtable();
    string sql = " select * from demoti where id = @id ";
    l_ht.Add("@id", new StructureSQLParameter(a_Search.ID, SqlDbType.Int));
    DataTable l_dt = l_dbc.FillDataTable(sql, l_ht);
    l_xlsTool.DataSource = l_dt;

    l_xlsTool.isUseTemplate = true;
    l_xlsTool.TemplateFileName = "DEMO.xlsx";
    l_xlsTool.SaveFileName = "列印範例";
    l_xlsTool.isXlsColumns = false;//不印出欄位標題
}

* 設定sql指令，還有標頭名稱
<br>

    //加入Detail 1 欄位定義
    List<XlsColumn> l_cols = new List<XlsColumn>();
    l_cols.Add(new XlsColumn("ID", "ID"));
    l_cols.Add(new XlsColumn("DID", "DID"));
    l_cols.Add(new XlsColumn("CONTENT", "CONTENT"));

* 定義欄位名稱
<br>

#region 固定欄位值設定
    List<XlsCustomCell> l_cells1 = new List<XlsCustomCell>();
    l_cells1.Add(new XlsCustomCell()
    {
        row_index = 1,
        col_index = 1,
        value = "DEMO",
        type = XlsColumnType.Text
    });
    l_cells1.Add(new XlsCustomCell()
    {
        row_index = 2,
        col_index = 1,
        value = "編號",
        type = XlsColumnType.Text
    });
    l_cells1.Add(new XlsCustomCell()
    {
        row_index = 2,
        col_index = 2,
        value = "序號",
        type = XlsColumnType.Text
    });
    l_cells1.Add(new XlsCustomCell()
    {
        row_index = 2,
        col_index = 3,
        value = "內容",
        type = XlsColumnType.Text
    });
    #endregion

* 設定每個欄位的名字以及類型
<br>

    #region Sheet 1設定
    string l_SheetName1 = "DEMO";
    l_xlsTool.XlsSheets.Add(new XlsSheet()
    {
        SheetName = l_SheetName1,
        isCreateHeader = false,
        XlsColumns = l_cols,
        StartPoint = new Point(1, 3),
        XlsCustomCells = l_cells1
    });
    //l_ds.Tables.Add(l_dt1);
    l_dt.TableName = l_SheetName1;
    #endregion

    return doExpXlsResult(l_xlsTool);

* 設定Excel相關設定
* Point是指從哪裡開始塞資料庫資料(第一列第三行開始)

###### tags: `實習筆記`