将excel中的数据读入C#中的Json对象

Reading data from excel in to Json object in c#

提问by chanduH

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?

我有一个 Excel 工作表，其中包含一组包含数据的列和行。我想将完整的 Excel 工作表数据读取为 JSON，以便稍后我可以将 JSON 写入文件。我怎样才能做到这一点？

Sample data:

样本数据：

Names    RegNo    Description
ABCD     12345    DemoInfo
XYZ      67890    DemoInfo2

回答by trebuchet

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.

将其另存为 CSV。然后使用 File.ReadLines 枚举每一行，然后使用 String.Split 读取每一列。将数据格式化为 JSON 字符串并将其保存到文件中。

回答by Zev Spitz

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).

通过 ADO.NET OleDb 提供程序连接到 Excel 工作表。然后，使用 C# 的 JSON 库生成 JSON 字符串，并保存文件。（或使用 JavascriptSerialzer，正如@boades 所建议的那样）。

This example uses the JSON.NET library.

此示例使用 JSON.NET 库。

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 stringsunder the 'OLEDB Providers' section
• JSON libraries for C# can be found on the JSONpage
• JSON.NET
• Microsoft Access Database Engine 2010 Redistributable, if you don't have Excel 2007+ installed

• 在 ADO.NET 中检索和修改数据- 如何使用连接、命令和阅读器
• Excel 2007 连接字符串
• “OLEDB 提供程序”部分下的旧 Excel 连接字符串
• 可以在JSON页面上找到 C# 的 JSON 库
• JSON.NET
• Microsoft Access Database Engine 2010 Redistributable，如果您没有安装 Excel 2007+

回答by boades

You can give a try to http://exceldatareader.codeplex.com/library. You must read data from excel to some object, and than convert it to json using JavaScriptSerializer

您可以尝试http://exceldatareader.codeplex.com/库。您必须将数据从 excel 读取到某个对象，然后使用JavaScriptSerializer将其转换为 json

public class MyRow
{
   public string Cell1;
   public string Cell2;
   public string Cell3;
}

class Program
{
        static void Main()
        {
            var list = new List<MyRow>();
            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);


            //5. Data Reader methods
            while (excelReader.Read())
            {
                   var obj = new MyRow 
                   {
                       Cell1 = excelReader.GetString(0),
                       Cell2 = excelReader.GetString(1),
                       Cell3 = excelReader.GetString(2),
                   }

                   list.Add(obj);
            }

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            var json = new JavaScriptSerializer().Serialize(list);
            Console.WriteLine(json);
        }
    }