Here is the way to Export some data to Excel. Code written in VB.net using some help of VBA script . Here are the steps for doing that:-
Sub ExportToExcelDetail(ByVal R_ID As String)
Dim strSQLQuery As String = String.Empty
Dim dtTable As DataTable = New DataTable
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim strRowCount As String = ""
Dim strXValues As String = ""
Dim strValues As String = ""
Dim strPath As String
Dim iSheetCount As Integer = 1
Dim linkArray As String()
Dim ds As DataSet
Try
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(Server.MapPath("~\Temp\Blank.xlsx"))
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
'DELETING TWO EXTRA SHEETS CREATED DEFAULT
''BEGIN
Dim wsD As Excel.Worksheet
wsD = xlWorkBook.Sheets("Sheet2")
wsD.Delete()
wsD = xlWorkBook.Sheets("Sheet3")
wsD.Delete()
''END
ds = GetData(R_ID)
dtTable = ds.Tables(0)
ExcelDataForamtCustom(xlWorkSheet, dtTable, 3, 4)
''Exporting File to Screen
xlWorkSheet.Name = "Default OCS Report"
Dim strReport As String
strReport = "OCS - OS- Report(" + Format(Date.Now, "yyyyMMdd-HHmmss").ToString + ").xlsx"
strPath = Server.MapPath("Temp\") & strReport.ToString
excelpath = strPath
excelfilename = strReport
Dim filename As String = strReport.ToString
Dim file As System.IO.FileInfo = New System.IO.FileInfo(strPath)
If file.Exists Then
file.Delete()
End If
xlWorkSheet.SaveAs(strPath)
xlWorkBook.Close()
file = Nothing
file = New System.IO.FileInfo(excelpath)
If file.Exists Then
Response.ContentType = "application/octet-stream"
Response.AppendHeader("content-disposition", "attachment; filename=" + filename)
' Response.Flush()
Response.TransmitFile(file.FullName)
Response.End()
End If
Catch ex As Exception
Finally
'' ReleaseObject(xlApp)
'' ReleaseObject(xlWorkBook)
'' ReleaseObject(xlWorkSheet)
End Try
2) Then Add bellow function for getting data. function is calling a procedure and returning dataset. you can do it in your way:
Private Function GetData(ByVal Report_Type As String) As System.Data.DataSet
Dim strSql As [String]
Dim DBConnection As SqlConnection
Dim cmd As SqlCommand
Dim adapter As New SqlDataAdapter
Dim dsExport = New DataSet()
Dim strGroup As String
Dim strQueue As String
Try
DBConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("OCS").ConnectionString)
DBConnection.Open()
cmd = New SqlCommand
cmd.Connection = DBConnection
cmd.CommandType = CommandType.Text
strSql = "EXEC [dbo].[PROC_OCS_R0010] @R_ID = '" & Report_Type & "', @R_GroupName = '" & hdSelGroup.Value & "'"
'strSql = "EEXEC PROC_GHR_MIS0010 @P_USERID = 1, @P_FROMDATE = '2011/05/05' ,@P_TODATE = '2011/06/01', @P_REPOT_TYPE = 'ISSUE COMPLIANCES'"
cmd.CommandText = strSql
adapter.SelectCommand = cmd
adapter.Fill(dsExport, Report_Type)
adapter.Dispose()
Return dsExport
Catch ex As Exception
Throw New Exception(ex.Message.ToString)
Finally
adapter.Dispose()
cmd.Dispose()
If DBConnection.State <> ConnectionState.Closed Then
DBConnection.Close()
End If
End Try
End Function
3) Add bellow function to add custom formatting to the sheets:
Public Function ExcelDataForamtCustom(ByRef xlWSOS As Excel.Worksheet, ByVal dtOSList As DataTable, Optional ByVal itopRow As Integer = 1, Optional ByVal itopCol As Integer = 1) As String()
Dim iOsROWCount As Integer = dtOSList.Rows.Count
Dim iOsCOLCount As Integer = dtOSList.Columns.Count
Dim OSListArray(iOsROWCount, iOsCOLCount) As String
Dim intR As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim xlRange As Excel.Range
Dim intBorderWeight As Integer = Excel.XlBorderWeight.xlMedium
Dim strLinkArray(iOsROWCount) As String
xlWSOS.Cells.Interior.PatternColorIndex = 10
'For displaying the column name in the the excel file.
For intColumn = 0 To iOsCOLCount - 1
xlWSOS.Cells(itopRow, intColumn + itopCol).Value = dtOSList.Columns(intColumn).ColumnName.ToString
Next
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To iOsROWCount - 1
For intColumnValue = 0 To iOsCOLCount - 1
' xlWorkSheet.Cells(intRow + 3, intColumnValue + 2).Value = dtTable.Rows(intRow).ItemArray(intColumnValue).ToString
OSListArray(intRow, intColumnValue) = dtOSList.Rows(intRow).ItemArray(intColumnValue).ToString
Next
strLinkArray(intRow) = OSListArray(intRow, 0)
Next
xlRange = xlWSOS.Range(ExcelColName(itopCol) & itopRow + 1, ExcelColName(iOsCOLCount + itopCol - 1) & iOsROWCount + itopRow)
xlRange.Value = OSListArray
xlRange.Interior.Color = RGB(230, 160, 160)
'Format the HEADER ROW
xlRange = xlWSOS.Range(ExcelColName(itopCol) & itopRow, ExcelColName(iOsCOLCount + itopCol - 1) & itopRow)
With xlRange
.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = intBorderWeight
.Interior.Color = RGB(211, 1, 1)
.Font.Size = 12
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
'Building Data Row & Columnss
For intR = itopRow + 1 To intRow + itopRow
If intR Mod 2 = 0 Then
xlRange = xlWSOS.Range(ExcelColName(itopCol) & intR, ExcelColName(iOsCOLCount + itopCol - 1) & intR)
xlRange.Interior.Color = RGB(250, 235, 235)
End If
Next
'AutoFit all Data Columns
For intR = itopCol To intColumn + itopCol + 1
xlWSOS.Columns(ExcelColName(intR) & ":" & ExcelColName(intR)).AutoFit()
Next
'Format the Data Columns
xlRange = xlWSOS.Range(ExcelColName(itopCol) & itopRow, ExcelColName(iOsCOLCount + itopCol - 1) & iOsROWCount + itopRow)
With xlRange
.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = intBorderWeight
.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = intBorderWeight
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlHairline
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlHairline
End With
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
Return strLinkArray
End Function
techcreation 