How to convert Excel file into xml document and read the data from that document using c# .net? : The Official Microsoft ASP.NET Forums

Reply

Sivagurunath...

0 Points

2 Posts

How to convert Excel file into xml document and read the data from that document using c# .net?

Jul 17, 2009 11:28 AM|LINK

Hi Friends,

I am siva from chennai.

I have excel sheet and this will be converted into xml document by saving excel as filename.xml.

I want to read the data from converted xml file but it contains lot of tags like

<Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="2144" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:StyleID="s24" ss:AutoFitWidth="0" ss:Width="61.5"/>
   <Column ss:StyleID="s25" ss:Width="180.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="93.75"/>
   <Column ss:StyleID="s30" ss:AutoFitWidth="0" ss:Width="84"/>
   <Column ss:StyleID="s23" ss:Width="93.75"/>
   <Column ss:StyleID="s23" ss:Width="90.75"/>
   <Column ss:StyleID="s23" ss:AutoFitWidth="0" ss:Width="78.75"/>
   <Column ss:StyleID="s23" ss:Width="93.75" ss:Span="1"/>
   <Column ss:Index="10" ss:AutoFitWidth="0" ss:Width="50.25"/>
   <Row>
    <Cell ss:StyleID="s31"><Data ss:Type="String">ID</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Name</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MgrID</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Budget_USD</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s34"><Data ss:Type="String">BBudget_USD</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">SBudget_USD</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">IBudget_CHF</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">BBudget_CHF</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">SBudget_CHF</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
   </Row>
   <Row ss:Height="13.5">
    <Cell ss:StyleID="s22"><Data ss:Type="Number">111644</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s35"><Data ss:Type="String">Cheong-Lee Shin</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="Number">99999</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">2375806.1449999991</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="Number">400000</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">62500</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s22" ss:Formula="=(RC[-3]*1.08254)"><Data ss:Type="Number">2571905.184208299</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s22" ss:Formula="=(RC[-3]*1.08254)"><Data ss:Type="Number">433016</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:StyleID="s22" ss:Formula="=(RC[-3]*1.08254)"><Data ss:Type="Number">67658.75</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
   </Row>
</Table>

How can i get the data using ASP .NET with C# .Net

Thanks in advance

Thanks & Regards,
Siva

Moved by XIII on Jul 17, 2009 04:53 PM

Reply

Martin_Honne...

Star

14481 Points

2006 Posts

MVP

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 17, 2009 05:57 PM|LINK

Which version of .NET do you use? With .NET 3.5 you can use LINQ to XML to extract data from XML documents. With earlier versions you can use XPathDocument/XPathNavigator if you only want to extract data or XmlDocument if you also need to change data.

Martin Honnen --- MVP Data Platform Development
My blog

Marked as answer by decker dong - msft on Jul 24, 2009 09:34 AM

Reply

Martin_Honne...

Star

14481 Points

2006 Posts

MVP

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 17, 2009 06:15 PM|LINK

Here is some LINQ to XML example:

XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
            XDocument sheet = XDocument.Load(@"map1.xml");
            foreach (XElement worksheet in sheet.Root.Elements(ss + "Worksheet"))
            {
                Console.WriteLine("Worksheet: {0}", worksheet.Attribute(ss + "Name").Value);
                foreach (XElement row in worksheet.Element(ss + "Table").Elements(ss + "Row"))
                {
                    foreach (XElement cell in row.Elements(ss + "Cell"))
                    {
                        Console.Write("{0}\t", cell.Value);
                    }
                    Console.WriteLine();
                }
                Console.WriteLine();
            }

Works for me against XML saved with Excel 2003.

Martin Honnen --- MVP Data Platform Development
My blog

Marked as answer by decker dong - msft on Jul 24, 2009 09:34 AM

Reply

kavita_khand...

Star

9767 Points

1930 Posts

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 17, 2009 08:14 PM|LINK

well i think u have just manually..saved an Excel file to xml..thts why it gives wierd out put like this.. i would surely suggest u to use XSLT transfomation over this xml..but u can hardly find any meaningful data in here... well have u thot of generateing xml file from Excel from code behind? It will make ur life easier...

Please mark this post as Answer if it is of help to you!
I would love to change the world, but they wont give me the source code.

Reply

Sivagurunath...

0 Points

2 Posts

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 18, 2009 07:33 AM|LINK

yes i am manually converting the excel file to xml.

already we have excel to xml convertion in the code behind.

its having some problem. excel.exe seesion does not closed in the asp .net worker processafter that we cant able to load any other excel file so that only we are directly loading xml instead of excel

Thanks & Regards,
Siva

Reply

kavita_khand...

Star

9767 Points

1930 Posts

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 18, 2009 05:32 PM|LINK

Sivagurunathan

yes i am manually converting the excel file to xml.

already we have excel to xml convertion in the code behind.

its having some problem. excel.exe seesion does not closed in the asp .net worker processafter that we cant able to load any other excel file so that only we are directly loading xml instead of excel

So wud u like to use XSLT over this xml? or u can go for the solution that Martin has provided.

Please mark this post as Answer if it is of help to you!
I would love to change the world, but they wont give me the source code.

Reply

manoj.tripat...

Member

2 Points

1 Post

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 29, 2009 07:18 AM|LINK

Hi Martin,

thanks for the useful code. Can u please provide me more example where i can read full row & modify each Cell's Data child node using Employee class object.

class Employee

{

public int caseNo;

public string name;

public string jobTitle;

public DateTime dateOfInjury;

}

<Row>
    <Cell ss:StyleID="s214"><Data ss:Type="int">1</Data></Cell>
    <Cell ss:StyleID="s215"><Data ss:Type="String">Manoj Tripathi</Data></Cell>
    <Cell ss:StyleID="s216"><Data ss:Type="String">Engineer</Data></Cell>
    <Cell ss:StyleID="s217"><Data ss:Type="DateTime">07/23/2009</Data></Cell>

   </Row>

i have converted Excel file (.xls) into .xml file.

Reply

shree_ars

Participant

1395 Points

776 Posts

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Jul 18, 2011 12:14 PM|LINK

how to convert excel document to xmldocuments.

actually i need to read excel file as XML document and that xml document i need to load into datatable.

xml document to datatable i can do it by readxml....

but how to make excel to xml document?

plz help me

If you win, you need not have to explain...If you lose, you should not be there to explain!

How to convert Excel file into xml document and read the data from that document using c# .net? RSS

7 replies

Sivagurunath...

How to convert Excel file into xml document and read the data from that document using c# .net?

Martin_Honne...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Martin_Honne...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

kavita_khand...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Sivagurunath...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

kavita_khand...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

Sivagurunathan

manoj.tripat...

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

shree_ars

Re: How to convert Excel file into xml document and read the data from that document using c# .ne...

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft