Click here to Skip to main content
15,880,037 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to copy, Edit ,save an excel file in a specific Location in C# using excel interop? i have following method which will edit the existing same excel.But i want to copy the original excel file and edit 2 colums and save that as a diffrent location with any name.

so i should have the original one and new one with edited.Please suggest an efficient way

My code which will edit the existing one and save as itself below.only  2 column values i have to edit


What I have tried:

private void EditExcel(string fileName)
{
  
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // excel sheet 
        Excel.Range xlRange = xlWorksheet.UsedRange;
   
        int lastUsedRow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
                           System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                           Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
                           false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;

   
           
            for (int i = 2; i <= lastUsedRow; i++)
            {
                int j = 0 + i;

                xlWorksheet.Cells[i, 1] = "new value";
                xlWorksheet.Cells[i, 2] = "new value";
                xlWorksheet.Cells[i, 3] = xlWorksheet.Cells[i, 3]
             
            }

            //cleanup  
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //release com objects to fully kill excel process from running in the background  
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release  
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release  
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);

        }
C#

Posted
Updated 24-Aug-21 5:28am
Comments
Richard MacCutchan 24-Aug-21 11:34am    
xlWorksheet.Cells[i, 3] = xlWorksheet.Cells[i, 3]

That is a circular reference and will cause problems in Excel.
[no name] 24-Aug-21 11:49am    
how can i fix? how can i copy the given excel and add this mtd?

1 solution

You can use Workbook.SaveAs(Object, Object, Object, Object, Object, Object, XlSaveAsAccessMode, Object, Object, Object, Object, Object) Method (Microsoft.Office.Tools.Excel) | Microsoft Docs[^] method to save the opened file to a desired location with a new name.

So in general
- Open the file you want to edit
- Make modifications needed
- Use SaveAs to save to a new location with a new name
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900