Solved

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
3
499 Views
Last Modified: 2013-06-01
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.Excel"
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.AutoFit();
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.
0
Comment
Question by:anbu210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39206615
mmmmmm
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.
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39207165
Hi,
there are various options - either commercial ones (with less coding) or free ones (with more coding).
As PortletPaul mentioned, Aspose is one of the best in regards to commercial solutions, alternatives are (not a complete list ;-) )
http://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx
http://www.infragistics.com/products/aspnet/excel-exporter/
http://www.componentone.com/SuperProducts/SpreadWin/

For free you can use the Office XML SDK (ONLY FOR XSLX format!!!)
http://msdn.microsoft.com/en-us/library/office/hh180830%28v=office.14%29.aspx

Sample:
http://stackoverflow.com/questions/11811143/export-datatable-to-excel-with-open-xml-sdk-in-c-sharp

HTH
Rainer
0
 

Author Closing Comment

by:anbu210
ID: 39212801
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question