Export to Excel With Multiple sheets using VB.NET
Export to Excel With Multiple sheets using VB.NET
Private Sub btnexport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnexport.Click
Try
Dim dt As New DataTable()
Dim dtLane As New DataTable
Dim dtAsset As New DataTable
Dim filterdate1 As DateTime
Dim filterdate2 As DateTime
Dim Container_id As Int64
Dim facility_lane_idalerts As Int64
facility_lane_idalerts = -1
Container_id = -1
If txtAlertDate.Text <> "" Then
filterdate1 = Convert.ToDateTime(txtAlertDate.Text)
filterdate2 = filterdate1.AddDays(1)
End If
If ddlAlertContainer.SelectedValue <> "" Then
Container_id = ddlAlertContainer.SelectedValue
End If
If ddlAlertContainer.SelectedValue = 0 Then
Container_id = -1
End If
If ddlAlertLane.SelectedValue <> 0 Then
facility_lane_idalerts = ddlAlertLane.SelectedValue
End If
If txtAlertDate.Text = "" And Container_id = -1 And facility_lane_idalerts = -1 Then
Dim qry As String = "SELECT OrphanRecord_ID" &
",a.Created_dt" &
",TagNumber" &
",b.door_name" &
" FROM OrphansForLaneEntry a " &
" inner join dbo.facility_lane b on a.TReadIP=b.IPReader " &
" WHERE a.Created_dt>DATEADD(DAY,-1,getdate()) and Reason='NO ASSET' and b.facility_ID=" &
hdnFacilityID.Value.ToString()
dtAsset = DataConnect.GetInstance.GetDt(qry)
qry = "SELECT OrphanRecord_ID" &
",a.Created_dt" &
",TagNumber" &
",b.door_name,con.container_nbr" &
" FROM OrphansForLaneEntry a " &
" inner join dbo.facility_lane b on a.TReadIP=b.IPReader " &
" left join dbo.container con on a.container_id=con.container_id " &
" WHERE a.Created_dt>DATEADD(DAY,-1,getdate()) and Reason='NO LANE ASSIGNMENT' and b.facility_ID=" &
hdnFacilityID.Value.ToString()
dtLane = DataConnect.GetInstance.GetDt(qry)
End If
'Create Tempory Table
Dim dtTemp As New DataTable()
Dim dtTemp1 As New DataTable()
'Creating Header Row
dtTemp.Columns.Add("Created")
dtTemp.Columns.Add("TagNumber")
dtTemp.Columns.Add("Door")
dtTemp1.Columns.Add("Created")
dtTemp1.Columns.Add("TagNumber")
dtTemp1.Columns.Add("Door")
dtTemp1.Columns.Add("Container")
Dim dtDate As New DateTime
Dim dtDate1 As New DateTime
Dim drAddItem As DataRow
For i As Integer = 0 To dtAsset.Rows.Count - 1
drAddItem = dtTemp.NewRow()
dtDate = Convert.ToDateTime(dtAsset.Rows(i)("Created_dt").ToString())
drAddItem(0) = dtDate
drAddItem(1) = dtAsset.Rows(i)("TagNumber").ToString()
drAddItem(2) = dtAsset.Rows(i)("door_name").ToString()
dtTemp.Rows.Add(drAddItem)
Next
Dim drAddItem1 As DataRow
For i As Integer = 0 To dtLane.Rows.Count - 1
drAddItem1 = dtTemp1.NewRow()
dtDate1 = Convert.ToDateTime(dtLane.Rows(i)("Created_dt").ToString())
drAddItem1(0) = dtDate1
drAddItem1(1) = dtLane.Rows(i)("TagNumber").ToString()
drAddItem1(2) = dtLane.Rows(i)("door_name").ToString()
drAddItem1(3) = dtLane.Rows(i)("container_nbr").ToString()
dtTemp1.Rows.Add(drAddItem1)
Next
'Dim ds As New DataSet
'ds.Tables.Add(dtTemp)
'ds.Tables.Add(dtTemp1)
'Dim dg As New DataGrid
'dg.DataSource = ds
'dg.DataBind()
'ExportToExcel("Alerts.xls", dg)
Dim rescDS As DataSet = New DataSet
Dim studiesDS As DataSet = New DataSet
' Get data
rescDS.Tables.Add(dtTemp)
studiesDS.Tables.Add(dtTemp1)
' Create Excel Application, Workbook, and WorkSheets
Dim xlExcel As New Excel.Application
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim xlSheets As Excel.Sheets
Dim stdSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As String
Dim sTemplate As String
Dim rescSheet As Excel.Worksheet
sFile = Server.MapPath("ExcelFormatFile\LaneAssignmentAlerts.xls")
' Formatted template the way you want.
' If you want to change the format, change this template
sTemplate = Server.MapPath("\ExcelFormatFile\LaneAssignment.xls")
xlExcel.Visible = False : xlExcel.DisplayAlerts = False
' Get all workbooks and open first workbook
xlBooks = xlExcel.Workbooks
xlBooks.Open(Server.MapPath("\ExcelFormatFile\LaneAssignment.xls"))
xlBook = xlBooks.Item(1)
' Get all sheets available in first book
xlSheets = xlBook.Worksheets
' Get first sheet, change its name and get all cells
stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)
stdSheet.Name = "Unidentified Lane Assignment"
xlCells = stdSheet.Cells
' Fill all cells with data
GenerateExcelFile(studiesDS.Tables(0), xlCells) 'Fill in the data
' Get second sheet, change its name and get all cells
rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)
rescSheet.Name = "Unidentified Asset Movement"
xlCells = rescSheet.Cells
' Fill all cells with data
GenerateExcelFile(rescDS.Tables(0), xlCells)
' Save created sheets as a file
xlBook.SaveAs(sFile)
' Make sure all objects are disposed
xlBook.Close()
xlExcel.Quit()
ReleaseComObject(xlCells)
ReleaseComObject(stdSheet)
ReleaseComObject(xlSheets)
ReleaseComObject(xlBook)
ReleaseComObject(xlBooks)
ReleaseComObject(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
xlSheets = Nothing
stdSheet = Nothing
xlCells = Nothing
rescSheet = Nothing
' Let GC know about it
GC.Collect()
' Export Excel for download
Dim File As FileInfo = New FileInfo(sFile)
Response.Clear()
Response.Charset = "UTF-8"
Response.ContentEncoding = System.Text.Encoding.UTF8
'Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(File.Name))
'Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", File.Length.ToString())
'Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-excel"
'Send file string to client
Response.TransmitFile(File.FullName)
Response.End()
Catch ex As Exception
pnlGlobalMessage.Visible = True
lblGlobalMessage.Text = "A technical issue has occurred. A message has been sent to the development team. Sorry for the inconvenience."
lblGlobalMessage.Visible = True
Dim dt As DataTable = Session("user_session")
Worker.HandleError(ex, "Lane History Excel", Int64.Parse(dt.Rows(0)("user_id")), Request.Browser.Browser.ToString())
End Try
End Sub
' Generates Excel sheet for the given DataTable's data
Private Function GenerateExcelFile(ByRef table As DataTable, ByVal xlCells As Excel.Range) As String
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To table.Columns.Count - 1
xlCells(1, iCol + 1) = table.Columns(iCol).ToString
Next
'Output Data
For iRow = 0 To table.Rows.Count - 1
dr = table.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function
Private Sub btnexport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnexport.Click
Try
Dim dt As New DataTable()
Dim dtLane As New DataTable
Dim dtAsset As New DataTable
Dim filterdate1 As DateTime
Dim filterdate2 As DateTime
Dim Container_id As Int64
Dim facility_lane_idalerts As Int64
facility_lane_idalerts = -1
Container_id = -1
If txtAlertDate.Text <> "" Then
filterdate1 = Convert.ToDateTime(txtAlertDate.Text)
filterdate2 = filterdate1.AddDays(1)
End If
If ddlAlertContainer.SelectedValue <> "" Then
Container_id = ddlAlertContainer.SelectedValue
End If
If ddlAlertContainer.SelectedValue = 0 Then
Container_id = -1
End If
If ddlAlertLane.SelectedValue <> 0 Then
facility_lane_idalerts = ddlAlertLane.SelectedValue
End If
If txtAlertDate.Text = "" And Container_id = -1 And facility_lane_idalerts = -1 Then
Dim qry As String = "SELECT OrphanRecord_ID" &
",a.Created_dt" &
",TagNumber" &
",b.door_name" &
" FROM OrphansForLaneEntry a " &
" inner join dbo.facility_lane b on a.TReadIP=b.IPReader " &
" WHERE a.Created_dt>DATEADD(DAY,-1,getdate()) and Reason='NO ASSET' and b.facility_ID=" &
hdnFacilityID.Value.ToString()
dtAsset = DataConnect.GetInstance.GetDt(qry)
qry = "SELECT OrphanRecord_ID" &
",a.Created_dt" &
",TagNumber" &
",b.door_name,con.container_nbr" &
" FROM OrphansForLaneEntry a " &
" inner join dbo.facility_lane b on a.TReadIP=b.IPReader " &
" left join dbo.container con on a.container_id=con.container_id " &
" WHERE a.Created_dt>DATEADD(DAY,-1,getdate()) and Reason='NO LANE ASSIGNMENT' and b.facility_ID=" &
hdnFacilityID.Value.ToString()
dtLane = DataConnect.GetInstance.GetDt(qry)
End If
'Create Tempory Table
Dim dtTemp As New DataTable()
Dim dtTemp1 As New DataTable()
'Creating Header Row
dtTemp.Columns.Add("Created")
dtTemp.Columns.Add("TagNumber")
dtTemp.Columns.Add("Door")
dtTemp1.Columns.Add("Created")
dtTemp1.Columns.Add("TagNumber")
dtTemp1.Columns.Add("Door")
dtTemp1.Columns.Add("Container")
Dim dtDate As New DateTime
Dim dtDate1 As New DateTime
Dim drAddItem As DataRow
For i As Integer = 0 To dtAsset.Rows.Count - 1
drAddItem = dtTemp.NewRow()
dtDate = Convert.ToDateTime(dtAsset.Rows(i)("Created_dt").ToString())
drAddItem(0) = dtDate
drAddItem(1) = dtAsset.Rows(i)("TagNumber").ToString()
drAddItem(2) = dtAsset.Rows(i)("door_name").ToString()
dtTemp.Rows.Add(drAddItem)
Next
Dim drAddItem1 As DataRow
For i As Integer = 0 To dtLane.Rows.Count - 1
drAddItem1 = dtTemp1.NewRow()
dtDate1 = Convert.ToDateTime(dtLane.Rows(i)("Created_dt").ToString())
drAddItem1(0) = dtDate1
drAddItem1(1) = dtLane.Rows(i)("TagNumber").ToString()
drAddItem1(2) = dtLane.Rows(i)("door_name").ToString()
drAddItem1(3) = dtLane.Rows(i)("container_nbr").ToString()
dtTemp1.Rows.Add(drAddItem1)
Next
'Dim ds As New DataSet
'ds.Tables.Add(dtTemp)
'ds.Tables.Add(dtTemp1)
'Dim dg As New DataGrid
'dg.DataSource = ds
'dg.DataBind()
'ExportToExcel("Alerts.xls", dg)
Dim rescDS As DataSet = New DataSet
Dim studiesDS As DataSet = New DataSet
' Get data
rescDS.Tables.Add(dtTemp)
studiesDS.Tables.Add(dtTemp1)
' Create Excel Application, Workbook, and WorkSheets
Dim xlExcel As New Excel.Application
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim xlSheets As Excel.Sheets
Dim stdSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As String
Dim sTemplate As String
Dim rescSheet As Excel.Worksheet
sFile = Server.MapPath("ExcelFormatFile\LaneAssignmentAlerts.xls")
' Formatted template the way you want.
' If you want to change the format, change this template
sTemplate = Server.MapPath("\ExcelFormatFile\LaneAssignment.xls")
xlExcel.Visible = False : xlExcel.DisplayAlerts = False
' Get all workbooks and open first workbook
xlBooks = xlExcel.Workbooks
xlBooks.Open(Server.MapPath("\ExcelFormatFile\LaneAssignment.xls"))
xlBook = xlBooks.Item(1)
' Get all sheets available in first book
xlSheets = xlBook.Worksheets
' Get first sheet, change its name and get all cells
stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)
stdSheet.Name = "Unidentified Lane Assignment"
xlCells = stdSheet.Cells
' Fill all cells with data
GenerateExcelFile(studiesDS.Tables(0), xlCells) 'Fill in the data
' Get second sheet, change its name and get all cells
rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)
rescSheet.Name = "Unidentified Asset Movement"
xlCells = rescSheet.Cells
' Fill all cells with data
GenerateExcelFile(rescDS.Tables(0), xlCells)
' Save created sheets as a file
xlBook.SaveAs(sFile)
' Make sure all objects are disposed
xlBook.Close()
xlExcel.Quit()
ReleaseComObject(xlCells)
ReleaseComObject(stdSheet)
ReleaseComObject(xlSheets)
ReleaseComObject(xlBook)
ReleaseComObject(xlBooks)
ReleaseComObject(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
xlSheets = Nothing
stdSheet = Nothing
xlCells = Nothing
rescSheet = Nothing
' Let GC know about it
GC.Collect()
' Export Excel for download
Dim File As FileInfo = New FileInfo(sFile)
Response.Clear()
Response.Charset = "UTF-8"
Response.ContentEncoding = System.Text.Encoding.UTF8
'Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(File.Name))
'Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", File.Length.ToString())
'Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-excel"
'Send file string to client
Response.TransmitFile(File.FullName)
Response.End()
Catch ex As Exception
pnlGlobalMessage.Visible = True
lblGlobalMessage.Text = "A technical issue has occurred. A message has been sent to the development team. Sorry for the inconvenience."
lblGlobalMessage.Visible = True
Dim dt As DataTable = Session("user_session")
Worker.HandleError(ex, "Lane History Excel", Int64.Parse(dt.Rows(0)("user_id")), Request.Browser.Browser.ToString())
End Try
End Sub
' Generates Excel sheet for the given DataTable's data
Private Function GenerateExcelFile(ByRef table As DataTable, ByVal xlCells As Excel.Range) As String
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To table.Columns.Count - 1
xlCells(1, iCol + 1) = table.Columns(iCol).ToString
Next
'Output Data
For iRow = 0 To table.Rows.Count - 1
dr = table.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function
Category:
