Read Data from Excel to Replace Items in Json

Excel数据读取与JSON修改

最新推荐文章于 2025-09-05 14:41:09 发布

weixin_34293902

最新推荐文章于 2025-09-05 14:41:09 发布

阅读量110

点赞数

CC 4.0 BY-SA版权

文章标签： json

原文链接：http://www.cnblogs.com/jizhiqiliao/p/9896998.html

本文介绍了一种从Excel中读取数据并修改JSON字符串的方法。通过使用Microsoft.Office.Interop.Excel库，可以将Excel文件中的数据读取到数组中，然后使用正则表达式修改JSON字符串中的特定字段。

You can implement reading data from excel and then modifying the json string. The specific implementation code is as follows:

private void Submit_Click(object sender, EventArgs e)
{
    string strFileName = @"D:\Book1.xlsx";
    object missing = System.Reflection.Missing.Value;
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
    if (excel == null)
    {
        MessageBox.Show("Can’t access excel");
    }
    else
    {
        excel.Visible = false; excel.UserControl = true;
        Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
            missing, missing, missing, true, missing, missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
        int rowsint = ws.UsedRange.Cells.Rows.Count;
        Microsoft.Office.Interop.Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
        Microsoft.Office.Interop.Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
        object[,] arry1 = (object[,])rng1.Value2;
        object[,] arry2 = (object[,])rng2.Value2;
        string numbers = "";
        string pn = "";
        foreach (var i in arry1)
        {
            numbers += i.ToString() + " ";
        }
        foreach (var i in arry2)
        {
            pn += i.ToString() + " ";
        }
        string[] nArray = numbers.Split(' ');
        string[] pArray = pn.Split(' ');
        for (int i = 0; i < rowsint - 1; i++)
        {
            string json = "[{'Number':'12345678910','DeviceEventTime':'2017-09-17T07:44:17.696Z','Identifier':'aaaaaaa-bbbb-1111-2223-123456789332','AssemblyCollection':[{'PN': 'ABC-00001','AssemblyNumber': '12345678910'}]]";
            Regex reg1 = new Regex(@"'Number':'(\d{11})'");
            string modified = reg1.Replace(json, $"'Number':'{nArray[i]}'");
            Regex reg2 = new Regex(@"'PN': '[A-Z]{3}-\w{5}'");
            string newmodified = reg2.Replace(modified, $"'PN': '{pArray[i]}'");
            // ...
            // the code post data
            // ...
        }
    }
}

View Code

Before using it, you need to use "System.Text.RegularExpressions" and add "Microsoft.Office.Interop.Excel" from "NuGet". The specific operation is as follows：

Right click the Reference and select "Manage NuGet Packages...", then type "Microsoft.Office.Interop.Excel" in the search bar and install it: