vb.net 使用VB在excel中求和列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25695488/
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
Sum columns in excel using VB
提问by BG-
OK, I've got a straight-forward 2-d block of data in excel: row 1 and column 1 are labels, the rest are numbers. My task right now is to put the sum of each column in the first empty cell(row) underneath.
好的，我在 excel 中有一个直接的二维数据块：第 1 行和第 1 列是标签，其余的是数字。我现在的任务是将每列的总和放在下面的第一个空单元格（行）中。
Whereas my practice dataset is of known dimensions, the actual datasets I'll be using this program on will have a variable number of rows and columns. To this end, I can't just say "=SUM(B2:B20)" because the last filled cell won't always be B20 (for example). The easiest way to total each column, I thought, would be a FOR..NEXT loop, but I just can't get VS to accept the summation formula. Here's what I've got so far:
虽然我的练习数据集具有已知维度，但我将在此程序上使用的实际数据集将具有可变数量的行和列。为此，我不能只说“=SUM(B2:B20)”，因为最后填充的单元格并不总是 B20（例如）。我认为，对每一列求和的最简单方法是 FOR..NEXT 循环，但我无法让 VS 接受求和公式。这是我到目前为止所得到的：
`With xlWsheet2 'check for last filled row and column of transposed data'
If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow2 = .Cells.Find(What:="*",
After:=.Cells(1, 1),
LookAt:=Excel.XlLookAt.xlPart,
LookIn:=Excel.XlFindLookIn.xlFormulas,
SearchOrder:=Excel.XlSearchOrder.xlByRows,
SearchDirection:=Excel.XlSearchDirection.xlPrevious,
MatchCase:=False).Row
Else : lRow2 = 1
End If
If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
lCol2 = .Cells.Find(What:="*",
After:=.Range("A1"),
LookAt:=Excel.XlLookAt.xlPart,
LookIn:=Excel.XlFindLookIn.xlFormulas,
SearchOrder:=Excel.XlSearchOrder.xlByRows,
SearchDirection:=Excel.XlSearchDirection.xlPrevious,
MatchCase:=False).Column
Else : lCol2 = 1
End If
lastcell2 = xlWsheet2.Cells(lRow2, lCol2) 'defines last row, column of transposed data'
emptyRow1 = xlWsheet2.Rows(lRow2).Offset(1) 'defines the first empty row'
'add in cell of SUM underneath each column'
For i As Integer = 2 To lCol2
colTop = xlWsheet2.Cells(2, i)
colBot = xlWsheet2.Cells(lRow2, i)
ELtotal = xlWsheet2.Range(emptyRow1, i)
ELtotal = .Sum(.Range(colTop, colBot))
Next i
End With
` Now, the ELtotal statements used to be one long line, but I was trying to see what part VS had a problem with. It breaks at the first one, .Range(emptyRow1, i). Here's other iterations of that equation I've tried that weren't accepted:
` 现在，ELtotal 语句曾经是一行很长的语句，但我想看看 VS 的哪个部分有问题。它在第一个 .Range(emptyRow1, i) 处中断。这是我尝试过但未被接受的该等式的其他迭代：
.Range(emptyRow1, I).Formula = "=SUM(colTop, colBot)"
.Range(emptyRow1, I).Formula = "=SUM(.cells(2,i), (lRow2,i))"
.Range(emptyRow1, I).Formula = .sum(.range(colTop, colBot)
.Range(emptyRow1, I).Value = etc...
ad inifintum
无止境
PS- I'm pretty new to this, so I'm probably going about this whole process the wrong way...
PS-我对此很陌生，所以我可能会以错误的方式处理整个过程......
采纳答案by Clif
Based on what you told me about the row and column headings, I believe that this code will do what you want, namely put a single column sum in the first empty cell underneath.
根据你告诉我的关于行和列标题的内容，我相信这段代码会做你想做的，即将单列总和放在下面的第一个空单元格中。
Sub find()
Dim lastrow As Long, lastcol As Long, thiscol As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
For thiscol = 2 To lastcol
Cells(lastrow + 1, thiscol).Select
ActiveCell.Value = WorksheetFunction.Sum(Range(Cells(1, ActiveCell.Column), ActiveCell))
Next
End Sub
Best of luck.
祝你好运。
回答by Pieter Geerkens
This formula will do the trick of summing two whole columns, A and B in this case:
在这种情况下，此公式将完成对两列 A 和 B 求和的技巧：
= sum($A:$B)
If it is possible for the headers to be interpreted as numeric values that might contribute to the sum then the formula should be amended to be
如果标题可以被解释为可能对总和有贡献的数值，那么公式应该修改为
= sum($A:$B) - sum($A:$B)
回答by Sumit Saha
In order to export to excel with sum of all numeric columns from DataGridView, add a button to your form and add the following code in its click event:-
为了使用 DataGridView 中所有数字列的总和导出到 excel，请在表单中添加一个按钮并在其单击事件中添加以下代码：-
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'--- Export to Excel -------------------------------------------------
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
Dim datarange As Excel.Range
Dim save_file As New SaveFileDialog
'give its extension
''save_file.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*"
'save_file.Filter = "xls files (*.xls)|*.xls"
save_file.Filter = "xls files (*.xls)|*.xls|Excel 2007|*.xlsx"
''Select xls
save_file.FilterIndex = 2
save_file.FileName = "My_excel_report_"
save_file.RestoreDirectory = True
Try
If save_file.ShowDialog() = DialogResult.OK Then
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
For x = 0 To DataGridViewSummary1.ColumnCount - 1
xlWorkSheet.Cells(0 + 1, x + 1) = _
DataGridViewSummary1.Columns(x).HeaderText
Next
For i = 0 To DataGridViewSummary1.RowCount - 1
For j = 0 To DataGridViewSummary1.ColumnCount - 1
If IsDate(DataGridViewSummary1(j, i).Value) Then
'MsgBox("The cell value is date")
xlWorkSheet.Cells(i + 2, j + 1) = FormatDateTime(CDate(DataGridViewSummary1(j, i).Value.ToString), DateFormat.ShortDate)
xlWorkSheet.Cells(i + 2, j + 1).HorizontalAlignment = Excel.Constants.xlCenter
xlWorkSheet.Cells(i + 2, j + 1).VerticalAlignment = Excel.Constants.xlCenter
Else
xlWorkSheet.Cells(i + 2, j + 1) = _
DataGridViewSummary1(j, i).Value.ToString()
End If
Next
Next
datarange = xlWorkBook.ActiveSheet.UsedRange
datarange.Font.Name = "Consolas"
datarange.Font.Size = 10
'--- added on 07/09/2016 -------------------------------------------------------------------
Dim lastrow, lastcol As Long
With xlWorkSheet
lastcol = .Cells(1, .Columns.Count).End(Excel.XlDirection.xlToLeft).Column
lastrow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
End With
'MessageBox.Show("The last column in Sheet1 which has data is " & lastcol)
'MessageBox.Show("The last row in Col A of Sheet1 which has data is " & lastrow)
For i = 2 To lastcol
If IsNumeric(xlWorkSheet.Cells(lastrow, i).Value) Then
xlWorkSheet.Cells(lastrow + 1, i).Select()
xlWorkSheet.Cells(lastrow + 1, i).Value = xlApp.WorksheetFunction.Sum(xlWorkSheet.Range(xlWorkSheet.Cells(1, i), xlWorkSheet.Cells(lastrow + 1, i)))
End If
Next i
xlWorkSheet.Columns.AutoFit()
'----------------------------------------------------------------------------------------------
xlWorkSheet.SaveAs(save_file.FileName) 'sd.filename reurns save file dialog path
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
'--------------------------------------
Dim proc As Process = Nothing
Dim startInfo As New ProcessStartInfo
startInfo.FileName = "EXCEL.EXE"
startInfo.Arguments = save_file.FileName
Process.Start(startInfo)
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
'GlobalErrorHandler(ex)
End Try
End Sub
