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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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