Yes, no problem. I'm not sure how much experience you
have with the Excel object library, but first, create a
reference to it and then you can do pretty much what you
want. Incidentally, I've 'heard' that Excel XP can import
XML, which you can create using DataSet.WriteXML
("SomeFile.xml")...However, I haven't done it personally .
Here's a class I wrote a long time ago that writes out a
query to a worksheet. All you need to do is take the data
from the second DataTable, Select the next sheet in the
workbook and then fill it.
This isn't my proudest work, I was in a hurry, but I think
it'll give you an idea how to get it done. If you have
any questions, let me know.
Bill
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Math
Imports System.Web.Mail
Imports Excel.ApplicationClass
Imports Excel.Constants
Imports Excel.XlLineStyle
Public Class Logs
Public Attachment As String
Private cn As New SqlConnection() 'Connection to be Set
Internally
Private Todays As Date = Today
Private Yesterday As Date = Today.AddDays(-1)
Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet As Excel.Worksheet
Private xlRange As Excel.Range
Private xlSecondRange As Excel.Range
Private Email As New MailMessage()
Private FacilityQuery As String
Private FacilityID As Integer
Public FacilityInits As String
Public Facility_Name As String
Private bOrientation As Boolean
Public Sub New(ByRef cnn As SqlConnection)
cn = cnn
End Sub
'The First Step after Instantiating the object
'We simply want to Create a New Excel Object and
Preformat
'it.
Public Sub CreateAndFormat(ByVal FacId As String)
Try
xlApp = New Excel.Application()
xlApp.Visible = True
Call WorkPrep(xlApp)
With xlApp
.ActiveCell.Value = ""
.ActiveWorkbook.ActiveSheet.Range("A2").Select
()
End With
xlSheet = xlApp.ActiveWorkbook.ActiveSheet
With xlSheet.PageSetup
If FacId = "8" Then
.Orientation =
Excel.XlPageOrientation.xlPortrait
Else
.Orientation =
Excel.XlPageOrientation.xlLandscape
End If
.LeftHeader = " For " & CStr(Todays)
.LeftFooter = "&BInfoPro Confidential&B"
.CenterFooter = "Page &P"
End With
'If FacilityQuery contains no records, an Excel
sheet will be sent
'with - No Jobs Dictated on - the date.
xlRange = xlSheet.Rows(1)
xlRange.Font.Bold = True 'Formats first row to
bold
xlRange.Interior.ColorIndex = 15 ' Changes
background color of Row 1
'Aligns cells to the left and autofit columns
xlRange = xlSheet.Columns
xlRange("A:M").Select()
With xlRange
.HorizontalAlignment = xlLeft
.AutoFit()
End With
xlRange.Cells.Borders.LineStyle = xlContinuous
xlApp.ActiveCell.Range("A1").Select()
Catch ExcError As Exception
' MessageBox.Show(ExcError.ToString, "Problem
creating Excel.")
Exit Sub
End Try
GetLogs(FacId)
End Sub
'This Adds the needed workbook for the class and sets
'Visually Disables it. The DisplayAlerts = False frees
'us from Unwanted dialog boxes (since we are using
automation
'this is important. It also deletes the extraneous
sheets
'that Excel creates by default.
Private Sub WorkPrep(ByRef xApp As Excel.Application)
Try
With xApp
.Workbooks.Add()
'.Visible = False
.DisplayAlerts = False
.Worksheets("Sheet3").Delete()
.Worksheets("Sheet2").Delete()
.Worksheets("Sheet1").Select()
End With
Catch exc As System.Exception
' MessageBox.Show(exc.ToString, "Error preparing
Excel Application")
End Try
End Sub
Friend Function CreateFormat()
Try
xlApp = New Excel.Application()
xlApp.Visible = True
Call WorkPrep(xlApp)
With xlApp
.ActiveCell.Value = ""
.ActiveWorkbook.ActiveSheet.Range("A2").Select
()
End With
xlSheet = xlApp.ActiveWorkbook.ActiveSheet
With xlSheet.PageSetup
.Orientation =
Excel.XlPageOrientation.xlLandscape
.LeftHeader = " For " & CStr(Todays)
.LeftFooter = "&BInfoPro Confidential&B"
.CenterFooter = "Page &P"
End With
'If FacilityQuery contains no records, an Excel
sheet will be sent
'with - No Jobs Dictated on - the date.
xlRange = xlSheet.Rows(1)
xlRange.Font.Bold = True 'Formats first row to
bold
xlRange.Interior.ColorIndex = 15 ' Changes
background color of Row 1
'Aligns cells to the left and autofit columns
xlRange = xlSheet.Columns
xlRange("A:M").Select()
With xlRange
.HorizontalAlignment = xlLeft
.AutoFit()
End With
xlRange.Cells.Borders.LineStyle = xlContinuous
xlApp.ActiveCell.Range("A1").Select()
Catch ExcError As Exception
' MessageBox.Show(ExcError.ToString, "Problem
creating Excel.")
Exit Function
End Try
End Function
Friend Function GetLog(ByVal Sql As String)
Dim dr As SqlDataReader
Dim schemaTable As DataTable
Dim glcmd As New SqlCommand()
Dim VaDeptNo As Integer
Dim y As Integer, x As Integer
glcmd.Connection = Me.cn
glcmd.CommandText = Sql
Try
If cn.State <> ConnectionState.Open Then cn.Open()
Catch sq As SqlException
'MessageBox.Show(sq.ToString, "Error Initializing
Connection")
End Try
Try
dr = glcmd.ExecuteReader()
'This will give us the column headings
'based on the query
schemaTable = dr.GetSchemaTable()
y = schemaTable.Rows.Count()
For x = 0 To y - 1
With xlApp.ActiveCell
.Value = dr.GetName(x)
.Offset(0, 1).Select()
End With
Next
'Reset the current cell to A2
With xlApp
.ActiveCell.Value = ""
.ActiveWorkbook.ActiveSheet.Range("A2").Select
()
End With
'Populate the Excel Sheet with the corresponding
data
'returned by Stored Procedure
While (dr.Read())
'If reader is null, insert No Records message
'and exit.
'
For x = 0 To y - 1
With xlApp.ActiveCell
.Value = dr(x)
.Offset(0, 1).Select()
End With
Next
x = 0
xlApp.ActiveCell.Offset(1, (y * -1)).Select()
End While
dr.Close()
xlRange("A:M").Select()
With xlRange
.HorizontalAlignment = xlLeft
.AutoFit()
End With
Call SaveAndFinish(xlApp)
Catch sqlErr As SqlException
' MessageBox.Show(sqlErr.ToString, "Error
Retrieving Data")
Catch SysErr As System.Exception
' MessageBox.Show(SysErr.ToString, "Error
Creating Excel")
End Try
End Function
Friend Function GetLogs(ByVal Fac As String)
Dim dr As SqlDataReader
Dim schemaTable As DataTable
Dim glcmd As New SqlCommand()
Dim sql As String = "Select SQLSTATEMENT from
Tbl_Dictation_Logs Where FacilityID = '" & CInt(Fac) & "'"
Dim VaDeptNo As Integer
Dim y As Integer, x As Integer
Try
glcmd.Connection = Me.cn
FacilityID = CInt(Fac)
glcmd.CommandText = sql
If Me.cn.State <> ConnectionState.Open Then
Me.cn.Open()
sql = glcmd.ExecuteScalar
FacilityQuery = sql
glcmd.CommandText = "Select FacilityInitials from
Tbl_Dictation_Logs Where FacilityID = '" & CInt(Fac) & "'"
sql = glcmd.ExecuteScalar
FacilityInits = sql
glcmd.CommandText = "Select Facility from
Tbl_Dictation_Logs Where FacilityID = '" & CInt(Fac) & "'"
sql = glcmd.ExecuteScalar
Facility_Name = sql
Catch sqlErr As SqlException
MessageBox.Show(sqlErr.ToString, "Query Error")
Catch sysErr As Exception
MessageBox.Show(sysErr.ToString, "System Error in
Procedure GetLogs")
End Try
VaDeptNo = GetDept(CInt(FacilityID))
glcmd.CommandType = CommandType.StoredProcedure
'This should Return name of Stored Procedure that
will be run
glcmd.CommandText = FacilityQuery
Try
If cn.State <> ConnectionState.Open Then cn.Open()
Catch sq As SqlException
' MessageBox.Show(sq.ToString, "Error
Initializing Connection")
End Try
Try
If Facility_Name <> "e2000" And Facility_Name
<> "e20001" _
And Facility_Name <> "e20002" And
Facility_Name <> "e20003" _
And Facility_Name <> "e20004" And
Facility_Name <> "e20005" _
And Facility_Name <> "e20006" Then
With glcmd.Parameters
.Clear()
.Add("@Department", CInt(VaDeptNo))
.Add("@Author_Date", Todays)
End With
dr = glcmd.ExecuteReader()
Else
glcmd.Parameters.Clear()
dr = glcmd.ExecuteReader()
End If
'This will give us the column headings
'based on the query
schemaTable = dr.GetSchemaTable()
y = schemaTable.Rows.Count()
For x = 0 To y - 1
With xlApp.ActiveCell
.Value = dr.GetName(x)
.Offset(0, 1).Select()
End With
Next
'Reset the current cell to A2
With xlApp
.ActiveCell.Value = ""
.ActiveWorkbook.ActiveSheet.Range("A2").Select
()
End With
'Populate the Excel Sheet with the corresponding
data
'returned by Stored Procedure
While (dr.Read())
'If reader is null, insert No Records message
'and exit.
'
For x = 0 To y - 1
With xlApp.ActiveCell
.Value = dr(x)
.Offset(0, 1).Select()
End With
Next
x = 0
xlApp.ActiveCell.Offset(1, (y * -1)).Select()
End While
dr.Close()
xlRange("A:M").Select()
With xlRange
.HorizontalAlignment = xlLeft
.AutoFit()
End With
If Fac = "12" Or FacilityID = 12 Then
xlApp.Range("I:I").Select()
xlApp.Selection.NumberFormat = "[h]:mm:ss"
End If
Catch sqlErr As SqlException
' MessageBox.Show(sqlErr.ToString, "Error
Retrieving Data")
Catch SysErr As System.Exception
' MessageBox.Show(SysErr.ToString, "Error
Creating Excel")
End Try
Call SaveAndFinish(xlApp)
End Function
Private Sub SaveAndFinish(ByRef xlAp As
Excel.Application)
Dim SaveString As New System.Text.StringBuilder(75)
Dim bExists As Boolean
Dim direc As Directory
Try
xlApp.ActiveSheet.Select()
xlApp.ActiveSheet.Name = CStr(Facility_Name)
SaveString.Append("P:\DailyReports\" &
FacilityInits)
bExists = direc.Exists(SaveString.ToString)
xlApp.ScreenUpdating = True
If (bExists) Then
If FacilityID = 14 Or FacilityID = 23 Then
Dim d As Date
d = DateAdd(DateInterval.Day, -1, Todays)
SaveString.Append("\" & d.ToShortDateString
& ".xls")
SaveString.Replace("/", "_")
Else
SaveString.Append("\" & CStr(Todays)
& ".xls")
SaveString.Replace("/", "_")
End If
Else
direc.CreateDirectory(SaveString.ToString)
SaveString.Append("\" & CStr(Todays) & ".xls")
SaveString.Replace("/", "_")
End If
Try
xlApp.ActiveWorkbook.SaveAs
(Filename:=SaveString.ToString)
Attachment = SaveString.ToString
Catch ioe As System.IO.PathTooLongException
' MessageBox.Show(ioe.ToString, "File
Extension Too Long")
End Try
Catch exc As System.Exception
' MessageBox.Show(exc.ToString, "Error Saving
Excel")
Finally
xlApp.ActiveWindow.Close()
xlApp.Quit()
xlApp = Nothing
End Try
End Sub
Function GetDept(ByVal ID As Integer) As Integer
Dim sql1 As String = "Select Department from
Tbl_Dictation_Logs Where FacilityID = '" & ID & "'"
Dim cmd As New SqlCommand()
Dim Dep As Integer
Dim SqCn As New SqlConnection("Server=Aug-
SQLSRV;Database=JobTracking;integrated
security=SSPI;packet size=4096")
Try
SqCn.Open()
cmd.Connection = SqCn
cmd.CommandText = sql1
'Returns Department Number
Dep = cmd.ExecuteScalar
Catch sqError As SqlException
' MessageBox.Show(sqError.ToString, "Sql
Exception")
Catch sysErr As SystemException
'MessageBox.Show(sysErr.ToString, "System Error")
Finally
SqCn.Close()
End Try
Return Dep
End Function
Function CloseExcel() As Boolean
'Closes Microsoft Excel and clears the variables
Try
xlApp.Quit()
xlApp = Nothing
xlBook = Nothing
xlSheet = Nothing
xlRange = Nothing
xlSecondRange = Nothing
Return True
Catch
Return False
End Try
End Function
Function IsLandscape(ByVal ID As Integer) As Boolean
Dim sql1 As String = "Select Facility from
Tbl_Dictation_Logs Where FacilityID = '" & ID & "'"
Dim cmd As New SqlCommand()
Dim FacilityName As String
Dim bIsLandscape As Boolean
Dim SqCn As New SqlConnection("Server=Aug-
SQLSRV;Database=JobTracking;integrated
security=SSPI;packet size=4096")
Try
SqCn.Open()
cmd.Connection = SqCn
cmd.CommandText = sql1
'Returns Department Number
FacilityName = cmd.ExecuteScalar
'Added 03/19/03 per Phyllis Prestegord because
she needs to be able to
'perform date arithmatic on the Job_Length
field. I suspect anyone would want
'this feature and may go back and add it for
other facilities. Not all facilities end
'in the "I" column, so this should be done at a
later date.
'WGR
If FacilityName = "Fargo" Then
xlApp.Range("I:I").Select()
xlApp.Selection.NumberFormat = "[h]:mm:ss"
End If
'May need to Add to this in the future if other
facilities
'Want landscape. This is a lame implementation,
but since
'we only have one facility that wants landscape,
it'll work
If FacilityName = "Chilicothe" Then
IsLandscape = False
bOrientation = False
Else
IsLandscape = True
bOrientation = True
End If
Catch sqError As SqlException
' MessageBox.Show(sqError.ToString, "Sql
Exception")
Catch sysErr As SystemException
' MessageBox.Show(sysErr.ToString, "System Error")
Finally
SqCn.Close()
End Try
Return IsLandscape
End Function
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
