Datatable to eXcel Workbook

Sub Page_Load(sender As Object, e As EventArgs)
    
        ' Create a DataSet from an XML file.  Modify this code to use
        ' any DataSet such as one returned from a database query.
        Dim xmlfile As String = Server.MapPath("files/spiceorder.xml")
        Dim dataset As New System.Data.DataSet()
        dataset.ReadXml(xmlfile)
        Dim datatable As System.Data.DataTable = dataset.Tables("OrderItems")
        
        ' Create a new workbook and worksheet.
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
        Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
        worksheet.Name = "Spice Order"
        
        ' Get the top left cell for the DataTable.
        Dim range As SpreadsheetGear.IRange = worksheet.Cells("A1")
        
        ' Copy the DataTable to the worksheet range.
        range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.None)
        
        ' Auto size all worksheet columns which contain data
        worksheet.UsedRange.Columns.AutoFit()
        
        ' Stream the Excel spreadsheet to the client in a format
        ' compatible with Excel 97/2000/XP/2003/2007.
        Response.Clear()
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97)
        Response.End()
        
    End Sub 'Page_Load

Post a Comment

Previous Post Next Post