Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reading data from excel in to Json object in c#

Tags:

json

c#

excel

I have an Excel sheet which has a set of columns and rows with data. I want to read the complete Excel sheet data as JSON, so that later I can write the JSON to a file. How can I do this?

Sample data:

Names    RegNo    Description
ABCD     12345    DemoInfo
XYZ      67890    DemoInfo2
like image 936
chanduH Avatar asked Dec 05 '12 05:12

chanduH


People also ask

How do I convert Excel data to JSON?

There is no predefined method in Excel to convert the Excel data to JSON. You can either use online Excel to JSON conversion software or download an add-in from the Microsoft store for this task to get done.

How do you read data from Excel sheet and convert to a JSON request?

Converting Excel Sheet to JSON String using Pandas Module We can use the to_json() function to convert the DataFrame object to JSON string. Let's look at a simple example to read the “Employees” sheet and convert it to JSON string. So, the JSON data is created with the orientation of columns.

Can we convert string to JSON in C?

Convert String to JSON Object With the JObject. Parse() Function in C# The JObject class inside the Newtonsoft. Json package is used to represent a JSON object in C#.


2 Answers

Connect to the Excel sheet via the ADO.NET OleDb provider. Then, use a JSON library for C# to generate the JSON string, and save the file. (Or use the JavascriptSerialzer, as @boades suggested).

This example uses the JSON.NET library.

using System;
using System.Linq;
using System.Data.OleDb;
using System.Data.Common;
using Newtonsoft.Json;
using System.IO;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            var pathToExcel = @"C:\path\to\excel\file.xlsx";
            var sheetName = "NameOfSheet";
            var destinationPath = @"C:\path\to\save\json\file.json";

            //Use this connection string if you have Office 2007+ drivers installed and 
            //your data is saved in a .xlsx file
            var connectionString = $@"
                Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source={pathToExcel};
                Extended Properties=""Excel 12.0 Xml;HDR=YES""
            ";

            //Creating and opening a data connection to the Excel sheet 
            using (var conn=new OleDbConnection(connectionString)) {
                conn.Open();

                var cmd=conn.CreateCommand();
                cmd.CommandText = $"SELECT * FROM [{sheetName}$]";

                using (var rdr=cmd.ExecuteReader()) {

                    //LINQ query - when executed will create anonymous objects for each row
                    var query = rdr.Cast<DbDataRecord>().Select(row => new {
                        name = row[0],
                        regno = row[1],
                        description = row[2]
                    });

                    //Generates JSON from the LINQ query
                    var json = JsonConvert.SerializeObject(query);

                    //Write the file to the destination path    
                    File.WriteAllText(destinationPath, json);
                }
            }
        }
    }
}

Links:

  • Retrieving and Modifying Data in ADO.NET - how to use connections, commands and readers
  • Excel 2007 connection strings
  • Older Excel connection strings under the 'OLEDB Providers' section
  • JSON libraries for C# can be found on the JSON page
  • JSON.NET
  • Microsoft Access Database Engine 2010 Redistributable, if you don't have Excel 2007+ installed
like image 79
Zev Spitz Avatar answered Sep 24 '22 08:09

Zev Spitz


Save it as a CSV. Then use File.ReadLines to enumerate over each line, followed by String.Split to read each column. Format the data as a JSON string and save it to a file.

like image 39
trebuchet Avatar answered Sep 22 '22 08:09

trebuchet