Read Data from Excel to Replace Items in Json
You can implement reading data from excel and then modifying the json string. The specific implementation code is as follows:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
// ...
}
}
}
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:
把圈子变小,把语言变干净,把成绩往上提,把故事往心里收,现在想要的以后你都会有。