Excel Automation in SalesLogix Web 7.5.4
Posted By: Alex.Cottner on August 1st, 2012 in General
No Gravatar

I’ve had a few projects now where clients wanted to be able to click a button, and have some excel automation happen. In the days of the Windows application, this was easy. We didn’t have to worry about permissions on the client and we had direct access to the database. But in a web application we don’t have these luxuries. So what do we do?

Well first, we need to get the data into JavaScript… somehow. The easiest way to do that is using JSON. If your data could easily be pulled from an SData query, that’s probably the easiest thing to do. If not, just create the JSON string in C# and pass it into your javascript function by registering a startup script. Something like this will work.
String script = "populateExcel('" + json + "');";
ScriptManager.RegisterStartupScript(this, GetType(), Guid.NewGuid().ToString(), script, true);

That wasn’t so difficult. But now we actually need to use JavaScript to do the Excel automation. The normal way to do this is to declare an ActiveX object and go. Unfortunately, doing this will require the end user having some IE settings changed. This isn’t desirable. Instead, we can use the SLX Web Enhancements to do some work for us. This way the user doesn’t have to change any security settings.

var excelApp = null;
if (Sage.gears) {
excelApp = Sage.MailMergeService.prototype.NewActiveXObject("Excel.Application");
}
if (!excelApp)
{
alert('Please install the SLX Desktop Enhancements.');
return;
}

Now we have an excel object… how do we use it? Pretty much the same way as in the Windows application.

excelApp.Workbooks.Open("URL_To_Excel_File_Goes_Here");
excelApp.Visibule = true;
excelApp.UserControl = true;
var sheet1 = excelApp.Workbooks(1).Sheets(1);
var sheet2 = excelApp.Workbooks(1).Sheets(2);
sheet1.Cells(1, 2).Value = "blah blah";
sheet2.Cells(3, 2).Value = "data data";
etc...

Note: This isn’t a step by step guide. If it was, it would be way longer because of some dependencies (ie: the web service to return the excel file, encoding and decoding JSON strings, etc). All of this other information is easily found with some Google searches.


Leave a Reply