ByteScout Data Extraction Suite – C# – Convert xls to xml and xml to xls with spreadsheet sdk

Home
/
Articles
/
ByteScout Data Extraction Suite – C# – Convert xls to xml and xml to xls with spreadsheet sdk

printable version:
ByteScout-Data-Extraction-Suite-C-sharp-Convert-xls-to-xml-and-xml-to-xls-with-spreadsheet-sdk.pdf

How to convert xls to xml and xml to xls with spreadsheet sdk in C# using ByteScout Data Extraction Suite

Learning is essential in computer world and the tutorial below will demonstrate how to convert xls to xml and xml to xls with spreadsheet sdk in C#

We made thousands of pre-made source code pieces for easy implementation in your own programming projects. ByteScout Data Extraction Suite can convert xls to xml and xml to xls with spreadsheet sdk. It can be applied from C#. ByteScout Data Extraction Suite is the bundle that includes three SDK tools for data extraction from PDF, scans, images and from spreadsheets: PDF Extractor SDK, Data Extraction SDK, Barcode Reader SDK.

Want to quickly learn? This fast application programming interfaces of ByteScout Data Extraction Suite for C# plus the guidelines and the code below will help you quickly learn how to convert xls to xml and xml to xls with spreadsheet sdk. This C# sample code is all you need for your app. Just copy and paste the code, add references (if needs to) and you are all set! If you want to use these C# sample examples in one or many applications then they can be used easily.

You can download free trial version of ByteScout Data Extraction Suite from our website to see and try many others source code samples for C#.

On-demand (REST Web API) version:
Web API (on-demand version)

On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)

Bytescout.XLS.Utils.SimpleXMLConverter.cs

      using System;
using System.Data;
using System.IO;
using Bytescout.Spreadsheet;

namespace Bytescout.Spreadsheet.Utils
{
    /// <summary>
    /// class for simple XLS to XML and XML to XLS conversion
    /// </summary>
    public class SimpleXMLConverter
    {
        private readonly Spreadsheet document;

        public SimpleXMLConverter(Spreadsheet document)
        {
            this.document = document;
        }

        public void LoadXML(string path)
        {
            DataSet dataSet = new DataSet();
            dataSet.ReadXml(path);

            foreach (DataTable table in dataSet.Tables)
            {
                Worksheet worksheet = document.Workbook.Worksheets.Add(table.TableName);

                // Add columns
                worksheet.Columns.Insert(0, table.Columns.Count);

                /*// Add row for header
                worksheet.Rows.Insert(0);

                // Fill headers
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    worksheet.Cell(1, i).Value = table.Columns[i].ColumnName;
                }*/

                // Add rows for data
                worksheet.Rows.Insert(0, table.Rows.Count);

                // Fill data
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        worksheet.Cell(i + 1, j).Value = table.Rows[i][j].ToString();
                    }
                }
            }
        }

        public void SaveXML(string path)
        {
            DataSet dataSet = new DataSet();

            for (int i = 0; i < document.Workbook.Worksheets.Count; i++)
            {
                Worksheet worksheet = document.Workbook.Worksheets[i];

                DataTable table = dataSet.Tables.Add(worksheet.Name);

                #region Add Columns

                for (int column = 0; column <= worksheet.UsedRangeColumnMax; column++)
                {
                    table.Columns.Add(
                        string.Format("Column_{0}", column));
                }

                #endregion

                #region Add rows

                for (int row = 0; row <= worksheet.UsedRangeRowMax; row++)
                {
                    object[] data = new object[worksheet.UsedRangeColumnMax + 1];

                    for (int column = 0; column <= worksheet.UsedRangeColumnMax; column++)
                    {
                        if (worksheet.Cell(row, column).Value != null &&
                            worksheet.Cell(row, column).Value.ToString().Trim() == "")
                            data[column] = null;
                        else
                            data[column] = worksheet.Cell(row, column).Value;
                    }

                    table.Rows.Add(data);
                }

                #endregion
            }

            dataSet.WriteXml(path);
        }

        private void PrintDataSet(DataSet ds)
        {
            Console.WriteLine("DataSet name: {0}", ds.DataSetName);
            foreach (DataTable table in ds.Tables)
            {
                int rowCount = table.Rows.Count;
                int columnCount = table.Columns.Count;

                Console.WriteLine("\nTable: {0} ({1} rows)", table.TableName, rowCount);
                foreach (DataColumn column in table.Columns)
                {
                    Console.Write("{0}\t", column.ColumnName);
                }
                Console.WriteLine();

                for (int i = 0; i < rowCount; i++)
                {
                    for (int column = 0; column < columnCount; column++)
                    {
                        Console.Write("{0}\t", table.Rows[i][column]);
                    }

                    Console.WriteLine();
                }
            }
        }
    }
}

ON-PREMISE OFFLINE SDK

60 Day Free Trial or Visit ByteScout Data Extraction Suite Home Page

Explore ByteScout Data Extraction Suite Documentation

Explore Samples

Sign Up for ByteScout Data Extraction Suite Online Training

ON-DEMAND REST WEB API

Get Your API Key

Explore Web API Docs

Explore Web API Samples

Program.cs

      using System;
using System.Data;
using System.IO;
using Bytescout.Spreadsheet;
using Bytescout.Spreadsheet.Utils;
using System.Diagnostics;

namespace SimpleXMLConverterSample
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            // sample XLS to XML conversion
            SampleXLStoXMLConversion();
            // sample XML to XLS conversion
            SampleXMLtoXLSConversion();
        }

        /// <summary>
        /// shows how to convert existing XLS (Excel) document into XML using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
        /// </summary>
        private static void SampleXLStoXMLConversion(){
            Spreadsheet document;

            // read XLS and save as XML
            document = new Spreadsheet();
	document.LoadFromFile("AdvancedReport.xls");
            SimpleXMLConverter tools = new SimpleXMLConverter(document);
            tools.SaveXML("AdvancedReport.xml");
            document.Close();
        }

        /// <summary>
        /// shows how to convert XML data into XLS excel using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
        /// </summary>
        private static void SampleXMLtoXLSConversion()
        {
            // read XML and convert into XLS (Excel) and save

            Spreadsheet document = new Spreadsheet();

            SimpleXMLConverter tools = new SimpleXMLConverter(document);

            tools.LoadXML("AdvancedReport.xml");

            // delete output file if exists already
            if (File.Exists("Output.xls")){
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");

        }
    }

}