RecorteX

Exportar a Excel

descargar
 
  		public static void DT2xls(System.Web.HttpResponse Response,  DataTable dt)
		{
			// exportar a xls
			Response.Clear();
			Response.Buffer= true;
			Response.AddHeader("Content-Disposition", "attachment; filename=Export.xls");
			Response.ContentType = "application/vnd.ms-excel";
			System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
			System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
			System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
			dg.DataSource = dt;
			dg.DataBind();
			dg.RenderControl(oHtmlTextWriter);
			Response.Write(oStringWriter);
			Response.End();
		}
 

Para consola

descargar
 
    Public Sub htmlExport()
        Dim gvEx As New System.Web.UI.WebControls.GridView
        gvEx.AutoGenerateColumns = True
        gvEx.AllowSorting = False
        gvEx.AllowPaging = False
        Dim stringWrite As New System.IO.StringWriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        gvEx.DataSource = Me.DataSource
        gvEx.DataBind()
        gvEx.RenderControl(htmlWrite)
        WriteFile(Me.FileName, stringWrite.ToString())
    End Sub
 

Usando excel

descargar
 
    Public Sub ExcelExport()
        Dim oexcel As Excel.Application
        Try
            If File.Exists(FileName) Then File.Delete(FileName)
            'create new excel application
            oexcel = CreateObject("Excel.Application")
            oexcel.Application.DisplayAlerts = True
            'add a new workbook
            'obook = oexcel.Workbooks.Open(FileName)
            Dim obook As Excel.Workbook = oexcel.Workbooks.Add()
            Dim ws As Excel.Worksheet = obook.Sheets(1)
 
            ' Add each row of data to the sheet.
            ' The sheet cell row is incremented by one because the first row was used for the header.
            For rowIndex As Integer = 1 To DataSource.Rows.Count
                For colIndex As Integer = 1 To DataSource.Columns.Count
                    TryCast(ws.Cells(rowIndex + 1, colIndex), Excel.Range).Value2 = DataSource.Rows(rowIndex - 1)(colIndex - 1).ToString()
                Next
            Next
 
            ' Create the headers on the sheet.
            For colIndex As Integer = 1 To DataSource.Columns.Count
                TryCast(ws.Cells(1, colIndex), Excel.Range).Value2 = DataSource.Columns(colIndex - 1).ColumnName
                TryCast(ws.Cells(1, colIndex), Excel.Range).Font.Bold = True
                TryCast(ws.Cells(1, colIndex), Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                TryCast(ws.Cells(1, colIndex), Excel.Range).EntireColumn.AutoFit()
            Next
 
 
            oexcel.Visible = False
 
            If Password <> "" Then
                obook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, Password, Nothing, False, False, Excel.XlSaveAsAccessMode.xlExclusive, False, False, Nothing, Nothing)
            Else
                obook.SaveAs(FileName)
            End If
            'end application object and session
            obook.Close()
            obook = Nothing
            oexcel.Quit()
            oexcel = Nothing
        Catch ex As Exception
            Throw New System.Exception(ex.Message)
        Finally
        End Try
    End Sub