使用LightweightExcelReader将Excel转换为JSON

问题描述：

我有一个带有工作表的Excel文件(.xlsx)，如下所示:

I have a Excel file (.xlsx) with a sheet, which looks like this:

Name     | Age | Country      |
Nik        17    Switzerland
Thomas     28    Kuba
Waslim     12    Russia

我想将此Excel工作表转换为JSON格式.

I want to convert this excel sheet into JSON Format.

结果应如下所示:

[
    {
        "Name":"Nik",
        "Age":17,
        "Country":"Switzerland"
    },
    {
        "Name":"Thomas",
        "Age":28,
        "Country":"Kuba"
    },
    {
        "Name":"Waslim",
        "Age":12,
        "Country":"Russia"
    }
]

我想使用 LightweightExcelReader 框架.我知道有一个

I would like to use the LightweightExcelReader framework. I know that there is a similar question already asked, but the answer uses OLEDB which shouldn't be best practice anymore. I would like to solve this transformation with a easier and faster framework.

重要提示:行和列的数量是动态的，可以变化，但是excel工作表的格式在工作表之间保持不变.

IMPORTANT: The number of rows and columns is dynamic and can vary, but the format of the excel sheet stays the from sheet to sheet the same.

这是我的尝试.如您所见，我没有进行很多管理，这很基本.我设法在JSON中获得了密钥的第一行:

Here is my attempt. As you can see i didn't manage alot and it's pretty basic. I managed to get the first row for the key in the JSON:

var excelReader = new ExcelReader(@"path\to\file\test.xlsx");
var sheetReader = excelReader[0];

IEnumerable<object> keys = sheetReader.Row(1);

如何使用LightweightExcelReader Framework将Excel工作表转换为JSON格式?

答

如果您不介意依赖Newtonsoft JSON，则可以执行以下操作:

If you don't mind a dependency on Newtonsoft JSON you could do something like:

public static class ExcelJsonExtensionMethods
{
    public static string ToJson(this SheetReader sheetReader)
    {
        IDictionary<int, string> HeaderNames = GetHeaderNames(sheetReader);
        var jArray = new JArray();
        while (sheetReader.ReadNext())
        {
            var jObject = new JObject();
            do
            {
                var propertyName = HeaderNames[new CellRef(sheetReader.Address).ColumnNumber];
                jObject[propertyName] = sheetReader.Value?.ToString();
            } while (sheetReader.ReadNextInRow());
            jArray.Add(jObject);
        }

        return jArray.ToString();
    }

    private static IDictionary<int, string> GetHeaderNames(SheetReader sheetReader)
    {
        var headerNames = new Dictionary<int, string>();
        while (sheetReader.ReadNextInRow())
        {
            headerNames.Add(new CellRef(sheetReader.Address).ColumnNumber, sheetReader.Value?.ToString());
        }

        return headerNames;
    }
}

使用方式如下:

var excelReader = new ExcelReader(@"path\to\file\test.xlsx");
var sheetReader = excelReader[0];
var sheetJson = sheetReader.ToJson();

请记住，此代码可以正常工作:

Bear in mind that for this code to work:

标题必须位于第一行
标头名称必须是有效的JSON属性名称
您不能有重复的标题名称
标题中不能有任何空白列

使用LightweightExcelReader将Excel转换为JSON

相关推荐