Hi again,
Sorry for any confusion. Below is all the code in use. It's a prototype I work with before doing the real work. Both code generate exceptions when using templates:
[code]
Imports Syncfusion.XlsIO
Imports System.Data
Imports System.Data.SQLite
Public Class frmMain
Private Const cTitle = "Excel Reports"
Private stPath As String
Private stDB As String
Private stCon As String
Private xlEngine As ExcelEngine
Private xlApplication As IApplication
Private xlWBook As IWorkbook
Private xlWSheet As IWorksheet
Private xlRange As IRange
Private sqlConnection As SQLiteConnection
Private sqlAdapter As SQLiteDataAdapter
Private dsData As New DataSet
Private dsTable As New DataTable
Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
With Me
.Text = cTitle
.WindowState = FormWindowState.Normal
.StartPosition = FormStartPosition.CenterScreen
End With
stPath = AppDomain.CurrentDomain.BaseDirectory.ToString()
stDB = stPath + "Templates\Production.db"
stCon = "Data Source=" + stDB + ";Version=3;Pooling=True;Max Pool Size=10;"
sqlConnection = New SQLiteConnection
sqlConnection.ConnectionString = stCon
Try
sqlConnection.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString, cTitle, MessageBoxButtons.OK)
Exit Sub
End Try
xlEngine = New ExcelEngine
xlApplication = xlEngine.Excel
xlApplication.DefaultVersion = ExcelVersion.Excel2013
End Sub
Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
Try
xlEngine.Dispose
With sqlConnection
.Close
.Dispose
End With
Catch ex As Exception
'
Finally
Me.Close
End Try
End Sub
Private Sub btnChart_Click(sender As Object, e As EventArgs) Handles btnChart.Click
Dim stMonth As String = String.Empty
Dim intColCounter As Integer = 4
Dim intRow As Integer
If Create_DataSet = True Then
If dsData.Tables(0).Rows.Count > 0 Then
xlWBook = xlApplication.Workbooks.Open(stPath & "Templates\ChartReport.xltx",ExcelOpenType.Automatic)
xlWSheet = xlWBook.Worksheets(0)
For i As Integer = 0 To dsData.Tables(0).Rows.Count - 1
stMonth = dsData.Tables(0).Rows(i).Item(0).ToString
Select Case stMonth
Case "January" : intRow = 4
Case "February" : intRow = 5
Case "Mars" : intRow = 6
Case "April" : intRow = 8
Case "May" : intRow = 9
Case "June" : intRow = 10
Case "July" : intRow = 12
Case "Augusti" : intRow = 13
Case "September" : intRow = 14
Case "October" : intRow = 16
Case "November" : intRow = 17
Case "December" : intRow = 18
End Select
If intColCounter = 9 Then intColCounter = 4
xlRange = xlWSheet.Range(intRow, intColCounter)
xlRange.Value = dsData.Tables(0).Rows(i).Item(1)
intColCounter = intColCounter + 1
Next
End If
xlWSheet.Protect("A4jb]&")
Dim stDateTime = CStr(Format(Now(), "yyyyMMddHHmmss"))
xlWBook.SaveAs(stPath + "\Templates\ChartReport " + stDateTime + ".xlsx")
Else
MessageBox.Show("No data found!", cTitle, MessageBoxButtons.OK)
End If
End Sub
Private Sub btnPivot_Click(sender As Object, e As EventArgs) Handles btnPivot.Click
If Create_DataTable = True Then
xlWBook = xlApplication.Workbooks.Open(stPath & "Templates\PivotReport.xltx",openType:=ExcelOpenType.Automatic)
xlWSheet = xlWBook.Worksheets(0)
xlwsheet.ImportDataTable(dsTable, True, 4, 2)
Dim stDateTime = CStr(Format(Now(), "yyyyMMddHHmmss"))
xlWBook.SaveAs(stPath + "\Templates\PivotReport " + stDateTime + ".xlsx")
Else
MessageBox.Show("No data found!", cTitle, MessageBoxButtons.OK)
End If
End Sub
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
MessageBox.Show(ex.ToString, cTitle, MessageBoxButtons.OK)
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
MessageBox.Show(ex.ToString, cTitle, MessageBoxButtons.OK)
Return False
End Try
End Function
End Class
[/code]
TIA,
Dennis
