none
使用c#将excel转换成JSON, 并且将excel中第一行作为key RRS feed

  • 问题

  • 我想从excel 2010通过指定读取数据表的名称。然后我想转换成JSON格式的数据。假设我有一个这样的excel表。

    Name             |   Age  |    Country

    Waugh, Timothy       10        UK

    Freeman, Neil         20       USA

    Andy, Robert          30       Poland

     

    我想得到的结果是这样的.

      [{"Name":"Waugh, Timothy","Age":10.0,"Country":"UK"},

    {"Name":"Freeman, Neil","Age":20.0,"Country":"USA"},

    {"Name":"Andy, Robert","Age":30.0,"Country":"Poland"}]

     

    怎样才能实现。
    2016年2月2日 13:10

答案

  • 你好,

    可以使用oledb 查询出结果,在序列化成JSON的数据。下面有一段相关代码供你参考。

    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";
    
                //This connection string works if you have Office 2007+ installed and your 
                //data is saved in a .xlsx file
                var connectionString=String.Format(@"
                    Provider=Microsoft.ACE.OLEDB.12.0;
                    Data Source={0};
                    Extended Properties=""Excel 12.0 Xml;HDR=YES""
                ",pathToExcel);
    
                //Creating and opening a data connection to the Excel sheet 
                using (var conn=new OleDbConnection(connectionString)) {
                    conn.Open();
    
                    var cmd=conn.CreateCommand();
                    cmd.CommandText = String.Format(
                        @"SELECT * FROM [{0}$]",
                        sheetName
                    );
    
                    using (var rdr=cmd.ExecuteReader()) {
    
                        //LINQ query - when executed will create anonymous objects for each row
                        var query =
                            from DbDataRecord row in rdr
                            select 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);
                    }
                }
            }
        }
    }

    Best regards,

    Cole Wu

    2016年2月3日 4:29
    版主