export to xsl

[kebele]

I am trying to create a csv file for the customer to load it to xsl instead of viewing it from the gridview. I did create a link and i am just wondering if i am on the right track. so far i am getting this error msg: operator'&' is not defined for types 'char' and system.date.tablecollection'. thanks


Imports System.Data
Imports System.Data.OracleClient
Imports System.Text.StringBuilder
Imports System.Configuration.ConfigurationManager


Partial Class OneItemPerLine
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


' Dim connectionString As String
If Not IsPostBack Then

Dim oOracleConn As OracleConnection
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
oOracleConn = New OracleConnection(connectionString)


'Dim oOracleConn As OracleConnection = New OracleConnection(ConfigurationManager.AppSettings("OneItemConn")).ConnectionString
Dim sql As String = " SELECT DISTINCT IDESCR,IUNITS "
sql = sql & "FROM "
sql = sql & "ITEMLIST "


'oOracleConn.Open()

'create a new command and pass our sql statement and our connection object.
Dim myCommand1 As New OracleCommand(sql, oOracleConn)

'open the connection
oOracleConn.Open()


'create a new sqladapter and set its command object with our sqlcommand
Dim myAdapter1 As New OracleDataAdapter(myCommand1)

'create a new dataset to hold our data
Dim myDataSet As New DataSet


'fill the dataset with the result of our query from the specified command
myAdapter1.Fill(myDataSet, "OneItem")

'Bind the DataSet to the GridView
gvCustomers.DataSource = myDataSet
gvCustomers.DataBind()


'Close the connection


End If
End Sub




Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Dim myDataSet As New DataSet
Dim filename As String
Dim i As Integer
Dim sb As System.Text.StringBuilder
filename = "phllist.csv"

For i = 0 To myDataSet.Tables(0).Columns.Count - 1
If i < (myDataSet.Tables(0).Columns.Count - 1) Then
sb.Append(Chr(34)&(myDataSet.Tables[0].Columns.ColumnName & Chr(34)) & ",")
Else
sb.Append(Chr(34)&(myDataSet.Tables[0].Columns.ColumnName & Chr(34)) & vbCrLf)
End If

Next


Response.ContentType = "Application/x-csv"
Response.AddHeader("content-disposition", "attachment;filename=""" & filename & """")
Response.Write(sb.ToString)
Response.End()


End Sub
End Class

 

[ca8msm]

sb.Append(Chr(34)&(myDataSet.Tables[0].Columns.ColumnName & Chr(34)) & ",")

You should add a space before and after the ampersands and also use curly brackets rather than square brackets in VB code.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Oh, and do you really mean xsl (as in Extensible Stylesheet Language) or do you mean Microsoft Excel?

As for exporting the GridView, have you considered exporting it to excel rather than CSV (if that is indeed what you meant)? If so, read:

http://www.gridviewguy.com/ArticleDetails.aspx?articleID=86

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[kebele]

Thank you ca8msm for your reply. yes, I meant microsoft excel..sorry for the confusion. I am looking at the link you provided to me and the code is in c# and i am working to convert it to vb.net and i will let you know the outcome. thank you again for the help

 

[kebele]

I converd the code to vb.net and now I am getting the below error msg. I did have the gridview inside a form tag and do not know why i am getting this error msg. thanks

Server Error in '/' Application.
--------------------------------------------------------------------------------

Control 'gvCustomers' of type 'GridView' must be placed inside a form tag with runat=server.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Control 'gvCustomers' of type 'GridView' must be placed inside a form tag with runat=server.

Source Error:


Line 79: Dim htmlWrite As New HtmlTextWriter(stringWrite)
Line 80:
Line 81: gvCustomers.RenderControl(htmlWrite)
Line 82:
Line 83: Response.Write(stringWrite.ToString())

Imports System.Data
Imports System.IO.StringWriter
Imports System.Data.OracleClient
Imports System.Text.StringBuilder
Imports System.Web.UI.HtmlTextWriter
Imports System.Configuration.ConfigurationManager


Partial Class OneItemPerLine
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


' Dim connectionString As String
If Not IsPostBack Then

Dim oOracleConn As OracleConnection
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
oOracleConn = New OracleConnection(connectionString)


'Dim oOracleConn As OracleConnection = New OracleConnection(ConfigurationManager.AppSettings("OneItemConn")).ConnectionString
Dim sql As String = " SELECT DISTINCT IDESCR,IUNITS "
sql = sql & "FROM "
sql = sql & "ITEMLIST "


'oOracleConn.Open()

'create a new command and pass our sql statement and our connection object.
Dim myCommand1 As New OracleCommand(sql, oOracleConn)

'open the connection
oOracleConn.Open()


'create a new sqladapter and set its command object with our sqlcommand
Dim myAdapter1 As New OracleDataAdapter(myCommand1)

'create a new dataset to hold our data
Dim myDataSet As New DataSet


'fill the dataset with the result of our query from the specified command
myAdapter1.Fill(myDataSet, "OneItem")

'Bind the DataSet to the GridView
gvCustomers.DataSource = myDataSet
gvCustomers.DataBind()


'Close the connection


End If
End Sub




Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")

Response.Charset = ""

' If you want the option to open the Excel file without saving than

' comment out the line below

' Response.Cache.SetCacheability(HttpCacheability.NoCache)

Response.ContentType = "application/vnd.xls"

Dim stringWrite As New System.IO.StringWriter()

Dim htmlWrite As New HtmlTextWriter(stringWrite)

gvCustomers.RenderControl(htmlWrite)

Response.Write(stringWrite.ToString())

Response.End()


End Sub
Public Overridable Sub VerifyRenderingInServerForm(ByVal gvCustomers, ByVal control)


'Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

End Sub

End Class

<html xmlns="

http://www.w3.org/1999/xhtml"

>
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>

One Item Per Line Test Page</h1>
<p>
<asp:LinkButton ID="LinkButton1" runat="server">LinkButton</asp:LinkButton>

<asp:GridView ID="gvCustomers" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Width="97%" HorizontalAlign="Left" >
<Columns>
<asp:BoundField>
<HeaderStyle HorizontalAlign="Left" />
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

 

[kebele]

hi all,
I managed to fix some of the error msg i got before and now i have one more thing and do not know how to fix this.I run my app. and got to the page where it ask me to open Ms excel and when i opened it came up blank.Can someone tell me why this is happening and how to fix it. thanks

Imports System.Data
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager


Partial Class phldetail
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


' Dim connectionString As String
If Not IsPostBack Then

'This will get the value from the url using query string method
Dim ContractId As String = Request.QueryString("ConId")

'Test if the correct contract id has been retrieved
' Response.Write("Test Contract Id Value passed to phl detail = " & ContractId)

'Dim oOracleConn As OracleConnection
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)

'sql to generate the planholders list.Don't alter this code
Dim SQL As String = " SELECT DISTINCT Decode(trim(Min(J.ROUTE)),null,'Unknown', Min(J.ROUTE)) rt, v.vendor vd, r.addrnum ad, v.vnamel vn, "
SQL = SQL & "TRIM (r.aaddr1 || decode(trim(r.aaddr2),null,'',' - ') || r.aaddr2) ad1,"
SQL = SQL & "p.billto, r.acity cy, r.astate st,r.azipcode zp, "
SQL = SQL & " substr(decode(trim(r.vasst1),null, 'N/A','000/000-0000?','N/A','000/000-0000','N/A', r.vasst1),1,12) fx, "
SQL = SQL & " substr(decode(trim(r.aphone),null, 'N/A','000/000-0000?','N/A', r.aphone),1,12)ph, "
SQL = SQL & " substr(l.letting,3,2)||'-'|| substr(l.letting,5,2)||'-'|| substr(l.letting,1,2)lt, "
SQL = SQL & " l.CALL cl, l.lcontid cid, q.cprojnum sp, q.cfacssup ds,q.ccnty1 cty ,"
'Description for county name retrived by passing contract id



SQL = SQL & "(select initcap(FUNC_GET_COUNTY_NAME(CCNTY1)) "

'the value for contract id comes from the querystirng
SQL = SQL & "FROM proposal where contid=" & "'" & ContractId & "'" & ") cname"
SQL = SQL & ",q.ccnty1 cty "


'ends here to retrive the county name from the proposal table
SQL = SQL & "FROM VENDOR V, VENDADDR R, LETPROP L, PLANHOLD P,PROPOSAL Q, PROJECT J,PROPPROJ K "
SQL = SQL & "WHERE V.VENDOR = R.VENDOR "
SQL = SQL & "AND K.CONTID = Q.CONTID "
SQL = SQL & "AND K.PCN = J.PCN "
SQL = SQL & "AND L.LCONTID = K.CONTID "
'value for contract id passed using querystring
SQL = SQL & "and l.lcontid = " & "'" & ContractId & "'"
SQL = SQL & "AND P.VENDOR = V. VENDOR "
SQL = SQL & "AND L.LETTING = P.LETTING "
SQL = SQL & "AND L.CALL = P.CALL "
SQL = SQL & "AND R.ADDRNUM = P.BILLTO "
SQL = SQL & "group by V.VENDOR,R.ADDRNUM, V.VNAMEL, R.AADDR1, R.AADDR2,P.BILLTO,R.ACITY, R.ASTATE, "
SQL = SQL & "R.AZIPCODE,R.VASST1,R.APHONE,L.LETTING, L.CALL,L.LCONTID,Q.CPROJNUM,Q.CFACSSUP,Q.CCNTY1 "
SQL = SQL & "ORDER BY Q.CPROJNUM DESC"


'create a new command and pass our sql statement and our connection object.
Dim myCommand1 As New OracleCommand(SQL, oOracleConn)

'open the connection
oOracleConn.Open()


'create a new sqladapter and set its command object with our sqlcommand
Dim myAdapter1 As New OracleDataAdapter(myCommand1)

'create a new dataset to hold our data
Dim myDataSet As New DataSet


'fill the dataset with the result of our query from the specified command
myAdapter1.Fill(myDataSet, "phldetail")

'Bind the DataSet to the GridView
gvphldetail.DataSource = myDataSet
gvphldetail.DataBind()
lblsp.Text = myDataSet.Tables("phldetail").Rows(0).Item("sp").ToString()
lblcid.Text = myDataSet.Tables("phldetail").Rows(0).Item("cid").ToString()
lblRt.Text = myDataSet.Tables("phldetail").Rows(0).Item("Rt").ToString()
lblds.Text = myDataSet.Tables("phldetail").Rows(0).Item("ds").ToString()
lblcty.Text = myDataSet.Tables("phldetail").Rows(0).Item("cname").ToString()
lblcl.Text = myDataSet.Tables("phldetail").Rows(0).Item("cl").ToString()
lbllt.Text = myDataSet.Tables("phldetail").Rows(0).Item("lt").ToString()

'Close the connection
oOracleConn.Close()
lblTotal.Text = gvphldetail.Rows.Count.ToString
lblDate.Text = DateTime.Now.ToString("d")
End If
End Sub

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-Disposition", "attachment;filename=planholders.xls;")

Response.Charset = String.Empty

Me.EnableViewState = False

Dim oStringWriter As New System.IO.StringWriter

Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

gvphldetail.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString)

Response.End()

End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub

End Class