我有一个GridView以这种格式返回数据:
Member ID | Yesterday Pounds | Yesterday Tons | Yesterday Dollars | Total Pounds | Total Tons | Total Dollars
000100-001 | 10,000.00 | 5.00 | $10,000.00 | 1,000,000.00 | 3,000.00 | $2,000,000.00
000100-002 | 40,000.00 | 20.00 | $10,500.00 | 4,000,000.00 | 22,000.00 | $1,000,000.00
000102-001 | 80,000.00 | 40.00 | $20,000.00 | 2,000,000.00 | 9,000.00 | $4,000,000.00
000200-005 | 40,000.00 | 20.00 | $14,000.00 | 600,000.00 | 300.00 | $200,000.00
我正在查询我们的数据库,并且必须在XML文档中编译信息并将信息发送给另一家公司。
我要做的第一件事是将我们的公司ID转换为他们的ID,我编写了一个类来完成此操作。例如,成员ID 000100-001、000100-002和000102-001都转换为1200,成员ID 000200-005转换为1201。
我还需要按公司ID汇总所有列。因此,对于会员ID 000100-001、000100-002和000102-001,我需要对“昨天磅”,“昨天吨”,“昨天美元”,“总磅”,“总吨”和“美元”进行汇总,并将结果作为一行发送给“会员ID”为1200成员ID 00200-005无需任何操作,在这种情况下,它是独立的。结果将如下所示:
Member ID | Yesterday Pounds | Yesterday Tons | Yesterday Dollars | Total Pounds | Total Tons | Total Dollars
1200 | 130,000.00 | 65.00 | $40,000.00 | 7,000,000.00 | 34,000.00 | $7,000,000.00
1201 | 40,000.00 | 20.00 | $14,000.00 | 600,000.00 | 300.00 | $200,000.00
我确实有一些转换会员ID并计算总数的类。我遇到的问题是,将数据写入XML时,它正在写入所有记录,因此我得到了ID为1200的三个条目,而不仅仅是总数的最后一个。
因此,我想将数据移动到DataTable中,在进行计算时对其进行迭代,然后仅使用所需记录重新创建DataTable,然后将DataTable移至XML。从语法上讲,我很难弄清楚这一点。
如果有更好的方法,我会全力以赴。我欢迎所有建议。
这是我的代码:
网格视图:
<asp:GridView ID="grdDailyShipments" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" ForeColor="Gray" OnRowDataBound="grdDailyShipments_RowDataBound">
<AlternatingRowStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField DataField="MEMBERID" HeaderText="MEMBER ID" ReadOnly="True" SortExpression="MEMBERID" />
<asp:BoundField DataField="YESTERDAYPOUNDS" HeaderText="YESTERDAY POUNDS" ReadOnly="True" DataFormatString="{0:N}" SortExpression="YESTERDAYPOUNDS" />
<asp:BoundField DataField="YESTERDAYTONS" HeaderText="YESTERDAY TONS" ReadOnly="True" DataFormatString="{0:N}" SortExpression="YESTERDAYTONS" />
<asp:BoundField DataField="YESTERDAYDOLLARS" HeaderText="YESTERDAY DOLLARS" ReadOnly="True" DataFormatString="{0:C}" SortExpression="YESTERDAYDOLLARS" />
<asp:BoundField DataField="TOTALPOUNDS" HeaderText="TOTAL POUNDS" ReadOnly="True" DataFormatString="{0:N}" SortExpression="TOTALPOUNDS" />
<asp:BoundField DataField="TOTALTONS" HeaderText="TOTAL TONS" ReadOnly="True" DataFormatString="{0:N}" SortExpression="TOTALTONS" />
<asp:BoundField DataField="TOTALDOLLARS" HeaderText="TOTAL DOLLARS" ReadOnly="True" DataFormatString="{0:C}" SortExpression="TOTALDOLLARS" />
</Columns>
</asp:GridView>
GridView后面的代码:
DataTable dt = new DataTable();
dt.Columns.Add("DailyReportRecordId", typeof(string));
dt.Columns.Add("Date", typeof(string));
dt.Columns.Add("ProgramID", typeof(string));
dt.Columns.Add("MemberID", typeof(string));
dt.Columns.Add("YesterdayPounds", typeof(string));
dt.Columns.Add("YesterdayTons", typeof(string));
dt.Columns.Add("YesterdayDollars", typeof(string));
dt.Columns.Add("TotalPounds", typeof(decimal));
dt.Columns.Add("TotalTons", typeof(decimal));
dt.Columns.Add("TotalDollars", typeof(string));
for (int i = 0; i < grdDailyShipments.Rows.Count; i++)
{
recordId = (i + 1).ToString();
memberId = grdDailyShipments.Rows[i].Cells[0].Text.ToString();
memberId = MemberIdConversion.convertId(memberId);
yesterdayPounds = grdDailyShipments.Rows[i].Cells[1].Text.ToString();
yesterdayTons = grdDailyShipments.Rows[i].Cells[2].Text.ToString();
yesterdayDollars = grdDailyShipments.Rows[i].Cells[3].Text.ToString();
// Get the value returned to the Grid
totalPounds = Convert.ToDecimal(grdDailyShipments.Rows[i].Cells[4].Text.ToString());
// Call combine totals to total all the subsidiaries
totalPounds = CombineTotalPounds.combineTotalPounds(memberId, totalPounds);
// Get the value returned to the Grid
totalTons = Convert.ToDecimal(grdDailyShipments.Rows[i].Cells[5].Text.ToString());
// Call combine totals to total all the subsidiaries
totalTons = CombineTotalTons.combineTotalTons(memberId, totalTons);
// Get the value returned to the Grid
totalDollarsStr = grdDailyShipments.Rows[i].Cells[6].Text.ToString();
// Strip off the $, if you don't to this the Convert.ToDecimal WILL NOT
// work due to formatting issues
totalDollarsStr = totalDollarsStr.Substring(1);
// Convert the string to a decimal and round it.
totalDollars = Math.Round(Convert.ToDecimal(totalDollarsStr), 2);
// Convert back to a string.
totalDollarsStr = totalDollars.ToString();
// Format to Currency.
totalDollarsStr = String.Format("{0:C}", totalDollars);
DataRow dr = dt.NewRow();
dr[0] = recordId;
dr[1] = invoiceDateStr;
dr[2] = "1202";
dr[3] = memberId;
dr[4] = yesterdayPounds;
dr[5] = yesterdayTons;
dr[6] = yesterdayDollars;
dr[7] = totalPounds;
dr[8] = totalTons;
dr[9] = totalDollarsStr;
xmlWriter.WriteStartElement("DailyReportRecordId");
xmlWriter.WriteAttributeString("id", recordId);
xmlWriter.WriteStartElement("Date");
xmlWriter.WriteString(invoiceDateStr);
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("ProgramID");
xmlWriter.WriteString("1202");
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("MemberID");
xmlWriter.WriteString(memberId);
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("YesterdayPounds");
xmlWriter.WriteString(yesterdayPounds);
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("YesterdayTons");
xmlWriter.WriteString(yesterdayTons);
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("YesterdayDollars");
xmlWriter.WriteString(yesterdayDollars);
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("TotalPounds");
xmlWriter.WriteString(totalPounds.ToString("#,##0.00"));
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("TotalTons");
xmlWriter.WriteString(totalTons.ToString("#,##0.00"));
xmlWriter.WriteEndElement();
xmlWriter.WriteStartElement("TotalDollars");
//xmlWriter.WriteString(totalDollarsStr.ToString());
xmlWriter.WriteString(totalDollarsStr);
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement(); // End of Daily Report Record Id
}
我了解到,每条记录在遍历网格时都会在for循环中写入XML。会员ID转换有效,总计也是如此。重申一下,问题在于只有最后一条记录(如果有多个记录)具有正确的总数,其上方的记录随着代码的进行而累积。
因此,现在使用数据表中的数据,我想执行以下操作:
for(int i = 0; i < dt.Rows.Count; i ++)
{
if(dt(i).memberid = dt(i - 1).memberid)
(
set variable here to write to XML
)
}
但这是伪代码,因为我很难弄清楚这一点。
再说一次,如果我的方法不是最好的,我很乐于接受想法。
谢谢
有很多方法可以解决此问题,但是基于已有的最简单的方法是向后遍历GridView,因为您说过要获取每个成员ID的最后一行。我已经修改了您的伪代码以显示我的意思:
string lastId = null;
for(int i = (dt.Rows.Count-1); i >= 0; i--)
{
if(lastId == null || lastId != dt(i).memberid)
{
//write XML, as we're at the last for the given memberid,
//assuming the GridView is sorted by memberid.
}
lastId = dt(i).memberid;
}
如果没有对成员ID进行分组,我仍将向后浏览DataGrid,但添加一个Dictionary或List变量以跟踪已添加到XML的memberId:
List<string> memberIdsAddedToXml = new List<string>();
for(int i = (dt.Rows.Count-1); i >= 0; i--)
{
if(!memberIdsAddedToXml.Contains(dt(i).memberid))
{
memberIdsAddedToXml.Add(dt(i).memberid);
//write XML, as we're at the last row for the given memberid
}
}
我认为最好是创建一个Foo
表示网格视图中数据的类()。然后创建一个方法,该方法Foo
通过遍历将数据复制到列表中的网格视图行来返回对象列表。创建另一个方法,该方法采用Foo
对象列表并将其写入XML文档。最后,创建一个结合了前面两种方法的方法。一个例子:
void ExportTotalsAsXml(DataTable dt, string xmlPath)
{
List<Foo> list = ExtractDataFromData(dt);
List<Foo> filteredList = new List<Foo>
//loop through the list backwards to get the total row per memberid, storing it in filteredList
WriteToXml(filteredList, xmlPath);
}
通过这种方式将其分离,您将能够更轻松地了解每个部分的功能,尤其是稍后再进行更改或错误修复时。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句