Please suggest ways to manipulate Excel spreadshee

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 binary 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

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 3

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

EDIT: I see that MSDASQL is deprecated by Microsoft, so these are better connection strings.

scn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFile & ";" & _
        "Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=No"""

cn.Open scn

If HDR is set to No, it is possible to overwrite existing headers using either the position counting from zero, or the default field (column) name, which is F1, F2 ... Fn counting from the first column of the selection range. If HDR is set to yes, then field (column) names are the first row. Invalid names will be set to the default name.

Mode is an enum, so you could have Mode=Share Deny None; or Mode=Read|Share Deny Read|Share Deny Write;, for example.

You can optionally include in the connection string:

MaxScanRows=0;
IMEX=0;
Persist Security Info=False;

Setting MaxScanRows and IMEX will only be of much use if you tamper with the registry.

Possible values of IMEX are:

    0 is Export mode
    1 is Import mode
    2 is Linked mode (full update capabilities)

-- http://support.microsoft.com/kb/194124

Answer 4

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 5

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 6

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