使用NPOI修改Excel数据

  • 使用NPOI修改Excel数据已关闭评论
  • 86 次浏览
  • A+
所属分类:.NET技术
摘要

需求:格式为exp的文件,具有json结构,替换掉其中某些数据
解决方法:读取excel,用npoi读取指定内容,在exp中找到特定结构,然后替换

需求:格式为exp的文件,具有json结构,替换掉其中某些数据
解决方法:读取excel,用npoi读取指定内容,在exp中找到特定结构,然后替换

读取excel文件

读取其他格式的文件也是用此方法

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

关键代码

找到json的特定结构,替换数据

{   "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 30103.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 65535.000 65535.000 65535.000 65535.000",] } 
// 使用正则表达式查找目标 JSON 结构 string pattern = $@"{{[^{{}}]*""Name"":s*""{sheetName}""[^{{}}]*}}"; System.Text.RegularExpressions.Match match = Regex.Match(json, pattern, RegexOptions.Singleline); if (match.Success) {     // 获取匹配的 JSON 结构     string jsonStructure = match.Value;      // 解析 JSON 数据     JObject jObject = JObject.Parse(jsonStructure);      // 查找 "DataList" 数组并替换内容     JArray dataList = (JArray)jObject["DataList"];      // 替换 "DataList" 的内容     dataList.ReplaceAll(newDataList.Select(item => new JValue(item)));      // 更新 JSON 结构     string updatedJsonStructure = jObject.ToString(Formatting.None);      // 替换原始文本中的 JSON 结构     json = Regex.Replace(json, pattern, updatedJsonStructure); } 

全部代码

public partial class XlsxToExpViewModel : ObservableObject {     [ObservableProperty] private string _excelPath = @"C:Users63214Desktop20220809_160644C15256.xlsx";     [ObservableProperty] private string _expPath = @"C:ProgramDataVirtueExperiment20220809_160644C.exp";     [ObservableProperty] private string _newExpPath = @"C:Users63214DesktopnewExp.exp";     [ObservableProperty] private double _schedule = 0;      public XlsxToExpViewModel()     {      }      #region Excel     [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()     {         //配置文件目录         string dict = null;         //IOUtil.Exists(dict);         Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog()         {             Title = "请选择导出配置文件...",            //对话框标题             Filter = "Exp Files(*.exp)|*.exp",    //文件格式过滤器             FilterIndex = 1,                          //默认选中的过滤器             FileName = "newfile",                      //默认文件名             DefaultExt = "exp",                      //默认扩展名             InitialDirectory = dict,                  //指定初始的目录             OverwritePrompt = true,                   //文件已存在警告             AddExtension = true,                      //若用户省略扩展名将自动添加扩展名         };         if (sfd.ShowDialog() == true)         {             NewExpPath = sfd.FileName;         }     }      [RelayCommand]     public void GenerateNewExpFile()     {         if (File.Exists(NewExpPath))         {             File.Delete(NewExpPath);             Console.WriteLine("文件已删除。");         }          // 指定要读取的工作表名称         List<string> listSheet = new List<string>() { "Raw Data", "Calibrated Data", "Amplification Data" };         string json = File.ReadAllText(ExpPath);         foreach (string sheetName in listSheet)         {             try             {                 // 使用FileStream打开Excel文件                 using (FileStream fs = new FileStream(ExcelPath, FileMode.Open, FileAccess.ReadWrite))                 {                     // 使用XSSFWorkbook打开.xlsx文件(如果是.xls文件,使用HSSFWorkbook)                     IWorkbook workbook = new XSSFWorkbook(fs);                      // 获取指定工作表                     ISheet sheet = workbook.GetSheet(sheetName);                      if (sheet != null)                     {                         List<string> newDataList = new List<string>();                          // 获取指定sheet的内容并添加到list集合                         // 遍历列                         for (int columnIndex = 1; columnIndex < sheet.GetRow(0).LastCellNum; columnIndex++)                         {                             string data = "-1," + sheet.GetRow(0).GetCell(columnIndex) + ",";                             //string data = "-1,";                             // 遍历行                             for (int row = 1; row <= sheet.LastRowNum; row++)                             {                                 IRow currentRow = sheet.GetRow(row);                                  if (currentRow != null)                                 {                                     ICell cell = currentRow.GetCell(columnIndex);                                      if (cell != null)                                     {                                         // 获取单元格的值(假设它是文本)                                         string cellValue = Convert.ToDouble(cell.ToString()).ToString("F3") + " ";                                         data += cellValue;                                     }                                 }                             }                             newDataList.Add(data);                         }                         // 使用正则表达式查找目标 JSON 结构                         string pattern = $@"{{[^{{}}]*""Name"":s*""{sheetName}""[^{{}}]*}}";                         System.Text.RegularExpressions.Match match = Regex.Match(json, pattern, RegexOptions.Singleline);                         if (match.Success)                         {                             // 获取匹配的 JSON 结构                             string jsonStructure = match.Value;                              // 解析 JSON 数据                             JObject jObject = JObject.Parse(jsonStructure);                              // 查找 "DataList" 数组并替换内容                             JArray dataList = (JArray)jObject["DataList"];                              // 替换 "DataList" 的内容                             dataList.ReplaceAll(newDataList.Select(item => new JValue(item)));                              // 更新 JSON 结构                             string updatedJsonStructure = jObject.ToString(Formatting.None);                              // 替换原始文本中的 JSON 结构                             json = Regex.Replace(json, pattern, updatedJsonStructure);                         }                         else                         {                             Console.WriteLine("未找到目标结构.");                         }                     }                     else                     {                         Console.WriteLine("工作表 " + sheetName + " 不存在.");                     }                 }             }             catch (Exception ex)             {                 Console.WriteLine("发生错误: " + ex.Message);             }         }         // 保存更新后的文本回文件         File.WriteAllText(NewExpPath, json);         MessageBoxX.Show(Application.Current.MainWindow, "新文件已生成!", "提示", MessageBoxButton.OK, MessageBoxIcon.Success, DefaultButton.YesOK, 5);     }     #endregion }