Click here to Skip to main content
15,895,423 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Friends,

I need to update an existing excel file, while googling i found mainly three method, OLEDB,Interop and openXML. So i am confused which one will be better and effective.
My Requirement is, I need to open an excel file an make necessary changes and save as in different location. For updating excel i have a mapper file. i need to read the mapper file and update the cells. My excel file contain around 15 sheets and each contains around 30 rows and 10 columns.
My Mapper file structure is

Item, SheetName, CellName
Amount, sheet1, B2
Tax, sheet2, D10

Please guide me to achieve this.

What I have tried:

C#
public static void excelsave()
       {
           Excel.Application oExcel = new Excel.Application();
           try
           {
               string excelorignalpath = @"C:\Users\Hp\Desktop\exceltest.xlsx";
               string excelbackuppath = @"C:\Users\Hp\Desktop\Backup\exceltest.xlsx";

               Excel.Workbook book = oExcel.Workbooks.Open(excelorignalpath);
               Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[1];
               Excel.Range excelRange = sheet.UsedRange;
               object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
               valueArray[2, 2] = 12345678;
               excelRange.Value = valueArray;
               book.SaveAs(excelbackuppath);
               book.Close();
               Marshal.ReleaseComObject(sheet);
               Marshal.ReleaseComObject(book);
           }
           catch (Exception ex)
           {

           }
           finally
           {
               Marshal.ReleaseComObject(oExcel);
               GC.Collect();
               GC.WaitForPendingFinalizers();
           }

       }
Posted
Updated 24-Jul-16 10:51am
v2

1 solution

This looks pretty complicated:
C#
Excel.Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
valueArray[2, 2] = 12345678;
excelRange.Value = valueArray;

try this instead:
C#
Cells(2, 2).Value = 12345678;
 
Share this answer
 
v2
Comments
jinesh sam 28-Jul-16 3:50am    
Excel.Cells(2,2) is not a valid statement. its throwing error. please help
Patrice T 28-Jul-16 6:07am    
oops, should be better now.
jinesh sam 2-Aug-16 11:28am    
Thanks sheet.Cells[2, 2] works :) My question was which one is the best way to update the excel. Any suggestions on that?
Patrice T 2-Aug-16 13:35pm    
The way I update the cell is better than yours.
jinesh sam 2-Aug-16 13:39pm    
100% I agree..I modified my code accordingly. Thanks :)

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