3 Replies - 2469 Views - Last Post: 01 November 2010 - 07:35 AM Rate Topic: -----

#1 Lennie   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-May 07

C#NET2008 Transfer XML Data to EXCEL 2003 Spreadsheet

Posted 01 November 2010 - 12:43 AM

Hullo Good Guys,
Need your hekp. Please help me.
Being requested to develop C#NET2008 Window application with functionality to transfer XML Data to EXCEL 2003 Speadsheet.

I have not done that before and doesn't know who to start coding.
Please share with me sample coding for that and also WEB SITE Tutorial URL Address so that I can learn something new.

Here are the XML Data Structure:
<?xml version="1.0" encoding="utf-8"?>
<!--File Created on 25/10/2010 8:58:51 p.m.-->
<table>
    <row>
	<CustomerID>FRANK</CustomerID>
	<OrderID>10267</OrderID>
	<OrderDate>29/07/1996</OrderDate>
	<RequiredDate>26/08/1996</RequiredDate>
	<ShippedDate>06/08/1996</ShippedDate>
	<Freight>208.58</Freight>
     </row>
     <row>
	<CustomerID>FRANK</CustomerID>
	<OrderID>10337</OrderID>
	<OrderDate>24/10/1996</OrderDate>
	<RequiredDate>21/11/1996</RequiredDate>
	<ShippedDate>29/10/1996</ShippedDate>
	<Freight>108.26</Freight>
     </row>
     <row>
	<CustomerID>FRANK</CustomerID>
	<OrderID>10342</OrderID>
	<OrderDate>30/10/1996</OrderDate>
	<RequiredDate>13/11/1996</RequiredDate>
	<ShippedDate>04/11/1996</ShippedDate>
	<Freight>54.83</Freight>
     </row>
     <row>
	<CustomerID>FRANK</CustomerID>
	<OrderID>10396</OrderID>
	<OrderDate>27/12/1996</OrderDate>
	<RequiredDate>10/01/1997</RequiredDate>
	<ShippedDate>06/01/1997</ShippedDate>
	<Freight>135.35</Freight>
     </row>
     <row>
	<CustomerID>FRANK</CustomerID>
	<OrderID>10488</OrderID>
	<OrderDate>27/03/1997</OrderDate>
	<RequiredDate>24/04/1997</RequiredDate>
	<ShippedDate>02/04/1997</ShippedDate>
	<Freight>4.93</Freight>
    </row>
</table>



Thank You.

Cheers,
Sam Hiller

MOD EDIT: When posting code...USE CODE TAGS!!! Even XML!

:code:

This post has been edited by JackOfAllTrades: 01 November 2010 - 03:45 AM


Is This A Good Question/Topic? 0
  • +

Replies To: C#NET2008 Transfer XML Data to EXCEL 2003 Spreadsheet

#2 ragingben   User is offline

  • D.I.C Addict
  • member icon

Reputation: 177
  • View blog
  • Posts: 646
  • Joined: 07-October 08

Re: C#NET2008 Transfer XML Data to EXCEL 2003 Spreadsheet

Posted 01 November 2010 - 07:01 AM

I would have though quite a simple way of doing this would be to create a .csv file from your xml data. I'm not sure if this fits your criteria, but it defiently wouldn't be overly hard to do, and this can open in Excel.

You could do this by:
1. Itterate each row tag.
2. For each ro tag itterate each property and add as a colum (CustomerID, OrderID etc) to the row

This will give you data that would look like...

FRANK, 10267, 29/07/1996, 28/06/1996, 06/08/1996, 208.58
FRANK, 10337, 24/10/1996, 21/11/1996, 29/10/1996, 108.26

...which when opened in excel would be put into coloumns.

If you google excel and C# you will probally find there is some kind of managed support for reading/writing/using excel files in .Net
Was This Post Helpful? 0
  • +
  • -

#3 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: C#NET2008 Transfer XML Data to EXCEL 2003 Spreadsheet

Posted 01 November 2010 - 07:05 AM

VSTO (Visual Studio Tools for Office) Developer Center
Was This Post Helpful? 0
  • +
  • -

#4 fixo   User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: C#NET2008 Transfer XML Data to EXCEL 2003 Spreadsheet

Posted 01 November 2010 - 07:35 AM

Give this a shot
(tested on Excel2007 only
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Reflection;
using System.Runtime.InteropServices;
using System.IO;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Threading;
using System.Globalization;
---------------------------------------

        private void button3_Click(object sender, EventArgs e)
        {
            //http://support.microsoft.com/kb/306023/
            //http://support.microsoft.com/kb/302084/EN-US/
         // http://www.c-sharpcorner.com/UploadFile/saj/XMLDataIntegration04212005214701PM/XMLDataIntegration.aspx
            //' This line is very important!
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-EN");//<--change on what you need
            Excel.Application m_objExcel;
            Excel.Workbooks m_objBooks;
            Excel._Workbook m_objBook;
            Excel.Sheets m_objSheets;
            Excel._Worksheet m_objSheet;
            Excel.Range m_objRange;
            Excel.XmlMap xmlmap;
            string xlFileName = @"C:\UsedFilEs\Points.xls";//<--change file name here
            m_objExcel = new Excel.Application();
            try
            {
                // just for get xmlschema string
                DataSet ds = new DataSet();
                ds.ReadXmlSchema(@"C:\UsedFilEs\datatable.xml");//<--change file name here
                string schema = ds.GetXmlSchema();
                object m_objOpt = Missing.Value;
                
                m_objExcel.Visible = true;
                m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
               
               m_objBooks.Open(xlFileName, true, false, m_objOpt, "", m_objOpt, false, m_objOpt, m_objOpt, true, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                
                m_objBook = m_objExcel.ActiveWorkbook;
                m_objSheets = m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

                m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(2);
                m_objSheet.Activate();
                m_objRange = (Excel.Range)m_objSheet.get_Range("A20", m_objOpt);

                //to bypass error:
                try
                {
                    m_objBook.XmlMaps.get_Item("Table").Delete();//<--change dummy name "Table" here on what you need
                }
                catch { }


               xmlmap= m_objBook.XmlMaps.Add(schema, m_objOpt);
               xmlmap.Name = "Table";
                 //string strSchemaXML = m_objBook.XmlMaps["Table"].Schemas[1].XML;


                 m_objBook.XmlImport(@"C:\UsedFilEs\datatable.xml", out xmlmap, true, (object)m_objRange);//<--change file name here


                ds = null;
              
                // Save theExcel file in the typical workbook format and quit  Excel .
                m_objBook.SaveAs(xlFileName, Excel.XlFileFormat.xlWorkbookNormal,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
                m_objExcel.Quit();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(m_objExcel);
            }
        }

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1