Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("content-disposition", "attachment; filename=Dashboard.xls")
Response.Charset = ""
Response.Write(DataTable2ExcelString(dtInternalsTemp, dtSuppliersTemp, dtCustomersTemp, "Dashboard"))
Response.End()
Response.ContentType = "application/vnd.ms-excel"
Private Function DataTable2ExcelString(ByVal dt1 As DataTable, ByVal dt2 As DataTable, ByVal dt3 As DataTable, ByVal strfrom As String) As String
Dim sbTop As New Text.StringBuilder()
sbTop.AppendLine("<?xml version=""1.0""?>")
sbTop.AppendLine("<?mso-application progid=""Excel.Sheet""?>")
sbTop.AppendLine("<Workbook")
sbTop.AppendLine(" xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
sbTop.AppendLine(" xmlns:o=""urn:schemas-microsoft-com:office:office""")
sbTop.AppendLine(" xmlns:x=""urn:schemas-microsoft-com:office:excel""")
sbTop.AppendLine(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
sbTop.AppendLine(" xmlns:html=""http://www.w3.org/TR/REC-html40"">")
sbTop.AppendLine(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
sbTop.AppendLine(" <Author>Surgere</Author>")
sbTop.AppendLine(" <LastAuthor></LastAuthor>")
sbTop.AppendLine(" <Created></Created>")
sbTop.AppendLine(" <Company>Surgere</Company>")
sbTop.AppendLine(" <Version>11.8036</Version>")
sbTop.AppendLine(" </DocumentProperties>")
sbTop.AppendLine(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")
sbTop.AppendLine(" <WindowHeight>6795</WindowHeight>")
sbTop.AppendLine(" <WindowWidth>8460</WindowWidth>")
sbTop.AppendLine(" <WindowTopX>120</WindowTopX>")
sbTop.AppendLine(" <WindowTopY>15</WindowTopY>")
sbTop.AppendLine(" <ProtectStructure>False</ProtectStructure>")
sbTop.AppendLine(" <ProtectWindows>False</ProtectWindows>")
sbTop.AppendLine(" </ExcelWorkbook>")
sbTop.AppendLine(" <Styles>")
sbTop.AppendLine(" <Style ss:ID=""Default"" ss:Name=""Normal"">")
sbTop.AppendLine(" <Alignment ss:Vertical=""Bottom"" />")
sbTop.AppendLine(" <Borders />")
sbTop.AppendLine(" <Font />")
sbTop.AppendLine(" <Interior />")
sbTop.AppendLine(" <NumberFormat />")
sbTop.AppendLine(" <Protection />")
sbTop.AppendLine(" </Style>")
sbTop.AppendLine(" <Style ss:ID=""s21"">")
sbTop.AppendLine(" <Font x:Family=""Swiss"" ss:Bold=""1"" />")
sbTop.AppendLine(" </Style>")
sbTop.AppendLine(" </Styles>")
If strfrom = "Dashboard" Then
If Not dt1 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Internal"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
If Not dt2 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Supplier"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
'Dim i As Integer
For i = 0 To dt2.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt2.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
sType = String.Empty
For x = 0 To dt2.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt2.Columns.Count - 1
If dt2.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt2.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
If Not dt3 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Customer"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt3.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt3.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt3.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt3.Columns.Count - 1
If dt3.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt3.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
ElseIf strfrom = "DashboardAsset" Then
If Not dt1 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""AssetList"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
ElseIf strfrom = "LaneAlert" Then
If Not dt1 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Unidentified Asset Assignment"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
If Not dt2 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Unidentified Lane Movement"">")
sbTop.AppendLine(" <Table>")
sbTop.AppendLine("<Row>")
'Dim i As Integer
For i = 0 To dt2.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt2.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")
'Items
Dim x As Integer
Dim sType As String
sType = String.Empty
For x = 0 To dt2.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt2.Columns.Count - 1
If dt2.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt2.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, " ") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next
sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
End If
sbTop.AppendLine("</Workbook>")
Return sbTop.ToString()
End Function
