'** Description: Export Excel (Follows Help Me Resolve My Task, IT IS Very Good, I Like IT) in ASP.NET
Public class cmpdatagridtoexcel inherits system.componentmodel.component
Public Shared Sub DataGridToExcel (ByVal dgExport As DataGrid, ByVal response As HttpResponse) 'clean up the response.object (I did not use below 2 lines for can not get data) response.Clear () response.Charset = ""' set the response mime type for excel response.ContentType = "application / vnd.ms-excel" 'create a string writer Dim stringWrite As New System.IO.StringWriter ()' create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System. Web.ui.htmlTextWriter (StringWrite)
'Instantiate a DataGrid DIM DG As New DataGrid ()' Just Set The Input DataGrid = To The New DG Grid DG = DGEXPORT
'I want to make sure all, no annoying gridlines dg.gridlines = gridlines.none' make the header text bold dg.headerstyle.font.bold = true
'If Needed, Here's How To Change Colors / Formatting At The Component Level' DG.Headerstyle.ForeColor = System.drawing.Color.Black 'Dg.ItemStyle.ForeColor = system.drawing.color.black
'Bind the modified datagrid dg.DataBind ()' tell the datagrid to render itself to our htmltextwriter dg.RenderControl (htmlWrite) 'output the html response.Write (stringWrite.ToString) response.End () End Sub
END CLASS
-------------------------------------------------- ---------------
** Description: The following ASP code implementation directly into Excel
<% OPTION Explicit Class Excelgen Private Objspreadsheet Private ICOLOFFSET Private Irowoffset
Sub class_initialize () set objspreadsheet = server.createObject ("owc.spreadsheet") rowoffset = 2 iColoffset = 2 End subsis Class_Terminate () set objspreadsheet = Nothing 'Clean Up End Sub
Public Property Let Columnoffset (ICOLOFF) ICOLOFF> 0 Then ICOLOFFSET = ICOLOFF ELSE ICOLOFFSET = 2 End If End Property
Public Property Let RowoffSet (irowoff) if irowoff> 0 Then Irowoffset = irowoff else irowoffset = 2 End if End Property
Sub GenerateWorksheet (objRS) 'Populates the Excel worksheet based on a Recordset's contents' Start by displaying the titles If objRS.EOF then Exit Sub end if Dim objField, iCol, iRow iCol = iColOffset iRow = iRowOffset For Each objField in objRS.Fields objSpreadsheet .Cells (iRow, iCol) .Value = objField.Name objSpreadsheet.Columns (iCol) .AutoFitColumns' an Excel table font objSpreadsheet.Cells (iRow, iCol) .Font.Bold = True objSpreadsheet.Cells (iRow, iCol) .FONT.ITIC = false objspreadsheet.cells (irow, icol) .font.size = 10 objspreadsheet.cells (irow, icol) .halignment = 2 'homo = iCol 1
Next 'objfield' Display All of the data do while not objrs.eof irow = row 1 icol = icoloffset
For each objfield in objrs.fields if isnull (objfield.value) Then objspreadsheet.cells (iRow, ICOL) .value = "" "" "" "" "Else Objspreadsheet.cells (iRow, Icol). Value = objField.Value objSpreadsheet.Columns (iCol) .AutoFitColumns objSpreadsheet.Cells (iRow, iCol) .Font.Bold = False objSpreadsheet.Cells (iRow, iCol) .Font.Italic = False objSpreadsheet.Cells (iRow, iCol). Font.Size = 10 End If iCol = iCol 1 Next 'objField objRS.MoveNext Loop End subFunction SaveWorksheet (strFileName)' Save the worksheet to a specified filename On Error Resume Next Call objSpreadsheet.ActiveSheet.Export (strFileName, 0) SaveWorksheet = (Err.Number = 0) End Function End Class
'Connect to the database Dim objRS Dim strConn Dim conn Dim sql Set conn = Server.CreateObject ( "ADODB.Connection") strConn = "Provider = SQLOLEDB; Data Source = localhost; Initial Catalog = pubs; User ID = sa; Password = sa"
Conn.open strconn set objrs = server.createObject ("adoDb.recordset") SQL = "Select * from authors Objrs.open SQL, STRCONN
Dim Savename 'This is because there is no cookies, so write Savename! I have to change myself! 'Savename = Request.cookies ("Savename") ("name") savename = request.form ("excelname")
DIM Objexcel Dim ExcelPath ExcelPath = "Excel /" & Savename & ".xls" 'Here, set the save path in a folder named "Excel" ("Excel" folder must exist) set objectel = new Excelgen Objexcel. RowOffset = 1 objExcel.ColumnOffset = 1 objExcel.GenerateWorksheet (objRS) If objExcel.SaveWorksheet (Server.MapPath (ExcelPath)) then Response.Write "
