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.

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 :

- Start the SQL Server Management Studio

- Expand the SQL Server Agent folder on the left, right-click on Jobs and choose New Job.





- In the New Job dialog, give a Name to the job :





- Click on the Steps page from the list on the left. Then click on the New button to create a new step :





- In the New Job Step dialog, give a name to the step, then choose the SQL Server Integration Services Package Package-type from the Type drop-down :





- In the General tab, select File System from the Package source drop-down : (this is a simple case, packages may also come from the SSIS catalog)





- As the Package field at the bottom gets enabled, click on the [...] button and browse the file system for finding Package.dtsx :





- Click OK to quit the New Job Step dialog.

- If you would like to test the running of this job right away, you can click OK again to quit the New Job dialog. In SQL Server Management Studio, the new job is listed :





- If however you are willing to schedule the running of this job, in the New Job dialog, click on the Schedules item and click on New in order to create a new schedule (or select one of the existing schedules if any) :





- In the following we are create a schedule such that the job will run every 5 minutes. Enter a name for the schedule in the Name field. In the Occurs dropdown, choose Daily. Click on Occurs every, enter 5 in the field next to it and choose minutes in the dropdown. Click OK to quit the dialog :





- Click OK to end the New Job dialog and return to the main SQL Server Management Studio dialog.

- After 5 minutes, the first run occurs automatically. You can view the outcome by right-clicking on the job item and choosing View history.





- At any moment, you may run the job by right-clicking on the job item and choosing Start Job.

- The View history action lets you see previous job runs and act upon them :





This concludes a simple job scheduling and barely scratches the surface of what can be done by introducing query-based parameters and so on, for instance you could schedule the packaging and delivering of Excel files to individuals whose names and email addresses are known from a SQL query statement.

- Start SQL Server Business Intelligence development studio.

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





- The project is created and the Business Intelligence development studio shows up :





- If you don't see the Variables window, click on SSIS in the menu bar and choose Variables. This will be used later on.

- The project creates a file called a SSIS package, which is a file with a DTSX suffix. SSIS packages are designed, built, run, debugged and scheduled. Scheduling occurs in the SQL Server Management Studio. Everything else occurs right within the Business Intelligence development studio.

- Drag and drop the Script Task item from the Toolbox window onto the Package main surface area.





- Click elsewhere in the Package main surface area so that the Script Task object is unselected.

- In the Variables window, click the Add Variable icon in order to create a Variable. Name it ExcelFilepath. Note that spaces are not allowed. Change the Data Type value with the dropdown so it becomes a String. Enter c:\Temp\test.xls in the Value column. As expected, this variable will let know the Script Task where to store the Excel file that xlsgen is going to create.





- Start a Windows explorer instance and make sure the C:\Temp folder exists.

- Back to the SQL Server Business Intelligence development studio, double-click on the Script Task object in order to edit it.

- The Script Task Editor dialog shows up :





- Edit the Name so it becomes Generate Excel file.

- Then click on the Script tab on the left





- Click on the ReadOnlyVariables field and enter ExcelFilepath, which is the name of the Variable that was created earlier. We are telling the environment the names of the variables we are going to refer to in the script.

- Click on the Design Script button in order to start the actual source code editor.





- The source code editor window is filled with a template source code that we are going to edit in order to work with xlsgen. But before we do that, we must let know that we are going to use xlsgen by adding it as a .NET reference. So right-click on References in the Project Explorer window on the left and choose Add Reference.





- When you do this, the .NET assemblies dialog shows up and lists the known .NET assemblies. If you can't see Interop.xlsgen in there, you must follow the steps to Setup xlsgen for use in SSIS. And because SSIS caches the list of known .NET assemblies, you must also quit SSIS and start it again once the setup is done.

- Click on Interop.xlsgen, then on the Add button and click OK.

- Interop.xlsgen appears in the References tree in the Project Explorer window.

- Select the whole existing source code in the source code window, and replace it 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.[New](Dts.Variables("ExcelFilepath").Value.ToString) Dim wksht As xlsgen.IXlsWorksheet wksht = wbk.AddWorksheet("Sheet1") wksht.Label(1, 1) = "hello world!" wbk.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject(engine) Dts.TaskResult = Dts.Results.Success End Sub End Class

- This source code creates a trivial Excel file. Notice the use of the ExcelFilepath variable that we created earlier.

- Hit Ctrl+S in order to save the source code.

- At this point, if any part of the source code is underlined, it is because it is a compile error. This must be fixed before going any further.

- Then from the menu bar, click on Debug / Build. This does nothing visually.

- Quit the source code editor. Click OK to quit the Script Task Editor dialog.

- Hit Ctrl+S in order to save the package on the hard drive (or the SSIS catalog).

- Our package is now ready to run. From the menu bar, click on Debug / Start Debugging, or just click on the green triangle in the main toolbar.





- The Script Task should become yellow when the package is running. And then, depending on the outcome, it will become either red, which means there is an error somewhere, and an exception dialog will show up, or it becomes green, which means the package successfully run.





- Regardless how the execution went well or not, you are still in debugging mode and can end it whenever you want by using the menu bar and choosing Debug / Stop debugging.

- If the Script Task was green during execution, you can open up a Windows explorer window and verify that the Excel file exists in the C:\Temp folder.

- The SSIS package is ready.

- You can now create a SQL Agent job from the SQL Server management studio, where this package will be referred to, and schedule it.

A future build will make this automated, but in the meantime, you can always do the following in order to enable the use of xlsgen in SQL Server Integration Services (SSIS) right now.

We assume that the machine has SSIS already installed and xlsgen installed by running the regular installer available from our website.

First of all, you need the .NET Framework SDK (any version) because we will be using 3 tools that are part of it, namely : sn.exe, tlbimp.exe and gacutil.exe. The .NET Framework SDK (any version) is a free download on the Microsoft website.

In order to setup xlsgen for use in SSIS, we need to do the following : create a public/private key pair for signing the .NET assembly, create the .NET assembly with this public/private key pair and finally install the .NET assembly in the global assembly cache, which is a subfolder of /Windows where .NET stores any .NET assembly meant to be loaded on the system arbitrarily.

Open-up a command-line window, and make sure the .NET Framework tools are accessible from the command-line. The easiest way to achieve this is to open the command line window from the Visual Studio program group : there is a shortcut waiting for you there. Otherwise, just add the .NET Framework /bin folder in your PATH.

Next, create a public/private key pair :

sn.exe -k <xlsgen>\keyfile.snk

Next, create the .NET assembly (interop) :

tlbimp.exe <xlsgen>\xlsgen.dll /keyfile:<xlsgen>\keyfile.snk /out:<xlsgen>\Interop.xlsgen.dll

Then, install the .NET assembly in the global assembly cache :

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


When this completes, you can see the .NET assemply in /Windows/assembly.

Also worth noting, if you are integrating xlsgen as part of SSIS 2005, you will also need to make a copy of the .NET assembly in the following folder : c:\windows\Microsoft.NET\Framework\v2.0.xxxxx. The reason why is that the SSIS Business Intelligence script design mode will list the assemblies from that folder as external .NET assemblies that you can add as a reference in your code. There is no such need for any version of SSIS other than 2005.

We are happy to announce that xlsgen can now be used in SQL Server Integration Services (SSIS) as a key component for loading data sources, extract/transforming data sources, or exporting data sources in different ways.

For this to happen, xlsgen must be made available to the SQL Server Integration Services at design time, in the SQL Server Business Intelligence Development Studio, and at run-time in SQL Agent jobs.

What is needed to make this happen is to make a copy of the xlsgen .NET assembly in a location known as the global assembly cache (a folder where the .NET runtime knows it can load assemblies).

This can be done by hand on a computer where the .NET SDK is installed (the version of the .NET SDK does not matter) and we will provide in next build an automated tool for doing this by double-clicking on a Setup file.

This works for all SQL Server Integration Services versions out there (2005, 2008, 2008 R2, 2012, 2014). In version 2005, a copy of the xlsgen .NET assembly must also be made in the following folder (c:\windows\Microsoft.NET\Framework\v2.0.xxxxx).

Once this is done, any Script Task (a concept that is much like VBA macros in spreadsheets) can reference the xlsgen object model, hence take advantage of the entire xlsgen API.

This becomes powerful as Script Tasks can use parameters such as Variables that can be configured in SSIS packages as well as SQL Agent jobs.

Build 3.5.0.78 of xlsgen adds support for Excel 2003 XML files.

The Excel 2003 XML file format is Microsoft Office's first attempt at saving Excel spreadsheets using XML. They retain much of what makes a spreadsheet, cells, formatting, formulas, a number of rich objects, but they lack some others, most notably charts. In 2006, Microsoft came with a much more mature file format, and that was XLSX files. Nevertheless a number of Excel customers still use this Excel 2003 XML file format because it is much richer and capable than say CSV files. Beginning with build 3.5.0.78 xlsgen can now read such file by just passing the filepath (or memory buffer) in the regular Open() method.

Build 3.5.0.77 of xlsgen fixes another problem related to rendering custom number formats, particularly accounting symbols.

Build 3.5.0.76 of xlsgen makes it possible to copy an existing page setup object to another. It means that with one line of code, you can copy all of the sheet print options including : page orientation, scale, gridlines, margins, print area, rows/columns to repeat, etc.

It works like this :

worksheet.PageSetup.CopyTo(destinationWorksheet.PageSetup)

Build 3.5.0.75 of xlsgen continues the previous build and adds two more fixes :

- support for rendering Accounting symbols : this allows to left align currency symbols and right align numbers in the same cell.

- better rendering of text fragments using the "Arial Narrow" font in PDF files.

Build 3.5.0.74 of xlsgen is a fix pack related improving the rendering of custom number formats :

- better handling of aligment sequences of the form _* in number formats (an underscore is an escape character followed by a character whose width is supposed to be rendered in space)

- better handling of left-prefixed currency symbols

- better accuracy in rendering row heights in cells

The impacted features are print, print preview, PDF, XPS and HTML.

Build 3.5.0.73 of xlsgen makes a number of improvements in speed for working with XLSX files.

Included is :
- Faster reading of depending parts when there are many depending parts (for instance many external references in spreadsheets).
- Faster opening in Excel of files created/updated by xlsgen by writing additional metadata in the file such as spreadsheet dimensions and row spans.
- Automatic removal of useless cells, whose number may be arbitrary (and a consequence of Excel creating orphans) which lowers the file size as well as the reopening speed in Excel
- Removal of rows referencing useless cells. Again the amount being removed is arbitrary and can mount to millions.
- Better handling of out of memory scenarios (2GB is the maximum working set per process on WIN32 systems)

Build 3.5.0.72 of xlsgen makes sure that no out-of-range internal indexes remain as such in XLSX files when they are read and written back. This automatic fix allows to remove one reason why files like this may or may not be seen as corrupt next time they are open in Excel (depending on the version of Excel you are using).

Build 3.5.0.71 of xlsgen ensures that a number of Unicode characters known as surrogates (in the interval [D800-DFFF]) are encoded properly in XLSX files. These are characters that, for some reason, are expected to be of the form _xHHHH_ internally and it is an undocumented behavior. These are often used in Hebrew spreadsheets.

Build 3.5.0.70 of xlsgen ensures that the ratio in width between the sheet tabs and the horizontal scrollbar in Excel is preserved. This is a figure saved by Excel inside the file, and was ignored by xlsgen until now.

Build 3.5.0.69 of xlsgen improves the memory performance for working with XLSX files.

The reading phase is made more optimal by unzipping chunks of internal parts instead of whole parts. Working with whole parts proves not scalable for any large file such as the ones coming from the US open data government website. So this is the reason why we are not doing it anymore.

This improvement avoids "not enough on-board memory" scenarios for running such XLSX scenarios. We think any of our customers running large XLSX files will be happy of the improvement.

Build 3.5.0.68 of xlsgen makes sure we are able to open properly a class of XLSX files generated by third parties, where streams are encoded as UTF16 (which is perfectly legal according to the OPC framework governing the storage of XLSX files).

Build 3.5.0.67 of xlsgen fixes a problem related to updating formulas when deleting rows of a XLSX files. The problem is for XLSX files only.

An old XLS file related line of code was being run and this would cause a column offset in ranges referenced by formulas.

Build 3.5.0.66 of xlsgen adds support for using Excel files from internet.

Because Excel files are not always sitting on hard drives, xlsgen must provide a mechanism for downloading Excel files. What we simply do is allow URLs to be passed in the Open() method call. Here is an example :

// open an Excel file identified by its URL
IXlsWorkbook workbook = engine.Open( "http://xlsgen.arstdesign.com/core/Book1_chart20.xls", "" );
//...
workbook.Close();

Build 3.5.0.65 of xlsgen improves the speed of working with XLSX/XLSM/XLST files by 25%.

Specifically, code branching is made simpler when reading and parsing such files. And additional pre-allocations avoid time lost from moving memory.

This opens up the ability to work with larger files. Hopefully, more optimizations are coming.