We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Using native template files with xlsio?

Thread ID:

Created:

Updated:

Platform:

Replies:

129075 Feb 24,2017 10:38 AM Mar 6,2017 01:23 AM Windows Forms 10
loading
Tags: XlsIO
Dennis Wallentin
Asked On February 24, 2017 10:38 AM

Hi all,

As some of You may know, MS Excel offers us to use native template files. They have the file extension xlt (-2003) and xltx (2007-).
They are very easy to work with, similar to the standard files.

1. Open MS Excel and a new workbook
2. Add all the necessary features and layoutto it so it looks like a report
3. Save it with the file extension xltx and a unique name.

When we use the template file in xlsio we first open it. But we never open the original file, rather a copy of it. The copy have 1 or higher added to its filename.
Then we can go ahead and fill it with data et al.

Maybe I have overlooked something but I cannot see that I can use native template files together with xlsio?
If it's not available please consider to make it possible as it's a powerful solution to use native template files.

Thanks in advance and all the best,
Dennis

Abirami Varadharajan [Syncfusion]
Replied On February 27, 2017 05:12 AM

Hi Dennis, 
 
We have prepared simple sample for modifying and saving the template file using XlsIO. The sample can be downloaded from the following location. 
 
 
If you wish to overwrite the changes to input template file, then we suggest to make use of Save() method. If you wish to save the file as different file, we recommend to use SaveAs() method. Kindly refer below codes for the same. 
 
Code Example:  
 
//Saving workbook as different Template file 
workbook.SaveAs("Output.xlsx"); 
 
//Saving workbook with same Template file 
workbook.Save(); 
 
If your scenario is different from this then kindly share us the details which will be helpful for us to provide prompt solution at the earliest. 
 
Regards, 
Abirami. 


Dennis Wallentin
Replied On February 27, 2017 06:56 AM

Hi again,

Thanks for the sample. It helped me with another thing but not my issue.

I have manually created an Excel template file, PivotReport.xltx, which includes a data area and a Pivot Table and Pivot Chart.
  • With XLSIO I want to open the ChartReport.xltx, populate the data area with data (data table) from a database and save it as PivotReport.xlsx.

When trying to open the ChartReport.xltx in XLSIO I receive an exception error. Here is the code:

[vb]

            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")
[/vb]

TIA,
Dennis



Abirami Varadharajan [Syncfusion]
Replied On February 28, 2017 06:47 AM

Hi Dennis, 
 
Thank you for updating us. 
 
In the shared code snippet, you are opening the file PivotReport.xltx but you have mentioned that exception thrown while opening the file ChartReport.xltx. It will be helpful to provide prompt solution if you share us the file which throws exception at your end along with the stack trace information. 
 
Regards, 
Abirami. 


Dennis Wallentin
Replied On February 28, 2017 07:08 AM

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

Abirami Varadharajan [Syncfusion]
Replied On March 1, 2017 08:57 AM

Hi Dennis, 
  
Thank you for sharing the complete code. On further analysis, we suspect that the exception is raised with specific documents which are used to open. 
  
As requested earlier, kindly share the input files (PivotReport.xltx and ChartReport.xltx) and exception stack trace to investigate further on this and provide a prompt solution at the earliest. 
  
Regards, 
Abirami 


Pon Geetha A J [Syncfusion]
Replied On March 1, 2017 11:37 PM

Hi again, 
 
Sorry, I'm not used to ask support for help so please bear with me. 
 
I attach the files in use, both Excel files and the SQLite DB. 
 
Thanks for Your patience and all the best, 
Dennis  
  
  
  
  
  


Abirami Varadharajan [Syncfusion]
Replied On March 2, 2017 09:05 AM

Hi Dennis, 
 
Thank you for sharing the template files.  
 
We have tried to reproduce the issue from the shared files and code snippet, but we are unable to reproduce the issue. We have shared the sample that we tried at our end to reproduce the issue and it can be downloaded from the following link 
 
 
Kindly try the above sample at your end. If the issue persists, kindly share the modified issue reproducing sample along with the stack trace of exception and Syncfusion working version which help us to provide the prompt solution at earliest. 
 
Regards, 
Abirami 


Pon Geetha A J [Syncfusion]
Replied On March 2, 2017 11:44 PM

  
Hello again, 
 
Many thanks for the input. I conclude that the issue is related to my computer and its set up. I will re-install the tool involved and update to the latest version of Syncfusion et al. 
 
I will come back to You with the outcome of the above asap. 
 
All the very best, 
Dennis M Wallentin 


Pon Geetha A J [Syncfusion]
Replied On March 3, 2017 06:59 AM

Hi again, 
I'm very happy as I finally managed to resolve the issue! 
Cannot thank You enough for Your input. 
 
Actually, it turned out to be two issues related. By updating to the latest version of Syncfusion's xlsIO files and by removing any references to two system XML-libraries from the project did do it. 
 
Now I can move on to finalize the solutions thanks to Your input. 
 
Many thanks and enjoy the weekend, 
Dennis  


Abirami Varadharajan [Syncfusion]
Replied On March 6, 2017 01:23 AM

Hi Dennis, 
 
Thank you for updating us. 
 
We are glad that the issue is resolved at your end. Please let us know if you need any further assistance. 
 
Regards, 
Abirami. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;