private static bool ReplaceCellText(ICell cell, IExcelReplace data)
{
if (cell == null)
{
return(false);
}
bool change = false;
try
{
if (cell.CellType == CellType.String)
{
string text = cell.ToString();
if (data.replaceList.Length > 0)
{
for (int i = 0; i < data.replaceList.Length; i++)
{
IReplaceText rep = data.replaceList[i];
if (text.Contains(rep.oldChar))
{
text = text.Replace(rep.oldChar, rep.newChar);
cell.SetCellValue(text);
change = true;
}
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(change);
}
private static void ExportExcel(string file, IExcelReplace data)
{
if (!File.Exists(file))
{
return;
}
bool change = false;
try
{
FileStream stream = new FileStream(file, FileMode.Open, FileAccess.Read);
IWorkbook workbook = ExcelUtil.NewWorkbook(stream);
if (workbook == null)
{
return;
}
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
for (int j = 0; j < sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
if (row != null)
{
for (int k = 0; k < row.LastCellNum; k++)
{
ICell cell = row.GetCell(k);
if (ReplaceCellText(cell, data))
{
change = true;
}
}
}
}
}
stream.Close();
if (change)
{
FileUtil.CheckPath(data.newUrl);
string newPath = data.newUrl + @"\" + Path.GetFileName(file);
using var fs = new FileStream(newPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
data.infoList.Add(file);
workbook.Write(fs);
fs.Close();
}
workbook.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
public static void Replace(IExcelReplace data)
{
List <string> paths = FileUtil.GetAllFileName(data.oldUrl, data.ext);
for (int i = 0; i < paths.Count; i++)
{
string path = paths[i];
data.action?.Invoke($"当前进度：{path} ({i+1}/{paths.Count})");
ExportExcel(path, data);
}
}
private void Btn_replace_Click(object sender, EventArgs e)
{
SetProgress("");
StartProgress();
listBoxMsg.Items.Clear();
string path = textBox_url.Text;
string oldChar = textBox_target.Text;
string type = textBox_type.Text;
string newChar = textBox_newChar.Text;
ThreadStart childref = new ThreadStart(() =>
{
IExcelReplace data = new IExcelReplace
{
oldUrl = path,
newUrl = path,
ext = type,
replaceList = new IReplaceText[1]
{
new IReplaceText
{
oldChar = oldChar,
newChar = newChar
}
},
infoList = new List <string>(),
action = SetProgress
};
ExcelReplace.Replace(data);
if (data.infoList.Count > 0)
{
AddBoxMsg($"操作成功");
AddBoxMsg($"已经把所有的‘{oldChar}’替换成‘{newChar}’");
AddBoxMsg($"修改过的文件如下：");
foreach (var info in data.infoList)
{
AddBoxMsg(info);
}
}
else
{
AddBoxMsg($"没有找到和‘{oldChar}’类似的内容");
}
StopProgress();
});
StartThread(childref);
}