如何使用 vb.net 将文本框中的数据插入到 Excel 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15307729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to insert data from textbox to excel using vb.net?
提问by user2107624
I'm stuck here, I want to insert the data from 5 textbox to existing excel file in columns. I found code but its inserting by rows. I have below a code that finds the last non empty row and from that row I want to move to the next row and insert data there, for example last row is A2, I want to insert new data to A3, B3, C3, D3, E3.
我被困在这里，我想将 5 个文本框中的数据插入到列中现有的 excel 文件中。我找到了代码，但它是按行插入的。我在下面的代码中找到了最后一个非空行，我想从该行移动到下一行并在那里插入数据，例如最后一行是 A2，我想将新数据插入到 A3、B3、C3、D3 ，E3。
I can't get the right loop for this.
我无法为此找到正确的循环。
Dim lRow As Long = 0
Call OpenExcelFile("C:\Users\PB\Desktop\BookRecords.xlsx", 1)
With xlWorkSheet
If EXL.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow = .Cells.Find(What:="*", _
After:=.Range("A2"), _
LookAt:=Excel.XlLookAt.xlPart, _
LookIn:=Excel.XlFindLookIn.xlFormulas, _
SearchOrder:=Excel.XlSearchOrder.xlByRows, _
SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
MatchCase:=False).Row
Else
lRow = 1
End If
lRow += 1
End With
MessageBox.Show("The last row in Sheet1 which has data is " & lRow)
Dim j As Integer
j = 3
For i As Integer = j To 8
xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtTitle.Text
xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtAuthor.Text
xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtEdition.Text
xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtPublisher.Text
xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtISBN.Text
Next
j += 1
Call SaveAndCloseExcelSub()
回答by Jeremy Thompson
Does this help:
这是否有帮助：
Dim j As Integer
j = 3
For i As Integer = j To 8
MsgBox("Cell column: " & ColumnNumberToName(i))
Next
Code to convert numbers to Excel Column Names (A-Z, AA, etc):
将数字转换为 Excel 列名（AZ、AA 等）的代码：
Public Shared Function ColumnNumberToName(columnNumber As Int32) As String
Dim dividend As Int32 = columnNumber
Dim columnName As [String] = [String].Empty
Dim modulo As Int32
While dividend > 0
modulo = (dividend - 1) Mod 26
columnName = Convert.ToChar(65 + modulo).ToString() + columnName
dividend = DirectCast(((dividend - modulo) / 26), Int32)
End While
Return columnName
End Function
Public Shared Function ColumnNameToNumber(columnName As [String]) As Int32
If [String].IsNullOrEmpty(columnName) Then
Throw New ArgumentNullException("columnName")
End If
Dim characters As Char() = columnName.ToUpperInvariant().ToCharArray()
Dim sum As Int32 = 0
Dim i As Int32 = 0
While i < characters.Length
sum *= 26
sum += (characters(i) - "A"C + 1)
System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
End While
Return sum
End Function
回答by Floris
As it is, all your values are being written to the same cell; then you move down a row and do it again. Cells are addressed as (row, column) - so you need to change your code like this:
实际上，您的所有值都被写入同一个单元格；然后你向下移动一排，然后再做一次。单元格被寻址为（行，列） - 因此您需要像这样更改代码：
Dim j As Integer
j = 3
For i As Integer = j To 8
xlWorkSheet.Cells(lRow + i, 1).Value = txtTitle.Text
xlWorkSheet.Cells(lRow + i, 2).Value = txtAuthor.Text
xlWorkSheet.Cells(lRow + i, 3).Value = txtEdition.Text
xlWorkSheet.Cells(lRow + i, 4).Value = txtPublisher.Text
xlWorkSheet.Cells(lRow + i, 5).Value = txtISBN.Text
Next
Not sure if you need to start with i = 3if you add lRowto it but I'm sure you can work that out.
不确定是否需要从i = 3添加lRow开始，但我相信你可以解决这个问题。
If you want the order reversed, do this (this example copies only one lot of text... I am beginning to suspect there is no need for the loop)
如果您希望顺序颠倒，请执行此操作（此示例仅复制大量文本...我开始怀疑不需要循环）
dim i as Integer
i = 1; ' if you want column A
With xlWorkSheet
.Cells(lRow , i).Value = txtTitle.Text
.Cells(lRow + 1, i).Value = txtAuthor.Text
.Cells(lRow + 2, i).Value = txtEdition.Text
.Cells(lRow + 3, i).value = txtPublisher.Text
.Cells(lRow + 4, i).Value = txtISBN.Text
End With
