Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dataSet1 As New DataSet
dataSet1
Dim dataSet2 As New DataSet
dataSet2
Dim dataSet3 As New DataSet
dataSet3
Dim dataSets As New List(Of DataSet)()
dataSets.Add(dataSet1)
dataSets.Add(dataSet2)
dataSets.Add(dataSet3)
Dim strout As String = Server.MapPath("test1.xls")
DataSetsToExcel(dataSets, strout)
End If
End Sub
______________________________________________________________________
Public Sub DataSetsToExcel(ByVal dataSets As List(Of DataSet), ByVal fileName As String)
Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
Dim xlWorkbook As Workbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
Dim xlSheets As Sheets = Nothing
Dim xlWorksheet As Worksheet = Nothing
For Each dataSet As DataSet In dataSets
Dim dataTable As System.Data.DataTable = dataSet.Tables(0)
Dim rowNo As Integer = dataTable.Rows.Count
Dim columnNo As Integer = dataTable.Columns.Count
Dim colIndex As Integer = 0
'Create Excel Sheets
xlSheets = xlWorkbook.Sheets
xlWorksheet = DirectCast(xlSheets.Add(xlSheets(1), Type.Missing, Type.Missing, Type.Missing), Worksheet)
'xlWorksheet.Name = dataSet.DataSetName
'Generate Field Names
For Each dataColumn As DataColumn In dataTable.Columns
colIndex += 1
xlApp.Cells(1, colIndex) = dataColumn.ColumnName
Next
Dim objData As Object(,) = New Object(rowNo - 1, columnNo - 1) {}
'Convert DataSet to Cell Data
For row As Integer = 0 To rowNo - 1
For col As Integer = 0 To columnNo - 1
objData(row, col) = dataTable.Rows(row)(col)
Next
Next
'Add the Data
Dim range As Range = xlWorksheet.Range(xlApp.Cells(2, 1), xlApp.Cells(rowNo + 1, columnNo))
range.Value2 = objData
'Format Data Type of Columns
colIndex = 0
For Each dataColumn As DataColumn In dataTable.Columns
colIndex += 1
Dim format As String = "@"
Select Case dataColumn.DataType.Name
Case "Boolean"
Exit Select
Case "Byte"
Exit Select
Case "Char"
Exit Select
Case "DateTime"
format = "dd/mm/yyyy"
Exit Select
Case "Decimal"
format = "$* #,##0.00;[Red]-$* #,##0.00"
Exit Select
Case "Double"
Exit Select
Case "Int16"
format = "0"
Exit Select
Case "Int32"
format = "0"
Exit Select
Case "Int64"
format = "0"
Exit Select
Case "SByte"
Exit Select
Case "Single"
Exit Select
Case "TimeSpan"
Exit Select
Case "UInt16"
Exit Select
Case "UInt32"
Exit Select
Case "UInt64"
Exit Select
Case Else
'String
Exit Select
End Select
'Format the Column accodring to Data Type
xlWorksheet.Range(xlApp.Cells(2, colIndex), xlApp.Cells(rowNo + 1, colIndex)).NumberFormat = format
Next
Next
'Remove the Default Worksheet
DirectCast(xlApp.ActiveWorkbook.Sheets(xlApp.ActiveWorkbook.Sheets.Count), Worksheet).Delete()
'Save
xlWorkbook.SaveAs(fileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
xlWorkbook.Close()
xlApp.Quit()
GC.Collect()
End Sub
***************************************************************************
How to create each excel sheet non-editable (read only)