Question Working with Excel

aeskan

Well-known member
Joined
Aug 10, 2011
Messages
63
Programming Experience
3-5
Hello everybody.

First of all I would apologise if this topic does not belong to this section of forum, for indeed I did not find its proper place. It's about Excel programming in VB.Net language.

Anyway, perhaps you know that there is a set of components named "Access 2010 Database Engine", also a rich platform named "Microsoft Access 2010 Runtime", which can be used to facilitate transfer of data between 2010 Microsoft Office System files and non-Microsoft Office applications, or enables you to distribute Access 2010 applications to users who do not have the full version of Access 2010 installed on their computers. But how about Excel?

Suppose in my VB.Net codes I have the following lines to connect to an Excel workbook:

Dim oExcel As Object = GetObject("", "Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open(sPrintFile)
Dim oSheet1 As Object = oBook.Worksheets(1)

Well, for end users who do not have the full version of Microsoft Excel Application installed on their computers the above codes would not work and an error will occur and it's normal, for there is no Excel.Application object.

The question is; for a successful "Access Database" connection (OLEDB) in VB.Net applications, installing "Access 2010 Database Engine" will solve the problem and end user does not need to install full version of "Microsoft Access 2010", now for "Excel" programming in VB.Net, which Engine or Runtime we could install instead of full version of "Microsoft Excel 2010"?

Note that I also used Access 2010 Database Engine and it did not solve the Excel problem.

Thanks in advance for any idea.


 
Last edited:
An excellent question and I only have a partial answer. You can install the following if any Microsoft Office 2003 application (e.g. Word 2003) is installed first. You do not have to have a license to Excel. But it does not work unless at least one item from the Microsoft Office 2003 family is installed first.

The free MSOffice interop library can be installed from here, there are installation instructions provided by Microsoft, but it's not an "easy install" for users and I want to find an easier way to package this with my own application, perhaps by writing a custom installer instead of using ClickOnce deployment.

Office 2003 Update: Redistributable Primary Interop Assemblies is available for download

Then in your application, add for example the following:

Imports Excel = Microsoft.Office.Interop.Excel

Private Sub Write_DataTable_to_Excel(MyFileName As String, ByRef MyTable as DataTable)

    ' This example was first published here, then modified slightly by Rob Sherratt to encapsulate in a Subroutine.
    ' [url]http://www.ehow.com/how_8732484_convert-values-xls-format-vbnet.html[/url]

    Dim app As Excel.Application 
    app = new Excel.Application()
    Dim sheet As Excel.Worksheet = (Excel.Worksheet)app.ActiveSheet 
    sheet.Name = MyFileName

    ' Iterate through each table row and write it to the spreadsheet:

    Dim count As Integer = 1

    For Each row As DataRow In MyTable.Rows
        count += 1
        For j As Integer = 1 To MyTable.Columns.Count
            If count = 2 Then
                sheet.Cells(1, j) = MyTable.Columns(j - 1).ColumnName
            End If
            sheet.Cells(count, j) = row.Item(j - 1).ToString
        Next j
    Next row

    ' Save the changes to the Excel file and close the workbook:

    app.SaveAs("MyFileName" & .xls)
    app.Close()
    Exit Sub

End Sub


I hope this partial answer is at least slightly helpful,
Best regards,
Rob Sherratt
 
Last edited:
You can't do Excel automation if Excel is not installed in client computer, there is also no alternative.

With OleDB you can use Excel file as data source to read data from and write data to, search web for "vb.net excel oledb" for example.
 
Thank you Rob and thanks JohnH.

I guessed, it's rational when we use GetObject("", "Excel.Application") therefore there must be an Excel Application. But still I'm hopeful LOL. Where there's a will there's a way.:apple:

And Rob I followed your link and it was somehow useful or atleast thought provoking. Eventually I found some links perhaps help you to package PIAs with your own applications.

Adding the Office 2003 Primary Interop Assembly (PIAs) as a prerequisite to a Visual Studo 2005 Setup Project
Adding Prereqs To VS 2008 : The Official Microsoft ASP.NET Forums
How to add Microsoft Office 2003 Primary Interop Assemblies in prerequities box?


And finally Microsoft Office 2010 Primary Interop Assemblies Bootstrapper Package:
Download Office 2010 PIA BootStrapper from Official Microsoft Download Center


Thank you again boys.
All the best.



 
@aeskan@

Thanks.

I have a similar need to you and was already researching it and then found your OP here. My application currently saves .csv files which the user then has to open with Excel and apply sensible formatting. I don't want to change the application so it "only" works on a computer which has Excel installed. But if there was a way to save my DataTables in Excel format (without the restriction of the app being tied to an Excel installation, then it would be great. I can offer you my latest versions of .csv import and export procedures if you want them. They are now heavily modified and "bug fixed" and tested with huge files we import from commercial databases provided by Thomson Reuters, both the Delphion and Thomson Innovation systems.

The "interesting" fact is that the former Open Office Calc, now Libre Calc has the capability to save .xls and .xlsx files, and it does not require Excel to be installed on the user's computer. So there "is" a way to do what we want, and perhaps one of us can contact the Libre Calc developers to seek their help?

Meanwhile let's keep each other updated on this thread?

Incidentally, I am using Visual Studio Express 2012 and there is no need to load Interop assemblies for COM interworking with Microsoft Office. In fact Microsoft advise against it, see the link I posted in my previous message.

There are some big pitfalls when interworking with Excel. The cell size limit of 32,767 characters is not "tested" by any version of Excel when importing data as delimited text, and if you try to import a .csv file which has any comma separated field containing more than 32,767 characters, then Excel ceases to process the remainder of the .csv file correctly. It ignores the current record's closing delimiters, it concatenates subsequent records, and it loses track of which data has to go in which column. It's a real buggy program, even the very latest version has this problem, it's never been fixed. I can "live" with a cell size limit, but the fact that the .csv file import algorithm has this serious bug is very bad news.

So I have another procedure that checks all DataTable cells for length of contents and creates extra cells to hold the "overflow", just so I can get the data into Excel in the first place. Also it's no good setting the record size for truncation at 32,767, that will not work. You have to make allowance for commas, double quotes, double double quotes and so on that have to be added to each record by the .csv parser when it generates the output file. So currently I set my truncation threshold to 30,000 characters per DataTable cell, allowing a safe margin for the .csv parser to add lots of delimiters!

Another slight problem I face, again in connection with interworking with Excel, is that when my "truncation" algorithm has to add extra columns to the main data table for the overflow text, it is only possible to add new columns at the end of the data table, you can't insert columns adjacent to the column that has a text overflow problem and do everything on the fly. So I have to know "in advance" which user data is likely to cause a problem and create the extra columns during initialization of the main DataTable. This is very "bad" programming technique because who can possible know "in advance" which users in what circumstances will generate large data cell records?

Best regards,
Rob
 
Last edited:
Here is the most helpful link I found, following up @JohnH@'s earlier suggestion:

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

So if an Excel file already "exists somewhere", e.g. in my application's installation folder, I can "clone" and "rename" the file using standard file handling techniques. Then using one of the "clones" I can export my DataTable into the Excel file. This is a good enough workaround for me, and I will proceed with implementation. My plan is to generate a "Template File" using Excel 2012 already with columns and rows formatted correctly for the large DataTable I was previously saving as a .csv file. Then all my "Save as Excel" procedure has to do is insert data into the correct cell locations, similarly to the example in my first reply, but using the OLE DB technique that JohnH hinted at, and which is fully described in the link I just posted.

Best regards,
Rob
 
Well it completely works fine... :rugby:

Dim m_sConn As String = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=C:\MyFile.Xlsx;Extended Properties=""Excel 12.0;HDR=YES"""
Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn.Replace("HDR=YES", "HDR=NO"))
' NOTE: The connection string indicates that the table does *NOT*
' have a header row.
conn.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "UPDATE [SheetName$F3:G3] SET F1 = 'Cell F3', F2 = 'Cell G3'"
cmd.ExecuteNonQuery()
conn.Close()

And I'm now just searching for how to print the Xlsx files in existing circumstances.

Thank you Rob.

And here are some links perhaps would be useful to see them:
Working with MS Excel(xls / xlsx) Using MDAC and Oledb - CodeProject
Excel | Using XML | Extensible Markup Language

Good luck buddy.
 
Last edited:
Wow @aescan@ that was quick, you beat me to it! I got a bit sidetracked this weekend learning to play some Ron Kenoli songs on my piano, but I'll catch up with you during the week.

For printing the Excel File, take a look at this:


You can probably access it in some way using a similar OLE-DB command, but I don't think the details are published. My solution is the lazy man's solution. After I've generated the .xslx file I will "manually" load it into the Excel viewer and print from there. I believe the download license permits redistribution in which case you could include it in your application's installation folder for distribution to your users. But hey - guess what my "users" all have Excel 2012 on their computers anyway.

Anyway I am really happy that between us we worked out how to create Excel files without restricting our source software in such a way that it always has to be built on a machine with MS Office installed. That was my number 1 objective. Case solved for me! If you have any other code updates to help me catch up with you, that would be great!!
 
I am stuck at this point and would appreciate help.
The problem is that the OLEdb command will not let me pass more than 255 characters as data values.
I get an OLEdb Exception ""The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
This occurs when I try to pass records of up to 31,000 characters.
The exception is thown after executing cmd.ExecuteNonQuery().
Here is my code so far, I've tried everything I know to try to get OOLEdb to accept long string records, but am getting nowhere.

Thanks a lot in advance for any advice on this!

Private Function Table_To_XLSX(MyDataTable As DataTable, FilePathName As String, Optional HasHeader As Boolean = False) As Boolean

        ' Saves a DataTable as an .xlsx file. 

        Try

            ' Copy the template file into the new .xlsx file name specified by the user.

            Dim MyTemplateFile As String = MyApplicationFolder & "\Excel Template.xlsx"
            If System.IO.File.Exists(MyTemplateFile) Then
                System.IO.File.Copy(MyTemplateFile, FilePathName)
                PrintLine("... Created a copy of the Excel Worksheet Template.xlsx file.")
            End If

            Dim m_sConn As String = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & _
                FilePathName & ";Extended Properties=""Excel 12.0;HDR=YES"""

            Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn)
            conn.Open()

            Dim cmd As New System.Data.OleDb.OleDbCommand()

            cmd.Connection = conn

            Const SEPARATOR As String = "', '"

            Dim RowValues As String

            ' The following definition requires that a NAME is assigned in Excel to the collection of 
            ' column names in the header row of the .xls template, and the name must be "ExcelTable".

            Dim TableName As String = "MainTable"

            ' Create the header names in SQL syntax, corresponding to the .xlsx file

            Dim col As DataColumn
            Dim HeaderRow As String = ""
            For Each col In LowerDataTable.Columns
                HeaderRow = HeaderRow & "[" & col.ColumnName.ToString & "]"
            Next col
            HeaderRow = Replace(HeaderRow, "][", "], [")
            Dim HeaderValues As String = Replace(Replace(HeaderRow, "[", "'"), "]", "'")

            PrintLine("Header Row is:")
            PrintLine(HeaderRow)

            Dim RowCount As Integer = 0

            For Each row As DataRow In MyDataTable.Rows

                ' Write row to Excel sheet

                ' The SQL syntax is e.g. "INSERT INTO ExcelTable VALUES ('value1', 'value2')"

                ' Array_To_SQL() parses the records in MyDataTable and performs character substitutions 
                ' in accordance with the SQL specification to allow "special characters" in the user
                ' data fields to be treated not as delimiters.

                RowValues = String.Join(SEPARATOR, Array_To_SQL(row.ItemArray))

                PrintLine("Record (" & CStr(RowCount) & ") is:")
                PrintLine("'" & RowValues & "'")

                cmd.Parameters.Clear()
                cmd.CommandText = "INSERT INTO " & TableName & "(" & HeaderRow & ") VALUES ('" & RowValues & "')"
                cmd.CommandType = CommandType.Text

' Added the following to try to resolve an exeption being thrown by OLEdb due to some of the RowValues() being
' up to 31000 characters in length.

                For Each col In LowerDataTable.Columns
                    Dim Parameter As New OleDbParameter(col.ColumnName.ToString(), OleDbType.LongVarChar, 32000)
                    cmd.Parameters.Add(Parameter)
                Next col

                cmd.ExecuteNonQuery()

                ProgressBar1.Value = RowCount
                Application.DoEvents()
                Thread.Sleep(1)
                RowCount += 1

            Next row

            conn.Close()

            Return True

        Catch ex As Exception
            PrintLine(ex.ToString())
            Return False
        End Try

    End Function

    Private Function Array_To_SQL(ByRef ObjectArray() As Object) As String()

        ' Processes all rows in StringArray, converting them into records suitable for 
        ' writing to a database via a SQL INSERT statement. Returns the results in a String() array.

        Const QUOTE As String = """"
        Const TAB As String = Chr(9)
        Const CR As String = Chr(13)
        Const LF As String = Chr(10)
        Dim StringItem As String
        Dim StringArray(ObjectArray.GetLength(0) - 1) As String

        ' The following special character ESCAPE substitutions are made in the user's data records in order 
        ' they can be passed via the SQL INSERT statement.

        '   \'    A single quote (“'”) character.
        '   \"    A double quote (“"”) character.
        '   \n    A newline (linefeed) character.
        '   \r    A carriage return character.
        '   \t    A tab character.
        '   \\    A backslash (“\”) character.
        '   \%    A “%” character.
        '   \_    A “_” character.

        For n As Integer = 0 To ObjectArray.GetLength(0) - 1

            StringItem = ObjectArray(n).ToString
            StringItem = Replace(StringItem, "\", "\\")
            StringItem = Replace(StringItem, QUOTE, "\" & QUOTE)
            StringItem = Replace(StringItem, "'", "\'")
            StringItem = Replace(StringItem, CR, "\r")
            StringItem = Replace(StringItem, LF, "\n")
            StringItem = Replace(StringItem, TAB, "\t")
            StringItem = Replace(StringItem, "%", "\&")
            StringItem = Replace(StringItem, "_", "\_")

            StringArray(n) = StringItem

        Next n
        Return StringArray

    End Function
 
@Admins@ I wanted to edit the above to try to remove some of the unnecessary indents etc. Whenever I try to edit, the "editor" contents are blank. I've tried this using Google Chrome, IE and Firefox. Same thing happens with all of them. So I think there may be a technical problem of some kind. Could you see if you can reproduce the problem?
 
I am abandoning using OleDb. There is a serious bug in the Microsoft drivers that prevents the programmer sending records containing more than 4,000 characters. The "Variant" data type that maps to "Object" of unlimited size just generates wierd exception messages when encountering more than 4,000 characters of data. Eventually I tracked down a Microsoft site where they admit to the bug in OleDb. Anyway I am currently re-writing using Odbc. I believe that Microsoft developers used OleDb as their "starting point" and they seem to have made Odbc much more robust with some additional capabilities. Already the diagnostic exception messages have helped me in my "rewrite". I am hoping that the "bug" with handling long records in OleDb is not present with Odbc.

A question:

OleDb requires UTC8 character encoding for text, not ANSI.
Does Odbc work correctly with ANSI encoding as used by VB.NET Strings?

Thanks,
Rob
 
Rob,
Nonce I have no idea, but think you could discuss about it on ODBC and VB.NET development section, hope you find the solution.
ODBC
 
Hi aescan,

This has consumed far too much of my time trying to get a generic solution working using either Odbc or Ole.
The basic problem is that there is almost no debug information provided, and the documentation is a nightmare!
So, I'm calling a halt.

I just bought an excellent library from xPort tools that does everything I need.
It was very good value at $159 for both xlReader and xlWriter.
The library also contains methods to perform formatting and other editing of .xlsx files, which is a bonus.
And their support is first class, they responded with a good answer to my first question in less than 1 hour.
xPortTools Components
 
Back
Top