C# - How to remove and merge rows for excel using C# code

Question

Using this table as an example:

I need to code in C# a way to merge rows 2 and 3 (so that all data is merged into 1 row because this excel document has metrics on diff rows like the example) and delete any row in excel that contains the word 'N/A' in column B, how would I go about doing that in C#? I need to code in C# to alter excel files

Answer 1

1. First create a model that contains each property which are now in the top row of your excel file
2. Loop through the rows and within that loop through the columns
3. Within the loop check whether the item (row/column combination) contains a value which does not equal 'N/A' and if so set the property corresponding to the column with the value of the item

For code I would suggest the following question: Modify excel cell

Answer 2

It's not entirely clear what you want from your question. Are you only trying to merge row 1 and 2 so that your row looks like:

|ID1|NameShirt|Size|Qty1|Price|?

This should be achievable using SQL and C#.

Excel files can actually be treated like a database, and Microsoft has some database drivers made specifically for Excel, as seen here.

You'll have to make some sort of SQL or OLEDB connection, like so:

OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source="%%ExcelFilePath%%";Extended Properties=Excel 8.0;");

And then query that connection using SQL syntax to get record values or to update them, like so:

 using (OleDbConnection connection = new OleDbConnection(con))
        {
            connection.Open();
            try
            {

                string sqlCommand = "Your SQL Syntax to get the rows or to update them";
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                OleDbCommand cmd = new OleDbCommand(sqlCommand, con);

                cmd.ExecuteNonQuery(); //or cmd.ExecuteQuery()

                // this is where you would also be able to extract rows 
                //from the excel file and modify the information

                con.Close();


            }
            catch (Exception ex) { }
        }

Another option you can use is referencing the Microsoft Interop libraries to directly open an excel file and manage it, but be warned the interop library doesn't always work well if the application is supposed to be hidden from the user because Office applications have modal popups that will freeze your application if a user cannot dismiss them.

Once you pick your method of Excel manipulation, then you can get a list of rows/columns and iterate through them, updating the information in accordance with your conditions.