ASP.. NET c#如何在GridView中更改数据并写入XML

本文关键字：数据 XML NET GridView ASP | 更新日期: 2023-09-27 18:08:28

我有一个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 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的三个条目，而不仅仅是最后一个带有总数的条目。

所以我想我会将数据移动到数据表中，通过它进行迭代计算，然后仅使用我想要的记录重新创建数据表，然后将数据表移动到XML。从语法上讲，我搞不清楚。
如果有更好的办法，我洗耳恭听。我欢迎所有的建议。

下面是我的代码: GridView: 

<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转换可以工作，total也可以。重申一下，问题是只有最后一个记录(如果有多个记录)有正确的总数，它上面的记录是随着代码的进展而累积的。

对于DataTable中的数据我想这样做:

for(int i = 0; i < dt.Rows.Count; i ++)
{
     if(dt(i).memberid = dt(i - 1).memberid)
     (
       set variable here to write to XML
     )
}

但这是伪代码，因为我搞不清楚。

再次强调，如果我的方法不是最好的，我愿意听取别人的意见。

谢谢

ASP.. NET c#如何在GridView中更改数据并写入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中的成员id:

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);
}

通过这种方式将其分开，您将能够更容易地看到每个部分正在做什么，特别是在稍后进行更改或修复错误时。

我不知道我对你的理解是否正确，但也许这将在某种程度上帮助你。您可以使用LINQ计算总数，并将结果写入新数据表，然后使用数据表将数据表写入xml。WriteXml方法即可https://msdn.microsoft.com/en-us/library/system.data.datatable.writexml (v = vs.110) . aspx 

dt = new DataTable();
        dt.Columns.Add("MemberID", typeof(int));
        dt.Columns.Add("YesterdayPounds", typeof(double));
        dt.Columns.Add("YesterdayTons", typeof(double));
        dt.Columns.Add("YesterdayDollars", typeof(double));
        dt.Columns.Add("TotalPounds", typeof(decimal));
        dt.Columns.Add("TotalTons", typeof(decimal));
        dt.Columns.Add("TotalDollars", typeof(string));
        dt.Rows.Add("1200", 10000.00, 500, 10000.00, 1000, 3000.00, 2000000.00);
        dt.Rows.Add("1200", 10000.00, 500, 10000.00, 1000, 3000.00, 2000000.00);
        dt.Rows.Add("1201", 10000.00, 500, 10000.00, 1000, 3000.00, 2000000.00);
        dt.Rows.Add("1202", 10000.00, 500, 10000.00, 1000, 3000.00, 2000000.00);
        var result = dt.AsEnumerable()
          .GroupBy(r => r.Field<int>("MemberID"))
          .Select(g =>
          {
              var row = dt.NewRow();
              row["MemberID"] = g.Key;
              row["YesterdayPounds"] = g.Sum(r => r.Field<double>("YesterdayPounds"));
              row["YesterdayTons"] = g.Sum(r => r.Field<double>("YesterdayTons"));
              row["YesterdayDollars"] = g.Sum(r => r.Field<double>("YesterdayDollars"));
              return row;
          }).CopyToDataTable();
        ds = new DataSet();
        ds.Tables.Add(dt);
        ds.Tables.Add(result);
        result.WriteXml(@"G:'MyDataset.xml");
相关文章：