Export Datagridview To XML File C#

Question

I have a requirement where I need to export data from excel file that import to a 'datagridview' to a specified format 'xml' firstly I fill to datagrid from excel and save as Xml ,folowing this code

        namespace SAMPLE_
        {


            public partial class Form1 : Form
            {
                public Form1()
                {
                    InitializeComponent();

                }

Here I am using Microsoft Office Interop Excel to import Excel file:

private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorlSheet;
            Microsoft.Office.Interop.Excel.Range xlRange;

            int xlRow;
            string strfileName;

            openFilalog1.Filter = "Excel Office | *.xls; *.xlsx";
            openFilalog1.ShowDialog();
            strfileName = openFilalog1.FileName;


             if(strfileName != string.Empty)
            {

                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(strfileName);
                xlWorlSheet = xlWorkBook.Worksheets["SHEET1"];
                xlRange = xlWorlSheet.UsedRange;
                int i=0;
                for (xlRow = 2; xlRow <= xlRange.Rows.Count; xlRow++)
                {
                    if(xlRange.Cells[xlRow,2].Text != "")
                    {
                        i++;
                        dataGridView1.Rows.Add(i, xlRange.Cells[xlRow, 1].Text, xlRange.Cells[xlRow, 
                        2].Text,
                        xlRange.Cells[xlRow, 3].Text, xlRange.Cells[xlRow, 4].Text,
                        xlRange.Cells[xlRow, 5].Text, xlRange.Cells[xlRow, 6].Text,
                        xlRange.Cells[xlRow, 7].Text, xlRange.Cells[xlRow, 8].Text,
                        xlRange.Cells[xlRow, 9].Text,xlRange.Cells[xlRow, 10].Text,
                        xlRange.Cells[xlRow, 11].Text,xlRange.Cells[xlRow, 12].Text,
                        xlRange.Cells[xlRow, 13].Text);

                    }

            }
                xlWorkBook.Close();
                xlApp.Quit();

            }

This is the button I use to export:

       private void button1_Click(object sender, EventArgs e)
                    {
                        DataSet ds = (DataSet)dataGridView1.DataSource;
                        SaveFilalog sfd = new SaveFilalog();
                        sfd.Filter = "SHEET1|*.xml";
                        if (sfd.ShowDialog() == DialogResult.OK)
                        {
                            try
                            {
                                ds.Tables[0].WriteXml(sfd.FileName);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex);
                            }
                        }
                    }

                    private void button2_Click(object sender, EventArgs e)
                    {

                    }
                }


            }

I need to know the xml format. Apparently the current write ds.Tables[0].WriteXml(sfd.FileName); is not the correct format. — jdweng, May 05 '20 at 03:16
You have to tell me. You said "to a specified format 'xml". So what is the format? — jdweng, May 05 '20 at 06:53
This is the format that i want on the outpout xml: ` column 1 cell 1 column 2 cell 1 column 3 cell 1 column 4 cell 1 column 5 cell 1 column 6 cell 1 column 7 cell 1 column 8 cell 1 column 9 cell 1 ` — Uzarsef, May 05 '20 at 15:37

jdweng · Accepted Answer · 2020-05-07T06:45:21.410

From a datatable it would be something like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("last", typeof(string));
            dt.Columns.Add("phone", typeof(string));
            dt.Columns.Add("adresse", typeof(string));
            dt.Columns.Add("citie", typeof(string));
            dt.Columns.Add("age", typeof(string));
            dt.Columns.Add("mp", typeof(string));
            dt.Columns.Add("dpa", typeof(string));
            dt.Columns.Add("New A", typeof(string));
            dt.Columns.Add("New B", typeof(string));
            dt.Columns.Add("New C", typeof(string));
            dt.Columns.Add("New D", typeof(string));
            for (xlRow = 2; xlRow <= xlRange.Rows.Count; xlRow++)
            {
                if (xlRange.Cells[xlRow, 2].Text != "")
                {

                    dt.Rows.Add(new object[] {
                        xlRange.Cells[xlRow, 1].Text, 
                        xlRange.Cells[xlRow,2].Text,
                        xlRange.Cells[xlRow, 3].Text, xlRange.Cells[xlRow, 4].Text,
                        xlRange.Cells[xlRow, 5].Text, xlRange.Cells[xlRow, 6].Text,
                        xlRange.Cells[xlRow, 7].Text, xlRange.Cells[xlRow, 8].Text,
                        xlRange.Cells[xlRow, 9].Text, xlRange.Cells[xlRow, 10].Text,
                        xlRange.Cells[xlRow, 11].Text, xlRange.Cells[xlRow, 12].Text,
                        xlRange.Cells[xlRow, 13].Text
                    });

                }
            }
            dataGridView1.DataSource = dt;


            string header = "<?xml version=\"1.0\" encoding=\"utf-8\" ?><Sheet1></Sheet1>";
            XDocument doc = XDocument.Parse(header);
            XElement sheet1 = doc.Root;

            foreach (DataRow row in dt.AsEnumerable())
            {
                XElement rd = new XElement("rd", new object[] {
                    new XElement("id", row["id"]),
                    new XElement("name", row["name"]),
                    new XElement("last", row["last"]),
                    new XElement("phone", row["phone"]),
                    new XElement("refF", new object[] {
                        new XElement("adresse", row["adresse"]),
                        new XElement("citie", row["citie"]),
                    }),
                    new XElement("age", row["age"]),
                    new XElement("mp", new XElement("degree", row["mp"])),
                    new XElement("dpa", row["dpa"])
                });

                sheet1.Add(rd);
            }

            doc.Save(FILENAME);

        }

    }

}

After debugging i found this problem: System.NullReferenceException: The object reference is not defined to an instance of an object. to SAMPLE.Form1.button1_Click (Object sender, EventArgs e) in C: \ Users \ Ysf \ Documents \ Visual Studio 2015 \ Projects \ SAMPLE \ SAMPLE \ Form1.cs: line 129 line 129= ds.Tables[0].WriteXml(save.FileName); — Uzarsef, May 05 '20 at 16:59
A DataGridView does not always have a DataSource. You can load the DGV manually like you did in your code and not have a datasource. So DataSet ds = (DataSet)dataGridView1.DataSource; the DataSource is null. — jdweng, May 05 '20 at 21:05
You have to choices 1) Instead of putting data directly into a DGV put data into a datartable. The make table the DataSource of the DGV so my code works 2) Change my code to take data directly from DGV instead of from a table. — jdweng, May 05 '20 at 21:34
Instead of enumerating through the datatable enumerate through the rows of the DGV. Se : https://stackoverflow.com/questions/6487839/reading-data-from-datagridview-in-c-sharp — jdweng, May 05 '20 at 21:52
I updated code to fill a datatable from the excel input. You can then put into a DGV and output to XML. It looks like you have 13 columns you are reading into the DGV but only 9 that are going into the xml. So you need to adjust the columns names to be correct. — jdweng, May 06 '20 at 17:50
Thank you very much sir The code works perfectly now but there was a slight problem with saving the file in a specific path — Uzarsef, May 06 '20 at 22:28

Export Datagridview To XML File C#

1 Answers1

Linked