WRITING DATA TO AN EXISTING EXCEL FILE IN C#

Ashish Pandey

Total Post:128

Points:898
Posted by  Ashish Pandey
C#  Excel 
 1495  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. Manoj Bhatt

    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;

  1. Jayden Bell

    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

                }

            }

        }

    }

Answer

NEWSLETTER

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