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

Radu Costel 21 Reputation points
2021-10-03T09:35:09.477+00:00

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();
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,633 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,713 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jack J Jun 24,496 Reputation points Microsoft Vendor
    2021-10-04T01:50:30.117+00:00

    @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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Radu Costel 21 Reputation points
    2021-10-03T10:19:31.79+00:00

    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!


  2. David 151 Reputation points
    2021-10-25T02:36:42.84+00:00

    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");
            }
        }
    }
    
    0 comments No comments