4

I want to write a program that saves the text in textbox to an Excel file using a loop because I want to insert multiple text into Excel. I found codes but it only overwrites data in cells. I want the program to find the last row and insert new data into the next row. I'm stuck here, please someone help me how to do that in c#.

object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

xlWorkSheet.Cells[1, 1] = "FirstName";
xlWorkSheet.Cells[1, 2] = "LastName";
xlWorkSheet.Cells[1, 3] = "JobTitle";
xlWorkSheet.Cells[1, 4] = "Address";

for (int i=2; i<=6; i++)
{
    xlWorkSheet.Cells[i, 1] = textBox1.Text;
    xlWorkSheet.Cells[i, 2] = textBox2.Text;
    xlWorkSheet.Cells[i, 3] = textBox3.Text;
    xlWorkSheet.Cells[i, 4] = textBox4.Text;
}
Improve this question
1
  • Why are you using a loop? The above code will write to cells in a loop. Shouldn't you be putting the above code in say a button click and then simply write to the last row? Commented Oct 14, 2013 at 7:37

2 Answers 2

Reset to default
4

Like I mentioned that you don't need to use a loop. See this example

Let's say your form looks like this.

enter image description here

CODE

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

Namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        Public Form1()
        {
            InitializeComponent();
        }

        //~~> Open File
        private void button1_Click(object sender, EventArgs e)
        {
            xlexcel = new Excel.Application();

            xlexcel.Visible = true;

            // Open a File
            xlWorkBook = xlexcel.Workbooks.Open("C:\\MyFile.xlsx", 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);

            xlWorkSheet.Cells[1, 1] = "FirstName";
            xlWorkSheet.Cells[1, 2] = "LastName";
            xlWorkSheet.Cells[1, 3] = "JobTitle";
            xlWorkSheet.Cells[1, 4] = "Address";
        }

        //~~> Add Data
        private void button2_Click(object sender, EventArgs e)
        {
            int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1 ;

            xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
            xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
            xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
            xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
        }

        //~~> Once done close and quit Excel
        private void button3_Click(object sender, EventArgs e)
        {
            xlWorkBook.Close(true, misValue, misValue);
            xlexcel.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
        }

        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();
            }
        }
    }
}

FOLLOWUP FROM COMMENTS

Range object is a part of worksheet object. So you shouldn't be getting any errors there. And Like I mentioned above, the code is tried and tested.

enter image description here

MORE FOLLOWUP (From Comments)

The above code was tested on VS 2010 Ultimate. If you have VS 2008 then replace the line

int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count,
               1].End[Excel.XlDirection.xlUp].Row + 1;

with

int _lastRow = xlWorkSheet.Cells.Find(
                                      "*",
                                      xlWorkSheet.Cells[1,1],
                                      Excel.XlFindLookIn.xlFormulas,
                                      Excel.XlLookAt.xlPart,
                                      Excel.XlSearchOrder.xlByRows,
                                      Excel.XlSearchDirection.xlPrevious,
                                      misValue,
                                      misValue,
                                      misValue
                                      ).Row + 1 ;
Improve this answer
Sign up to request clarification or add additional context in comments.

22 Comments

xactly i wntd same thng...thnx bro.... – user2877742 23 secs ago I thought so :)
getting a problem in the above code regarding :- Property, indexer, or event 'Range' is not supported by the language; try directly calling accessor method 'Microsoft.Office.Interop.Excel._Worksheet.get_Range(object, object)' wt shld b needed to do...??
in this line:- int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1 ;
@SiddharthRout: How do you get xlWorkSheet.Range for exel worksheet object? I don't see Range option for Excel worksheet (interop).
@Sangram: Because I know Range is a part of xlWorkSheet like I know xlWorkSheet is a part of xlexcel Please note that the above code is tired and tested. I am surprised that you are not getting that in intellisense. Updating my above post with a screenshot
|
0

Your main concern is to find the last used row in your excel.

For that you can use 

 Excel.Range usedRange = xlWorkSheet .UsedRange;
 Excel.Range _lastCell= usedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,  
 Type.Missing);

  int _lastRow= lastCell.Row; // Gives you the last used row in your Excel sheet
  int _lastCol = lastCell.Column; // Give you the last used column



 _lastRow++; // To get the next row 

    for (int i=_lastRow; i<=_lastrow+6; i++) // Set your i value to _lastRow
    {
        xlWorkSheet.Cells[i, 1] = textBox1.Text;
        xlWorkSheet.Cells[i, 2] = textBox2.Text;
        xlWorkSheet.Cells[i, 3] = textBox3.Text;
        xlWorkSheet.Cells[i, 4] = textBox4.Text;
    }
Improve this answer

6 Comments

bro dis is nt solving my prblm.......i want to insert different data each time i insert a diffeerent value in textbox........bt it shows the same value six times in a row(i<=6) and repeating the previou one....
I have made slight changes in code Lets say the last used row in excel sheet is 10 then according to the code your values are written at [11,1][11,2].....[12,1][12,2]...[13,1],[13,2]...[16,1][16,2][16,3][16,4] Now when you run this whole code again next time the last row value you get is 16 and then the loop values are written at [17,1][17,2]... so where are the values overwritten ????
bt hw cn i insert a new value to the last row without replacing the previous one.......my main concern is dat,,.....??bcoz i m runiing the code it says replace the pprevious file.....i dnt wnt 2 replace the previous file
I guess it is asking to replace the excel file ...for that you have to save the excel file and then open it again in next time
yes it is aassking to replace the existing file.....nd wen i replce it ....it shows new data.....nd i dnt replace it....den error occurs...
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.