将数据行转列分组显示

来源：互联网发布：sql select返回值类型编辑：程序博客网时间：2024/05/15 11:21

效果图：

需打印界面：

打印出效果：

前台：

<head runat="server">
<title></title>
<style type="text/css">
table{
border:0px solid ; border-collapse: collapse; cellspacing:0px ; cellpadding:0px;
}
.td2{border:0px}
td{
border:1px solid; cellspacing:0px ; cellpadding:0px;height:20px;
}
th{
border:1px solid; cellspacing:0px ; cellpadding:0px;height:20px;
}
th,td{
word-wrap:break-word;
word-break:break-all;
}

.tdStyle{
font-size:10px;
font-weight:bold;
}

</style>
<script language="javascript" type="text/javascript">
function doPrint() {
document.getElementById("btnPrint").style.display="none";
window.print();
}

window.onblur=function(){
document.getElementById("btnPrint").style.display="block";
}
</script>
</head>

<asp:Literal ID="Literal1" runat="server"></asp:Literal>

</div>

后台：

XMLHelper xmlHelper = new XMLHelper();

string procInstID = string.Empty;
if (Request.QueryString["procinstid"] != null)
{
procInstID = Request.QueryString["procinstid"].ToString();
}
else
{
return;
}
string xmlString = bisDataCmp.GetGlobalXmlData(Convert.ToInt32(procInstID));

XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);

DataTable dt = this.RowChangeColumn(doc);
this.ShowDataTableRecords(dt);

/// <summary>
/// 行转列：
/// </summary>
/// <param name="doc"></param>
/// <returns></returns>

如图：

private DataTable RowChangeColumn(XmlDocument doc)
{
DataTable dt = this.CreateDataTableRecords(doc, "root/gridSupplier/row");
DataTable newTable=CreateDataTableColumn(2);
List<string> list = new List<string>();
int newGroup = dt.Rows.Count / 4;
if (dt.Rows.Count % 4 != 0)
{
for (int index1 = 0; index1 < newGroup+1;index1++) //被4整除有余数；
{
for (int index3 = 0; index3 < this.GetFieldKeys().Length; index3++)
{
DataRow drData = newTable.NewRow();
for (int index2 = index1 * 4; index2 < index1 * 4 + 4; index2++)
{
if (index2>dt.Rows.Count-1)
{
list.Add("");
}
else
{
list.Add(dt.Rows[index2][index3].ToString());
}

}
for (int index4 = 0; index4 < 4; index4++)
{
drData[index4] = list[index4];
}
newTable.Rows.Add(drData);
list.Clear();
}
}
}
else //刚好被4整除；
{
for (int index1 = 0; index1 < newGroup; index1++) //被4整除有余数；
{
for (int index3 = 0; index3 < this.GetFieldKeys().Length; index3++)
{
DataRow drData = newTable.NewRow();
for (int index2 = index1 * 4; index2 < index1 * 4 + 4; index2++)
{
list.Add(dt.Rows[index2][index3].ToString());
}
for (int index4 = 0; index4 < 4; index4++)
{
drData[index4] = list[index4];
}
newTable.Rows.Add(drData);
list.Clear();
}
}
}
return newTable;
}

/// <summary>
/// grid表中数据行转列显示：this.Literal1.Text
/// </summary>

如图：this.Literal1.Text 调试时候以html方式预览：

private void ShowDataTableRecords(DataTable dt)
{

if (dt.Rows.Count > 0) //表示有数据的情况下；
{
StringBuilder sb = new StringBuilder();
sb.Append("<table id='table3' style='width: 100%; font-size: 10px; border: solid 0px; margin: 0px #F2F2F2'><tr><td class='td2'><table style='width:100%; font-size:10px; border:solid 0px;margin:0px #F2F2F2'><tr><td colspan='4' style='font-weight: bold;font-size:11px;border-bottom: 0' align='left'>供应商信息</td></tr>");
int group = dt.Rows.Count / 14;

for(int i=0;i<group;i++)
{
for(int j=i*14;j<i*14+14;j++)
{
if(j%14==0)
{
List<string> list = new List<string>();
for(int k=1;k<4;k++)
{
list.Add(this.GetChkStatus(dt.Rows[j][k].ToString()));;
}
sb.Append("<tr><td style='width:15%;font-weight: bold;word-break:keep-all'>" + dt.Rows[j][0].ToString() + "</td><td style='width:28.3%'>" + list[0] + "</td><td style='width:28.3%'>" + list[1] + "</td><td style='28.4%'>" + list[2] + "</td></tr>");
}
else
{
sb.Append("<tr><td style='width:15%;font-weight: bold;word-break:keep-all'>" + dt.Rows[j][0].ToString() + "</td><td style='width:28.3%'>" + dt.Rows[j][1].ToString() + "</td><td style='width:28.3%'>" + dt.Rows[j][2].ToString() + "</td><td style='28.4%'>" + dt.Rows[j][3].ToString() + "</td></tr>");
}
}
if(i!=group-1)
{
sb.Append("<tr><td colspan='4' style='font-weight: bold; border-bottom: 0; height: 20px;'></td></tr>");
}
else
{
sb.Append("</table></td></tr></table>");
}
}

this.Literal1.Text = sb.ToString();
}
}

/// <summary>
/// 将数字转化成相应checkbox：1表示选中，0表示未选中，""表示没有这个数据；
/// </summary>
/// <param name="type">类型</param>
/// <returns></returns>
private string GetChkStatus(string type)
{
string chk="";
switch(type)
{
case"0":
chk = "<input id='Checkbox1' type='checkbox' disabled='disabled'/>";
break;
case "1":
chk="<input id='Checkbox1' type='checkbox' disabled='disabled' checked='checked'/>";
break;
}
return chk;
}

/// <summary>
/// 将xml中每3条记录添加1条属性列记录，形成新的表：
/// </summary>
/// <param name="doc"></param>
/// <param name="path"></param>
/// <returns></returns>

如图：

private DataTable CreateDataTableRecords(XmlDocument doc, string path)
{
XmlNodeList gridSupplier = doc.SelectNodes(path);
DataTable dt = this.CreateDataTableColumn(1);
if (gridSupplier != null)
{
int group = gridSupplier.Count / 3;
if(gridSupplier.Count %3!=0)
{
if (group == 0)
{
AddTitleRow(dt);
foreach (XmlNode node in gridSupplier)
{
DataRow drData = dt.NewRow();
drData["orgCheckBox"] = xmlHelper.GetSingleNodeValue(node, "orgCheckBox", "");
drData["CompanyName"] = xmlHelper.GetSingleNodeValue(node, "CompanyName", "");
drData["Currency"] = GetCurrency(xmlHelper.GetSingleNodeValue(node, "Currency", ""));
drData["ExchangeRate"] = xmlHelper.GetSingleNodeValue(node, "ExchangeRate", "");
drData["UnitPriceFirstRound"] = xmlHelper.GetSingleNodeValue(node, "UnitPriceFirstRound", "");
drData["UnitPriceFinal"] = xmlHelper.GetSingleNodeValue(node, "UnitPriceFinal", "");
drData["Quantity"] = xmlHelper.GetSingleNodeValue(node, "Quantity", "");
drData["Savings"] = xmlHelper.GetSingleNodeValue(node, "Savings", "");
drData["ProcurementTotal"] = xmlHelper.GetSingleNodeValue(node, "ProcurementTotal", "");
drData["LeadTime"] = xmlHelper.GetSingleNodeValue(node, "LeadTime", "");
drData["TermsTrade"] = xmlHelper.GetSingleNodeValue(node, "TermsTrade", "");
drData["PaymentMethods"] = xmlHelper.GetSingleNodeValue(node, "PaymentMethods", "");
drData["PaymentRemark"] = xmlHelper.GetSingleNodeValue(node, "PaymentRemark", "");
drData["Remark"] = xmlHelper.GetSingleNodeValue(node, "Remark", "");
dt.Rows.Add(drData);
}
}
else
{
for (int index2 = 0; index2 < group; index2++) //表示大于一组的数据，需要列出的组数；
{
AddTitleRow(dt);
for (int index3 = index2 * 3; index3 < index2 * 3 + 3; index3++)
{
DataRow drData = dt.NewRow();

drData["orgCheckBox"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "orgCheckBox", "");
drData["CompanyName"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "CompanyName", "");
drData["Currency"] = GetCurrency(xmlHelper.GetSingleNodeValue(gridSupplier[index3], "Currency", ""));
drData["ExchangeRate"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "ExchangeRate", "");
drData["UnitPriceFirstRound"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "UnitPriceFirstRound", "");
drData["UnitPriceFinal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "UnitPriceFinal", "");
drData["Quantity"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "Quantity", "");
drData["Savings"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "Savings", "");
drData["ProcurementTotal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "ProcurementTotal", "");
drData["LeadTime"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "LeadTime", "");
drData["TermsTrade"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "TermsTrade", "");
drData["PaymentMethods"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "PaymentMethods", "");
drData["PaymentRemark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "PaymentRemark", "");
drData["Remark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index3], "Remark", "");
dt.Rows.Add(drData);
}
}
AddTitleRow(dt);
for (int index4 = group * 3; index4 < gridSupplier.Count; index4 ++) //表示剩余的行数，即转变成相应的列数；
{
DataRow drData = dt.NewRow();

drData["orgCheckBox"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "orgCheckBox", "");
drData["CompanyName"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "CompanyName", "");
drData["Currency"] = GetCurrency(xmlHelper.GetSingleNodeValue(gridSupplier[index4], "Currency", ""));
drData["ExchangeRate"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "ExchangeRate", "");
drData["UnitPriceFirstRound"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "UnitPriceFirstRound", "");
drData["UnitPriceFinal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "UnitPriceFinal", "");
drData["Quantity"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "Quantity", "");
drData["Savings"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "Savings", "");
drData["ProcurementTotal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "ProcurementTotal", "");
drData["LeadTime"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "LeadTime", "");
drData["TermsTrade"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "TermsTrade", "");
drData["PaymentMethods"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "PaymentMethods", "");
drData["PaymentRemark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "PaymentRemark", "");
drData["Remark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index4], "Remark", "");
dt.Rows.Add(drData);
}
}
}
else
{
for (int index5 = 0; index5 < group; index5++) //表示大于一组的数据，需要列出的组数；
{
AddTitleRow(dt);
for (int index6 = index5 * 3; index6 < index5 * 3 + 3; index6++)
{
DataRow drData = dt.NewRow();

drData["orgCheckBox"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "orgCheckBox", "");
drData["CompanyName"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "CompanyName", "");
drData["Currency"] = GetCurrency(xmlHelper.GetSingleNodeValue(gridSupplier[index6], "Currency", ""));
drData["ExchangeRate"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "ExchangeRate", "");
drData["UnitPriceFirstRound"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "UnitPriceFirstRound", "");
drData["UnitPriceFinal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "UnitPriceFinal", "");
drData["Quantity"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "Quantity", "");
drData["Savings"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "Savings", "");
drData["ProcurementTotal"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "ProcurementTotal", "");
drData["LeadTime"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "LeadTime", "");
drData["TermsTrade"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "TermsTrade", "");
drData["PaymentMethods"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "PaymentMethods", "");
drData["PaymentRemark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "PaymentRemark", "");
drData["Remark"] = xmlHelper.GetSingleNodeValue(gridSupplier[index6], "Remark", "");
dt.Rows.Add(drData);
}
}
}
}
return dt;
}

/// <summary>
/// 每3条记录，添加1条属性集记录：
/// </summary>
/// <param name="dt"></param>
private void AddTitleRow(DataTable dt)
{
string[] strTitle=new string[] { "推荐", "公司名", "币种", "汇率", "单价(第一轮)", "单价(最终)", "数量", "节省费用(CNY)", "采购总价(CNY)", "订货提前期(天)", "贸易条款", "支付方式", "支付说明", "备注" };
DataRow drTitle = dt.NewRow();
for (int i = 0; i < strTitle.Length; i++)
{
drTitle[i] = strTitle[i];
}
dt.Rows.Add(drTitle);
}

/// <summary>
/// 创建表数据列字段：
/// </summary>
/// <returns></returns>
private string[] GetFieldKeys()
{
return new string[] { "orgCheckBox", "CompanyName", "Currency", "ExchangeRate", "UnitPriceFirstRound", "UnitPriceFinal", "Quantity", "Savings", "ProcurementTotal", "LeadTime", "TermsTrade", "PaymentMethods", "PaymentRemark", "Remark" };
}

/// <summary>
/// 创建表中的列
/// </summary>
/// <returns></returns>
private DataTable CreateDataTableColumn(int type)
{
DataTable dt = new DataTable();
switch(type)
{
case 1:
foreach (string columnName in this.GetFieldKeys())
{
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = columnName;
dt.Columns.Add(column);
}
break;
case 2:
string[] columns = new string[] {"title","data01","data02","data03"};
foreach(string columnName in columns)
{
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = columnName;
dt.Columns.Add(column);
}
break;
}
return dt;
}

/// <summary>
/// 根据code过去对应的币种；
/// </summary>
/// <param name="resCode">code</param>
/// <returns>币种</returns>
private string GetCurrency(string resCode)
{
DataTable dt = resCmp.GetResource(resCode); ;
return dt.Rows[0]["Res_Name"].ToString();
}
}

调试：