-
Feb 28th, 2007, 02:59 PM
#1
Thread Starter
Hyperactive Member
VB.NET - Create Excel Spreadsheet From Array
This is a short method that accepts a 2 dimensional string array and turns it into an excel spreadsheet. You can also pass a file name and it will save the spreadsheet. FYI...As noted in the code I'm using late binding to avoid version issues with Excel.
This is the Original code. See Below (post 4) for the Updated Code
VB Code:
Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
'To avoid conflicts with different versions of Excel...We are using late binding.
Dim objxlOutApp As Object 'Excel.Application
Dim objxlOutWBook As Object 'Excel.Workbook
Dim objxlOutSheet As Object 'Excel.Worksheet
Dim objxlRange As Object 'Excel.Range
Try
'Try to Open Excel, Add a workbook and worksheet
objxlOutApp = CreateObject("Excel.Application") 'New Excel.Application
objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
objxlOutSheet = objxlOutWBook.Sheets.Item(1)
Catch ex As Exception
MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Try
If Not IsNothing(objxlOutWBook) Then
objxlOutWBook.Close() 'If an error occured we want to close the workbook
End If
If Not IsNothing(objxlOutApp) Then
objxlOutApp.Quit() 'If an error occured we want to close Excel
End If
Catch
End Try
objxlOutSheet = Nothing
objxlOutWBook = Nothing
If Not IsNothing(objxlOutApp) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
End If
objxlOutApp = Nothing
Exit Sub 'An error occured so we don't want to continue
End Try
Try
objxlOutApp.DisplayAlerts = False 'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
objxlOutApp.Visible = False 'We don't want the app visible while we are populating it.
'This is the easiest way I have found to populate a spreadsheet
'First we get the range based on the size of our array
objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
'Next we set the value of that range to our array
objxlRange.Value = strOutputArray
'This final part is optional, but we Auto Fit the columns of the spreadsheet.
objxlRange.Columns.AutoFit()
If strExcelFileOutPath.Length > 0 Then 'If a file name is passed
Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
If Not objFileInfo.Directory.Exists Then 'Check if folder exists
objFileInfo.Directory.Create() 'If not we create it
End If
objFileInfo = Nothing
objxlOutWBook.SaveAs(strExcelFileOutPath) 'Then we save our file.
End If
objxlOutApp.Visible = True 'Make excel visible
Catch ex As Exception
MessageBox.Show("While trying to Export to Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Try
objxlOutWBook.Close() 'If an error occured we want to close the workbook
objxlOutApp.Quit() 'If an error occured we want to close Excel
Catch
End Try
Finally
objxlOutSheet = Nothing
objxlOutWBook = Nothing
If Not IsNothing(objxlOutApp) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
End If
objxlOutApp = Nothing
End Try
End Sub
Last edited by ProphetBeal; Mar 2nd, 2007 at 02:09 PM.
Reason: Added note for new code
-
Mar 1st, 2007, 12:23 AM
#2
Re: VB.NET - Create Excel Spreadsheet From Array
you might want to mention that the code requires the use of the Office Interop Assemblies
-
Mar 1st, 2007, 12:53 AM
#3
Re: VB.NET - Create Excel Spreadsheet From Array
Also, the use of ReleaseComObject should not be needed ever if the object variables are handled correctly.
Lastly, you shouold have Option Strict On which will generate several errors as the code doesnt have any casting.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Mar 2nd, 2007, 02:03 PM
#4
Thread Starter
Hyperactive Member
Re: VB.NET - Create Excel Spreadsheet From Array
Thanks for the feedback guys. I have updated the code with Option Strict On. In order to use this code you will need to add a reference to the Microsoft Excel Object Library.
VB Code:
Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
Dim objxlOutApp As Excel.Application
Dim objxlOutWBook As Excel.Workbook
Dim objxlOutSheet As Excel.Worksheet
Dim objxlRange As Excel.Range
Try
'Try to Open Excel, Add a workbook and worksheet
objxlOutApp = New Excel.Application
objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
objxlOutSheet = DirectCast(objxlOutWBook.Sheets.Item(1), Excel.Worksheet)
Catch ex As Exception
MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Try
If Not IsNothing(objxlOutWBook) Then
objxlOutWBook.Close() 'If an error occured we want to close the workbook
End If
If Not IsNothing(objxlOutApp) Then
objxlOutApp.Quit() 'If an error occured we want to close Excel
End If
Catch
End Try
objxlOutSheet = Nothing
objxlOutWBook = Nothing
'If Not IsNothing(objxlOutApp) Then
'System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
'End If
objxlOutApp = Nothing
Exit Sub 'An error occured so we don't want to continue
End Try
Try
objxlOutApp.DisplayAlerts = False 'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
objxlOutApp.Visible = False 'We don't want the app visible while we are populating it.
'This is the easiest way I have found to populate a spreadsheet
'First we get the range based on the size of our array
objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
'Next we set the value of that range to our array
objxlRange.Value = strOutputArray
'This final part is optional, but we Auto Fit the columns of the spreadsheet.
objxlRange.Columns.AutoFit()
If strExcelFileOutPath.Length > 0 Then 'If a file name is passed
Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
If Not objFileInfo.Directory.Exists Then 'Check if folder exists
objFileInfo.Directory.Create() 'If not we create it
End If
objFileInfo = Nothing
objxlOutWBook.SaveAs(strExcelFileOutPath) 'Then we save our file.
End If
objxlOutApp.Visible = True 'Make excel visible
Catch ex As Exception
MessageBox.Show("While trying to Export to Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Try
objxlOutWBook.Close() 'If an error occured we want to close the workbook
objxlOutApp.Quit() 'If an error occured we want to close Excel
Catch
End Try
Finally
objxlOutSheet = Nothing
objxlOutWBook = Nothing
'If Not IsNothing(objxlOutApp) Then
' System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
'End If
objxlOutApp = Nothing
End Try
End Sub
-
Mar 2nd, 2007, 02:27 PM
#5
Re: VB.NET - Create Excel Spreadsheet From Array
Yes, with Option Strict On you will have to either add thereference and switch to Early Binding or use Reflection and have it as Late Binding with Option Strict On. Its allot of work to use Late Binding with Reflection so unless there is a good reason for late binding I would just early bind.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Apr 2nd, 2008, 04:32 PM
#6
New Member
Re: VB.NET - Create Excel Spreadsheet From Array
thanks for the post - it acutally works - would change the last part since the spreadsheet stays locked by the system and you can't delete it - i am creating this in a web app to download - will probably bring the ire of those who will say this automation was not meant to be used on a server. sorry bout that.
revised code is
Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
Dim objxlOutApp As Microsoft.Office.Interop.Excel.Application
.
.
.
If strExcelFileOutPath.Length > 0 Then
'If a file name is passed
Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
If Not objFileInfo.Directory.Exists Then 'Check if folder exists
objFileInfo.Directory.Create() 'If not we create it
End If
objFileInfo = Nothing
objxlOutWBook.SaveAs(strExcelFileOutPath) 'Then we save our file.
End If
Catch ex As Exception
End Try
Try
objxlOutWBook.Close() 'want to close the workbook
objxlOutApp.Quit() 'want to close Excel
Finally
objxlOutSheet = Nothing
objxlOutWBook = Nothing
objxlOutApp = Nothing
End Try
End Sub
-
Jun 11th, 2008, 04:44 PM
#7
New Member
Re: VB.NET - Create Excel Spreadsheet From Array
This works well up to 26 columns in Excel 2003. However, it breaks when computing the upper bound going from Z to AA.
If you replace line 38 with the following code in the version posted by ProphetBeal on Mar 2nd, 2007, 01:03 PM, all columns up to IV are accepted.
'Let's determine the column name
Dim sUpperBoundLetter As String
Select Case strOutputArray.GetUpperBound(1)
Case Is > 234
sUpperBoundLetter = "I" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
Case Is > 208
sUpperBoundLetter = "H" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
Case Is > 182
sUpperBoundLetter = "G" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
Case Is > 156
sUpperBoundLetter = "F" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26)
Case Is > 130
sUpperBoundLetter = "E" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26)
Case Is > 104
sUpperBoundLetter = "D" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26)
Case Is > 78
sUpperBoundLetter = "C" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26)
Case Is > 52
sUpperBoundLetter = "B" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26)
Case Is > 26
sUpperBoundLetter = "A" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26)
Case Else
sUpperBoundLetter = Chr(strOutputArray.GetUpperBound(1) + 1 + 64)
End Select
objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & sUpperBoundLetter & (strOutputArray.GetUpperBound(0) + 1))
-
Feb 5th, 2009, 02:58 PM
#8
Hyperactive Member
Re: VB.NET - Create Excel Spreadsheet From Array
How to call this method if I pass one dimension string array?
-
Feb 5th, 2009, 04:29 PM
#9
Thread Starter
Hyperactive Member
Re: VB.NET - Create Excel Spreadsheet From Array
Originally Posted by zhshqzyc
How to call this method if I pass one dimension string array?
Well this was created to handle 2 dimensional arrays. 1 dimension for the rows and a 2nd for the columns. If you only have a 1 dimension array where would the data be populated? A single row or a single column?
-
Feb 5th, 2009, 06:07 PM
#10
Hyperactive Member
Re: VB.NET - Create Excel Spreadsheet From Array
Thanks.
I resloved it.
But I deploy the executable to my flash drive(run .exe file), I get an "Unhandled exception error" stating "Could not find file or assembly "Interop.Excel."
Do I need to add more ferences or modify the codes?
Or shall I copy something to the flash drive?
I am using your the earliest code.
Last edited by zhshqzyc; Feb 5th, 2009 at 06:23 PM.
-
Feb 9th, 2009, 04:40 PM
#11
Hyperactive Member
Re: VB.NET - Create Excel Spreadsheet From Array
The code is good except one thing.
If there is an excel file opened. It can't been export it to the excel file.
Any advice?
-
Jul 7th, 2009, 06:51 AM
#12
Re: VB.NET - Create Excel Spreadsheet From Array
Nice Thread, helped a lot, thanks.
Alex
.NET developer
"No. Not even in the face of Armageddon. Never compromise." (Walter Kovacs/Rorschach)
Things to consider before posting.
Don't forget to rate the posts if they helped and mark thread as resolved when they are.
.Net Regex Syntax (Scripting) | .Net Regex Language Element | .Net Regex Class | DateTime format | Framework 4.0: what's new
My fresh new blog : writingthecode, even if I don't post much.
System: Intel i7 920, Kingston SSDNow V100 64gig, HDD WD Caviar Black 1TB, External WD "My Book" 500GB, XFX Radeon 4890 XT 1GB, 12 GBs Tri-Channel RAM, 1x27" and 1x23" LCDs, Windows 10 x64, ]VS2015, Framework 3.5 and 4.0
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|