插入多文本数据转换成Excel文件 [英] Inserting multiple textbox data into an Excel file

问题描述

我想编写一个程序，使用一个循环，因为我想多个文本插入Excel保存在文本框中的文本到Excel文件。我发现代码，但它只是在细胞覆盖的数据。我希望程序找到了最后一排，然后插入新的数据到下一行。我在这里卡住了，请帮助我的人怎么做，在C＃。

 对象misValue = System.Reflection.Missing。值; 
 
 xlApp =新Excel.ApplicationClass（）; 
 xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）; 
 
 xlWorkSheet.Cells [1,1] =姓; 
 xlWorkSheet.Cells [1,2] =名字; 
 xlWorkSheet.Cells [1,3] =JOBTITLE; 
 xlWorkSheet.Cells [1,4] =地址; 
 
的for（int i = 2; I< = 6;我++）
 {
 xlWorkSheet.Cells [1,1] = textBox1.Text; 
 xlWorkSheet.Cells [Ⅰ，2] = textBox2.Text; 
 xlWorkSheet.Cells [I，3] = textBox3.Text; 
 xlWorkSheet.Cells [Ⅰ4] = textBox4.Text; 
} 
  

解决方案

就像我提到，你不'T需要使用一个循环。见这个例子

让我们假设你的形式看起来像这样

码：（久经考验的）

 使用系统; 
使用System.Collections.Generic; 
使用System.ComponentModel; 
使用System.Data这; 
使用System.Drawing中; 
使用System.Linq的; 
使用System.Text;使用System.Windows.Forms的
;使用Excel =的Microsoft.Office.Interop.Excel 
; 
 
命名空间WindowsFormsApplication2 
 {
公共部分Form1类：表格
 {
 Microsoft.Office.Interop.Excel.Application xlexcel; 
 Microsoft.Office.Interop.Excel.Workbook xlWorkBook; 
 Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; 
对象misValue = System.Reflection.Missing.Value; 
 
公共Form1中（）
 {
的InitializeComponent（）; 
} 
 
 // ~~>打开文件
私人无效的button1_Click（对象发件人，EventArgs五）
 {
 xlexcel =新Excel.Application（）; 
 
 xlexcel.Visible = TRUE; 
 
 //打开一个文件
 xlWorkBook = xlexcel.Workbooks.Open（C：\\MyFile.xlsx，0，真实，5，，，真，
 Microsoft.Office.Interop.Excel.XlPlatform.xlWindows，\t，假的，假的，0，真正的，1，0）; 
 
 xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）; 
 
 xlWorkSheet.Cells [1,1] =姓; 
 xlWorkSheet.Cells [1,2] =名字; 
 xlWorkSheet.Cells [1,3] =JOBTITLE; 
 xlWorkSheet.Cells [1,4] =地址; 
} 
 
 // ~~>添加数据
私人无效button2_Click（对象发件人，EventArgs五）
 {
 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 
私人无效button3_Click（对象发件人，EventArgs五）
 {
 xlWorkBook.Close（真，misValue，misValue）; 
 xlexcel.Quit（）; 
 
 releaseObject（xlWorkSheet）; 
 releaseObject（xlWorkBook）; 
 releaseObject（xlexcel）; 
} 
 
私人无效releaseObject（obj对象）
 {
试
 {
 System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ ）; 
的obj = NULL; 
} 
赶上（异常前）
 {
的obj = NULL; 
 MessageBox.Show（无法释放对象+ ex.ToString（））; 
} 
终于
 {
 GC.Collect的（）; 
} 
} 
} 
} 
  

< STRONG>后续自评

Range对象是工作表对象的一部分。所以，你不应该得到任何错误那里。就像我上面提到的，代码是经得起考验的。

更多随访（从评论）

上面的代码是在VS 2010终极测试。如果你有2008 VS再更换行

  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屋！