'C#, how to update particular Cell in an Excel file

I have created a windows application in C# and I am using excel as a temporary database. I am able to read in the excel data, but I have no idea how to update the cell value in that file using C#.

The structure is as follows:

enter image description here

I want to update the done field to yes after finishing the execution.

c++sharpexcelwindows-applications


Solution 1:[1]

I have to update 

Extended Properties=HDR=NO; IMEX=1

with

Extended Properties=HDR=YES;

so it will be:

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0; Extended Properties=HDR=YES;Data Source=" + Directory.GetCurrentDirectory() + "/swtlist.xls";

OleDbConnection oledbConn = new OleDbConnection(connString);

oledbConn.Open();
DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

OleDbCommand cmd = new OleDbCommand("UPDATE [Sheet1$] SET done='yes' where id=1", oledbConn);

cmd.ExecuteNonQuery();

Solution 2:[2]

If you're using excel as an OLEDB data source then you will use SQL just like if it were a database. Queries look a little like:

SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]

In your case

UPDATE [Sheet One$]
SET [column1]=value, [done]='yes'
WHERE [some_column]=some_value

IF you need more help look around, there is tons of information on this available. Maybe http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB will help you get started.

Solution 3:[3]

To update the excel using C#, you simply have to get the worksheet object and by using that update cell using the address of the cell (xlWorksheet .Cells[1,1]). See the below sample code.

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace _1605_Stud_Nut_Analysis
{
    
    public class ExtractWeldSpotExcel
    {
        public string[,] aWeldSpotData;
        public string[,] ExtractExcelData(string sWeldPath)
        {
            try
            {   
                // Excel Instance declartion
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sWeldPath);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // Insert your sheet index here
                Excel.Range xlRange = xlWorksheet.UsedRange;  
               
                // Some code goes here


                // Update the excel worksheet
                  xlWorksheet.Cells[1, 1] = 4;
                  xlWorksheet.Cells[1, 2] = "Value";
                
                xlApp.Workbooks.Close();
                xlApp.Quit();

                Marshal.ReleaseComObject(xlWorksheet);
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlApp);

                return aWeldSpotData;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
     }
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vishal Suthar
Solution 2
Solution 3 Hearty Programmer

Related Questions