CODEWITHSUNDEEP
c#excel-interop
Gerald
Gerald

Reputation: 69

how can i GET all/total worksheet in a xlsx by using interop?

how can I get all worksheets (for example 3) with a total of 10 'ROW' and combine it into another new sheet? Problem: the result I get is it looped through/read through the last sheet/ NO.3 which only display 10 rows. Cannot display all each sheet of the total row in a sheet which is 30 rows.

WHAT I TRIED: I tried looping through the sheet which is 3 and looping through the rows which is 10.

           for (int sheet = 1; sheet <= 3; sheet++)
         {
    xlworkSheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(sheet);

        //rw is equal to 10 rows
            for (int row = 0; row <= rw; row++)
            {


                //extract out excel value and store it into empID variable

var empID=(string)(xlworkSheet.Cells[row,1]as(Excel.Range).Value.ToString();

                //store empID into employeeobj               
                employeeobj.employeeID = empID;
   xlNewSheet2.Cells[row, 1].Value = employeeobj.employeeID;
             }
           }

what I get: 10 rows of the last sheets in a new sheet. my goals: 30 rows of the total sheet in a new sheet.

Upvotes: 0

Views: 108

Answers (1)

Tomato32
Tomato32

Reputation: 2245

You can create a list of data contains the value of all rows or using a count variable. Hope to help, my friend :))

//Solution 1: Using a count variable

int k = 0;
for (int sheet = 1; sheet <= 3; sheet++)
{
    xlworkSheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(sheet);

    //rw is equal to 10 rows
    for (int row = 0; row <= rw; row++)
    {    

       //extract out excel value and store it into empID variable    
       var empID=(string)(xlworkSheet.Cells[row,1]as(Excel.Range).Value.ToString();

       //store empID into employeeobj               
       employeeobj.employeeID = empID;

       xlNewSheet2.Cells[k, 1].Value = employeeobj.employeeID;
       k++;
     }
}

//Solution 2: Using a List

List<string> data = new List<string>();
for (int sheet = 1; sheet <= 3; sheet++)
{
    xlworkSheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(sheet);

        //rw is equal to 10 rows
     for (int row = 0; row <= rw; row++)
     {    

         //extract out excel value and store it into empID variable    
         var empID=(string)(xlworkSheet.Cells[row,1]as(Excel.Range).Value.ToString();

         //store empID into employeeobj               
         employeeobj.employeeID = empID;
         //xlNewSheet2.Cells[row, 1].Value = employeeobj.employeeID;
         data.Add(employeeobj.employeeID);
       }
 }

//Finally, just bind data to the new sheet.

Upvotes: 1

Related Questions