locked
C# code to update excel cell RRS feed

  • Question

  • User1132714760 posted

    Hi,

    I need c# code to update an excel cell by searching on key word in specific range.

    for example: from A1 to E20 cell range I will search on "hi" word.

    if I found the word in cell, I will replace by "abc" and need to save excel.

    How I can achieve this functionality.

    Thanks.

    Friday, September 7, 2018 2:33 AM

Answers

  • User2103319870 posted

    if I found the word in cell, I will replace by "abc" and need to save excel.

    You can take a look at EPPlus to read and save values in excel sheet. It has many features which allows user to manipulate the Excel sheet. You can also use Nugget to add the EPPlus in your solution 

    var existingFile = new FileInfo(@"C:\Forums\Temp\TestDocument.xlsx");
                using (var pck = new ExcelPackage(existingFile))
                {
                    string cellRange = "A1:E20";
                    ExcelWorkbook WorkBook = pck.Workbook;
                    ExcelWorksheet worksheet = WorkBook.Worksheets.First();
                    //Get all cells which has value as hi
                    var searchCells = from cell in worksheet.Cells[cellRange] 
                                     where cell.Value.ToString() == "hi"
                                     select cell.Address;
    
                    foreach (string cellindex in searchCells)
                    {
                        //Change the textt content in cell
                        worksheet.Cells[cellindex].Value ="abc";
                    }
                    pck.Save();
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 8, 2018 2:22 AM

All replies

  • User2103319870 posted

    if I found the word in cell, I will replace by "abc" and need to save excel.

    You can take a look at EPPlus to read and save values in excel sheet. It has many features which allows user to manipulate the Excel sheet. You can also use Nugget to add the EPPlus in your solution 

    var existingFile = new FileInfo(@"C:\Forums\Temp\TestDocument.xlsx");
                using (var pck = new ExcelPackage(existingFile))
                {
                    string cellRange = "A1:E20";
                    ExcelWorkbook WorkBook = pck.Workbook;
                    ExcelWorksheet worksheet = WorkBook.Worksheets.First();
                    //Get all cells which has value as hi
                    var searchCells = from cell in worksheet.Cells[cellRange] 
                                     where cell.Value.ToString() == "hi"
                                     select cell.Address;
    
                    foreach (string cellindex in searchCells)
                    {
                        //Change the textt content in cell
                        worksheet.Cells[cellindex].Value ="abc";
                    }
                    pck.Save();
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 8, 2018 2:22 AM
  • User-893317190 posted

    Hi TejasviRebba,

    You could try code below using   Microsoft.Office.Interop.Excel. Before using it , please import  it through the com bar of  add reference wizard,

      Microsoft.Office.Interop.Excel.Application xlApp = new Application();
                //open the excel
                Workbook xlWorkbooK = xlApp.Workbooks.Open(@"d:\range3.xlsx");
                //get the first sheet of the excel
                Worksheet xlWorkSheet = (Worksheet)xlWorkbooK.Worksheets.get_Item(1);
                Range range = xlWorkSheet.UsedRange;
                int rowCount = range.Rows.Count;
                int columnCount = range.Columns.Count;
                // specify the rows
                for (int i = 1; i <=20; i++)
                {
                    //specify the columns
                    for (int j= 1; j <=5; j++)
                    {
                     
                        Range cell = range.Cells[i, j] as Range;
                     
                        if ((string)cell.Value2 == "hi")
                        {
                         // change the cell which has value "hi"
                            cell.Value = "abc";
                        }
                    }
                }
                xlWorkbooK.Save();
                //release the resource
                xlApp.Quit();
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkbooK);
                Marshal.ReleaseComObject(xlApp);

    Best regards,

    Ackerly Xu

    Monday, September 10, 2018 9:34 AM
  • User426857743 posted

    Hi,

    You can try spire.xls.dll available on NuGet to replace data in excel, the code is very straightforward.

    https://www.nuget.org/packages/Spire.XLS/

    //Load excel
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("Input.xlsx");
    
    //Get the 1st sheet
    Worksheet sheet = workbook.Worksheets[0];
    
    //Specify the cell range
    CellRange range = sheet.Range["A1:A20"];
    
    //Find all matched text in the range
    CellRange[] cells = range.FindAllString("hi", false, false);
    
    //Replace text
    foreach (CellRange cell in cells)
    {
        cell.Text = "abc";
    }
    
    //Save
    workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

    Monday, September 17, 2018 3:55 AM