Need feedback - Export dataset to excel without using a datagrid..

[manju]

Hi,

In my asp.net application I want to export the results of my dataset to an excel spreadsheet without using a datagrid.

Is it possible and if possible how??

Anyone please..

[Steven McGarva]

Assuming you've got a connection to excel then

Public objConn As OleDb.OleDbConnection
Public appExcel As Excel.Application


[CODE]


' Set up Workbook for Report.
oBook = appExcel.Workbooks.Add
' Set up Worksheet for Missing Data Report.
oBook.Worksheets.Add()
oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)
oSheet.Name = "Results"
oSheet.Range("A:Z").ColumnWidth = 30

call FillReport()

appExcel.Visible = True



Private Sub FillReport()


'*******************************************
' Now get the data................
'*******************************************
Dim mySQL As String
mySQL = "select * from MyTable"

Dim dsMyDataset As DataSet = New DataSet
Dim cmdObject As New OleDb.OleDbCommand(mySQL, objConn)
cmdObject.CommandTimeout = 30
Dim adaptObject As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
adaptObject.SelectCommand = cmdObject
adaptObject.Fill(dsMyDataset, mySQL)

'*******************************************
' Now fill the spreadsheet...............
'*******************************************
Dim iColumn, iRow As Integer
Dim iColumnMax, iRowMax As Integer
iRowMax = dsMyDataset.Tables(0).Rows.Count - 1
iColumnMax = dsMyDataset.Tables(0).Columns.Count - 1

' For each row....
For iRow = 0 To iRowMax
' and each column in this row....
For iColumn = 0 To iColumnMax
oSheet.Cells(iRow + 1, iColumn + 1) = dsMyDataset.Tables(0).Rows(iRow).Item(iColumn)
Next
Next

'*******************************************
' Finaly get rid of the dataset Object.
'*******************************************
dsMyDataset = Nothing

'*******************************************
' Format Worksheet here.
'*******************************************


End Sub

[\CODE]

[manju]

Thanks a lot Steven. I have tried to export the dataset into excel sheet in another way. Just wanted to know if its a proper and valid way to do or not.

I converted my dataset into a TabDelimited file and flush it out to he user:

Response.Clear();
Response.ContentType = "application/vnd.ms-excel"; // Set the file content type to Excel
Response.AddHeader( "Content-Disposition", "attachment; filename=Counts.xls" );
Response.Charset = ""; // Remove the charset from the Content-Type header.
for( int iCol = 0; iCol < dt.Columns.Count; iCol++ )
Response.Write( dt.Columns[iCol].ToString() + "\t" ); // Add column headers seperated by tab character

foreach( DataRow dr in dt.Rows ) // Now add the data rows
{
Response.Write( "\n" ); // New line character
for( int i = 0; i < dt.Columns.Count; i++ )
Response.Write( dr[i].ToString() + "\t" ); // Seperate each column with tab character

} // End of foreach loop
Response.Flush();
Response.End();


Is this a valid method???