question

RaduCostel-2613 avatar image
0 Votes"
RaduCostel-2613 asked David-9140 Suspended answered

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

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();
dotnet-csharpoffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JackJJun-MSFT avatar image
1 Vote"
JackJJun-MSFT answered RaduCostel-2613 commented

@RaduCostel-2613, 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, I've found this method last night and it works like a charm. This should be a lesson to me to use already implemented algorithms from the library.

Thank you!

0 Votes 0 ·
RaduCostel-2613 avatar image
0 Votes"
RaduCostel-2613 answered RaduCostel-2613 commented

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!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unfortunately, this only copies the last value that was read through all the set range.

Could someone assist please?

Thank you!

0 Votes 0 ·
David-9140 avatar image
0 Votes"
David-9140 Suspended answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.