将多个文本框数据插入Excel文件 [英] Inserting multiple textbox data into an Excel file

问题描述

我想编写一个程序，将文本框中的文本保存到使用循环的Excel文件中，因为我想在Excel中插入多个文本。我发现代码，但它只覆盖单元格中的数据。我希望程序找到最后一行并将新数据插入下一行。我被困在这里，请有人帮助我如何在c＃中。

  object misValue = System.Reflection.Missing。值; 
 
 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] =地址; （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; 
} 
  

解决方案

不需要使用循环。看到这个例子

让我们来看看你的表格。

代码：（TRIED AND TESTED）

  using System; 
使用System.Collections.Generic; 
使用System.ComponentModel; 
使用System.Data; 
使用System.Drawing; 
使用System.Linq; 
使用System.Text; 
使用System.Windows.Forms; 
使用Excel = Microsoft.Office.Interop.Excel; 
 
命名空间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; 
 
公共Form1（）
 {
 InitializeComponent（）; 
} 
 
 // ~~>打开文件
 private void button1_Click（object sender，EventArgs e）
 {
 xlexcel = new Excel.Application（）; 
 
 xlexcel.Visible = true; 
 
 //打开文件
 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] =地址; 
} 
 
 // ~~>添加数据
 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; 
} 
 
 // ~~>一旦完成关闭并退出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（无法释放对象+ ex.ToString（））; 
} 
 finally 
 {
 GC.Collect（）; 
} 
} 
} 
} 
  

FOLLOWUP FROM COMMENTS

范围对象是工作表对象的一部分。所以你不应该在那里得到任何错误。就像上面提到的那样，代码被试用和测试。

更多FOLLOWUP（来自评论）

上述代码在VS 2010 Ultimate上进行了测试。如果你有VS 2008然后替换行

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

与

  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 ; 
  

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

解决方案

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

Let's say your form looks like this.

Code: (TRIED AND TESTED)

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.

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 ;

这篇关于将多个文本框数据插入Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！