Use NPOI to modify Excel data

Requirements: A file in the format of exp, with a json structure, replacing some of the data
Solution: Read excel, use npoi to read the specified content, find the specific structure in exp, and then replace

Read excel file

This method is also used to read files in other formats

public void OpenExcel()
 {
     Microsoft.Win32.OpenFileDialog openFileDialog = new Microsoft.Win32.OpenFileDialog();
     if (openFileDialog.ShowDialog() == true)
     {
         ExcelPath = openFileDialog.FileName;
     }
 }
 

Key code

Find the specific structure of json and replace the data

{
   "Name": "Raw Data",
   "Version": "",
   "FieldName": "TakeOffPointID,CurveID,CurveData",
   "FieldType": "i,s,arr",
   "Data": null,
   "DataList": [
     "-1,A01.Green,32231.000 32695.000 31543.000 31639.000 31623.000 31367.000 31271.000 30807.000 30007.000 29895.000 29911.000 3010 3.000 29559.000 29607.000 29415.000 29735.000 29799.000 29495.000 29463.000 29015.000 29287.000 29639.000 30375.000 32071.000 35704.000 42474.000 51324.000 60334.000 65535.000 65535.000 65535.000 65535.000 65535.000 65535.000 65535.000 65535.000 6553 5.000 65535.000 65535.000 65535.000",  ]
 }
 

// Use regular expressions to find the target JSON structure
 string pattern = $@"\{{[^{{}}]*""Name"":\s*""{sheetName}""[^{{}}]*\}}";
 System.Text.RegularExpressions.Match match = Regex.Match(json, pattern, RegexOptions.Singleline);
 if(match.Success)
 {
     // Get the matching JSON structure
     string jsonStructure = match.Value;

     // Parse JSON data
     JObject jObject = JObject.Parse(jsonStructure);

     // Find the "DataList" array and replace the contents
     JArray dataList = (JArray)jObject["DataList"];

     // Replace the contents of "DataList"
     dataList.ReplaceAll(newDataList.Select(item => new JValue(item)));

     //Update JSON structure
     string updatedJsonStructure = jObject.ToString(Formatting.None);

     // Replace the JSON structure in the original text
     json = Regex.Replace(json, pattern, updatedJsonStructure);
 }
 

all code

public partial class XlsxToExpViewModel : ObservableObject
 {
     [ObservableProperty] private string _excelPath = @"C:\Users\63214\Desktop\20220809_160644C15256.xlsx";
     [ObservableProperty] private string _expPath = @"C:\ProgramData\Virtue\Experiment\20220809_160644C.exp";
     [ObservableProperty] private string _newExpPath = @"C:\Users\63214\Desktop\newExp.exp";
     [ObservableProperty] private double _schedule = 0;

     public XlsxToExpViewModel()
     {

     }

     #regionExcel
     [RelayCommand]
     public void OpenExcel()
     {
         Microsoft.Win32.OpenFileDialog openFileDialog = new Microsoft.Win32.OpenFileDialog();
         if (openFileDialog.ShowDialog() == true)
         {
             ExcelPath = openFileDialog.FileName;
         }
     }

     [RelayCommand]
     public void OpenExp()
     {
         Microsoft.Win32.OpenFileDialog openFileDialog = new Microsoft.Win32.OpenFileDialog();
         if (openFileDialog.ShowDialog() == true)
         {
             ExpPath = openFileDialog.FileName;
         }
     }

     [RelayCommand]
     public void Export()
     {
         //Configuration file directory
         string dict = null;
         //IOUtil.Exists(dict);
         Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog()
         {
             Title = "Please select the export configuration file...", //Dialog title
             Filter = "Exp Files(*.exp)|*.exp", //File format filter
             FilterIndex = 1, //filter selected by default
             FileName = "newfile", //Default file name
             DefaultExt = "exp", //Default extension
             InitialDirectory = dict, //Specify the initial directory
             OverwritePrompt = true, //File already exists warning
             AddExtension = true, //If the user omits the extension, the extension will be added automatically
         };
         if (sfd.ShowDialog() == true)
         {
             NewExpPath = sfd.FileName;
         }
     }

     [RelayCommand]
     public void GenerateNewExpFile()
     {
         if (File.Exists(NewExpPath))
         {
             File.Delete(NewExpPath);
             Console.WriteLine("The file has been deleted.");
         }

         //Specify the name of the worksheet to be read
         List listSheet = new List() { "Raw Data", "Calibrated Data", "Amplification Data" };
         string json = File.ReadAllText(ExpPath);
         foreach (string sheetName in listSheet)
         {
             try
             {
                 //Open Excel file using FileStream
                 using (FileStream fs = new FileStream(ExcelPath, FileMode.Open, FileAccess.ReadWrite))
                 {
                     //Use XSSFWorkbook open the .xlsx file (if it is a .xls file, use HSSFWorkbook)
                     IWorkbook workbook = new XSSFWorkbook(fs);

                     // Get the specified worksheet
                     ISheet sheet = workbook.GetSheet(sheetName);

                     if (sheet != null)
                     {
                         List newDataList = new List();

                         // Get the contents of the specified sheet and add it to the list collection
                         // iterate through columns
                         for (int columnIndex = 1; columnIndex < sheet.GetRow(0).LastCellNum; columnIndex++)
                         {
                             string data = "-1," + sheet.GetRow(0).GetCell(columnIndex) + ",";
                             //string data = "-1,";
                             // iterate through rows
                             for (int row = 1; row  new JValue(item)));

                             //Update JSON structure
                             string updatedJsonStructure = jObject.ToString(Formatting.None);

                             // Replace the JSON structure in the original text
                             json = Regex.Replace(json, pattern, updatedJsonStructure);
                         }
                         else
                         {
                             Console.WriteLine("Target structure not found.");
                         }
                     }
                     else
                     {
                         Console.WriteLine("sheet " + sheetName + " does not exist.");
                     }
                 }
             }
             catch (Exception ex)
             {
                 Console.WriteLine("An error occurred: " + ex.Message);
             }
         }
         //Save the updated text file
         File.WriteAllText(NewExpPath, json);
         MessageBoxX.Show(Application.Current.MainWindow, "New file has been generated!", "Prompt", MessageBoxButton.OK, MessageBoxIcon.Success, DefaultButton.YesOK, 5);
     }
     #endregion
 }