Read from db and output to excel at client side

A

a1jit

Regular
Joined
Aug 19, 2005
Messages
89
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..
 
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.
 
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.
Code: 
sqlCommand = "Select * from table1"
sqlAdapter.selectcommand = sqlcommand
sqlAdapter.fill(dsDataset)

Secondly, you must create the .CSV file for the user on the server.
Code: 
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.
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:
Code: 
file.delete(filepath)

Mike55.
 
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?>
 
Back
Top