Read data from Excel files

Question

I'm having some trouble reading from an Excel spreadsheet in C#. I have this code which I read every cell from A to X.

System.Array myvalues; string[] strArray;
Microsoft.Office.Interop.Excel.Range range = 
    worksheet.get_Range("A" + i.ToString(), "W" + i.ToString());

while(range.Count!=0)
{
    i++;
    //Console.WriteLine(i);
    range = worksheet.get_Range("A" + i.ToString(), "W" + i.ToString());
    myvalues = (System.Array)range.Cells.Value;
    strArray = ConvertToStringArray(myvalues);
    name = clearstr(strArray[1]);

    for ( int j = 1 ; j <= Int32.Parse(number_add_file)*4 ; j++) 
    {
        name = "";
        lang_add = "";
        price = "";
        description = "";
        Console.WriteLine("I got in!");

        Microsoft.Office.Interop.Excel.Range range_add = 
            worksheet.get_Range("X" + i.ToString(),Type.Missing);

        System.Array values = (System.Array)range_add.Cells.Value;
        string[] str = ConvertToStringArray(values);
        name = str[0];
        lang_add = str[1];
        price = str[2];
        description = str[3];
        Console.WriteLine(name + "   " 
          + lang_add + "   " + price + "   " + description);

        addfile();
    }

My question is: How could i read next 4 * "number" rows in excel based on "number" value ?

For example:

         A B C D E F G H I J
         a a a a a 1 a a a a 

F's cell value is 1 so I would like to read ( G H I J) If F's cell value is 2 the I would like to read ( G H I J K L M N)

         A B C D E F G H I J K L M N
         a a a a a 2 a a a a a a a a

F's cell value 3 :

        A B C D E F G H I J K L M N O P Q R
        a a a a a 3 a a a a a a a a a a a a

Answer 1

string testList = "";
            String str1 = "";
            string logPath = @"E:\LogForConsoleApp.txt";
            string filePath = @"E:\SaveSheetName.txt";
            string Path = @"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\MSTest";
            List<string> ltSheetName = new List<string>();
            List<string> ltMethodName = new List<string>();
            Process myProcess = new Process();
            Excel.Application appExl = new Excel.Application();
            Excel.Workbook workbook = null;
            Excel.Worksheet NwSheet;
            Excel.Range ShtRange;
            appExl = new Excel.Application();
            workbook = appExl.Workbooks.Open("E:\\inputSheet3", Missing.Value, ReadOnly: false);
            NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
            ShtRange = NwSheet.UsedRange; //gives the used cells in sheet
            int rCnt = 0;
            int cCnt = 0;

            for (rCnt = 1; rCnt <= ShtRange.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= ShtRange.Columns.Count; cCnt++)
                {
                    if (Convert.ToString(NwSheet.Cells[rCnt, cCnt].Value2) == "Y")
                    {
                        ltSheetName.Add(NwSheet.Cells[rCnt, cCnt - 1].Value2);
                        //ltMethodName.Add(" /test:" + NwSheet.Cells[rCnt, cCnt - 1].Value2);
                    }
                }
            }
            workbook.Close(false, Missing.Value, Missing.Value);
            appExl.Quit();

            for (int sht = 0; sht < ltSheetName.Count; sht++)
            {
                ltMethodName.Clear();
                appExl = new Excel.Application();
                workbook = appExl.Workbooks.Open(ltSheetName[sht].ToString(), Missing.Value, ReadOnly: false);
                NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                ShtRange = NwSheet.UsedRange; //gives the used cells in sheet
                int rCnt1 = 0;
                int cCnt1 = 0;

                for (rCnt1 = 1; rCnt1 <= ShtRange.Rows.Count; rCnt1++)
                {
                    for (cCnt1 = 1; cCnt1 <= ShtRange.Columns.Count; cCnt1++)
                    {
                        if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "Y")
                        {
                            ltMethodName.Add(" /test:" + NwSheet.Cells[rCnt, cCnt - 1].Value2);
                        }
                    }
                }
                workbook.Close(false, Missing.Value, Missing.Value);
                appExl.Quit();


                for (int i = 0; i < ltMethodName.Count; i++)
                {
                    str1 = ltMethodName[i].ToString();
                    testList += str1;
                }

                string foldername = "TestResult_" + DateTime.Today.ToString().Remove(DateTime.Today.ToString().LastIndexOf("/") + 5);
                foldername = foldername.Replace("/", "");

                string direc = @"E:\" + foldername;
                string fileName = ltSheetName[sht].ToString().Substring(ltSheetName[sht].ToString().LastIndexOf("\\") + 1) + "_InderdeepAutRes.trx";
                if (!Directory.Exists(direc))
                    Directory.CreateDirectory(direc);
                string testcase = "";

                if (!File.Exists(direc + "\\" + fileName))
                    testcase = " /testcontainer:" + "E:\\Practice\\TestingSample\\TestingSample\\bin\\Debug\\TestingSample.dll" + testList + " /resultsfile:" + direc + "\\" + fileName;
                else
                {
                    Directory.Delete(direc, true);
                    Directory.CreateDirectory(direc);
                    testcase = " /testcontainer:" + "E:\\Practice\\TestingSample\\TestingSample\\bin\\Debug\\TestingSample.dll" + testList + " /resultsfile:" + direc + "\\" + fileName;
                }

                ProcessStartInfo myProcessStartInfo = new ProcessStartInfo(Path, testcase);

                try
                {
                    TextWriter tw = new StreamWriter(filePath, false);
                    tw.WriteLine(ltSheetName[sht].ToString());
                    tw.Close();
                    myProcess.StartInfo = myProcessStartInfo;
                    myProcessStartInfo.UseShellExecute = false;
                    myProcessStartInfo.RedirectStandardOutput = true;
                    myProcess.Start();
                    string output = myProcess.StandardOutput.ReadToEnd();
                    //myProcess.WaitForExit();
                    Console.WriteLine(output);

                }
                catch (Exception ex)
                {
                    TextWriter tw = new StreamWriter(logPath, true);
                    tw.WriteLine(ex.StackTrace);
                    tw.Close();
                }
            }

Answer 2

This is for .NET 4.0:

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("somefile.xls");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange; // get the entire used range
int value = 0;
if(Int32.TryParse(xlRange.Cells[1,6].Value2.ToString(), out value)) // get the F cell from the first row
{
   int numberOfColumnsToRead = value * 4;
   for(int col=7; col < (numberOfColumnsToRead + 7); col++)
   {
      Console.WriteLine(xlRange.Cells[1,col].Value2.ToString()); // do whatever with value
   }
}

This will open the workbook and get the first worksheet in the workbook. We then get the entire used range and put that in the range variable. From there, we try to parse the int in column "F" (which is the 6th column, it is 1 based not zero based) in the first row. If that parsing is successful, we then multiply that number by 4 to see how many columns you need (in your post you said rows, but your examples were columns). We use a for loop to start at the G column (column 7) and go to the number of columns to read + 7 (to account for the columns that we skipped). You would be free to do what you want with the values but for this example I just wrote them to the console.

Answer 3

It is inot direct answer to your question, but you can easily refer to your cells like this:

int row1 = 1;
int row2 = 5;

sheet1.Cells[row1, row1+row2].Value=row1.ToString();
String Rng = Convert.ToString(sheet1.Cells[row1, row2-row1].Address());