如何在excel中找到下一个空行并将数据导出到该行？ [英] How do I find the next empty row in excel and export data to it?

问题描述

我当前可以将每个textBox导出到第2行的单个单元格。但是它只能写入覆盖先前数据的位置。我试图弄清楚如何在每次提交后确定下一个可用的空白行。下面是我当前只写入第2行的代码。

 Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; 
 Microsoft.Office.Interop.Excel.Sheets xlBigSheet; 
 
对象misValue; 
 String myPath; 
 private void Button1_Click（object sender，EventArgs e）
 {
 
 string myPath = @C：\\Users\\N0m4d \\Desktop \\ \\\Longhouse\\database.xlsx; //这必须是完整路径。 
 FileInfo fi = new FileInfo（myPath）; 
 if（！fi.Exists）
 {
 Console.Out.WriteLine（file is not exists！）; 
} 
 else 
 {
 var excelApp = new Microsoft.Office.Interop.Excel.Application（）; 
 var workbook = excelApp.Workbooks.Open（myPath）; 
工作表工作表= workbook.ActiveSheet作为工作表; 
 
 
范围range1 = worksheet.Cells [2,1]作为Range; 
 range1.Value2 = textBox1.Text; 
范围range2 = worksheet.Cells [2,2]作为Range; 
 range2.Value2 = textBox2.Text; 
范围range3 = worksheet.Cells [2,3]作为Range; 
 range3.Value2 = textBox3.Text; 
范围range4 = worksheet.Cells [2,4]作为Range; 
 range4.Value2 = textBox4.Text; 
范围range5 = worksheet.Cells [2,5]作为Range; 
 range5.Value2 = textBox5.Text; 
范围range6 = worksheet.Cells [2,6]作为Range; 
 range6.Value2 = textBox6.Text; 
 
 excelApp.Visible = true; 
 workbook.Save（）; 
 //workbook.Close（）; 
 
 textBox1.Text = string.Empty; 
 textBox2.Text = string.Empty; 
 textBox3.Text = string.Empty; 
 textBox4.Text = string.Empty; 
 textBox5.Text = string.Empty; 
 textBox6.Text = string.Empty; 
 
 xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlBigSheet.get_Item（Sheet1）; 
 Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells（Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell，Type.Missing）; 
 int lastUsedRow = last.Row; 
 getData（lastUsedRow + 1）; 
} 
} 
 
 private void getData（int lastRow_）
 {
 
 lastRow_ = xlWorkSheet.Cells.Find（
 *，
 xlWorkSheet.Cells [1,1]，
 misValue，
 Microsoft.Office.Interop.Excel.XlLookAt.xlPart，
 Microsoft.Office.Interop.Excel .XlSearchOrder.xlByRows，
 Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious，
 misValue，
 misValue，
 misValue）.Row + 1; 
} 
 

我尝试过：



尝试了谷歌的多项建议，但我并不完全了解如何根据我的需要修改它们。

解决方案

使用C＃将数据附加到现有Excel文件 - Stack Overflow [< a href =https://stackoverflow.com/questions/41841251/appending-data-to-existing-excel-file-using-c-sharptarget =_ blanktitle =New Window> ^ ]

Quote：

xlWorkSheet =（Microsoft.Office.Interop.Excel .Worksheet）xlBigSheet.get_Item（Sheet1）;

Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells（Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell，Type .Missing）;

int lastUsedRow = last.Row;

getData（lastUsedRow + 1）;

在vba中，我使用

 RowS = worksheet.UsedRange.RowS.Count 
 ColS = worksheet.UsedRange.Columns .Count 
 

Interop应提供非常相似的东西。

I can currently currently export each textBox to an individual cell on row 2. However it can only write to that location which overwrites the previous data. I am trying to figure out how to make it identify the next available blank row after every submission. Below is my current code that only write to row 2.

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
       Microsoft.Office.Interop.Excel.Sheets xlBigSheet;

       object misValue;
       String myPath;
       private void Button1_Click(object sender, EventArgs e)
       {

           string myPath = @"C:\\Users\\N0m4d\\Desktop\\Longhouse\\database.xlsx"; // this must be full path.
           FileInfo fi = new FileInfo(myPath);
           if (!fi.Exists)
           {
               Console.Out.WriteLine("file doesn't exists!");
           }
           else
           {
               var excelApp = new Microsoft.Office.Interop.Excel.Application();
               var workbook = excelApp.Workbooks.Open(myPath);
               Worksheet worksheet = workbook.ActiveSheet as Worksheet;


               Range range1 = worksheet.Cells[2, 1] as Range;
               range1.Value2 = textBox1.Text;
               Range range2 = worksheet.Cells[2, 2] as Range;
               range2.Value2 = textBox2.Text;
               Range range3 = worksheet.Cells[2, 3] as Range;
               range3.Value2 = textBox3.Text;
               Range range4 = worksheet.Cells[2, 4] as Range;
               range4.Value2 = textBox4.Text;
               Range range5 = worksheet.Cells[2, 5] as Range;
               range5.Value2 = textBox5.Text;
               Range range6 = worksheet.Cells[2, 6] as Range;
               range6.Value2 = textBox6.Text;

               excelApp.Visible = true;
               workbook.Save();
               //workbook.Close();

               textBox1.Text = string.Empty;
               textBox2.Text = string.Empty;
               textBox3.Text = string.Empty;
               textBox4.Text = string.Empty;
               textBox5.Text = string.Empty;
               textBox6.Text = string.Empty;

               xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item("Sheet1");
               Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
               int lastUsedRow = last.Row;
               getData(lastUsedRow + 1);
           }
       }

       private void getData(int lastRow_)
       {

           lastRow_ = xlWorkSheet.Cells.Find(
                       "*",
                       xlWorkSheet.Cells[1, 1],
                       misValue,
                       Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                       Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                       Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
                       misValue,
                       misValue,
                       misValue).Row + 1;
       }

What I have tried:

Tried multiple suggestions from google but I do not full understand how to modify them to fit my needs.

解决方案

Appending data to existing Excel file using C# - Stack Overflow[^]

Quote:

xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
int lastUsedRow = last.Row;
getData(lastUsedRow + 1);

In vba, I use

RowS = worksheet.UsedRange.RowS.Count
ColS = worksheet.UsedRange.Columns.Count

Interop should offer something pretty similar.

这篇关于如何在excel中找到下一个空行并将数据导出到该行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！