ASP.NET 產生 匯出 Excel 到Server端

假使要從Client產在一個excel檔到Server端
必須在web.config加上一個有權限執行excel的使用者
如:
<system.web>
<identity impersonate="true" userName ="Administrator" password ="123123"/>
</system.web>

參考自:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

Try
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

For i As Integer = 0 To ds.Tables.Count - 1
oSheet = oBook.Worksheets.Add
'合併欄位
oSheet.Range("A1", "I1").Merge()
oSheet.Range("J1", "K1").Merge()
'文字置中
oSheet.Range("A1", "I1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oSheet.Range("J1", "K1").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oSheet.cells(1, 1).Value = "自製工具個人分戶帳"
oSheet.cells(1, 10).Value = "資料日期: " & DateTime.Now.ToShortDateString
oSheet.cells(1, 1).Font.Bold = True
'加入外框線
oSheet.Range("A2", "K" & ds.Tables(i).Rows.Count + 2).Borders.LineStyle = 1
'強制轉型 不加這行的話如果字串是0002 匯到excel會變成2
oSheet.Cells.NumberFormatLocal = "@"
For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
'Add data to cells of the first worksheet in the new workbook.
oSheet.Name = ds.Tables(i).Rows(j).Item(1)
'oSheet.Range("A1").Value = "Last Name"
oSheet.cells(2, 1).Value = "項次"
oSheet.cells(2, 2).Value = "借用人"
oSheet.cells(2, 3).Value = "借用人帳號"
oSheet.cells(j + 3, 1).value = RTrim(ds.Tables(i).Rows(j).Item(0))
oSheet.cells(j + 3, 2).value = RTrim(ds.Tables(i).Rows(j).Item(1))
oSheet.cells(j + 3, 3).value = RTrim(ds.Tables(i).Rows(j).Item(2))
'自動調整欄寬
oSheet.Columns("A:Z").EntireColumn.AutoFit()
Next
Next
'Save the Workbook and quit Excel.
'如果檔案已存在 則自動刪除
If System.IO.File.Exists("C:\" & filename & "borrow.xls") = True Then
System.IO.File.Delete("C:\" & filename & "borrow.xls")
End If
oBook.SaveAs("C:\" & filename & "borrow.xls")

oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

'KillExcel()
Catch ex As Exception

End Try

如果要強制刪除Excel的程序可用以下語法
不過要注意執行後會強制關掉畫面上所有的excel
Private Sub KillExcel()
Dim myProcesses() As Diagnostics.Process
Dim myProcess As Diagnostics.Process
' Returns array containing all instances of "EXCEL".
On Error Resume Next
myProcesses = Diagnostics.Process.GetProcessesByName("EXCEL")
For Each myProcess In myProcesses
myProcess.Kill()
Next
On Error GoTo 0
myProcesses = Nothing
myProcess = Nothing
End Sub

DataTable 加入流水編號方式

Dim dt as new DataTable
Dim column As New DataColumn("項次", Type.GetType("System.Int32"))
With column
.AutoIncrement = True
.AutoIncrementSeed = 1
.AutoIncrementStep = 1
End With
dt.Columns.Add(column)

輸出畫面為千分位 加逗號

SELECT convert(nvarchar,cast('12345.999' as money),1)

如何使 ASP.NET 元件 MENU 能夠更漂亮 CSS

套用以下基本CSS，再依個人審美觀做適當的修改

<asp:Menu ID="Menu1" runat="server" CssClass="menu_Main" DynamicHorizontalOffset="2"
Orientation="Horizontal" StaticSubMenuIndent="10px" Width="296px">
<StaticMenuStyle CssClass ="menu_StaticMenuStyle" />
<StaticMenuItemStyle  CssClass="menu_StaticMenuItemStyle " />
<StaticHoverStyle CssClass="menu_StaticHoverStyle" />
<StaticSelectedStyle CssClass="menu_StaticSelectedStyle"/>
<DynamicMenuStyle CssClass="menu_DynamicMenuStyle" BackColor="#F8F8D4" />
<DynamicSelectedStyle CssClass="menu_DynamicSelectedStyle"  />
<DynamicHoverStyle  CssClass ="menu_DynamicHoverStyle"/>
<Items>
</Items>
</asp:Menu>

/*menu style*/

.menu_Main{
height:30px;
}
.menu_StaticMenuStyle{
}
.menu_StaticMenuItemStyle {
width:70px;
font-size:15px;
/*HorizontalPadding="5px" VerticalPadding="2px"*/
}
.menu_StaticHoverStyle{
color:#FFFFFF;
background-color:#668BB2 ;
width:60px;
}
.menu_StaticSelectedStyle{
}
.menu_DynamicMenuStyle{
font-size:15px;
border-right: #B1B266 thin solid;
border-top: #B1B266 thin solid;
border-left: #B1B266 thin solid;
border-bottom: #B1B266 thin solid ;
z-index: 100;
width:100px;
}
.menu_DynamicMenuItemStyle{
width:200px;
/*HorizontalPadding="5px" VerticalPadding="2px"*/
}
.menu_DynamicSelectedStyle{
}
.menu_DynamicHoverStyle{
color:#FFFFFF;
background-color:#B1B266 ;
width:100px;
}

Gridview 套用 CSS

使用Visual Studio 直接拉gridview到畫面， 如果沒有再套上個css的話，真的是醜到暴， 這邊簡單做了一個css套上gridview，畫面好看多了

<asp:GridView ID="GridView1" runat="server" Width="800px" CssClass="fixedheader">
<RowStyle CssClass="gv_ItemCSSR" />
<HeaderStyle CssClass="gv_HeaderCSSM" />
<FooterStyle CssClass="gv_FooterCSS" />
<SelectedRowStyle CssClass ="gv_SelectedRowCSS" />
<Columns>
<asp:TemplateField HeaderText="選取" ShowHeader="False">
<ItemTemplate>
<asp:CheckBox ID="Chk" runat="server" Checked="True" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

/* gridview style*/

.fixedheader
{
left: 0px;
position: relative;
top: 0px;
font-size: 12px;
}
/* header 漸層*/
.gv_HeaderCSSN{
font-size: 14px;
height:20px;
font-weight :100; /* 不設定粗體的話 標題字會太粗 */
background-image: url(../Image_File/gv_bk01.gif);
}
/* header 條紋*/
.gv_HeaderCSSM{
font-size: 14px;
height:20px;
font-weight :100;
background-image: url(../Image_File/gv_bk02.gif);
}
/* header 深藍 字白字*/
.gv_HeaderCSSB
{
font-size: 14px;
height:20px;
font-weight :100;
color:#FFFFFF;
background-image: url(../Image_File/gv_bk03.gif);
}
.gv_ItemCSSG{
background-color: #EEFCF5;
height:15px;
font-size:9pt;
color:#000000;
}
.gv_ItemCSSB{
background-color: #EEEEFC;
height:15px;
font-size:9pt;
color:#000000;
}
.gv_ItemCSSR{
background-color: #FCEEEE;
height:15px;
font-size:9pt;
color:#000000;
}
.gv_SelectedRowCSS
{
background-color:#80FF80;
}
.gv_FooterCSS{
background-color: #1FBBBD;
color: white;
height:20px;
font-size:9pt;
}

jQuery plugin: Autocomplete

為了實現類似搜尋引擊自動完成的功能， 上網找有沒有現成的套件可以使用， http://bassistance.de/jquery-plugins/jquery-plugin-autocomplete/ jQuery提供不少好用的套件， 不過產生了一個問題， 如果我畫面上使用UpdatePanel則會沒有反應， 上網查了一下，原來是這個套件不支援非同步動作， 必須下載新版的jQuery並使用live綁定 原始:

$("#ctl00_ContentPlaceHolder1_TextBox8").autocomplete('Return_B.aspx?q=a',
{ 
delay: 200,
highlight: false,
multiple: true,
multipleSeparator: " ",
scroll: true,
scrollHeight: 300
});

改成:

$("#ctl00_ContentPlaceHolder1_TextBox8").live("keypress",function(){
$(this).autocomplete('Return_B.aspx?q=a',
{
delay: 200,
highlight: false,
multiple: true,
multipleSeparator: " ",
scroll: true,
scrollHeight: 300,
});
});

使用live()綁定之後雖然自動完成有反應， 不過每次自動搜尋出結果後， 又回來初始的搜尋結果， 只好暫時先把這個畫面的UpdatePanel拿掉， 再找辦法解決

IE8 與 MENU元件的BUG

引用自:http://blogs.msdn.com/giorgio/archive/2009/02/01/asp-net-menu-and-ie8-rendering-white-issue.aspx

加上<DynamicMenuStyle CssClass="IE8Fix" />

-------------------------------------
<asp:Menu ID="Menu1" runat="server" DataSourceID="SiteMapDataSource1">
<DynamicMenuStyle CssClass="IE8Fix" />
</asp:Menu>

.IE8Fix{
z-index: 100;
}

Web Service 介紹

Web Service主要是利用HTTP與XML進行網路上的資料交換，不論是不同的企業、公司內部網路等等。而一個完整的Web Service平台則是加上SOAP、WSDL、UDDI
UDDI (Universal Description Discovery and Integration) : 提供描述與搜尋與整合Web Service個標準。
WSDL (Web Service Description Language): 描述Web Services的Operation。
SOAP (Simple Object Access Protocol): 是一種通訊協定，雙方同時支援SOAP就可進行交談。
 
Client
                            UDDI

                            WSDL
(資料交換)
                            SOAP

                            XML

                            HTTP
Web Service提供者

Visual Studio 2005 中斷點無效問題

這算是IE8的bug吧，只要是先開了IE8，再開Visual Studio 2005開始偵錯時，
就會造成中斷點無法使用，解決方法:
開始 => 執行 => regedit
在HKEY_LOCALMACHINE -> SOFTWARE -> Microsoft -> Internet Explorer -> Main
在Main目錄下，新增OWORD值取名為TabProcGrowth 給值0 就ok了
參考下面連結:
http://www.microsoft.com/windows/internet-explorer/beta/readiness/developers-existing.aspx#lcie

Older versions of the Visual Studio Debugger get confused by this and cannot figure out how to attach to the correct process. You can work around this by disabling the process growth feature of LCIE. Here's how:

1) Open RegEdit
2) Browse to HKEY_LOCALMACHINE -> SOFTWARE -> Microsoft -> Internet Explorer -> Main
3) Add a dword under this key called TabProcGrowth
4) Set TabProcGrowth to 0

Since you are running on Windows Server 2003, this is all you should need to do. If you run into the same problem on Vista or newer, you will also need to turn off protected mode.