Re: exporting datagrid to excel in vb.net - Visual Basic Discussion Boards

i solved
AcceptButton.PerformClick

hi everybody

Can someone tell how i can access file of a folder on server
means www.something.com/pdf where pdf is a folder and it has 5 files such as file1 to file5 so i want to access these file name

is it possible in visual basic ?
if anyone have idea about it pleaase help me

Thanks

If you have access to the folder over a LAN and it's a shared folder, then you can use the Dir[^] function.

Dim fileName As String
fileName = Dir("\\MyComputer\SharedFolder\*.pdf")
Do While fileName <> ""
   ' Do something with the file.
   '
   ' Get next file name.
   fileName = Dir()
Loop

help me, I want to export the datagrid's data to excel using vb.net,asp.net. Please Suggest me what will I do.

I would loop through the datasource as follows: NB The starting range eg "A1" is sent to the function.

'--------------------------------------------------
Public Overloads Sub WriteDataToRange(ByVal dt As DataTable, ByVal StartingRange As String)
Dim iRow As Integer
Dim iColumn As Integer
Dim iRows = dt.Rows.Count
Dim iColumns = dt.Columns.Count

'Fill an array with the datatable
Dim aRange(1, 1)
With dt
ReDim aRange(iRows - 1, iColumns - 1)
For iRow = 0 To iRows - 1
For iColumn = 0 To iColumns - 1
aRange(iRow, iColumn) = dt.Rows(iRow).Item(iColumn).
Next
Next
End With

'copy array to range on excel worksheet
'NB: oWS (Excel.WorkSheet object must be set first. In this case it is set
'as a property before this function is called
Dim range As Object = oWS.Range(StartingRange)
range = range.resize(iRows, iColumns)
range.value = aRange
aRange = Nothing
End Sub
'--------------------------------------------------

Alternatively you could make use of ado.net as follows (without the need to open an instance of excel):

Conn.Open _
"Provider=Microsoft.Jet." & _
"OLEDB.4.0;" & _
"Data Source=C:\Temp\Nwind.mdb;" & _
"Jet OLEDB:Engine Type=4;"
Conn.Execute "SELECT * INTO " & _
"[ExcelCust] IN " & _
"'C:\Temp\Cust.xls'" & _
"'Excel 8.0;' FROM [Customers]"

Unless the resultset is large, I use the first method as it enables me to apply custom formats.

If these methods are not suitable then I will work out how to do it directly from the grid.

Roland Pheasant

Hi,
I am working crystal reports. I want total of field which has datatype as text in database. So in grand total crystal reports doesn't show the 'sum'
option. i tried to convert into currency then apply sum formula in formaula fields. but not working. if anybody there has soln plz.

Ash. R.

I want to display data in a datagrid and then want to export that data to excel in vb.net.plz help me ASAP.

thnx
josh

<%@ Import Namespace="System.Data.OleDb" %><br />
<%@ Import Namespace="System.Data" %><br />
<HTML><br />
<script language="VB" runat="server"><br />
Dim MyConnection As OleDbConnection<br />
Public Sub Page_Load(ByVal Sender As System.Object, ByVal E As System.EventArgs) Handles MyBase.Load<br />
  MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & Server.MapPath("database/utenti.mdb"))<br />
  BindGrid("id")<br />
End Sub<br />
<br />
Sub BindGrid(ByVal SortField As String)<br />
  Dim DS As DataSet<br />
  Dim MyCommand As OleDbDataAdapter<br />
  MyCommand = New OleDbDataAdapter("select * from utenti", MyConnection)<br />
  DS = New DataSet<br />
  MyCommand.Fill(DS, "utenti")<br />
  Dim Source As DataView = DS.Tables("utenti").DefaultView<br />
  Source.Sort = SortField<br />
  MyDataGrid.DataSource = Source<br />
  MyDataGrid.DataBind()<br />
End Sub<br />
<br />
Sub ToExcel(sender As Object, e As System.EventArgs)<br />
  Response.ContentType = "application/vnd.ms-excel"<br />
  Response.Charset = ""<br />
  Me.EnableViewState = False<br />
  Dim tw As New System.IO.StringWriter<br />
  Dim hw As New System.Web.UI.HtmlTextWriter(tw)<br />
  MyDataGrid.RenderControl(hw)<br />
  Response.Write(tw.ToString())<br />
  Response.End()<br />
End Sub<br />
</script><br />
<body><br />
<form runat="server" ID="Form1"><br />
<asp:Button id="Button1" runat="server" Text="Excel" OnClick="ToExcel"></asp:Button><br />
<br><br />
<br><br />
<ASP:DataGrid id="MyDataGrid" runat="server" Width="700px" BackColor="White" BorderColor="#CCCCCC"<br />
CellPadding="3" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" BorderStyle="None"<br />
BorderWidth="1px" Font-Names="Verdana" EnableViewState="False"><br />
<ItemStyle ForeColor="#000066"></ItemStyle><br />
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699"></HeaderStyle><br />
</ASP:DataGrid><BR><br />
</form><br />
</body><br />
</HTML>

_____________________

Proud to be Albanian
_____________________

if i work on Vb.net (not asp.net) , what will i do?

I subclassed a datagrid and use this code:

Dim x As Integer, y As Integer
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim oExcel As Excel.Application
Static SheetCount As Integer = 1

Dim tbl As DataTable = CType(Me.DataSource, DataTable)

Cursor.Current = Cursors.WaitCursor

If Not Me.ReadOnly Then
Dim changedRows As New ArrayList
Dim row As DataRow
For Each row In tbl.Rows
If row.RowState <> DataRowState.Unchanged Then
changedRows.Add(row)
End If
Next

If changedRows.Count > 0 Then
Cursor.Current = Cursors.Default
MsgBox("There are pending data updates for this grid. Please save these updates to the database prior to exporting the data to Excel.", MsgBoxStyle.Information, "Pending Updates")
Exit Sub
End If
End If

If oExcel Is Nothing Then
oExcel = New Excel.Application
End If

wb = oExcel.Workbooks.Add
ws = wb.Sheets.Add
ws.Name = "Export" & SheetCount
SheetCount = SheetCount + 1

'write column headers
For x = 0 To tbl.Columns.Count - 1
ws.Range(Chr(65 + x) & "1").Value = tbl.Columns(x).ColumnName
ws.Range(Chr(65 + x) & "1").Font.Bold = True
Next

'write data
For x = 0 To tbl.Rows.Count - 1
For y = 0 To tbl.Columns.Count - 1
ws.Range(Chr(65 + y) & (x + 2).ToString).Value = tbl.Rows(x).Item(y)
Next
Next

oExcel.Application.Visible = True

' Release Application object and allow Excel to be closed by user:
If Not oExcel.UserControl Then oExcel.UserControl = True
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

oExcel = Nothing

Cursor.Current = Cursors.Default

As you can see there are several references to "Me" in this routine for my use. In your case you would simply replace "Me" with the name of your datagrid. You will also need to add a reference to Excel in your project.

Hope this works for you.

Dean

Does anybody know others IDE for write and debug vb code? (for replace VB6)

Thanks in advance.

nobody knowes?

Can anyone help me, please!!! Cry | :((

I got problem regarding to my datareport. I need a long size paper (8.5" X 14") to print my data. How can I do that in "LANDSCAPE" format??? Confused | :confused:

Thanks in advanced!!!;)

hello guys its me again i would like to ask anybody on how to toggle the check state in context menu sub item......how to disappear the check when i click and vice versa.....

hope anybody can help me......i admit im new in vb.net

have a nice day everyone Smile | :)

-- modified at 3:41 Thursday 30th March, 2006

If ContextMenuStripMenuItem1.Checked = True Then<br />
            ContextMenuStripMenuItem1.Checked = False<br />
        your code <br />
       Else<br />
            ContextMenuStripMenuItem1.Checked = True<br />
        your code  <br />
       End If

Regards,

Chatura Dilan

If at first you don't succeed, try; try again, so that you know
what not to do the next time. The answer is out there.

hey Chatura thank you for sending me this code it helps me a lot....now im willing to learn more about VB.net coz i know theres people outher willing to share their knowledge. thanks a lot God Bless you.... Smile | :)

before i forgot here's my YM (Yahoo Messenger) id i hope we could talk! thank you again!

and to all people outher thank you guys!!!!!!

You are welcome Smile | :)

It’s nice to have an amiable friend like you.

Regards,

Chatura Dilan

If at first you don't succeed, try; try again, so that you know
what not to do the next time.

Your question was along the lines of:
My car does not work. Please tell me what is wrong D'Oh! | :doh:

--------------------------------------------------------
Q:What does the derived class in C# tell to it's parent?
A:All your base are belong to us!

hello..
my project is about when web cam detect motion, it will sent alert or messag to pda emulator.How it can sent alert or message to the pda emulator using vb,net 2005??
thanks

hello..
how can i access the folder that store video using pda simulator with vb.net 2005??
thanks

hi all...

i hav problems wif repeater function...d situation is...i hav a repeater where the user can input values which is in currency format (e.g. 20.00) by using the textbox..

Blush | :O

d coding ( inside the repeater at <ItemTemplate> ) Blush | :O

:

<td class="t_row" align="center"> 
<asp:TextBox id="txtRealAmt" runat="server"></asp:TextBox>
</td>

there's a calculate button which is also created inside the repeater itself

Blush | :O

d coding :

<FooterTemplate>
<tr>
<th class="t_footer">
</th>
<th class="t_footer">
</th>
<th class="t_footer">
</th>
<th class="t_footer">
<asp:Button ID="btnCalc" CssClass="btn" Runat="server" CommandName="Calc" Text="Calculate"></asp:Button>
</th>
</tr>
</TABLE>
</FooterTemplate>

n d real problem is...how to calculate the total values which is entered by the user when the calculate button is click..the total values will be put at the textbox which is not in the repeater

hopefully u guyz can help me to solve dis prob..

tq

mada mada dane

txtspk is used for sms messaging due to the limited length of messages, and to make them quick and easy to type.

Technical forums have no such limitation, and mostpeople post using a keyboard rather than num-pad....so suffice it to say txtspk is not useful in describing a problem in clear terms.

I want to put a field from table in database to a combo box without a wizard. I want to using coding...can you help me ??? Please... Blush | :O

Request the table from the database, make it the datasource, and make the displaymember property the name of the correct column in the table.

Christian Graus - Microsoft MVP - C++