rearranging data inside a excel sheet c#

Question

I have an excel file which contains data in the following format

I want to rearrange the data in the following format.

I am using office.interop assembly for opening and editing excel

Answer 1

@ankit goel , Welcome to Microsoft Q&A, you could try the following code to rearrange the data in the excel.

            Excel.Application excel = null;  
            excel = new Excel.Application();  
            //excel.Visible = true;  
            Excel.Workbook wkb = null;  
            wkb = excel.Workbooks.Open("C:\\Users\\Administrator\\Desktop\\Test.xlsx");  
            Excel.Worksheet sheet = wkb.Sheets[1];  
            Excel.Range range1 = sheet.UsedRange;  
            // Read all data from data range in the worksheet  
            var valueArray = (object[,])range1.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);  
            int m = 1;  
            sheet.Cells.ClearContents();  
            for (int i = 1; i <= valueArray.GetLength(0); i++)  
            {  
                if (i % 2 != 0)  
                {  
                    sheet.Cells[m, 1].Value = valueArray[i, 1];  
                    sheet.Cells[m, 2].Value = valueArray[i, 2];  
                    sheet.Cells[m, 3].Value = valueArray[i+1, 1];  
                    sheet.Cells[m, 4].Value = valueArray[i+1, 2];  
                    m++;  
                }  
                  
            }  
           
            wkb.Save();  
            wkb.Close(true);  
            excel.Quit();  
   

Tested result:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.