Share via

Excel interopability architecture

Adam Newcombe 156 Reputation points
2021-06-05T10:24:50.863+00:00

In summary we use SQL Server databases, vb.net and Excel as core applications.

Data connections between SQL Server and Excel work well and .net Interop with Excel works well when pulling a process together i.e. Call sql stored proc, generate data, open excel file, refresh data connection, pivots etc etc.

My query is concerning the best physical architecture to run this on. Excel needs to be installed and ideally I would like to deploy onto a windows server, as a central location to run schedules to refresh and distribute the information.

Developer technologies VB
SQL Server Other

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Karen Payne MVP 35,586 Reputation points Volunteer Moderator
    2021-06-06T01:08:41.89+00:00

    Rather than use the native Excel libraries consider writing to Excel via Open Office XML, not pretty or easy but does not require Excel to be installed.

    Another option is to use a wrapper library which uses Open Office XML like SpreadSheetLight which is free or a paid for library from Gembox or Aspose.

    Our team builds web and server base solutions, started with Open Office XML then moved to GemBox as it's much easier than writing Open Office XML. Don't have any simple code sample for Open Office XML, we did away with that code.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.