I am trying to generate XML based on excel rows upload.. lets say i have 20 rows in excel including heading it has to take 19 rows into consideration and generate 4 XML files.
Below is the code i have tried..
public void PublishSACHistoryXML()
{
//Total Rows from Excel
int totalRows = 0;
DataTable TotRows = _datatable.AsEnumerable() // retriews 19 rows excluding header
.CopyToDataTable();
totalRows = TotRows.Rows.Count;
int endDataRow = 5;
int pagecount;
// int startDataRow = 1;
bool _break = false;
int fileNo = 0;
if (totalRows % endDataRow == 0)
{
pagecount = totalRows / endDataRow;
}
else
{
pagecount = (totalRows / endDataRow) + 1;
}
for (int startDataRow = 0; startDataRow < pagecount; startDataRow++)
{
DataTable SelectedRows = _datatable.AsEnumerable()
.Skip(startDataRow * endDataRow)
.Take(endDataRow)
.CopyToDataTable();
// selectedrows datatable is picking as below
row number 2-6 for first XML
row number 7-11 for second XML
row number 12-16 for third XML
row number 17-19 for fourth XML
// generate XML based on selected rows
}
Expected Output
row number 2-5 for first XML
row number 6-10 for second XML
row number 11-15 for first XML
row number 16-19 for second XML
row number 2-5 for first XML
row number 6-10 for second XML
row number 11-15 for first XML
row number 16-19 for second XML
The expected output looks a bit strange to me. The first XML has 4 items and the rest have 5 or less?
If this what you want then in the first loop Take(endDataRow-1). By the way the variable
endDataRow could be named as PageSize. Also change the
for loop into a while loop and adjust the
Skip() accordingly.
As for this issue, please refer to the following code:
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[2] { new DataColumn("EventID"), new DataColumn("Status") });
for (int i = 1; i <= 19; i++)
{
dt.Rows.Add(1000 + i, "status" + i.ToString());
}
DataTable TotRows =dt.AsEnumerable().CopyToDataTable();
GridView1.DataSource = TotRows;
GridView1.DataBind();
int totalRows = TotRows.Rows.Count;
int endDataRow = 5;
int pagecount;
if (totalRows % endDataRow == 0)
{
pagecount = totalRows / endDataRow;
}
else
{
pagecount = (totalRows / endDataRow) + 1;
}
StringBuilder sb = new StringBuilder();
for (int startDataRow = 0; startDataRow < pagecount; startDataRow++)
{
sb.AppendLine("Page " + startDataRow.ToString());
sb.AppendLine("<br/>");
DataTable SelectedRows = dt.AsEnumerable()
.Skip(startDataRow * endDataRow)
.Take(endDataRow)
.CopyToDataTable(); //use foreach to loop through the datatable
foreach (DataRow row in SelectedRows.Rows)
{ //If you want to remove the first row
if (row.Field<string>("EventID") != "1001")
sb.AppendLine(row.Field<string>("EventID") + " ");
}
sb.AppendLine("<br/>");
}
Response.Write(sb.ToString());
The ouyput screenshot:
Best regards,
Dillion
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
17 Points
151 Posts
XML Paging
Sep 21, 2015 03:18 PM|sreekanth.jonna|LINK
Hi,
I am trying to generate XML based on excel rows upload.. lets say i have 20 rows in excel including heading it has to take 19 rows into consideration and generate 4 XML files.
Below is the code i have tried..
public void PublishSACHistoryXML() { //Total Rows from Excel int totalRows = 0; DataTable TotRows = _datatable.AsEnumerable() // retriews 19 rows excluding header .CopyToDataTable(); totalRows = TotRows.Rows.Count; int endDataRow = 5; int pagecount; // int startDataRow = 1; bool _break = false; int fileNo = 0; if (totalRows % endDataRow == 0) { pagecount = totalRows / endDataRow; } else { pagecount = (totalRows / endDataRow) + 1; } for (int startDataRow = 0; startDataRow < pagecount; startDataRow++) { DataTable SelectedRows = _datatable.AsEnumerable() .Skip(startDataRow * endDataRow) .Take(endDataRow) .CopyToDataTable(); // selectedrows datatable is picking as belowrow number 2-6 for first XML row number 7-11 for second XML row number 12-16 for third XML row number 17-19 for fourth XML
// generate XML based on selected rows }
Expected Output
row number 2-5 for first XML row number 6-10 for second XML row number 11-15 for first XML row number 16-19 for second XMLContributor
4812 Points
1289 Posts
Re: XML Paging
Sep 21, 2015 05:20 PM|jerryjoseph|LINK
The expected output looks a bit strange to me. The first XML has 4 items and the rest have 5 or less?
If this what you want then in the first loop Take(endDataRow-1). By the way the variable endDataRow could be named as PageSize. Also change the for loop into a while loop and adjust the Skip() accordingly.
linkedin | twitter | www.jerryjoseph.net
Member
17 Points
151 Posts
Re: XML Paging
Sep 21, 2015 05:33 PM|sreekanth.jonna|LINK
Hi joseph,
Thanks for your response, could you please provide some sample.
Thanks
Sree
Contributor
4812 Points
1289 Posts
Re: XML Paging
Sep 21, 2015 06:32 PM|jerryjoseph|LINK
Try something like this.
DataTable dataTable = _datatable.AsEnumerable() // retrieves 19 rows excluding header .CopyToDataTable(); var totalRows = dataTable.Rows.Count; // 19 int startRowIndex = 0; int pageNumber = 1; while(totalRows >= startRowIndex) { var pageSize = (pageNumber == 1) ? 4 : 5; // 4 items for first XML & max 5 for the rest DataTable selectedRows = _datatable.AsEnumerable() .Skip(startRowIndex) .Take(pageSize) .CopyToDataTable(); startRowIndex += pageSize; // calculate new startRowIndex }linkedin | twitter | www.jerryjoseph.net
Member
17 Points
151 Posts
Re: XML Paging
Sep 21, 2015 09:51 PM|sreekanth.jonna|LINK
Hi Joseph,
I tired above solution, but it is taking 4 items for the whole loop. It is not taking 5 items from second time when it loops. Could you pls help
Thanks
Sree
All-Star
45489 Points
7008 Posts
Microsoft
Re: XML Paging
Sep 22, 2015 01:09 AM|Zhi Lv - MSFT|LINK
Hi Sree,
As for this issue, please refer to the following code:
DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[2] { new DataColumn("EventID"), new DataColumn("Status") }); for (int i = 1; i <= 19; i++) { dt.Rows.Add(1000 + i, "status" + i.ToString()); } DataTable TotRows =dt.AsEnumerable().CopyToDataTable(); GridView1.DataSource = TotRows; GridView1.DataBind(); int totalRows = TotRows.Rows.Count; int endDataRow = 5; int pagecount; if (totalRows % endDataRow == 0) { pagecount = totalRows / endDataRow; } else { pagecount = (totalRows / endDataRow) + 1; } StringBuilder sb = new StringBuilder(); for (int startDataRow = 0; startDataRow < pagecount; startDataRow++) { sb.AppendLine("Page " + startDataRow.ToString()); sb.AppendLine("<br/>"); DataTable SelectedRows = dt.AsEnumerable() .Skip(startDataRow * endDataRow) .Take(endDataRow) .CopyToDataTable();//use foreach to loop through the datatable foreach (DataRow row in SelectedRows.Rows) {
//If you want to remove the first row if (row.Field<string>("EventID") != "1001") sb.AppendLine(row.Field<string>("EventID") + " "); } sb.AppendLine("<br/>"); } Response.Write(sb.ToString());
The ouyput screenshot:
Best regards,
Dillion
Contributor
4812 Points
1289 Posts
Re: XML Paging
Sep 22, 2015 03:25 PM|jerryjoseph|LINK
Oh I missed to increment the pageNumber. Try this.
DataTable dataTable = _datatable.AsEnumerable() // retrieves 19 rows excluding header .CopyToDataTable(); var totalRows = dataTable.Rows.Count; // 19 int startRowIndex = 0; int pageNumber = 1; while(totalRows >= startRowIndex) { var pageSize = (pageNumber == 1) ? 4 : 5; // 4 items for first XML & max 5 for the rest DataTable selectedRows = _datatable.AsEnumerable() .Skip(startRowIndex) .Take(pageSize) .CopyToDataTable(); startRowIndex += pageSize; // calculate new startRowIndex pageNumber++; // increment pageNumber }linkedin | twitter | www.jerryjoseph.net