Excel Automation for Business Solution and Server-Side Processing

Question

Hi,

Our company has thousands of Excel files, where each Excel file describes one IPVPN network. This Excel files follow single format, which keep data on contact information, site detail, router specifications, primary/backup link specific details, VRF etc.

From time to time, the format change, and we have to dedicate few person to manually transfer data from one format to another.

I hope to get advices on how to automate this process. Jargons are not feared. Better if you could give step-by-step high level approach to solving this problem.

In addition, one specific question:

If the Excel worksheet has a mix of data, can XML Mapping work? The worksheet does not contain a single nice table but few rowspans and colspans here and there.

Thanks.

Answer 1

Use powershell. I'm not near a box that has powershell installed (it comes with Win7, have to install it for XP, Vista, Server....), but it'd be something like this (stolen from the first msdn blog i found:
http://blogs.technet.com/heyscriptingguy/archive/2009/01/13/how-do-i-run-an-office-excel-macro-on-multiple-workbooks.aspx:

$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm -Recurse
Foreach($file in $excelFiles)
{
 $workbook = $excel.workbooks.open($file.fullname)
 $worksheet = $workbook.worksheets.item(1)
 $excel.Run("CreateChart")
 $workbook.save()
 $workbook.close()
}
$excel.quit()

Answer 2

You do not mention in what way the format changes, but it is not difficult to update a succession of workbooks obtained from various directories (FileSystemObject will get the files) using VBA. If it is a matter of manipulating columns, ADO may be the best bet.

Answer 3

Here is what Microsoft says about using COM to automate Excel on a server (see this article):

"Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment."

SpreadsheetGear for .NET has an API which is similar to Excel. You can see some live ASP.NET samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC