ASP.NET - Clase para Exportar DataSet a Excel

Si teneis necesidad de exportar un DataSet a Excel, se puede utilizar una clase.

Esta clase, pasa cualquier DataSet a Excel, dando formato incluso a las columnas.
Se pasan parámetros(Título, Nombre Excel, Nombre hoja y Dataset)



Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Drawing

Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports System.Data
Imports config = System.Configuration
Imports ficheros = System.IO

Namespace Exportar_a_Excel
Public Class ExportToExcel

Public Sub New()
End Sub

Public Shared Sub Export(ByVal Titol As String, ByVal ExcelName As String, ByVal sheets As String, ByVal DS As DataSet)

' Prevenir conflicto de idiomas. Si no se pone genera :
' Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

Try

Dim _excel As New Application()
Dim _wBook As Workbook = _excel.Workbooks.Add(Missing.Value)

Dim idx As Integer = 0
While idx < DS.Tables.Count

Dim _sheet As Worksheet = DirectCast(_wBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value), Worksheet)
_sheet.Name = sheets

'Insertamos el logo —si tenemos— en A1
Dim imagen = _sheet.Pictures.Insert(_UrlLogo) '_UrlLogo = Dirección URL Logo de la empresa
Dim cell = _sheet.Cells(1, 1)

'Centro en ancho
Dim ancho As Double = cell.Offset(0, 1).Left - cell.Left
imagen.Left = cell.Left + ancho / 2 - imagen.Width / 2
If imagen.Left < 1 Then imagen.Left = 1
'Centro en alto
Dim alto As Double = cell.Offset(1, 0).Top - cell.Top
imagen.Top = cell.Top + alto / 2 - imagen.Height / 2
If imagen.Top < 1 Then imagen.Top = 1


'Suponiendo que el logo ocupe cinco filas..., montamos el título del informe en la linea 6 y damos formato
Dim r As Integer = 6
_sheet.Cells(r, 1) = Titol.ToString
Dim rng As Range = DirectCast(_sheet.Cells(r, 1), Range)
rng.EntireRow.Font.Bold = True
rng.EntireRow.Font.Size = 20
rng.EntireRow.Interior.ColorIndex = 40
rng.EntireRow.Font.ColorIndex = 30

'Dos lineas más y montamos las cabeceras de las columnas y les damos formato
r += 2
Dim k = 0
While k < DS.Tables(idx).Columns.Count
_sheet.Cells(r, k + 1) = DS.Tables(idx).Columns(k).ColumnName.ToString()
System.Math.Max(System.Threading.Interlocked.Increment(k), k - 1)
End While
rng = DirectCast(_sheet.Cells(r, DS.Tables(idx).Columns.Count), Range)
rng.EntireRow.Font.Bold = True
rng.EntireRow.Interior.ColorIndex = 30
rng.EntireRow.Font.ColorIndex = 40

'Y a partir de ahí, montamos todos los datos del DataSet
r = 0
While r < DS.Tables(idx).Rows.Count
k = 0
While k < DS.Tables(idx).Columns.Count
_sheet.Cells(r + 9, k + 1) = DS.Tables(idx).Rows(r).ItemArray(k)
System.Math.Max(System.Threading.Interlocked.Increment(k), k - 1)
End While
System.Math.Max(System.Threading.Interlocked.Increment(r), r - 1)

End While
System.Math.Max(System.Threading.Interlocked.Increment(idx), idx - 1)
End While

If ficheros.File.Exists(ExcelName) Then
ficheros.File.Delete(ExcelName)
End If

'Salimos del Excel
_excel.ActiveCell.Worksheet.SaveAs(ExcelName, XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
Missing.Value, Missing.Value, Missing.Value, Missing.Value)
_excel.Quit()

' Mostrar el excel
_excel.Visible = False

'Matamos el proceso
deleteProcess()
Catch ex As Exception
Dim ss As String = ex.Message
End Try

End Sub

Private Shared Sub deleteProcess()

Dim miproceso As System.Diagnostics.Process() = System.Diagnostics.Process.GetProcessesByName("EXCEL")

For Each pc As System.Diagnostics.Process In miproceso
pc.Kill()
Next

End Sub
End Class
End Namespace