sql-server - 如何在 VB.NET 循环中检查 DataTables
问题描述
我想通过 VB.NET 将数据插入 Excel。数据是从 SQL Server 中的存储过程中检索的。
这是我的循环代码片段。
For h As Integer = 0 To ds2.Tables.Count - 1
Dim dt As DataTable = ds2.Tables(h)
For i As Integer = 0 To dt.Rows.Count - 1
Dim DT_CollectionDate_temp As DateTime = Convert.ToDateTime(dt.Rows(i)("CollectionDate").ToString())
Dim str_CollectionDate = DT_CollectionDate_temp.ToString("dd-MMM-yyyy")
ws_Term.Cells(int_details_row, 1).Value = dt.Rows(i)("KioskBatchID").ToString()
If (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) AndAlso (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) Then
batchIDTotal += dt.Rows(i)("BatchTotal")
ws_Term.Cells(int_details_row, 2).Value = batchIDTotal
End If
ws_Term.Cells(int_details_row, 3).Value = str_CollectionDate.ToString()
If (prevDate <> str_CollectionDate) AndAlso (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) Then
If (dt.Rows(i)("KioskBatchID").ToString() = dt.Rows(i)("KioskBatchID").ToString()) Then
If (prevDate <> str_CollectionDate) AndAlso (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) Then
batchTotalCount += dt.Rows(i)("RowTotalCount")
ws_Term.Cells(int_details_row, 4).Value = batchTotalCount
End If
End If
End If
If (prevDate <> str_CollectionDate) AndAlso (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) Then
If (dt.Rows(i)("KioskBatchID").ToString() = dt.Rows(i)("KioskBatchID").ToString()) Then
If (prevDate <> str_CollectionDate) AndAlso (prevBatchID <> dt.Rows(i)("KioskBatchID").ToString()) Then
TotalTransaction += dt.Rows(i)("RowTotalTransaction")
ws_Term.Cells(int_details_row, 5).Value = TotalTransaction
'ws_Term.Cells(int_details_row, 5).Value = String.Format("{0:0.00}", Convert.ToDecimal(dt.Rows(i)("RowTotalTransaction").ToString()))
End If
End If
End If
ws_Term.Cells(int_details_row, 1).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
ws_Term.Cells(int_details_row, 2).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right
ws_Term.Cells(int_details_row, 3).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right
ws_Term.Cells(int_details_row, 4).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right
ws_Term.Cells(int_details_row, 5).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right
prevDate = str_CollectionDate
prevBatchID = dt.Rows(i)("KioskBatchID").ToString()
int_details_row += 1
Next
Next
我运行的数据(04-02-2021 直到 07-02-2021)有两个表(因为两个商家:0007 和 0008 在日期之间存在),因此循环通过第一个(导致前两个行),然后是第二个(导致接下来的三行)。
它根据用户的开始日期、结束日期和商户输入生成数据,由 SQL 存储过程在此处读取并由 VB.NET 上的代码检索以生成 Excel 表
select *
from (
select KioskBatchID, sum(a.BillTransactionTotal + a.ReloadTransactionTotal) as BatchTotal
From tblExcelD002 a with(nolock)
left join tblMerchant b with (nolock) on a.Merchant = b.MerchantCode
where
(cast(CollectionDate as date) between cast(@strStartDate as date) and cast(@strEndDate as date))
And (b.MerchantCode = @strMerchant)
group by KioskBatchID
) As q1
FULL OUTER JOIN(
select CollectionDate, sum(BillTransactionTotal + ReloadTransactionTotal) as RowTotalTransaction,
sum(BillTransactionCount + ReloadTransactionCount) as RowTotalCount,KioskBatchID
From tblExcelD002 a with(nolock)
left join tblMerchant b with (nolock) on a.Merchant = b.MerchantCode
where
(cast(CollectionDate as date) between cast(@strStartDate as date) and cast(@strEndDate as date))
And (b.MerchantCode = @strMerchant)
group by KioskBatchID, CollectionDate
) AS Q2
on q1.KioskBatchID = Q2.KioskBatchID
order by Q2.KioskBatchID, CollectionDate
desc
检索存储过程的代码片段
For Each row In dsData.Tables(0).Rows
strTableName = "tbl" + row("Merchant").ToString()
Dim Parameters2 As New List(Of SqlParameter)
Parameters2.Add(New SqlParameter("@strStartDate", strAppStartDate))
Parameters2.Add(New SqlParameter("@strEndDate", strAppEndDate))
Parameters2.Add(New SqlParameter("@strMerchant", row("Merchant")))
If Not modDBCommon.ReadDataSetByStoredProcedure(StoredProc, Parameters2, strTableName, enuDatabase.dbMESH_BODB, dsD002, strMessage) = 0 Then
LogError(DEFAULT_LOG_PATH, DEFAULT_LOG_NAME, "Failed To Read StorePro", FunctionName)
Else
dsData1.Tables.Add(dsD002.Tables(0).Copy())
dsData2.Tables.Add(dsD002.Tables(1).Copy())
End If
Next
dsData2 从 Stored Proc 检索第二个查询,因为它位于其中的另一个查询之下。如您所见,它会将条件分组到 tblMerchant。因此,如果用户为商家输入“全部”,它将检索所有商家并将它们分类到不同的表中(在我的情况下,我为商家输入“全部”,开始日期 = 04-02-2021,结束日期 = 07- 02-2021 导致两个日期之间现有的商家:tbl0007 和 tbl0008)
如何使输出像下面的查询结果一样输出?我想根据 KioskBatchID 汇总其 BatchTotal 制作 KioskBatchID 的汇总表。SQL Server 本身的查询输出已经是正确的,因此我必须修复循环,让它像它一样出来。
任何帮助将不胜感激
解决方案
SQL Server 本身的查询输出已经正确
太棒了,所以你有一些工作查询,如:
SELECT * FROM whatever
您可以将数据下载到数据表中:
Using da as New SqlDataAdapter("SELECT * FROM whatever", "your conn string here")
Dim dt = new DataTable
da.Fill(dt)
End Using
您可以使用一个名为 EPPlus 的库将它变成一个非常容易的 excel 文件,如下所示:
Using ep as new ExcelPackage(fileinfo_or_stream_here))
ep.Workbook.Worksheets.Add("SheetNameHere").Cells["A1"].LoadFromDataTable(dt, true)
ep.Save()
End Using
这很好,因为您不必安装 excel,也不必在 SO 上发布一千行让每个人都感到困惑的代码;)
推荐阅读
- php - 将 DES-EDE3-CBC 从 PHP 移植到 Ruby 会产生不同的结果
- apache-spark - 将 sql 代码转换为 Pyspark 代码的问题
- ios - lineWidth SwiftUI 不正确
- flutter - 当我在颤动中添加容器小部件时显示空白屏幕
- javascript - 带参数的 Reacjs setInterval 函数
- r - 如何将大型列表对象(igraph.vs 类)转换为 R 中的数据框
- encryption - HTTPS 使用非对称或对称加密吗?
- r - dcast 和重新排序数据帧的数据
- reactjs - Typescript - 它是什么类型的函数?
- mongodb - 从 mongo db 集合中删除重复记录