Help required Alternative way to export to Excel from a datatable without the MS-office Excel installed on the server
Posted on 2013-05-29
One of my requirement was to create a automated job which needs to be placed in the scheduled task of the Server.
I have written the code on Windows Form Load job performs below activities.
1. Calls a 2 stored procedure (MS-SQL)
2. Stores data in couple of datatables
3. This 2 datatables are passed to function which exports to excel and stores in a path.
I have used "using Excel = Microsoft.Office.Interop.Excel"
Excel.Application xlApp ;
Where I am storing the output in a workbook with 2 sheets (2 tabs). I have also used some of the Excel features like.
xlWorkSheet1.Name = "Completed_Projects";
Now everything is working as expected in my local system. When I ran the exe on test server I found that MS-Office Excel needs to be installed on the server to use this Excel object features.
Server Team are not ready to install the MS-Office on server nature of Office it presents additional attack vectors that potentially risk their security and Office may exhibit unstable behavior and/or deadlock when Office is run in server environment.
Could someone let me know alternative way used Export to Excel (with datatable\datagridview as source) and save in particular path?
I need to have features like Auto Alignment of column width, able to store two worksheet in single workbook, name the worksheet.