3

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.

Hari
  • 31
  • 1
  • 2
  • Let me try to elaborate. I need to upload an excel sheet, validate it and create an output excel file showing validation errors. All records which pass validation needs to be imported to SQL server. My web server and database servers are different machines. Basically I need to validate and add error messages to input excel and display back to user – Hari Apr 15 '10 at 09:32

6 Answers6

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.

adrianbanks
  • 81,306
  • 22
  • 176
  • 206
  • thanks but any commercial tool is not possible so we would not be able to use this. – Hari Apr 15 '10 at 09:57
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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • thats great. Are there any prerequisites for installing on web server before we can manipulate excel using ADO in absence of MS office installation – Hari Apr 15 '10 at 09:55
  • Do we not have to add reference to Microsoft Excel Object Library to get the features for interacting with Excel? In that case without MS office been installed how will a object library be present? – Hari Apr 15 '10 at 10:14
  • You only need drivers, and the Jet driver(s) will usually suit. The drivers for Jet are available in most installations of Windows. – Fionnuala Apr 15 '10 at 10:32
  • BTW Excel will happily open .CSV files, you might also like to look at http://stackoverflow.com/questions/440892/how-to-output-an-excel-xls-file-from-classic-asp, which is one of several similar posts. – Fionnuala Apr 15 '10 at 11:42
1

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

Thomas Levesque
  • 286,951
  • 70
  • 623
  • 758
  • The Excel documents would be prior 2007 versions so we would not be able to use this Open XML SDK – Hari Apr 15 '10 at 09:58
0

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).

SheetJS
  • 22,470
  • 12
  • 65
  • 75
M.A. Hanin
  • 8,044
  • 33
  • 51
  • THanks for the options but since excel version is prior to 2007 we would not be able to use Open XML and commercial conversion tools cannot be brought due to financial, security constraints. – Hari Apr 15 '10 at 09:59
  • @Hari, FYI, Excel 2003 also supports an XML format for spreadsheets, though it probably isn't Open XML. – M.A. Hanin Apr 15 '10 at 10:14
0

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

Jasper
  • 451
  • 3
  • 7
0

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.

Wolf5
  • 16,600
  • 12
  • 59
  • 58