XEROF

 

xlsgen 3.5 build #79 : Strong-named .NET assembly


Build 3.5.0.79 of xlsgen includes the interop .NET assembly (Interop.xlsgen.dll) strong-named from now on. You can find it in the samples folder.

What is strong-named for? What this allows is install this .NET assembly in the global assembly cache should the need arise. For instance we have been using it lately for use in Sql Server Integration Services scenarios. Custom SSIS packages make use of custom .NET assemblies provided they can be found arbitrarily on the system, and that's when the global assembly cache comes into play.

Just for reference, if you would like to install Interop.xlsgen.dll in the global assembly cache, all you have to do is bring a command line window and type :

gacutil.exe /i <xlsgen install path>\Interop.xlsgen.dll

The interop assembly is the same whether you are using the 32-bit or the 64-bit version of xlsgen.dll. The reason is that an interop assembly, by definition, is just MSIL, not target code.

Posted on 15-December-2014 22:23 | Category: xlsgen, Excel generator | comment[0] | trackback[0]

 

Sample : SSIS package for each loop container and exporting to PDF files


One simple thing that xlsgen can do in your Sql Server Integration Services (SSIS) package is convert files. In the SSIS package we are creating, we iterate over a folder of arbitrary Excel files and convert them as PDF files. This SSIS package could be part of a larger package where, after PDF files are created, they are sent by email to a list of known recipients.


- Start SQL Server Business Intelligence development studio.

- Select File / New / Project, pick Integration Services Project and click OK.




- From the Toolbox, drag and drop the Foreach Loop Container item onto the main work surface :




What the Foreach Loop Container does for us is allow to iterate over a collection of objects, from files or from a database, then pass each such object name down to a data flow object for transformation purposes. What we need to do then is to configure the Foreach Loop Container, actually define the root filepath where our Excel files are stored.

- In the C:\temp folder, we have a bunch of Excel files standing there :




- Double-click on the Foreach Loop Container, click on the Collection tab on the left and make sure that in front of the Enumerator drop down, it reads Foreach File Enumerator, which means this container will enumerate files in a folder (and sub-folders) that is going to be set right away.

- Click on the Browse button and navigate towards C:\temp and click OK. Back in the Foreach Loop Container dialog, edit the Files edit box so that it filters out anything other than *.xls files :




- Before we are done, we must associate a variable for passing the object identifier, in this case an Excel filepath, so it can be passed to tasks and components for processing. Click on the Variable mappings tab on the left, then even though it is not obvious, click on the white cell below the Variable header so that a drop down appears and lets you select New Variable....




- Click on it and then the Add Variable dialog appears. Make sure the Name of the variable is ExcelFilepaths and click OK.




- The variable name User::ExcelFilepaths appears in the list of variables. Click OK.




- The rest of the setup is a matter of creating a new Script Task and making sure that this script task creates an instance of xlsgen and then uses the User::ExcelFilepaths variable to obtain the current Excel file being processed and then exporting to a PDF file in the C:\temp folder. So in a nutshell, drag and drop a Script Task item from the Toolbox into the Foreach Loop Container :




- Double-click on the Script task being selected. In the Script Task Editor dialog, click on the Script tab on the left, then edit the ReadOnlyVariables box so that our Excel::UserFilepaths is referred to :




- Click on the Design Script... button to enter the script editor and then replace the template source code with the following :



VB.NET code


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports xlsgen = Interop.xlsgen

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim engine As xlsgen.CoXlsEngine = New xlsgen.CoXlsEngine

Dim wbk As xlsgen.IXlsWorkbook
wbk = engine.Open(Dts.Variables("User::ExcelFilepaths").Value.ToString, _
Dts.Variables("User::ExcelFilepaths").Value.ToString & ".pdf")
wbk.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(engine)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class



- Before you quit the script editor, we must let SSIS know of xlsgen as a component. Right-click on References on the left and choose Add Reference....

- In the Add Reference dialog, click on Interop.xlsgen, click on the Add button and click OK. If you don't see Interop.xlsgen in the list of .NET assemblies, read our Setup.

- As you do this, all compile errors originating from xlsgen being unknown as a component are resolved, click on File / Save and then quit the script editor.

- Click OK which gets you back to the main SQL Server window.

- We are now ready to run. Just click on Debug / Start without debugging. The items become yellow and then green when the package execution is complete :




- Just open-up the C:\temp folder, and the PDF files are there if all went well :




Posted on 15-December-2014 21:56 | Category: xlsgen, Excel generator | comment[0] | trackback[0]

 

 

<-- previous page

< December >
0102030405
0607080910
1112131415
1617181920
2122232425
2627282930
31



 

 

This site
Home
Articles

DevTools
CPU-Z
EditPlus
ExplorerXP
Kill.exe
OllyDbg
DependencyWalker
Process Explorer
autoruns.exe
Araxis
COM Trace injection
CodeStats
NetBrute
FileMon/Regmon
BoundsChecker
AQTime profiler
Source monitor
GDI leaks tracking
Rootkit revealer
Rootkit removal
RunAsLimitedUser(1)
RunAsLimitedUser(2)

 

 

Liens
Le Plan B
Un jour à Paris
Meneame
Rezo.net (aggr)
Reseau voltaire
Cuba solidarity project
Le grand soir
L'autre journal
Le courrier suisse
L'Orient, le jour
Agoravox (aggr)