Help required Alternative way to export to Excel from a datatable without the MS-office Excel installed on the server
HI All,
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.E
Excel.Application xlApp ;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet1;
Excel.Worksheet xlWorkSheet2;
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.Cells.Columns
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.
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.E
Excel.Application xlApp ;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet1;
Excel.Worksheet xlWorkSheet2;
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.Cells.Columns
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rainer!! giving me an idea of OpenXML . I found a nice article at below link I was able to use it without having Office Excel installed on Server.
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
not sure if this will be appropriate for you, but perhaps look at www.aspose.com (aspose.cells in particular) they have libraries for .net and java that provide server based capabilities that might do what you need.
I haven't used their excel libraries but have used their word library (with java) and it worked for me extremely well on both windows and linux servers - but my needs were very different to what you describe here.