Public Class ExcelHolder
Implements System.IDisposable
'the app and state members.
Protected xlApp As Microsoft.Office.Interop.Excel.Application
Protected ImOpen As Boolean
'A book
Protected xlBook As Microsoft.Office.Interop.Excel.Workbook
'A few sheet members.
Protected mSheetNo As Integer
Protected mNewBook As Boolean
Protected mBookName As String
Protected mSheetName As String
#Region "Constructors and Destructors"
Public Sub New()
'This does not open the app.
ImOpen = False
mSheetNo = 1
mNewBook = True
mBookName = ""
End Sub
Public Sub Dispose() Implements System.IDisposable.Dispose
If Not xlBook Is Nothing Then
Try
xlBook.Close()
Catch ex As Exception
'Do Nothing.
End Try
End If
If Not xlApp Is Nothing Then
Try
xlApp.Quit()
Catch ex As Exception
Windows.Forms.MessageBox.Show("Excel App didn't close. You should never see this error. It is particularly bad, because it doesn't appear to cause problems.", "Hidden Issue", MessageBoxButtons.OK, MessageBoxIcon.Hand)
End Try
End If
End Sub
#End Region
#Region "Properties"
Public Property NewBook() As Boolean
Get
NewBook = mNewBook
End Get
Set(ByVal Value As Boolean)
mNewBook = Value
If Value Then
If Not xlBook Is Nothing Then
xlBook.Save()
xlBook.Close()
xlBook = Nothing
End If
mBookName = ""
mSheetNo = 1
End If
End Set
End Property
#End Region
#Region "Public Functions"
'This function is used to save data to the current book. If there is no current book,
'then a new one is opened for it.
Public Function SaveDataOld(ByRef dr As System.Data.OleDb.OleDbDataReader) As Boolean
Dim fileNam As String
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim flag As Boolean
Dim x As Integer
Dim y As Integer
Dim v As Integer
'Check for an open app.
If Not ImOpen Then
If Not OpenApp() Then
Windows.Forms.MessageBox.Show("Failed to open the Excel App.", "No Can DOH!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
SaveDataOld = False
Exit Function
End If
End If
'Now dump the data.
Try
'If this is a new book.
If mNewBook Then
xlBook = xlApp.Workbooks.Add
End If
'Get the first sheet.
xlSheet = xlBook.Worksheets(mSheetNo)
xlSheet.Cells.Clear()
If mSheetName <> "" Then
xlSheet.Name = mSheetName
End If
'Set the flag so the names can be written.
flag = True
y = 2
'The loop.
Do While dr.Read
'In the first round, put the names in.
If flag Then
flag = False
v = dr.FieldCount
For x = 0 To v - 1
xlSheet.Cells(1, x + 1) = dr.GetName(x)
Next
End If
'This is the standard.
For x = 0 To v - 1
xlSheet.Cells(y, x + 1) = dr.Item(x)
Next
'Increment the row counter.
y += 1
Loop
If mNewBook Then
If Not SetBookName() Then
'NOTE: This is an early exit condition. If the book name failed, then
'it is important that everything beyond this is not executed.
Windows.Forms.MessageBox.Show("Data NOT saved. No valid filename.", "Not Saved", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
xlBook.Close()
Return False
End If
End If
'Now the data has been put away. Save the book.
If mNewBook Then
xlBook.SaveAs(mBookName)
Else
xlBook.Save()
End If
'Increment the sheet number.
mSheetNo += 1
Windows.Forms.MessageBox.Show("Data saved.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Keep the book open.
NewBook = False
SaveDataOld = True
Catch ex As Exception
MsgBox("Something failed while writing the data." & vbNewLine & vbNewLine & ex.Message, MsgBoxStyle.Exclamation, "Failure")
SaveDataOld = False
End Try
End Function
Public Function SaveDataOld(ByRef dr As System.Data.OleDb.OleDbDataReader, ByVal shtName As String) As Boolean
mSheetName = shtName
Return SaveDataOld(dr)
End Function
'Use this function to save always to a new book. The book will remain open.
Public Function SaveDataNewBook(ByRef dr As System.Data.OleDb.OleDbDataReader) As Boolean
NewBook = True
'Now, just call this basic function to do all the work.
SaveDataNewBook = SaveDataOld(dr)
End Function
Public Function SaveDataNewBook(ByRef dr As System.Data.OleDb.OleDbDataReader, ByVal shtName As String) As Boolean
mSheetName = shtName
Return SaveDataNewBook(dr)
End Function
'This is just here in case there is an advantage to not maintaining the app. If there
'is an open book, it is closed by setting the newbook to true.
Public Function CloseApp()
NewBook = True
If Not xlApp Is Nothing Then
xlApp.Quit()
End If
End Function
#End Region
#Region "Private Functions"
Protected Function SetBookName() As Boolean
Dim flNm As String
Dim cdlg1 As New System.Windows.Forms.SaveFileDialog
Dim st1 As String
SetBookName = False
flNm = ""
mBookName = ""
Do
'Get it.
cdlg1.FileName = flNm
cdlg1.Filter = "Excel Files (*.xls)|*.xls"
cdlg1.CheckFileExists = False
cdlg1.OverwritePrompt = False
If cdlg1.ShowDialog() = DialogResult.Cancel Then
'The user opted to cancel. Bail out.
flNm = "Blue"
Else
flNm = cdlg1.FileName
'Check it.
If System.IO.File.Exists(flNm) Then
'If it exists, warn on it.
If Windows.Forms.MessageBox.Show("That file already exists. If you use it, you will overwrite at least one of the sheets in the existing spreadsheet. Is this what you want to do?", "Watch Out", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = DialogResult.No Then
flNm = ""
Else
mBookName = flNm
End If
Else
mBookName = flNm
End If
End If
Loop While flNm = ""
'Only have to check one thing for the return value.
If mBookName <> "" Then
SetBookName = True
End If
End Function
Protected Function OpenApp() As Boolean
'Create the app.
Try
If xlApp Is Nothing Then
xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
End If
xlApp.AlertBeforeOverwriting = False
ImOpen = True
Catch ex As Exception
ImOpen = False
End Try
OpenApp = ImOpen
End Function
#End Region
End Class
Problem using Excel with VB.Net
Reply With Quote
Originally Posted by Asgorath
