How can I find some value from cell and replace by new value in Excel?
I tryed this but it doesn't works:
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb =default(Microsoft.Office.Interop.Excel.Workbook);
wb = xlapp.Workbooks.Open(FileName.ToString());
wb.Worksheets[0].Cells.Replace("find","replace");
I would recommend you use NPOI which can be accessed either via codeplex or directly through Nuget in Visual Studio. It gives you the ability to easily upload, edit and create spreadsheets in .NET
Example of uploading a spreadsheet:
HSSFWorkbook hssfworkbook;
void InitializeWorkbook(string path)
{
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
//book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
You can then use the IRow and ICell collections of the spreadsheet to locate and edit the data you need before doing an export.
More examples can be found here
All you have to do is replace
wb.Worksheets[0].Cells.Replace("find","replace");
with
wb.Worksheets[1].Cells.Replace("find","replace");
If interested, you can use GemBox.Spreadsheet for this, like so:
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
// Load your XLS, XLSX, ODS or CSV file.
ExcelFile wb = ExcelFile.Load(FileName.ToString());
ExcelWorksheet ws = wb.Worksheets[0];
// Replace all "find" occurances with "replace" text.
int row, column;
while(ws.Cells.FindText("find", out row, out column))
ws.Cells[row, column].ReplaceText("find", "replace");
// Save your XLS, XLSX, ODS or CSV file.
wb.Save(FileName.ToString());
Also you can find another searching in Excel example here.
