Read from db and output to excel at client side

[a1jit]

Hi Guys,

 

Have you guys seen any sample tutorial or example that teaches us how to do the following appilication

 

1) User click export button

2) Then the asp application gets all records from database (server side)

3) it opens excel at clients side and start to write to it...

 

How can i accomplish this..Aany help will be greatly appreciated..

 

Thank you very very much..

[penfold69]

It would be better to:

 

1. User click export button

2. ASP application retrieves all records, creates a .CSV file (or .XLS file) server side

3. ASP application sends the .CSV file to the user

4. User is prompted to Save or Download

5. Profit!!

 

B.

[mike55]

Here's what you must do to write data to a .CSV file...

 

Firstly open a connection to your database and select all the data you need from the relevant tables and put the data into a dataset.

sqlCommand = "Select * from table1"
sqlAdapter.selectcommand = sqlcommand
sqlAdapter.fill(dsDataset)

 

Secondly, you must create the .CSV file for the user on the server.

Dim tableColumns As DataColumnCollection = dsDataset.Tables(0).Columns
Dim tableRows As DataRowCollection = dsDataset.Tables(0).Rows
dim strPath as string
dim strFile as string
strFile = "test.csv"
strPAth = "C:\myFiles\Download\"+strFile

'Create a fileStream.
Dim newFile As FileStream = New FileStream(strPath, FileMode.Create)
newFile.Close()

'Open the .csv file and write to it.
Dim file As System.IO.StreamWriter = New System.IO.StreamWriter(strPath)
Try
Dim ctrColumn As Int16
ctrColumn = 0
While ctrColumn < tableColumns.Count
Dim column As DataColumn = tableColumns(ctrColumn)
If ctrColumn < tableColumns.Count - 1 Then
 sqlInsert += column.ColumnName.ToString + ","
Else
 sqlInsert += column.ColumnName.ToString
End If
ctrColumn = ctrColumn + Convert.ToInt16(1)
End While
file.WriteLine(sqlInsert)

'Add the records from the table.
Dim ctrRow As Int16
ctrRow = 0
While ctrRow < tableRows.Count
sqlInsert = Nothing
Dim sqlValues As String
sqlValues = Nothing
Dim row As DataRow = tableRows(ctrRow)
Dim rowItems As Object() = row.ItemArray
ctrColumn = 0
While (ctrColumn < tableColumns.Count)
 If ctrColumn < tableColumns.Count - 1 Then
  sqlValues += rowItems(ctrColumn).ToString.Replace("''", "'") + ","
 Else
  sqlValues += rowItems(ctrColumn).ToString.Replace("''", "'")
 End If
 sqlInsert = sqlInsert + sqlValues
 ctrColumn = ctrColumn + Convert.ToInt16(1)
End While
file.WriteLine(sqlValues)
rowsCreated = Convert.ToInt16(rowsCreated) + Convert.ToInt16(1)
ctrRow = ctrRow + Convert.ToInt16(1)
End While

'Close the fileSteam
file.Close() 

 

Finally, you download the code.

       Dim fs As FileStream
       Dim strContentType As String

       fs = File.Open(filePath, FileMode.Open) 'Open the file.
       Dim byBytes(Convert.ToInt32(fs.Length)) As Byte
       fs.Read(byBytes, 0, Convert.ToInt32(fs.Length))
       fs.Close()
       Response.AddHeader("Content-disposition", "attachment; fileName=" + FileName)
       Response.ContentType = "application/octet-stream"
       Response.BinaryWrite(byBytes)
       Response.End()

 

Your much better off downloading the data into .csv format, as not everyone who uses you site/application will have excel.

 

In addition, suggest that you create a random number generator that generates numbers between 1 and 1,000,000. You then use this number generator to specify the name of the file been created on the web server.

 

Once the download has been started, delete the file off your web server using:

file.delete(filepath)

 

Mike55.

[a1jit]

Hello Guys,,

 

Thank you very much for the ideas and input..just a question..i have a small piece of code here that does exectly what i did..really good..

 

Response.ContentType = "application/csv"

Response.AddHeader "content-disposition", "inline; filename=export.csv"

Response.Write( "DateTime,UserID,ID,HOAccountNo,OrderNumber,BranchAccNo,DeliveryInstruction,ProductCode,Quantity,UnitPrice,BackOrder, PayMethod" & chr(13) & chr(10) )

 

But when im displaying can i specify which cells i want to display in? any way to do this..

 

Because when im retrieving data, i need to check against some rules..maybe if it matches..it goes in the second column, if not, maybe it get displayed in the first column..

 

Aany way i can solve this?>