DevExpress: Manage Excel Reports Through Automation

By | 2017-03-03

Introduction
In the past the common approach to manage Excel files was to install MS Excel on the server. For years it was applied despite Microsoft disagreement to do so. Today the situation is totally changed. Many third-part vendors provide us with solutions that do not require MS Excel to be available at all.

For me automation has always been attractive, mainly because by building information flows through organizations we free resources (man hours) and are much less depended on individuals. Of course, to some extent the quality of the information is also improved. Two expression I have come to dislike under all my years is “Your numbers…” and “My numbers…”. With automation we can instead say “Our numbers…”!

The solution I use is DevExpress, The Spreadsheet Document Server, which is a flexible and powerful platform. It’s a non-gui .NET Library that via its API provides us with all the functionality and many tools we also find in MS Excel.

The Case
The solution is a non-GUI console application and can be installed either on a workstation or on a server. If we look further down the road we can add it as a Windows Service that is executed on a fix day and time.

I plan to develop it further in some upcoming blog articles, creating it as a native Windows Service and also adding an e-mail service, i.e send the created reports as attachment to outgoing e-mails.

I should also mention that the case is based on the B4J: Generate Excel Reports blog article. In that article You see the Excel files that are used here. Unlike B4J it’s indeed possible to use real native Excel Template files which also is used in the below code.

As for the code itself it’s not so much to comment, it speaks quite well for itself:


Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SQLite
Imports DevExpress.Spreadsheet
Module Main

Private stPath As String
Private stDB As String
Private stCon As String

Private xlWBook As New Workbook()
Private xlWSheet As Worksheet
Private xlRange As Range

Private sqlConnection As SQLiteConnection
Private sqlAdapter As SQLiteDataAdapter
Private dsData As New DataSet
Private dsTable As New DataTable

Sub Main()

stPath = AppDomain.CurrentDomain.BaseDirectory.ToString()
stDB = stPath + "Templates\Production.db"
stCon = String.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=10;", stDB)

sqlConnection = New SQLiteConnection
sqlConnection.ConnectionString = stCon

Try
sqlConnection.Open()
Catch ex As Exception
Write_Log("Unable to open database.")
End Try

If Chart_Report = True Then
If Pivot_Report = True Then
'Done!
End If
End If

If xlWBook.IsDisposed=False Then
xlWBook.Dispose
End If

End Sub

#Region "Reports"
Private Function Chart_Report() As Boolean
Dim stMonth As String = String.Empty
Dim intColCounter As Integer = 4
Dim intRow As Integer

Try
If Create_DataSet = True Then
'Using a true native template.
xlWBook.LoadDocument(stPath & "Templates\ChartReport.xltx")
xlWSheet = xlWBook.Worksheets(0)

For i As Integer = 0 To dsData.Tables(0).Rows.Count - 1
'Columns and Rows zero based.
stMonth = dsData.Tables(0).Rows(i).Item(0).ToString
Select Case stMonth
Case "January" : intRow = 3
Case "February" : intRow = 4
Case "Mars" : intRow = 5
Case "April" : intRow = 7
Case "May" : intRow = 8
Case "June" : intRow = 9
Case "July" : intRow = 11
Case "Augusti" : intRow = 12
Case "September" : intRow = 13
Case "October" : intRow = 15
Case "November" : intRow = 16
Case "December" : intRow = 17
End Select

If intColCounter = 8 Then intColCounter = 3

xlRange = xlWSheet.Cells(intRow, intColCounter)
xlRange.SetValue(dsData.Tables(0).Rows(i).Item(1))

intColCounter = intColCounter + 1
Next

xlWSheet.Protect("A4jb]&", WorksheetProtectionPermissions.Default)

Dim stDateTime = CStr(Format(Now(), "yyyyMMddHHmmss"))
Dim stWorkbook As String = String.Format("{0}\Reports\ChartReport {1}.xlsx", stPath, stDateTime)

xlWBook.SaveDocument(stWorkbook,DocumentFormat.Xlsx)

Return True

Else
Write_Log("No data available for the Chart Report.")
Return False
End if

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

End Function

Private Function Pivot_Report() as Boolean

Try
If Create_DataTable = True Then
'Using a true native template.
xlWBook.LoadDocument(stPath & "Templates\PivotReport.xltx")

xlWSheet = xlWBook.Worksheets(0)
With xlWSheet
'Very smooth to import data table!
.Import(dsTable,False,4,2)
.PivotTables(0).Cache.Refresh
End With

'xlWSheet.Protect("A4jb]&", WorksheetProtectionPermissions.PivotTables)

Dim stDateTime = CStr(Format(Now(), "yyyyMMddHHmmss"))
Dim stWorkbook As String = String.Format("{0}\Reports\PivotReport {1}.xlsx", stPath, stDateTime)

xlWBook.SaveDocument(stWorkbook,DocumentFormat.Xlsx)

Return true

Else

Write_Log("No data available for the Pivot Report.")
Return False

End If

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

End Function

#End Region

#Region "Data"
Private Function Create_DataSet() As Boolean

Const stSQL As String = "SELECT Month, RESULT FROM Prod_Output ORDER BY Month;"

Try

sqlAdapter = New SQLiteDataAdapter(stSQL, sqlConnection)
sqlAdapter.Fill(dsData)
sqlAdapter.Dispose()
Return True

Catch ex As Exception

Write_Log("Chart Report data acquire:" + vbNewLine + ex.ToString)
Return False

End Try

End Function

Private Function Create_DataTable() As Boolean

Const stSQL As String = "SELECT Dept, Month, Result AS Amount FROM Prod_Output;"

Try

sqlAdapter = New SQLiteDataAdapter(stSQL, sqlConnection)
sqlAdapter.Fill(dsTable)
sqlAdapter.Dispose()
Return True

Catch ex As Exception

Write_Log("Pivot Report data acquire:" + vbNewLine + ex.ToString)
Return False

End Try

End Function
#End Region

#Region "Log"
Private sub Write_Log(ByVal stMsg As String)
'While the My.Application.Log is more powerful and flexible then below
'it's quitetime consuming to configure and set it up.

'Check File
Dim stDir As String = stPath + "Log\"
Dim fsStream As FileStream = New FileStream(stDir + "log.txt",FileMode.OpenOrCreate,FileAccess.ReadWrite)
Dim swStream As StreamWriter=New StreamWriter(fsStream)
swStream.Close
fsStream.Close

'Log error
Dim fsStreamError As FileStream = New FileStream(stDir + "log.txt",FileMode.OpenOrCreate,FileAccess.ReadWrite)
Dim swStreamError As StreamWriter = New StreamWriter(fsStreamError)
With swStreamError
.Write(String.Format("Date/Time: {0}{1}", Date.Now, vbNewLine))
.Write(String.Format("Message: {0}{1}", stMsg, vbNewLine))
.Write("------------------------------------------------------------------------------------------" + vbNewLine)
.Close
End With

fsStreamError.Close

End sub
#End Region

End Module

Until next time, take care and enjoy life!
Dennis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.