Protected Sub Button_excel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_excel.Click
Dim sql_string As String = "select * from smt_time_List where 1=1 "
If Me.TextBox_model.Text.Trim <> "" Then
sql_string = sql_string + " and Model = ‘" + Me.TextBox_model.Text.Trim + "’ "
End If
If Me.DropDownList_project.SelectedIndex > 0 Then
sql_string = sql_string + " and Project = ‘" + Me.DropDownList_project.SelectedValue.Trim + "’ "
End If
If Me.TextBox_updatedate.Text.Trim <> "" Then
sql_string = sql_string + " and Update_Date = ‘" + Me.TextBox_updatedate.Text.Trim + "’ "
End If
If Me.DropDownList_line.SelectedIndex > 0 Then
sql_string = sql_string + "and Line = ‘" + Me.DropDownList_line.SelectedValue.Trim + "’ "
End If
If Me.DropDownList_boardsider.SelectedIndex > 0 Then
sql_string = sql_string + " and Board_Side= ‘" + Me.DropDownList_boardsider.SelectedValue.Trim + "’ "
End If
If Me.DropDownList_owner.SelectedIndex > 0 Then
sql_string = sql_string + " and Owner = ‘" + Me.DropDownList_owner.SelectedValue.Trim + "’"
End If
Myconnection.Open()
Dim myds As New DataSet
Dim myad As New SqlDataAdapter(sql_string, Myconnection)
myad.Fill(myds)
‘write excel******************************************
‘*************************************************************
Dim sHtml As String
sHtml = "<table cellSpacing=’0′ borderColorDark=’#ffffff’ cellPadding=’0′ width=’100%’ border=’1′>"
sHtml = sHtml & "<tr bgColor=’#CCFFFF’><STRONG><td colspan=’1′ >Project</td><td colspan=’1′ >BU</td><td colspan=’1′ >Model</td><td colspan=’1′ >Description</td><td colspan=’1′ >Rev</td><td colspan=’1′ >Board_Side</td><td colspan=’1′ >Line</td><td colspan=’1′ >Quotation_Time</td><td colspan=’1′ >Standard_time</td><td colspan=’1′ >Update_Date</td><td colspan=’1′ >Owner</td></STRONG></tr>"
For i As Integer = 0 To myds.Tables(0).Rows.Count – 1
sHtml = sHtml & "<tr><STRONG><td >" & myds.Tables(0).Rows(i).Item("Project").ToString & "</td><td>" & myds.Tables(0).Rows(i).Item("BU").ToString & "</td><td>" & myds.Tables(0).Rows(i).Item("Model").ToString & "</td><td>" & myds.Tables(0).Rows(i).Item("Description").ToString & "</td><td>" & myds.Tables(0).Rows(i).Item("Rev").ToString & "</td><td>" & myds.Tables(0).Rows(i).Item("Board_Side").ToString & "</td><td >" & myds.Tables(0).Rows(i).Item("Line").ToString & "</td><td >" & myds.Tables(0).Rows(i).Item("Quotation_Time").ToString & "</td><td >" & myds.Tables(0).Rows(i).Item("Standard_time").ToString & "</td><td >" & myds.Tables(0).Rows(i).Item("Update_Date").ToString & "</td><td >" & myds.Tables(0).Rows(i).Item("Owner").ToString & "</td></STRONG></tr>"
Next
sHtml = sHtml & "</table>"
Dim urlstr As String
urlstr = MapPath(Request.ApplicationPath) + "xlssmt_std_report.xls"
Dim fileWrite As StreamWriter = New StreamWriter(urlstr, False, Encoding.Default) ‘Encoding.UTF8)
fileWrite.Write(sHtml)
fileWrite.Close()
fileWrite.Dispose()
Response.Write("<script>window.open(‘" + Request.ApplicationPath + "/xls/smt_std_report.xls" + "’);</script>")
End Sub