需求：格式为exp的文件，具有json结构，替换掉其中某些数据
解决方法：读取excel，用npoi读取指定内容，在exp中找到特定结构，然后替换文章来源：https://www.toymoban.com/news/detail-711607.html
读取excel文件
读取其他格式的文件也是用此方法文章来源地址https://www.toymoban.com/news/detail-711607.html
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:\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()
{
}
#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
}
到了这里，关于使用NPOI修改Excel数据的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
