Click here to Skip to main content
14,699,048 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:

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 11:51am
v2

1 solution

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

try this instead:
Cells(2, 2).Value = 12345678;
   
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