Write Range Values from an excel file to another excel in C#

Question

I have the following code and I am trying to copy certain Range values from one Excel file to another.

I have managed to select the range from the first excel, but it does not "paste" it where I've selected the range to.

In this example, it copies it to the first cells in the second excel.

Could anyone help?

Thank you!

Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
            Workbook workbook = application.Workbooks.Open(@"Excel.xlsx", 1);
            Worksheet xlWorksheet = (Worksheet)workbook.Sheets[9];


            Workbook workbook1 = application.Workbooks.Open(@"Excel2.xlsx", 1);
            Worksheet xlWorksheet1 = (Worksheet)workbook1.Sheets[1];

            Range range1 = xlWorksheet.get_Range("C1", "C5");
            object[,] valueArray = (object[,])range1.get_Value(XlRangeValueDataType.xlRangeValueDefault);

            Range range2 = xlWorksheet1.get_Range("F1", "F5");
            object[,] valueArray2 = (object[,])range2.get_Value(XlRangeValueDataType.xlRangeValueDefault);


            for (int i = 2; i <= valueArray.GetLength(0); i++)
            {
                for (int j = 1; j <= valueArray.GetLength(1); j++)
                {
                    for (int k = 1; k <= valueArray2.GetLength(0); k++)
                    {
                        for (int l = 1; l <= valueArray2.GetLength(1); l++)
                        {
                            valueArray2[i,j] = valueArray[i, j].ToString();

                        }
                    }
                }
            }
            workbook1.Save();
            workbook.Close();
            workbook1.Close();
            application.Quit();

Answer 1

@Radu Costel , based on my test, you could try to use Range.Copy method to Copy Range from one excel to anther excel.

Code:

        Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();  
        Workbook workbook = application.Workbooks.Open(@"D:\Excel.xlsx", 1);  
        Worksheet xlWorksheet = (Worksheet)workbook.Sheets[1];  
        Workbook workbook1 = application.Workbooks.Open(@"D:\Excel2.xlsx", 1);  
        Worksheet xlWorksheet1 = (Worksheet)workbook1.Sheets[1];  
        Range range1 = xlWorksheet.get_Range("C1", "C5");  
        Range range2 = xlWorksheet1.get_Range("F1", "F5");  
        range1.Copy(range2);  
        workbook1.Save();  
        workbook.Save();  
        workbook.Close();  
        workbook1.Close();  
        application.Quit();  
   

---

If the response is helpful, please click "Accept Answer" and upvote it.

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.

Answer 2

I think I have done it by using
range2.set_Value(XlRangeValueDataType.xlRangeValueDefault, valueArray2);

I will keep testing as I have to copy multiple columns to the second excel.

Nonetheless, it copies to the selected ranges as I've intended.

Thank you!

Answer 3

Install Spire.XLS via Nuget, and use the following code snippet to copy a cell range between different workbooks.

using Spire.Xls;

namespace CopyCellRange
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the source workbook
            Workbook sourceBook = new Workbook();
            sourceBook.LoadFromFile(@"C:\Users\Administrator\Desktop\source.xlsx");
            //Get the source worksheet
            Worksheet sourceSheet = sourceBook.Worksheets[0];
            //Get the source cell range
            CellRange sourceRange = sourceSheet.Range["A1:C3"];
            //Load the destination workbook
            Workbook destBook = new Workbook();
            destBook.LoadFromFile(@"C:\Users\Administrator\Desktop\destination.xlsx");
            //Get the destination worksheet
            Worksheet destSheet = destBook.Worksheets[0];
            //Get the destination cell range
            CellRange destRange = destSheet.Range["B2:D4"];
            //Copy data from the source range to the destination range
            sourceSheet.Copy(sourceRange, destRange);
            //Save the destination workbook to an Excel file
            destBook.SaveToFile("CopyRange.xlsx");
        }
    }
}