none
XML and Excel RRS feed

  • Question

  • Hi,

    This is first time for me to dealing with Excel and XML together. I have excel sheet where information resided. We have a application which uses the XML file to arrange the data on webpage. I want to fetch the Excel sheet information from my XML file. Could anyone tell me how could I do it?

    If datasource is SQL then it would have been simple for me to write Stored Procedure and/or SQL query and defined it in XML file inside CDATA section like below:

    <Command DataSource="dsource">
        <Text>
          <![CDATA[   
               SELECT TOP  10 date AS mydate FROM mytable 
         ]]>
        </Text>
    </Command>

    <Command DataSource="dsource2" >
        <Text>
          <![CDATA[
        DECLARE @a varchar(100)
       DECLARE @b [datetime]   
        EXEC [dbo].[mySP] @a, @b

        ]]>
        </Text>



    The above datasource is defined in another XML file along with appropriate connection string. Our application intelligent enough to make the connection to the required server, execute the SQL query or stored procedure, and uses the results further in XML.

    I am stuck in just getting the information from Excel file using queries that can be written in XML file like given above. We can specify required datasource is our datasource XML file. The excel sheet is available in network folder.

    Please feel free to let me know if you have any questions. Any help would be much appreciated.

    Thanks.
    Tuesday, August 31, 2010 3:40 PM

Answers

  • Hi CSharpLeader

    It's still not clear exactly what you're looking for. The end result, yes. But not the technologies you'd be able to use. And you still haven't said whihc versions of Excel (file formats) would be involved. That plays a significant role in the approaches available to you. Without that information, it's difficult to even begin proposing an approach.

    Obviously, Excel is not SQL Server and cannot provide a query interface or stored procedures.

    Excel files can be accessed via OLE DB; they can be worked with via automation (interop), although you shouldn't try it in a server environment; or the files can be processed directly (parsed), especially for the OpenXML file format (2007 and later).

    If we're talking OLE DB, you need to think in database terms. I assume you can uniquely identify the fields (columns). How about the records (rows)?


    Cindy Meister, VSTO/Word MVP
    Thursday, September 2, 2010 2:16 PM

All replies

  • Hi CSharpLeader

    Do I understand correctly, that your application reads the connection information then uses that with ADO.NET to access the data?

    So what you're looking for, basically, is the syntax for an OLE DB connection and the query language to use with the data source, once the connection is open?


    Cindy Meister, VSTO/Word MVP
    Tuesday, August 31, 2010 4:09 PM
  • Our application only supports SQL and XML as a data provider so I am not sure if I can use ADO.NET? For SQL, I just need to define the connection string and need to write query or execute the stored procedure. Rest of things taken care by the application.

    Regards.

    Wednesday, September 1, 2010 7:52 AM
  • Our application only supports SQL and XML as a data provider so I am not sure if I can use ADO.NET? For SQL, I just need to define the connection string and need to write query or execute the stored procedure. Rest of things taken care by the application.


    Hi CShaprpLeader

    So, I'm not sure what kind of help you're looking for, exactly? Can you elaborate on what it is you expect/hope to learn and use?

    What version of Excel (file format: .xls, xlsx, xlsm...) are you targeting?


    Cindy Meister, VSTO/Word MVP
    Wednesday, September 1, 2010 12:58 PM
  • <<<<

    Do I understand correctly, that your application reads the connection information then uses that with ADO.NET to access the data?

    So what you're looking for, basically, is the syntax for an OLE DB connection and the query language to use with the data source, once the connection is open?

    >>>>

    Sorry for the confusion. If this could done that also be fine. But the thing is I want to retrieve only specific information from excel file. For example say C10 to C30, D10 to D30, and E10 to E30.

    Thanks.

    Thursday, September 2, 2010 8:20 AM
  • Hi CSharpLeader

    It's still not clear exactly what you're looking for. The end result, yes. But not the technologies you'd be able to use. And you still haven't said whihc versions of Excel (file formats) would be involved. That plays a significant role in the approaches available to you. Without that information, it's difficult to even begin proposing an approach.

    Obviously, Excel is not SQL Server and cannot provide a query interface or stored procedures.

    Excel files can be accessed via OLE DB; they can be worked with via automation (interop), although you shouldn't try it in a server environment; or the files can be processed directly (parsed), especially for the OpenXML file format (2007 and later).

    If we're talking OLE DB, you need to think in database terms. I assume you can uniquely identify the fields (columns). How about the records (rows)?


    Cindy Meister, VSTO/Word MVP
    Thursday, September 2, 2010 2:16 PM
  • Okay. Thanks Cindy for followup. I have found the alternate solution.

    Thanks.

    Friday, September 3, 2010 8:07 AM