[.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

[UltraEdit] 使用區塊模式(column mode)替一堆字串一次加入單引號

說明：SQL使用IN查詢一堆同一欄位的資料，這些查詢的value需要單引號包起來，資料一多該怎麼處理比較方便呢?


作法：
使用UltraEdit的區塊模式(column mode)，功能列->直行->區塊模式，快捷鍵為alt + c，轉換該模式後，將游標移到第一行字串前，按住shift不放再按滑鼠左鍵往下拖曳，此時會出現一條線，按下單引號鍵即可。


參考資料：

[ASP.NET] GridView樣板內控制項文字設定斷行

說明：因GridView內控制項(label)文字太長，想設定斷行。


作法：
1. 在GridView的RowDataBound事件內，找到該控制項。
2. 再設定特定長度或字元斷行，例：
 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        Label lblTest = (Label)e.Row.FindControl("lblTest");
        if (lblTest .Text.Length > 0)
        {
            //1. 直接insert <br/>
            blTest.Text = (lblTestt.Text.IndexOf('.') > 0) ? lblTest.Text.Insert(lblTestt.Text.IndexOf('.') + 1, "<br/>") : blTest.Text;

        
            // 2. 用replace加入<br/>
            lblTest.Text = lblTest.Text.Replace(".", ".<br/>");
        }
    }
}


參考資料：
http://www.dreamincode.net/forums/topic/117242-wrapping-around-a-symbol/
http://msdn.microsoft.com/zh-tw/library/11w09h50(v=vs.110).aspx

[Oracle/Toad] 如何在toad測試function與procedure

說明：在修改完function或procedure (schema Browser -> copy script to an Editor Window)後，要如何測試呢?


作法：
測試function：
1. Menu -> View -> 打開DBMS Output
2. 在Editor視窗輸入如下語法：
begin
    parameter := '測試資料';
    dbms_output.put_line(packagename.functionname(parameter));
commit;
end;
3. return值會顯示於DBMS Output視窗上
或SELECT function FROM dual;

測試procedure：
1. schema Browser -> Tab Source上右鍵 -> Execute Procedure (或copy code到Editor執行)
2. SELECT procedure FROM dual;


參考資料：
http://stackoverflow.com/questions/4827134/how-do-i-get-the-return-value-from-a-function-in-oracle-using-toad
http://www.dotblogs.com.tw/kim/archive/2011/06/01/26827.aspx
http://blog.yam.com/csylvia/article/71505909
http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm
http://plsql-tutorial.com/plsql-functions.htm
http://blog.yam.com/csylvia/article/71505909
http://godleon.blogspot.tw/2008/12/oracle-stored-procedures.html

[AJAX] 使用ASP.NET Ajax與jQuery Ajax實作連動下拉式選單

說明：
使用ASP.NET Ajax與jQuery Ajax實作兩個下拉式選單，第一個為地區(id: ddlArea)，第二個顯示對應地區的縣市(id: ddlCounty)。


作法：
使用ASP.NET Ajax：
1. .NET 3.5已內建ASP.NET Ajax，若是更舊版本需自行import Ajax Extensions。
2. 從工具箱拉ScriptManager與UpdatePanel控制項，然後將ddlCounty放進UpdatePanel內，
     並設定Triggers為ddlArea，例：
xxx.aspx
<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode="Release" >
    </asp:ScriptManager>
<asp:DropDownList ID="ddlArea" runat="server" AutoPostBack="true" onselectedindexchanged="ddlArea_SelectedIndexChanged"></asp:DropDownList>
 <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
     <ContentTemplate>
         <asp:DropDownList ID="ddlCounty" runat="server"></asp:DropDownList>    
     </ContentTemplate>
     <Triggers>
          <asp:AsyncPostBackTrigger ControlID="ddlArea"
                                EventName="SelectedIndexChanged"></asp:AsyncPostBackTrigger>
     </Triggers>
 </asp:UpdatePanel>

xxx.aspx.cs
protected void ddlArea_SelectedIndexChanged(object sender, EventArgs e)
{
        if (this.ddlArea.SelectedIndex > 0)
        {
            DataTable dtCounty = 資料來源;
            this.ddlCounty.DataSource = dtCounty;
            this.ddlCounty.DataTextField = "County_Name";
            this.ddlCounty.DataValueField = "County_No";
            this.ddlCounty.DataBind();
        }
}


使用jQuery Ajax (後端使用webservice)：
1. 在webservice新增webmethod供ajax存取，例：
//WebService.cs
public class AreaInfo
{
        public string AreaNo { get; set; }
        public string AreaName { get; set; }
}

public class CountyInfo
{
        public string CountyNo { get; set; }
        public string CountyName { get; set; }
}

[WebMethod]
public List<AreaInfo> LoadArea()
{
        List<AreaInfo> AreaInfo1 = new List<AreaInfo>();

        DataTable dtResult = 資料來源;
        if (dtResult != null && dtResult.Rows.Count > 0)
        {
            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                AreaInfo1.Add(new AreaInfo()
                {
                    AreaNo = dtResult.Rows[i]["Area_No"].ToString(),
                    AreaName = dtResult.Rows[i]["Area_Name"].ToString()
                });
            }
        }
        return AreaInfo1;
}

[WebMethod]
public List<CountyInfo> LoadCounty(string strAreaNo)
{
        List<CountyInfo> CountyInfo1 = new List<CountyInfo>();

        DataTable dtResult = 資料來源;

        if (dtResult != null && dtResult.Rows.Count > 0)
        {
            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                CountyInfo1.Add(new CountyInfo()
                {
                    CountyNo = dtResult.Rows[i]["County_No"].ToString(),
                    CountyName = dtResult.Rows[i]["County_Name"].ToString()
                });
            }
        }
        return CountyInfo1;
}

2. include jQuery library並使用jQuery.ajax跟後端要資料(回傳格式使用json)，例：
//xxx.aspx
<script src="jquery-1.3.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
        $(document).ready(function() {
        $.ajax({
                type: 'post',
                contentType: "application/json; charset=utf-8",
                url: "WebService.asmx/LoadArea",
                data: "{}",
                dataType: "json",
                success: function(Result) {
                    $.each(Result.d, function(key, value) {
                        $("#ddlArea").append($("<option></option>").val(value.AreaNo).html(value.AreaName));
                    });
                },
                error: function() { alert('ajax fail'); }
            });
            $('#ddlArea').change(function() {
            $("#ddlCounty").find('option').remove();
                var ID = $(this).val();
                $.ajax({
                    type: 'post',
                    contentType: "application/json; charset=utf-8",
                    url: "WebService.asmx/LoadCounty",
                    data: "{strAreaNo:" + ID + "}",
                    dataType: "json",
                    success: function(Result) {
                        $.each(Result.d, function(key, value) {
                           $("#ddlCounty").append($("<option></option>").val(value.CountyNo).html(value.CountyName));
                        });
                    },
                    error: function() { alert('ajax fail'); }
                });
            });
        });
</script>

<asp:DropDownList ID="ddlArea" runat="server"></asp:DropDownList>
<asp:DropDownList ID="ddlCounty" runat="server"></asp:DropDownList>


參考資料：
http://stackoverflow.com/questions/10477476/why-0-in-textbox10-value-is-used-to-get-scalar-value-in-asp-net-control

http://www.dotblogs.com.tw/hatelove/archive/2009/12/22/jqueryajax.aspx
http://msdn.microsoft.com/zh-tw/dd310332.aspx#jQuery
http://www.dotblogs.com.tw/topcat/category/1656.aspx
http://pastie.org/2356308
http://social.msdn.microsoft.com/Forums/zh-TW/384800f1-d366-484e-bd30-aaeac5d07d85/aspnet-jqueryjqueryajaxashxaspxasmx?forum=236#9125942a-cc8a-41d5-981e-882fe0894f82
https://www.youtube.com/playlist?list=PLNPgPJ-90sSlwfSHvuyafzWJ8jQphAam0
http://www.codeproject.com/Tips/688228/Bind-Dropdownlist-in-ASP-NET-using-jQuery-AJAX
http://encosia.com/using-jquery-to-directly-call-aspnet-ajax-page-methods/

[C#] 加入參考Microsoft.office.core的dll

做法：
加入參考->選取COM tab ->找到Microsoft Office (1x.0) Object Library


參考資料：
http://social.msdn.microsoft.com/Forums/en-US/6fef9910-fed8-4b13-9366-66d24c0966a7/microsoftofficecoredll-location