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..
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..
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]
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???