Submitting changes to excel file using Excel Interop in C#

Question

I am still stuck in this code and bugs keep showing up.. I have excel file that is almost 24 columns, I am trying to adapt it to a 7 column excel file because the software I have works only on 7 columns and I don't want to re write the software from the beginning, so you will see some deletion and isertion of columns..

I have 5 columns inside this excel file that have either "x" value or null.

what I am trying to do is to create a new column between range A1,and B1 that is called category, so if there is an x in column 5, i write E in the category field, else if the x is in the 6th column then I write P in the category column.. and so on. And then I need to delete these 5 columns that I no longer need (Range E1:I1)

The problem is when I debug the code, I can see that the values[,] have the column inserted, and the values has been transfered correctly, but when the temp_data.csv is produced, it has the new excel file after deletion so now it contains 11 columns, but the new Category column together with the values are not there...

                Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(p_sUBKPath, Type.Missing, false, 4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;

                // delete columns that we don't need from the new excel file
                Microsoft.Office.Interop.Excel.Range range2 = ws.get_Range("A1","A1"); 
                range2.EntireColumn.Delete();
                Microsoft.Office.Interop.Excel.Range range3 = ws.get_Range("B1", "B1");
                range3.EntireColumn.Delete();
                Microsoft.Office.Interop.Excel.Range range4 = ws.get_Range("D1", "L1");
                range4.EntireColumn.Delete();
                Microsoft.Office.Interop.Excel.Range range5 = ws.get_Range("I1", "M1");
                range5.EntireColumn.Delete();
                Microsoft.Office.Interop.Excel.Range range6 = ws.get_Range("K1", "K1");
                range6.EntireColumn.Delete();

                //insert a new column ( Category)
                Microsoft.Office.Interop.Excel.Range range7 = ws.get_Range("B1", "B1");
                range7.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight);


                object[,] tempVal = (object[,])range.Value2;
                tempVal[1, 2] = (object)"Category";

                for (int row = 2; row <= tempVal.GetUpperBound(0); row++)
                {

                    try
                    {

                        if ((!String.IsNullOrEmpty((string)tempVal[row, 5])) && (string)tempVal[row, 5] == "x")
                        {
                            tempVal[row, 2] = (string)"E";
                        }
                        else if ((!String.IsNullOrEmpty((string)tempVal[row, 6])) && (string)tempVal[row, 6] == "x")
                        {
                            tempVal[row, 2] = (string)"P";


                        }
                        else if ((!String.IsNullOrEmpty((string)tempVal[row, 7])) && (string)tempVal[row, 7] == "x")
                        {
                            tempVal[row, 2] = (string)"Phy";


                        }
                        else if ((!String.IsNullOrEmpty((string)tempVal[row, 8])) && (string)tempVal[row, 8] == "x")
                        {
                            tempVal[row, 2] = (string)"L";


                        }
                        else if ((!String.IsNullOrEmpty(tempVal[row, 9].ToString())) && (string)tempVal[row, 9] == "x")
                        {
                            tempVal[row, 2] = (string)"Ex";


                        }
                        else
                            MessageBox.Show("unknow");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }


                }


                object[,] values = tempVal;
if (Convert.ToString(values[1, 1]).ToUpper().Trim() == "SHORT NAME" && Convert.ToString(values[1, 2]).ToUpper().Trim() == "CATEGORY" && Convert.ToString(values[1, 3]).ToUpper().Trim() == "LONG NAME EN" && Convert.ToString(values[1, 4]).ToUpper().Trim() == "LONG NAME DE" && Convert.ToString(values[1, 5]).ToUpper().Trim() == "ELEMENT" && Convert.ToString(values[1, 6]).ToUpper().Trim() == "PROPERNAME" && Convert.ToString(values[1, 7]).ToUpper().Trim() == "PHYSICAL" && Convert.ToString(values[1, 8]).ToUpper().Trim() == "LOGICAL" && Convert.ToString(values[1, 9]).ToUpper().Trim() == "EXTENSION" && Convert.ToString(values[1, 10]).ToUpper().Trim() == "CREATED BY" && Convert.ToString(values[1, 11]).ToUpper().Trim() == "CREATED ON" && Convert.ToString(values[1, 12]).ToUpper().Trim() == "STATE")

                {
                    for (int row = 1; row <= values.GetUpperBound(0); row++)
                        for (int col = 1; col <= values.GetUpperBound(1); col++)
                        {
                            string value = Convert.ToString(values[row, col]);

                            if (value.Contains(","))
                            {
                                range.Cells.set_Item(row, col, value.Replace(",", p_sPsuedostring));
                            }
                            if (value.Contains("    "))
                            {
                                range.Cells.set_Item(row, col, value.Replace("  ", p_sPsuedostring + "    " + p_sPsuedostring));
                            }
                        }
                    if (File.Exists(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv")) 
                        File.Delete(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv");

                   //Save the Latest databse as Data_Temp.csv
                    ws.SaveAs(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlUnicodeText, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    xl.DisplayAlerts = true;
                    try
                    {
                        xl.Workbooks[1].Close(false, Type.Missing, Type.Missing);
                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    xl.Application.Quit();
                    xl.Quit();
                    l_bClosedSuccessfully = true;

Answer 1

Based on your above comments, let's say your Excel File looks like this. I have colored the columns Yellow which needs to be deleted.

Screenshot:

Logic:

1. Delete the columns in reverse order so that the sequence of the columns do not change else you will have to account for the newly shifted columns
2. Avoid using usedrange. UsedRange can also include unnecessary rows/columns making your code slower. I am finding the last cell which has data and then using it's .Row and .Column property to construct my range which contains "X"
3. Use .Find and .Findnext to search for "X" in your range. Once you find that, get th found cell's column number and subtract 2 from it. Since you will have only 5 columns, simply use an IF condition to check what is the value of the Column after you subtract 2 from it and then decide on the keyword that you want to use.

Code:

Try this code (TRIED AND TESTED)

using System;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

Namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        Public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            Excel.Application xlexcel;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range Rng, aCell, bCell;
            String sMsg = "";

            object misValue = Missing.Value;
            xlexcel = new Excel.Application();
            xlexcel.Visible = true;

            //~~> Open a File (Chnage filename as applicable)
            xlWorkBook = xlexcel.Workbooks.Open("C:\\MyFile.xlsx",
                         0, true, 5, "", "", true,

            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "\t", false, false, 0, true, 1, 0);

            //~~> Set Sheet 1 as the sheet you want to work with
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //~~> Delete relevant columns in reverse order
            //A,c,f,g,h,i,j,k,l,m,n,t,u,v,w,x,AA

            xlWorkSheet.get_Range("AA1", "AA1").EntireColumn.Delete();
            xlWorkSheet.get_Range("T1", "X1").EntireColumn.Delete();
            xlWorkSheet.get_Range("F1", "N1").EntireColumn.Delete();
            xlWorkSheet.get_Range("C1", "C1").EntireColumn.Delete();
            xlWorkSheet.get_Range("A1", "A1").EntireColumn.Delete();

            //~~> Insert the Category Column
            xlWorkSheet.get_Range("B1", "B1").EntireColumn.Insert(
            Excel.XlInsertShiftDirection.xlShiftToRight);

            //~~> get the last row and the last column of your data range
            //~~> This is much better than using usedrange  which might
            //'~~> include unnecessary ranges
            int lRow = xlWorkSheet.Cells.SpecialCells(
                       Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int lCol = xlWorkSheet.Cells.SpecialCells(
                       Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;

            String Addr = xlWorkSheet.Cells[1, lCol].Address;
            //~~> This is to get the column name from column number
            String ColName = Addr.Split('$')[1];

            //~~> This is your data range. I am assuming that Row 1 has headers
            Rng = xlWorkSheet.get_Range("C2:" + ColName + lRow, misValue);

            //~~> Find the first occurance of "X"
            aCell = Rng.Find("X",misValue,Excel.XlFindLookIn.xlValues,
                    Excel.XlLookAt.xlWhole,misValue,
                    Excel.XlSearchDirection.xlNext,misValue,misValue,misValue);

            //~~> Find the next occurance of "X" using FindNext
            if(aCell != null)
            {
                //~~> Get the column number and subtract 2 from it
                int col = aCell.Column-2;

                //~~> Choose the relevant keyword
                if (col == 1)
                {
                    sMsg = "Element";
                }
                else if (col == 2)
                {
                    sMsg = "propername";
                }
                else if (col == 3)
                {
                    sMsg = "physical";
                }
                else if (col == 4)
                {
                    sMsg = "logical";
                }
                else if (col == 5)
                {
                    sMsg = "extension";
                }

                //~~> Populate the Category Column
                xlWorkSheet.Cells[aCell.Row, 2].Value = sMsg;

                string sFirstFoundAddress = aCell.get_Address(true, true,
                Excel.XlReferenceStyle.xlA1, misValue, misValue);

                bCell = Rng.Cells.FindNext(aCell);

                string sAddress = bCell.get_Address(true, true,
                Excel.XlReferenceStyle.xlA1, misValue, misValue);

                //~~> FindNext until the first found cell is found again
                While (!sAddress.Equals(sFirstFoundAddress))
                {
                    //~~> Get the column number and subtract 2 from it
                    col = bCell.Column-2;

                    //~~> Choose the relevant keyword
                    if (col == 1)
                    {
                        sMsg = "Element";
                    }
                    else if (col == 2)
                    {
                        sMsg = "propername";
                    }
                    else if (col == 3)
                    {
                        sMsg = "physical";
                    }
                    else if (col == 4)
                    {
                        sMsg = "logical";
                    }
                    else if (col == 5)
                    {
                        sMsg = "extension";
                    }

                    xlWorkSheet.Cells[bCell.Row, 2].Value = sMsg;

                    bCell = Rng.Cells.FindNext(bCell);

                    sAddress = bCell.get_Address(true, true,
                    Excel.XlReferenceStyle.xlA1, misValue, misValue);
                }
            }

            //~~> Once done close and quit Excel
            xlWorkBook.Close(true, misValue, misValue);
            xlexcel.Quit();

            //~~> CleanUp
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

ScreenShot (After the code is run):