Home > DeveloperSection > Forums > Writing Data to an Existing Excel File in C#
Ashish Pandey

Total Post:128

Points:898
Posted on    December-18-2014 10:11 PM

 ASP.Net C#  Excel 
Ratings:


 2 Reply(s)
 1024  View(s)
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();

            }

        }

    }

}



Manoj Bhatt
Manoj Bhatt

Total Post:153

Points:1079
Posted on    December-19-2014 1:42 AM

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;


Jayden Bell
Jayden Bell

Total Post:105

Points:739
Posted on    December-19-2014 6:42 AM

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

            }

        }

    }

}


Don't want to miss updates? Please click the below button!

Follow MindStick