Please suggest ways to manipulate Excel spreadsheet without using Excel object as web server does not have MS office installed

Question

I would like to know the options to manipulate the Excel spreadsheets using ASP.NET 2.0 without using Excel object as web server does not have MS Office installed.

Answer 1

Newer versions of Excel support XML-based spreadsheet files, which should be easy to manipulate.

Microsoft has lately released the full specifications for all Office binay file formats, but implementing those is a total overkill for your needs.

You can use 3rd party conversion tools to convert the Excel files to a format you're comfortable manipulating (assuming it can be converted back to Excel).

Answer 2

I don't know if you have any control over the excel files, but if you have you can save the files as XML. This way you can use XSLT/XPath to retrieve or transform the data inside the Excelml

Answer 3

You can use the Open XML SDK to manipulate Office 2007 documents

Answer 4

We use SpreadsheetGear in our application for this same reason. It is a .Net library that can read/write/edit Excel files without having Excel installed on the machine. It is a commercial library though, so you will have to buy it - although you can deploy it royalty free, meaning that you only need licences to develop using it, not for each deployment.

Answer 5

As all 3 above has said. You can only do it with Office 2007 excel sheets as those are stored as XML data in a zip file.

For older excel versions, you need 3rd party components.

Answer 6

You can use ADO to read and write to Excel.

strFile = "C:\Docs\TheFile.xls"

Set cn = CreateObject("ADODB.Connection")

With cn
   .Provider = "MSDASQL"
   .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
     "DBQ=" & strFile & "; ReadOnly=False;"
   .Open
End With

strSQL = "SELECT * FROM [Sheet4$]"

Set rs = CreateObject("ADODB.Recordset")
rs.Open strsql, cn, 2, 3

rs.Fields(1)="B"
rs.Fields("Type")="E"
rs.Update

More: http://support.microsoft.com/kb/257819