Writing Data to an Existing Excel File in C#

Total Post:128

Points:898
C#  Excel 
 1897  View(s)
Ratings:
Rate this:

i want to write data to existing excel file. the file have sheet1, i want to write on sheet2, then save. the problem is that every time i save it will create a new excel file and overwrite the existing one. any help to keep the old data while saving. 

I have the following function 

using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;
 
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);             MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString()); 
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit(); 
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        } 
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

  1. Post:154

    Points:1086
    Re: Writing Data to an Existing Excel File in C#

    Here is the code which will do the insertion in an already exists excel file. 

    private static Microsoft.Office.Interop.Excel.Workbook
    mWorkBook;
    private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
    private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
    private static Microsoft.Office.Interop.Excel.Application oXL;
    public static void ReadExistingExcel()
    {
       string path = @"C:\Tool\Reports1.xls";
       oXL = new Microsoft.Office.Interop.Excel.Application();
       oXL.Visible = true;
       oXL.DisplayAlerts =false;
       mWorkBook =oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
       //Get all the sheets in the workbook
      mWorkSheets =mWorkBook.Worksheets;
       //Get the allready exists sheet
       mWSheet1 =(Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
       Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;
       int colCount =range.Columns.Count;
       int rowCount=range.Rows.Count;
       for (int index = 1; index < 15; index++)
       {
          mWSheet1.Cells[rowCount + index, 1] = rowCount +index;
          mWSheet1.Cells[rowCount + index, 2] = "New Item"+index;
       }
       mWorkBook.SaveAs(path,Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
       Missing.Value, Missing.Value,Missing.Value, Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
       Missing.Value,Missing.Value, Missing.Value,   Missing.Value,Missing.Value);
       mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
       mWSheet1 = null;
       mWorkBook = null;
       oXL.Quit();
       GC.WaitForPendingFinalizers();
       GC.Collect();
       GC.WaitForPendingFinalizers();
       GC.Collect();
    }

    If you need to create a new Sheet use the following code. 

    oSheet = (Excel.Worksheet)oWB.Sheets.Add(Missing.Value, Missing.Value, Missing.Value,      Missing.Value); 

    oSheet.Name = SheetName;

      Modified On Apr-07-2018 05:16:11 AM
  1. Post:110

    Points:774
    Re: Writing Data to an Existing Excel File in C#

    public class ExcelFile

        private string excelFilePath = string.Empty;
        private int rowNumber = 1; // define first row number to enter data in excel 
        Excel.Application myExcelApplication;
        Excel.Workbook myExcelWorkbook;
        Excel.Worksheet myExcelWorkSheet; 
        public string ExcelFilePath
        {
            get { return excelFilePath; }
            set { excelFilePath = value; }
        } 
        public int Rownumber
        {
            get { return rowNumber; }
            set { rowNumber = value; }
        } 
        public void openExcel()
        {
            myExcelApplication = null;
     
            myExcelApplication = new Excel.Application(); // create Excell App
            myExcelApplication.DisplayAlerts = false; // turn off alerts 
     
            myExcelWorkbook = (Excel.Workbook)(myExcelApplication.Workbooks._Open(excelFilePath, System.Reflection.Missing.Value,
               System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
               System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
               System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
               System.Reflection.Missing.Value, System.Reflection.Missing.Value)); // open the existing excel file
     
            int numberOfWorkbooks = myExcelApplication.Workbooks.Count; // get number of workbooks (optional)
     
            myExcelWorkSheet = (Excel.Worksheet)myExcelWorkbook.Worksheets[1]; // define in which worksheet, do you want to add data
            myExcelWorkSheet.Name = "WorkSheet 1"; // define a name for the worksheet (optinal)
     
            int numberOfSheets = myExcelWorkbook.Worksheets.Count; // get number of worksheets (optional)
        }
     
        public void addDataToExcel(string firstname, string lastname, string language, string email, string company)
        {
     
            myExcelWorkSheet.Cells[rowNumber, "H"] = firstname;
            myExcelWorkSheet.Cells[rowNumber, "J"] = lastname;
            myExcelWorkSheet.Cells[rowNumber, "Q"] = language;
            myExcelWorkSheet.Cells[rowNumber, "BH"] = email;
            myExcelWorkSheet.Cells[rowNumber, "CH"] = company;
            rowNumber++;  // if you put this method inside a loop, you should increase rownumber by one or wat ever is your logic
     
        }
     
        public void closeExcel()
        {
            try
            {
                myExcelWorkbook.SaveAs(excelFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
                                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                               System.Reflection.Missing.Value, System.Reflection.Missing.Value); // Save data in excel
     
     
                myExcelWorkbook.Close(true, excelFilePath, System.Reflection.Missing.Value); // close the worksheet
     
     
            }
            finally
            {
                if (myExcelApplication != null)
                {
                    myExcelApplication.Quit(); // close the excel application
                }
            }
        }
    }

      Modified On Apr-07-2018 05:14:37 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!