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:
Rob,
Totally what's the usage of Excel in your applications?
I usually use it to produce predefined invoices, reports and similar things, and now I'm going to replace ReportViewer or Crystal Report.
What's your usage of Excel? Couldn't you replace it with some thing else?
 
Hi aeskan,

All my internal "customers" are Excel users and are senior business analysts whose skills in using Excel are first class.
If I do not deliver an "Excel"-friendly solution, then my software will not be used. Anyway I have made a promise to
deliver Excel import and export and I never break my promises. However I don't want to be "tied" to any particular
Excel implementation and I cannot assume all "customers" are using the same version of Excel.

The library from xPortTools is great and it works 100% reliably and has no binding to any version of Excel. It ticks all
the boxes for me, and is worth $159 of my money to relieve me of the nightmare experience of ODBC and OLE. I have never
until recently come across a library that is so unreliable, so complex to use, and so poor on diagnostic reporting as the
Microsoft ODBC and OLE library in VB.NET. And although I am a newcomer on this forum, I have been a software developer
for 30 years.
 
Obviously being newbie in forums does not mean being newbie in software developing, although all of us are always newbie in science, sepcially computer science, for it's an endless ocean.

Anyways, no dobt xPortTools should be a worthwhile tool as you described. I will research about that. But what makes the programming marvelous for me is this challenge of thinking and finding the solutions and solving the puzzles. What the odds if everything and every tool was prepared previously. So now this process led me to Excel-Writer and Perl 5.16 and now I'm researching about it. Have you ever heard or worked with Perl?
 
I did a small amount of PERL programming in a Linux context but never VB.NET.
The VB.NET environment is new for me and it's only in Dec 2012 that I decided to take on Windows7 and Windows8 apps development for my employer who previously outsourced.
If you like challenges, then implement this in PERL :) This code took me 15 minutes to implement and get working and the resulting .xlsx worksheet looks really cool!

Private Function Table_To_XLSX(MyDataTable As DataTable, FileName As String) As Boolean

        ' Saves a DataTable as an .xlsx file.  
        ' Uses the xPortTools library

        Try

            Dim xporter As VM.xPort.DS2XL
            Dim headerStyle As VM.xPort.Style
            Dim contentStyle As VM.xPort.Style
            Dim customFont As System.Drawing.Font
            Dim formatStyle As VM.xPort.Style

            xporter = New VM.xPort.DS2XL()

            'Make header row higher (header's index in DataTable is -1) setting it to 25 points 
            '(value of 1 is 1/20th of a point).
            SetRowHeight(xporter.RowHeights, "Header", MyDataTable.TableName, -1, -1, 60 * 20)

            'Set row height for all other rows to 15 points.
            SetRowHeight(xporter.RowHeights, "AllRows", MyDataTable.TableName, 0, MyDataTable.Rows.Count - 1, 15 * 20)

            'Define style for the header. 
            headerStyle = New VM.xPort.Style("HeaderStyle", MyDataTable.TableName, -1, 0, -1, MyDataTable.Columns.Count - 1)

            'Create font that will be used to format header text.
            customFont = New System.Drawing.Font("Tahoma", 9, FontStyle.Bold)
            headerStyle.Font = customFont
            headerStyle.ForeColor = Color.White
            headerStyle.HorizontalAlignment = Style.xpHAlignment.Left
            headerStyle.VerticalAlignment = Style.xpVAlignment.Top
            headerStyle.ReadingDirection = Style.xpReadingDirection.Context
            headerStyle.LeftBorderLine = Style.xpBorderLineStyle.Thin
            headerStyle.RightBorderLine = Style.xpBorderLineStyle.Thin
            headerStyle.TopBorderLine = Style.xpBorderLineStyle.Thin
            headerStyle.BottomBorderLine = Style.xpBorderLineStyle.Thin
            headerStyle.BackgroundColor = Color.FromArgb(0, 36, 64, 98)
            headerStyle.LeftBorderColor = Color.White
            headerStyle.RightBorderColor = Color.White
            headerStyle.TopBorderColor = Color.White
            headerStyle.BottomBorderColor = Color.White

            'Add custom style to the collection of styles. If we do not add style, it will not be applied.
            xporter.Styles.Add(headerStyle)

            'Create style for content
            contentStyle = New Style("ContentStyle", MyDataTable.TableName, 0, 0, MyDataTable.Rows.Count - 1, MyDataTable.Columns.Count - 1)

            'Create style for data cells
            customFont = New System.Drawing.Font("Tahoma", 9, FontStyle.Regular)
            contentStyle.Font = customFont
            contentStyle.HorizontalAlignment = Style.xpHAlignment.Left
            contentStyle.VerticalAlignment = Style.xpVAlignment.Top
            contentStyle.ReadingDirection = Style.xpReadingDirection.Context
            contentStyle.LeftBorderLine = Style.xpBorderLineStyle.Thin
            contentStyle.RightBorderLine = Style.xpBorderLineStyle.Thin
            contentStyle.TopBorderLine = Style.xpBorderLineStyle.Thin
            contentStyle.BottomBorderLine = Style.xpBorderLineStyle.Thin
            contentStyle.BackgroundColor = Color.FromArgb(0, 220, 230, 41)
            contentStyle.LeftBorderColor = Color.White
            contentStyle.RightBorderColor = Color.White
            contentStyle.TopBorderColor = Color.White
            contentStyle.BottomBorderColor = Color.White

            'Add custom style to the collection of styles
            xporter.Styles.Add(contentStyle)

            'Make a clone of the contentStyle style and set only specific properties of custom style. 
            formatStyle = contentStyle.Clone("DateValues", MyDataTable.TableName, 0, _
                MyDataTable.Columns("Application Date").Ordinal, MyDataTable.Rows.Count - 1, _
                MyDataTable.Columns("Application Date").Ordinal)

            'Set format of the date values in specific column. 
            'Refer to documentation about which built-in Excel formats are available. 
            ' Use custom (not built-in) format string.
            formatStyle.FormatIndex = VM.xPort.Style.xpFormat.Custom
            formatStyle.Format = "MMM dd, yyyy"

            'Add custom style to the collection of styles.
            xporter.Styles.Add(formatStyle)

            xporter.Export(MyDataTable, FileName, xpOutputFormat.XLSX, True, True)

        Return True

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

    Private Sub SetRowHeight(ByVal heights As RowHeightCollection, _
                             ByVal styleName As String, _
                             ByVal dataTableName As String, _
                             ByVal startRow As Integer, _
                             ByVal endRow As Integer, _
                             ByVal height As Integer)

        Dim customRowHeight As RowHeight
        customRowHeight = New RowHeight(styleName, dataTableName, startRow, endRow, height)
        heights.Add(customRowHeight)

    End Sub
 
Great,
Thank you for sample, seems to be useful. So you had tested it before. Doesn't it solve your problem in Excel?
I read about Perl the night I was searching for Printing Excel documents LOL. I'm just downloading ActivePerl and will tell the result whenever I learnd how it worls. Has it any function for Printing?
 
Back
Top