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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,662 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,713 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Karen Payne MVP 35,401 Reputation points
    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.