[.NET] 使用Microsoft.Office.Interop.Excel元件將資料匯出Excel

說明：有許多元件可將資料匯成Excel，這裡使用Excel 2010 PIA來實作，加入參考 -> 選.NET tab -> Microsoft.Office.Interop.Excel 12.0.0.0，namespace為Microsoft.Office.Interop.Excel。


做法：
//避免與Windows.Form.Application衝突
using Excel =  Microsoft.Office.Interop.Excel;

Excel.Application _Excel = null; //起一個Excel.exe
Excel.Workbook _Workbook = null; //活頁簿
Excel.Worksheet _Worksheet1 = null;
Excel.Sheets _Sheets = null;

string strTemplateFilePath = Application.StartupPath + @"\EXCEL\Template.xlsx"; //範本路徑
string strRptPath = Application.StartupPath + @"\EXCEL\Rpt\";
string strFilePath = string.Empty;
string strFileName = string.Empty;
           
try
{
    _Excel = new Excel.Application();
    _Excel.Visible = false;
    _Workbook = _Excel.Workbooks.Open(strTemplateFilePath);
    _Sheets = _Workbook.Worksheets;
    string[,] strTable;
 
    if (有資料)
    {
        _Worksheet1 = _Sheets.get_Item(1);
        _Worksheet1.Name = DateTime.Now.ToString("yyyyMM");

        int iInitialRow = 0; //起始列
        int iInitialCol = 0; //起始欄
        int iRowCnt = 資料來源筆數;
        int iColCnt = 資料來源欄位數;

        for (int x = 0; x < iRowCnt; x++)
        {
            for (int y = 0; y < iColCnt; y++)
            {
                strTable[x, y] = dt.Rows[x][y].ToString();
            }
        }

         Excel.Range _RangeStart = _Worksheet1.Cells[iInitialRow, iInitialCol];
         Excel.Range _RangeEnd = _Worksheet1.Cells[iInitialRow+iRowCnt, iInitialCol+iColCnt];
         Excel.Range _Range = (Excel.Range)_Worksheet1.get_Range(_RangeStart, _RangeEnd);
       
         _Range.Value2 = strTable;
         _Range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
         _Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
         _Range.Font.Name = "Times New Roman";
         _Range.Font.Size = 10;
         _Range.EntireColumn.AutoFit();
         _Range.Borders.Weight = Excel.XlBorderWeight.xlHairline;

         _Workbook.Application.DisplayAlerts = false;
         _Workbook.Application.AlertBeforeOverwriting = false;
         _Workbook.Saved = true;

         strFileName = "Rpt.xlsx";
         strFilePath = strRptPath + strFileName;
         _Workbook.SaveCopyAs(strFilePath);
    }
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    if (_Range != null)
   {
        Marshal.FinalReleaseComObject(_Range );
    }
    if (_Sheets != null)
    {
        Marshal.FinalReleaseComObject(_Sheets);
    }
    if (_Workbook != null)
    {
        _Workbook.Close(false);
        Marshal.FinalReleaseComObject(_Workbook);
     }
     if (_Excel != null)
     {
         _Excel.Workbooks.Close();
         _Excel.Quit();
         Marshal.FinalReleaseComObject(_Excel);
     }
}

 _Range.Cells.Text; //取得範本上資料
 //合併儲存格
 Excel.Range _RangeStart = _Worksheet1.Cells[int, int];
 Excel.Range _RangeEnd = _Worksheet1.Cells[int, int];
 _Worksheet1.get_Range(_RangeStart, _RangeEnd).Merge(0);
//新增工作表
_Worksheet1 = (Excel.Worksheet)_Workbook.Worksheets.Add(After: (Excel.Worksheet)_Workbook.Sheets[test], Count: 1);
_Worksheet1.Name = test+"_"+k;


參考資料：
http://www.dotblogs.com.tw/yc421206/archive/2012/03/09/70624.aspx
http://www.dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx //fill in data
http://blog.darkthread.net/post-2013-05-14-excel-interop-notes.aspx //release
http://www.dotblogs.com.tw/chou/archive/2013/03/26/99016.aspx //Excel.Range.Cells.Text
http://fecbob.pixnet.net/blog/post/38189181-c%23-excel-%E8%A1%8C%E9%AB%98%E3%80%81%E5%88%97%E5%AF%AC%E3%80%81%E5%90%88%E4%BD%B5%E5%84%B2%E5%AD%98%E6%A0%BC%E3%80%81%E5%84%B2%E5%AD%98%E6%A0%BC%E9%82%8A%E6%A1%86 //Excel語法彙集
http://ww0o0ww.pixnet.net/blog/post/64084894-%E3%80%90c%23%E3%80%91%E3%80%8Amicrosoft.office.interop.excel%E3%80%8B%E5%AF%AB%E5%85%A5excel%E6%AA%94

[Reporting Service] 使用CDate函式轉換Date Type為YYYYMMDD

說明：使用Reporting Service繫結Oracle或SQL Server資料庫欄位為Date Type資料時，會出現「上午12:00:00」，該如何去除呢?


做法：
在Reporting Service繫結的欄位 -> properties(找屬性視窗) -> Value -> =Format(CDate(Fields!DATE.Value), "yyyy/MM/dd")


參考資料：
http://stackoverflow.com/questions/9722516/report-builder-3-0-how-to-convert-string-representation-of-dates-in-mm-dd-yy-fo